如何在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`

但結果符合我的預期：

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() 來獲取必填欄位，這使得查詢變得復雜。你能告訴我如何在上面進行查詢。謝謝

uj5u.com熱心網友回復：

您必須將缺少的欄位添加到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 
    };

標籤：mysql林克实体框架核心

上一篇：如何在EF6中獲取包含另一個物件串列的物件串列

下一篇：如何獲取另一個表中沒有特定記錄的記錄