Navigation properties with sql/dapper

Question

I want to have the same experiance with slq/dapper for my navigation properties just like in Entity Framework. But I'm not sure which sql query i should use. As you see, every single Resource entity calls a "select query" for retrieving it's related Language. Does it effect the performance? Maybe i should use single "join query"?

public class Resource {
 public int Id { get; set; }
 public string Name { get; set; }
 public int LanguageId { get; set; }
 public Language Language = {
 return connection.Query<Language>("select * from Language where Id = @Id", new { Id = LanguageId }).First();
 }
}
public class Language {
 public intId { get; set; }
 public string Name { get; set; }
 public List<Resource> Resources => {
 return connection.Query<Resource>("select * from Resource where LanguageId = @LanguageId", new { LanguageId = Id }).ToList();
 } 
}

• Gert Arnold: Apparently, you're not yet aware of Dapper's Multi Mapping/Multiple Results capabilities.
• Ismail Yilmaz: Thanks for your comment i'm really appreciated! I'm already aware of dapper's that feature but my question is specifically addressing to sql query(one join or many select query), sorry if my question was not clear.

Answer 1

Your entities have a dependency to a connection object that's somehow available to them. If this is one instance you may experience technical impediments when reading entities and their nested entities in one statement. But that's not the most important thing.

The most important objection is that these "active" navigation properties will always give rise to the n + 1 problem: for each item you pull from the database by 1 query, you will trigger n queries to get their related data. That will certainly affect performance and it depends on the amount of data whether that's serious.

This effect is aggravated by the fact that the data aren't stored into the parent entities: each time a navigation property is accessed the query is executed.

There are more things to consider when it comes to reproducing Entity-Framework's (EF) behavior regarding navigation properties. EF loads entities into a context, which implements Identity Map: i.e. each database record will be represented by exactly one C# object. The benefits of this are hard to reproduce:

1. If one entity is modified, the changes are reflected wherever the entity is referenced. Also, changes are unambiguous: there are no "equal" entities around having old values. (You're probably getting read-only data, so this may not affect you).

2. Ability to perform operations that are based on referential equality. For example, in your case, grouping Resources by Language will produce groups having 1 item, because the language object aren't equal (sure, can be evaded by using LanguageId, but still, it's a gotcha).

3. EF can perform relationship fixup when it loads entities: loading Resources and Languages separately into one context will auto-populate the navigation properties. Your code won't ever do that.

Side note: Dapper's Multi Mapping Feature won't alleviate the first two points because it also creates separate object instances for "equal" entities.

So all in all, I wouldn't do this. I would get the data separately and perform some sort of "relationship fixup" manually.

• Ismail Yilmaz: thanks for your effort to my question! but i still couldn’t get my answer as solution but you made many things clear in my mind! Could you please add some sample code to demonstrate what do you mean by saying "So all in all, ... "relationships fixup" manually". I thought like you have your own way as solution. Please share your way for navigation properties by using dapper.
• Gert Arnold: Oh I mean nothing fancy, just loop through the objects and set Resource.Language and Language.Resources. I don't work with Dapper that way routinely, I only use it for single read-only queries.
• Gert Arnold: Sure, that's all up to you. I don't think you asked a "wrong/not proper question" though, if that's what you mean. Nor can a code review be plainly correct/wrong, at not least not as clear-cut a a Stack Overflow answers can be. It should be judged in terms of usefulness.
• t3chb0t: @IsmailYilmaz I find it's rude to not at least upvote an answer if it was hepful to you and as Gert Arnold said, there is usually no right or wrong or Code Review but most useful/helpful answer and if this is true here then you should mark it.
• Ismail Yilmaz: Being rude? Dear @t3chb0t , users who have reputation less than 15 already can not upvote an answer, that's why i couldn't do it. His answer was really so informative, i learned a lot from it but i think it was not the answer to my question which solves my problem because it's so clear that what i want to do is definetally not possible. So what should i do? Please teach me how to mark my question as not-possible? I'm asking because i'm not so familiar with this site and i didn't know that not to know something makes anyone rude...