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();
    }  
}

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.