如何在 LINQ 中使用"group by"选择多个字段

Question

我已经编写了这个查询

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`

但是结果我所期望的：

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((来获得所需的字段，这使得查询变得复杂。你能告诉我如何在上面提到这个问题吗。感谢

Answer 1

您必须将丢失的字段添加到GroupBy

var query =
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.Date, table1.TransactionNumber, table1.CustomerName } into g
select new 
{ 
Date = g.Key.Date, 
INV = g.Key.TransactionNumber, 
total = g.Count(), 
cus = g.Key.CustomerName 
};