如何在LINQ中使用Group By选择多字段并生成简洁SQL查询

我编写了如下LINQ查询：

from table1 in _dbContext.INVHeaders
where table1.Date >= From.Date && table1.Date <= To.Date && table1.CompanyCode == Branch
join table2 in (from a in _dbContext.INVHeaders 
                select new { MobileNumber = a.MobileNumber })
on table1.MobileNumber equals table2.MobileNumber
group table1 by new { table1.MobileNumber, table1.TransactionNumber } into result
select new { Date = result.FirstOrDefault().Date, INV = result.FirstOrDefault().TransactionNumber, total = result.Count(), cus = result.FirstOrDefault().CustomerName };

该查询生成的SQL包含大量嵌套子查询，结构复杂：

SELECT (
    SELECT `i1`.`Date`
    FROM `INVHeaders` AS `i1`
    INNER JOIN `INVHeaders` AS `i2` ON `i1`.`MobileNumber` = `i2`.`MobileNumber`
    WHERE (((`i1`.`Date` >= @__From_Date_0) AND (`i1`.`Date` <= @__To_Date_1)) AND (`i1`.`CompanyCode` = @__Branch_2)) AND (((`i`.`MobileNumber` = `i1`.`MobileNumber`) OR (`i`.`MobileNumber` IS NULL AND (`i1`.`MobileNumber` IS NULL))) AND ((`i`.`TransactionNumber`
= `i1`.`TransactionNumber`) OR (`i`.`TransactionNumber` IS NULL AND (`i1`.`TransactionNumber` IS NULL))))
    LIMIT 1) AS `Date`, (
    SELECT `i3`.`TransactionNumber`
    FROM `INVHeaders` AS `i3`
    INNER JOIN `INVHeaders` AS `i4` ON `i3`.`MobileNumber` = `i4`.`MobileNumber`
    WHERE (((`i3`.`Date` >= @__From_Date_0) AND (`i3`.`Date` <= @__To_Date_1)) AND (`i3`.`CompanyCode` = @__Branch_2)) AND (((`i`.`MobileNumber` = `i3`.`MobileNumber`) OR (`i`.`MobileNumber` IS NULL AND (`i3`.`MobileNumber` IS NULL))) AND ((`i`.`TransactionNumber`
= `i3`.`TransactionNumber`) OR (`i`.`TransactionNumber` IS NULL AND (`i3`.`TransactionNumber` IS NULL))))
    LIMIT 1) AS `INV`, (
    SELECT `i5`.`Total`
    FROM `INVHeaders` AS `i5`
    INNER JOIN `INVHeaders` AS `i6` ON `i5`.`MobileNumber` = `i6`.`MobileNumber`
    WHERE (((`i5`.`Date` >= @__From_Date_0) AND (`i5`.`Date` <= @__To_Date_1)) AND (`i5`.`CompanyCode` = @__Branch_2)) AND (((`i`.`MobileNumber` = `i5`.`MobileNumber`) OR (`i`.`MobileNumber` IS NULL AND (`i5`.`MobileNumber` IS NULL))) AND ((`i`.`TransactionNumber`
= `i5`.`TransactionNumber`) OR (`i`.`TransactionNumber` IS NULL AND (`i5`.`TransactionNumber` IS NULL))))
    LIMIT 1) AS `amount`, COUNT(*) AS `total`, (
    SELECT `i7`.`CustomerName`
    FROM `INVHeaders` AS `i7`
    INNER JOIN `INVHeaders` AS `i8` ON `i7`.`MobileNumber` = `i8`.`MobileNumber`
    WHERE (((`i7`.`Date` >= @__From_Date_0) AND (`i7`.`Date` <= @__To_Date_1)) AND (`i7`.`CompanyCode` = @__Branch_2)) AND (((`i`.`MobileNumber` = `i7`.`MobileNumber`) OR (`i`.`MobileNumber` IS NULL AND (`i7`.`MobileNumber` IS NULL))) AND ((`i`.`TransactionNumber`
= `i7`.`TransactionNumber`) OR (`i`.`TransactionNumber` IS NULL AND (`i7`.`TransactionNumber` IS NULL))))
    LIMIT 1) AS `cus` FROM `INVHeaders` AS `i` INNER JOIN `INVHeaders` AS `i0` ON `i`.`MobileNumber` = `i0`.`MobileNumber` WHERE ((`i`.`Date`
>= @__From_Date_0) AND (`i`.`Date` <= @__To_Date_1)) AND (`i`.`CompanyCode` = @__Branch_2) GROUP BY `i`.`MobileNumber`, `i`.`TransactionNumber`

而我期望生成的SQL是简洁的结构，直接在SELECT中获取字段并统计：

SELECT COUNT(*), `i`.`Date`, `i`.`CustomerName`, `i`.`MobileNumber`, `i`.`Total`  AS `total`
FROM `INVHeaders` AS `i`
INNER JOIN `INVHeaders` AS `i0` ON `i`.`MobileNumber` = `i0`.`MobileNumber`
WHERE ((`i`.`Date` >= '2022-07-01') AND (`i`.`Date` <= '2022-07-01')) AND (`i`.`CompanyCode` = '001')
GROUP BY `i`.`MobileNumber`, `i`.`TransactionNumber`;

问题在于我需要用FirstOrDefault()获取字段，导致查询复杂，请问如何修改LINQ查询以实现预期效果？

问题核心是使用FirstOrDefault()从分组结果中取字段时，EF无法识别这些字段属于分组逻辑内的一致值，只能生成嵌套子查询逐个获取。要生成简洁SQL，需利用同一分组内目标字段值一致的特性（按MobileNumber和TransactionNumber分组后，同组的Date、CustomerName等字段值应无差异），通过以下两种方式优化：

方式一：将需要的字段加入分组键 #

如果确定同组内Date、CustomerName等字段值完全一致，直接把这些字段加入分组对象，就能在select中直接引用分组键的属性：

from table1 in _dbContext.INVHeaders
where table1.Date >= From.Date && table1.Date <= To.Date && table1.CompanyCode == Branch
join table2 in _dbContext.INVHeaders.Select(a => new { a.MobileNumber })
on table1.MobileNumber equals table2.MobileNumber
group table1 by new { 
    table1.MobileNumber, 
    table1.TransactionNumber, 
    table1.Date, 
    table1.CustomerName,
    table1.Total
} into result
select new { 
    Date = result.Key.Date, 
    INV = result.Key.TransactionNumber, 
    total = result.Count(), 
    cus = result.Key.CustomerName,
    amount = result.Key.Total
};

方式二：使用聚合函数获取字段 #

若不想修改分组键，可使用Min或Max这类聚合函数（因同组字段值相同，聚合结果等于字段本身），EF会将其转换为SQL中的直接字段引用：

from table1 in _dbContext.INVHeaders
where table1.Date >= From.Date && table1.Date <= To.Date && table1.CompanyCode == Branch
join table2 in _dbContext.INVHeaders.Select(a => new { a.MobileNumber })
on table1.MobileNumber equals table2.MobileNumber
group table1 by new { table1.MobileNumber, table1.TransactionNumber } into result
select new { 
    Date = result.Min(x => x.Date), 
    INV = result.Key.TransactionNumber, 
    total = result.Count(), 
    cus = result.Min(x => x.CustomerName),
    amount = result.Min(x => x.Total)
};

两种方式都会生成你期望的简洁SQL，避免嵌套子查询。另外原查询中的join可简化为_dbContext.INVHeaders.Select(a => new { a.MobileNumber })，逻辑不变但写法更简洁。

内容的提问来源于stack exchange，提问作者Åfsħąl Ãtħąr