2
\$\begingroup\$

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();
    }  
}
\$\endgroup\$
  • \$\begingroup\$ Apparently, you're not yet aware of Dapper's Multi Mapping/Multiple Results capabilities. \$\endgroup\$ – Gert Arnold Jan 31 '18 at 12:55
  • \$\begingroup\$ 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. \$\endgroup\$ – Ismail Yilmaz Jan 31 '18 at 13:09
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Ismail Yilmaz Feb 2 '18 at 17:15
  • \$\begingroup\$ 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. \$\endgroup\$ – Gert Arnold Feb 3 '18 at 10:07
  • \$\begingroup\$ i think i got you. your comments and answer were really full of very valuable information. honestly i couldn’t get the answer i was waiting for. however after your comments and answer it’s enough clear for me that the way i try to handle navigation properties with dapper is wrong or not proper! so it means there is no correct answer for any wrong/not proper question. that’s why i can’t mark your answer as correct. again thank you so much! \$\endgroup\$ – Ismail Yilmaz Feb 3 '18 at 17:11
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Gert Arnold Feb 4 '18 at 10:29
  • 2
    \$\begingroup\$ @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. \$\endgroup\$ – t3chb0t Oct 30 '18 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.