LINQ子查询未按指定字段选择，EF错误查询全部列问题

问题描述 #

编写的LINQ查询中，子查询仅指定选择Customer表的Name和shelfId字段，但EF生成的SQL却查询了该表的全部列：

原LINQ查询 #

var query =
       from store in _dbContext.Stores.AsNoTracking()
       join shelf in _dbContext.Shelves.AsNoTracking()
            on store.Id equals shelf.StoreId into storeShelf
       join customers in 
        (
            from customer in _dbContext.Customers.AsNoTracking()
            where customer.IsActive
            select new { customer.Name, customer.shelfId }
        ) on storeShelf.Id equals customers.shelfId into shelfCustomer
       from shelfCustomer2 in shelfCustomer.DefaultIfEmpty()
       select new CompleteModel
       {
           StoreName = store.Name,
           CustomerName = shelfCustomer2.Name
       };

预期SQL子查询 #

...
SELECT c.Name, c.Age
FROM [dbo].[Customer]
WHERE c.IsActive = 1
...

实际生成的SQL子查询 #

...
SELECT c.Id, c.Name, c.Surname, c.Age, c.AddressId, ...
FROM [dbo].[Customer]
WHERE c.IsActive = 1
...

问题原因 #

1. 分组join写法错误：原查询中直接使用分组集合storeShelf.Id是无效的（storeShelf是IGrouping类型，无Id属性），这会导致EF的查询解析逻辑混乱，无法正确识别子查询的投影需求。
2. 部分旧版本EF（如EF Core 3.x之前）的查询优化存在缺陷，无法正确推导子查询的必要字段。

解决方案 #

1. 修正分组join的写法 #

先展开分组后的shelf集合，再进行后续join操作，确保EF能正确解析字段关联：

var query =
    from store in _dbContext.Stores.AsNoTracking()
    join shelf in _dbContext.Shelves.AsNoTracking()
         on store.Id equals shelf.StoreId into storeShelf
    // 展开分组集合，处理左连接逻辑
    from shelf in storeShelf.DefaultIfEmpty()
    join customers in 
     (
         from customer in _dbContext.Customers.AsNoTracking()
         where customer.IsActive
         select new { customer.Name, customer.ShelfId }
     ) on shelf.Id equals customers.ShelfId into shelfCustomer
    from shelfCustomer2 in shelfCustomer.DefaultIfEmpty()
    select new CompleteModel
    {
        StoreName = store.Name,
        CustomerName = shelfCustomer2.Name
    };

2. 显式定义子查询变量 #

将子查询提前抽离为单独变量，让EF更清晰地识别需要投影的字段：

// 提前定义子查询，明确投影字段
var activeCustomers = _dbContext.Customers.AsNoTracking()
    .Where(c => c.IsActive)
    .Select(c => new { c.Name, c.ShelfId });

var query =
    from store in _dbContext.Stores.AsNoTracking()
    join shelf in _dbContext.Shelves.AsNoTracking()
         on store.Id equals shelf.StoreId into storeShelf
    from shelf in storeShelf.DefaultIfEmpty()
    join customer in activeCustomers
         on shelf.Id equals customer.ShelfId into shelfCustomer
    from shelfCustomer2 in shelfCustomer.DefaultIfEmpty()
    select new CompleteModel
    {
        StoreName = store.Name,
        CustomerName = shelfCustomer2.Name
    };

3. 升级EF版本 #

如果使用的是EF Core 3.x及更早版本，建议升级到EF Core 6.x或更高版本，新版本修复了大量SQL翻译的优化问题，能更准确地处理这类投影查询。

验证效果 #

修正后，EF生成的子查询会正确仅选择指定字段，SQL输出符合预期。

内容的提问来源于stack exchange，提问作者cinosz