Entity Framework: Left Join with List Result

entity-framework entity-framework-core linq

Question

I'm trying to optimize my EF queries. I have an entity called Employee. Each employee has a list of tools. Ultimately, I'm trying to get a list of employees with their tools that are NOT broken. When running my query, I can see that TWO calls are made to the server: one for the employee entities and one for the tool list. Again, I'm trying to optimize the query, so the server is hit for a query only once. How can I do this?

I've been exploring with LINQ's join and how to create a LEFT JOIN, but the query is still not optimized.

In my first code block here, the result is what I want, but -- again -- there are two hits to the server.

public class Employee
{
    public int EmployeeId { get; set; }
    public List<Tool> Tools { get; set; } = new List<Tool>();
    ...
}

public class Tool
{
    public int ToolId { get; set; }
    public bool IsBroken { get; set; } = false;

    public Employee Employee { get; set; }
    public int EmployeeId { get; set; }
    ...
}
var x = (from e in db.Employees.Include(e => e.Tools)
         select new Employee()
         {
             EmployeeId = e.EmployeeId,
             Tools = e.Tools.Where(t => !t.IsBroken).ToList()
         }).ToList();

This second code block pseudoly mimics what I'm trying to accomplish. However, the GroupBy(...) is being evaluated locally on the client machine.

(from e in db.Employees
 join t in db.Tools.GroupBy(tool => tool.EmployeeId) on e.EmployeeId equals t.Key into empTool
 from et in empTool.DefaultIfEmpty()
 select new Employee()
 {
    EmployeeId = e.EmployeeId,
    Tools = et != null ? et.Where(t => !t.IsBroken).ToList() : null
 }).ToList();

Is there anyway that I can make ONE call to the server as well as not having my GroupBy() evaluate locally and have it return a list of employees with a filtered tool list with tools that are not broken? Thank you.

1
4
6/15/2019 1:18:19 AM

Popular Answer

Shortly, it's not possible (and I don't think it ever will be).

If you really want to control the exact server calls, EF Core is simply not for you. While EF Core still has issues with some LINQ query translation which leads to N+1 query or client evaluation, one thing is by design: unlike EF6 which uses single huge union SQL query for producing the result, EF Core uses one SQL query for the main result set plus one SQL query per each correlated result set.

This is sort of explained in the How Queries Work EF Core documentation section:

  1. The LINQ query is processed by Entity Framework Core to build a representation that is ready to be processed by the database provider
    • The result is cached so that this processing does not need to be done every time the query is executed
  2. The result is passed to the database provider
    • The database provider identifies which parts of the query can be evaluated in the database
    • These parts of the query are translated to database specific query language (for example, SQL for a relational database)
    • One or more queries are sent to the database and the result set returned (results are values from the database, not entity instances)

Note the word more in the last bullet.

In your case, you have 1 main result set (Employee) + 1 correlated result set (Tool), hence the expected server queries are TWO (except if the first query returns empty set).

2
6/15/2019 9:11:13 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