EF Core needlessly executing both branches of conditional assignment logic

c# entity-framework-core linq

Question

I am experiencing an issue with EF Core where if I assign a property with branching logic, both branches are executed (rather than just the branch where condition == true).

In the first code sample below, I have replaced the conditional I am actually trying to test with one that I know to always be false to verify my question. In my real-world example, the table that MyEntities maps to has no records with an Id as high as 1000.

However, when I call this method, I can see in my output window first the query to get where Id is 1000 (returns no results) and then N SQL queries trying to retrieve the Count at the end of the first branch's LINQ pipeline being executed. If the condition is always false, these queries to get the Count should never execute (the other (int?)null branch should only ever be executed), correct?

I suspect this could possibly be a bug with EF Core or my EF Core Provider (Pomelo MySQL), but I wanted to make sure I didn't misunderstand how EF and/or branching in LINQ works. Both branches should not be executed in a conditional, correct? If you think it might be a bug, how do I know whether it is with EF Core or the provider I am using?

public async Task<IEnumerable<MyEntityDto>> GetMyEntityDtosAsync() =>
    await DbContext.MyEntities
                   .Select(l => new MyEntityDto
                   {
                       Id = l.Id,
                       RuleCount = l.Id == 1000  // known to always be false
                           ? l.MyLinkedEntity
                              .Where(s => s.MyBoolean)
                              .FirstOrDefault()
                              .MyManyToMany
                              .Count
                           : (int?)null
                   });

The actual query I am trying to implement looks more like below to gracefully handle if each MyEntity has no MyLinkedEntitys where MyBoolean == true. This throws exceptions in the cases where there are not any MyLinkedEntitys satisfying those conditions (supposed to be handled by the conditional). Is there a workaround logic I might be able to try?

public async Task<IEnumerable<MyEntityDto>> GetMyEntityDtosAsync() =>
    await DbContext.MyEntities
                   .Select(l => new MyEntityDto
                   {
                       Id = l.Id,
                       RuleCount = l.MyLinkedEntity.Any(s => s.MyBoolean)
                           ? l.MyLinkedEntity
                              .Where(s => s.MyBoolean)
                              .FirstOrDefault()
                              .MyManyToMany
                              .Count
                           : (int?)null
                   });

Update: adding POCOs

public class MyEntity {
    public int Id { get; set; }
    public int MyLinkedEntityId { get; set; }
    public MyLinkedEntity MyLinkedEntity { get; set; }
    ...
}

public class MyLinkedEntity {
    public int Id { get; set; }
    public bool MyBoolean { get; set; }
    ...
}

public class MyManyToMany {
    public int MyLinkedEntityId { get; set; }
    public MyLinkedEntity MyLinkedEntity { get; set; }
    public int MyOtherLinkedEntity { get; set; }
    public MyOtherLinkedEntity MyOtherLinkedEntity { get; set; }
}
1
1
9/20/2018 12:53:44 AM

Accepted Answer

Here's my guess: EF is retrieving all data that could be a part of the final projection then evaluating your conditional logic client-side with the results.

The error you're getting is due to the fact that you're referencing MyLinkedEntity.MyManyToMany.Count with what could be a null reference returned from FirstOrDefault().

Since you haven't posted the POCOs I'm winging it, but work with the set of entities of the type of l.MyLinkedEntity in your current code, group by the principal (the entity you were projecting from) and project ManyToMany.Count before calling FirstOrDefault(). Hopefully this works, I'm not home to test :P

DbContext.MyLinkedEntities
    .GroupBy( mle => mle.MyEntity )
    .Select( g => new
    {
        Id = g.Key.Id,
        RuleCount = g.Where( s => s.MyBoolean )
            .Select( s => ( int? )s.ManyToMany.Count )
            .FirstOrDefault()
    }
1
9/20/2018 12:08:36 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow