CODEWITHSUNDEEP
sql-serverentity-frameworkentity-framework-core
DrGriff
DrGriff

Reputation: 4916

Entity Framework Core 3 - can't get query to work

I am trying to construct a query using EF Core 3.1 across multiple tables, and it's simply not working.

I'll try to explain with some dummy examples.

Assume my SQL tables all have the following defined within the SQL Server DB:

  1. Primary Keys (composite if needed)
  2. Foreign Keys

The dummy Entities are as follows:

Foreign Key relationships:

The Entities all have their Navigation Properties set, and I've set up the Primary and Foreign keys in the DB Context.

The vast majority of these tables have a bit field "Enabled", so rows can be disabled without deleting them

So the query I'm trying to write is similar to the following:

var data = await context.Town.AsNoTracking()
    .Where(t => t.TownName == request.TownName)
    .Include(t => t.Inhabitants.Where(i => i.Name == request.InhabitantName && i.Enabled)
    .ThenInclude(i => i.InhabitantCar.Where(ic => ic.Enabled))
    .ThenInclude(ic => ic.Cars.Where(c => c.Enabled))
    .ThenInclude(c => c.Manufacturer.Where(m => m.Enabled))

    .Include(t => t.Inhabitants.Where(i => i.Name == request.InhabitantName && i.Enabled)
    .ThenInclude(i => i.InhabitantCar.Where(ic => ic.Enabled))
    .ThenInclude(ic => ic.Cars.Where(c => c.Enabled))
    .ThenInclude(c => c.Mechanic.Where(m => m.Enabled && m.Name == request.AllowedMechanic))

    .ToListAsync().ConfigureAwait(false);

So to summarise, I want to know what cars are being driven by a "John Smith" who lives in "London" that are serviced by "MechanicsAreUs".

This seems quite long-winded to me, and that may be where my problem lies.

Anyhow, quite a few of the .WHERE clauses on the latter ThenIncludes just won't compile. Removing them one-by-one until it compiles gives me:

var data = await context.Town.AsNoTracking()
    .Where(t => t.TownName == request.TownName)
    .Include(t => t.Inhabitants.Where(i => i.Name == request.InhabitantName && i.Enabled)
    .ThenInclude(i => i.InhabitantCar.Where(ic => ic.Enabled))
    .ThenInclude(ic => ic.Cars)
    .ThenInclude(c => c.Manufacturer)

    .Include(t => t.Inhabitants.Where(i => i.Name == request.InhabitantName && i.Enabled)
    .ThenInclude(i => i.InhabitantCar.Where(ic => ic.Enabled))
    .ThenInclude(ic => ic.Cars)
    .ThenInclude(c => c.Mechanic)

    .ToListAsync().ConfigureAwait(false);

So, as written, it's going to bring back disabled entries, and I'm not specifying the mechanic. However, when I run it, I get the exception:

System.InvalidOperationException: Lambda expression used inside Include is not valid.

I've spent ages going through the various Microsoft Examples, but I'm not finding any examples that seems to be of this level of complexity. It's only a handful of inner-joins. Something that could be accomplished in a stored procedure within minutes. Just that I want to do this using Entity Framework.

Upvotes: 0

Views: 1300

Answers (3)

DrGriff
DrGriff

Reputation: 4916

One recommendation was to use Query Filters.

The idea behind this was great - in my DB Context file I could add a common set of filters, e.g.

builder.Entity<Town>()
    .HasQueryFilter(a => a.Enabled);
builder.Entity<Car>()
    .HasQueryFilter(a => a.Enabled);
builder.Entity<Manufacturer>()
    .HasQueryFilter(a => a.Enabled);

And that would be included in every query generated by my Service file - the Developers need not care.

However, when I analysed the resultant SQL, I found that my code was littered with multiple sub-queries, e.g.

Inner Join (Select...Where ...Enabled = 1)

On removing these centralized Query Filters and adding this to my WHERE clause in the LINQ statement resulted in a far more efficient query.

Upvotes: 1

Steve Py
Steve Py

Reputation: 34978

Entities reflect the data state. You cannot filter what related data you want, it's all or nothing. A town doesn't just have "enabled" inhabitants, it has inhabitants, just some of them are enabled and others aren't. That you don't want to view disabled ones or irrelevant ones, that is a view's concern, not the data model.

You can use Select to populate a model structure that is suited to your view. This can flatten out the joining tables, and load just the enabled records you want to see, plus streamline the fields your view needs rather than exposing everything about your domain. You can leverage AutoMapper to fill in the view models /w ProjectTo.

Upvotes: 0

Moho
Moho

Reputation: 16553

You cannot filter .Include(...) eager loads - it's all or nothing. As David Browne stated in a comment to your question, you should use query filters if you want to exclude records based on their Enabled flags, e.g.:

modelBuilder.Entity<Car>()
    .HasQueryFilter(c => c.Enabled);

I seems you are interested in Car entities so let's restructure the query make that the focus:

var query = context.Cars;

You want cars associated with an Inhabitant with a specific name that is associated with a specific Town but is also serviced by a specific Mechanic, so let's filter by that criteria:

query = query.Where( c => 
    c.InhabitantCar.Inhabitant.Name == request.InhabitantName
    && c.InhabitantCar.Inhabitant.Town.TownName == request.TownName
    && c.Mechanic == request.AllowedMechanic );

This query will now return the Car entities you desire, so let's now configure the eager loads:

query = query.Include( c => c.Manufacturer )
    .Include( c => c.Mechanic )
    .Include( c => c.InhabitantCar )
        .ThenInclude( ic => ic.Inhabitant )
            .ThenInclude( i => i.Town );

Give that a shot.

Upvotes: 2

Related Questions