How to use Entity Framework group date not date with time and sum total where condition-CodePudding

I have this table

CREATE TABLE Receipt
(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    idcustom INT NULL,
    idstaff INT NULL,
    payat DATETIME NOT NULL,
    totalINT NOT NULL
)

And I have a SQL query:

SELECT 
    CAST(payat AS date), SUM(total)
FROM
    Receipt
WHERE
    CAST(payat AS date) = '2021-11-13'
GROUP BY 
    CAST(payat AS date)

How to select this query in Entity Framework?

CodePudding user response：

It should be something like this

var result =  dbContext.Receipt
    .Select(p=> new  { 
            payat =  p.payat.Date,
            total =  p.total 
           })
    .GroupBy(p => p.payat )
    .Select(p => new {
        payat = p.Key,
        total = p.Sum(q => q.total)
    }).ToList();

CodePudding user response：

The exact equivalent in EF for this is

var result =
    from r in context.Receipt
    where payat.Date = new DateTime(2013, 11, 13)
    group r by r.payat.Date into g
    select new {
        date = g.Key,
        total = g.Sum(r => r.total),
    };

However, a more efficient solution presents itself (and the same goes for the equivalent SQL)

var result =
    new {
        date = new DateTime(2013, 11, 13),
        total =
           (from r in context.Receipt
            where payat >= new DateTime(2013, 11, 13)
               && payat < new DateTime(2013, 11, 14)
            select r.total
           ).Sum(),
    };

In other words, don't use functions on dates, instead use a start and end date range to compare to. And there is no need to group here because we only have a single date.