McFixit McFixit - 1 year ago 81
C# Question

Can someone explain to me how this LINQ/Entity Framework query actually works?

I'm pretty new to Entity Framework, and today I've been looking for a way to have a condition on the navigation properties I include. Basically all of my entities are 'flagged' as deleted in the database rather than being actually deleted, so I wanted my WebAPI to return all of the 'parent' objects that aren't flagged as deleted, but also only include their nested navigation properties that haven't been deleted as well.

The solution I keep finding, which does work, is this (after temporarily disabling lazy loading for my context):

var projectQuery = await db.Projects.Where(x => !x.IsDeleted).Select(x => new
Project = x,
Id = x.Id,
Name = x.Name,
Description = x.Description,
Owner = x.Owner,
Sponsor = x.Sponsor,
DateCreated = x.DateCreated,
DateOpened = x.DateOpened,
DateClosed = x.DateClosed,
IsDeleted = x.IsDeleted,
CreatedBy = x.CreatedBy,
Expenses = x.Expenses.Where(y => !y.IsDeleted),
TimeEntries = x.TimeEntries.Where(y => !y.IsDeleted)

results = projectQuery.Select(x => Mapper.Map<ProjectDto>(x.Project)).ToList();

Obviously I can follow the general idea of what's going on, i.e. throwing the results into anonymous objects so that I'm able to specify a Where condition on the child collections (Expenses and TimeEntries). My confusion comes from my need of getting the objects back to their original type... the original object itself is included as a property of each anonymous object I'm creating, and is then used as the 'final' object which I can map back to my DTOs or whatever else I need.

What black magic is this? How the heck does this object reference seemingly know to use the properties I've defined in the anonymous object as its own properties? I really would've expected the Project property to be exactly the same as the Project I'm getting from my DbContext and was shocked to find out that it does in fact work. I found this as a solution to my problem from several sources, none of which actually provided some insight as to what's going on here

Answer Source

EF is using Expression Trees to parse your queries. By passing in the original entity it can see what is being assigned where on the new object graph and then use that information to build an equivalent database query. When the query is executed it is also projected into the object structure you defined.

The mapping and projection is why only a limited set of .Net functions may be ran within the EF query

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download