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

entity-framework-core fluent linq sql-server

Question

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.

1
0
2/13/2020 7:03:59 PM

Accepted Answer

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.

2
2/14/2020 6:10:55 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow