CODEWITHSUNDEEP
sql-serverlinqentity-framework-corefluent
Ashwin
Ashwin

Reputation: 1230

SQL query to LINQ expression - Entity Core Framework 3 + SQL Server

DECLARE @thirtyDaysAgo DATETIME = getdate()-30

SELECT 
sum(case when CreatedWhen >= @thirtyDaysAgo then 1 else 0 end) lessThan30,
sum(case when CreatedWhen < @thirtyDaysAgo then 1 else 0 end) Greaterthan30
FROM ThisAwesomeTable

Cannot figure out how to convert this to a fluent query.

The intent is to count the number of rows that were created more than 30 days ago and also count the number of rows that were created within the last 30 days in the same query.

Upvotes: 0

Views: 51

Answers (1)

NetMage
NetMage

Reputation: 26917

You need to use the singleton GroupBy trick:

var ans = ThisAwesomeTable.GroupBy(r => 1)
                          .Select(rg => new {
                              lessThan30 = rg.Sum(r => r.CreatedWhen >= thirtyDaysAgo ? 1 : 0),
                              GreaterThan30 = rg.Sum(r => r.CreatedWhen < thirtyDaysAgo ? 1 : 0)
                          });

Note that EF Core 3 generates exactly the same SQL as listed for this query.

Upvotes: 2

Related Questions