How to select multiple fields using "group by" in LINQ-CodePudding

I've write this 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.TransactionNumber } into result
select new { Date = result.FirstOrDefault().Date, INV = result.FirstOrDefault().TransactionNumber, total = result.Count(), cus = result.FirstOrDefault().CustomerName };

and the sql query result of this:

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`

But the result what I expected:

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`;

The problem is that when I select columns, I need to use FirstOrDefault() to get required field which makes query complex. Can you please tell how I can make query mention above. Thanks

CodePudding user response：

You have to add missing fields to 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 
    };