Entity Framework Core Query Generation

Question

Related Topic: Entity framework performance tuning

Does Entity Framework Core always generate query with best performance? Does any body have a Counter-Example (EF query has less performance vs raw SQL query (EF generate a SQL query that we can write it in better way using raw SQL))?

Thanks

Answer 1

OK, one case maybe really stands out. It is that EF always generates outer joins when Include()-ing a collection. If we have prior knowledge that parents without children don't exist (or we don't need them) there's no way to instruct EF to generate an inner join for the Include, while there may be considerable performance differences between outer (worst) and inner joins (best).

To me, this was annoying enough to make me coin an IncludeInner method, which was rejected, unfortunately (and, fair enough, I didn't submit a pull request).

From an EF perspective it makes sense to generate outer joins. After all, a query like...

context.Parents.Include(p => p.Children)

...is expected to return all parents, not only the ones with children.

So now if we're interested in only parents with children we have to revert to a query like:

context.Parents.Include(p => p.Children)
    .Where(p => p.Children.Any()

We now have an OUTER JOIN and an EXISTS predicate that both access the Children table. If performance is really critical we can't but write SQL with an inner join ourselves, or write LINQ with a join statement, not using navigation proeprties.

In EF's defense, it should be noted that query generation in general has improved immensely in EF-core, as compared to its early versions and esp. Entity Framework 6 (for .net framework). While EF6 was lamentable, I dare say that EF-core-6 is one of the best now. (Although GroupBy support remains an issue).