CODEWITHSUNDEEP
sql-serverlinq
user169867
user169867

Reputation: 5870

How to write this SQL average query in linq

Here's the SQL I would like run:

DECLARE @StartDt DATETIME, @EndDt DATETIME
SET @StartDt='2009-01-01'
SET @EndDt='2010-06-01'

SELECT  
AVG(DATEDIFF(day, CreatedDt, CompletedDt)) 
AVG(DATEDIFF(day, CreatedDt, ComplianceDt)) 
FROM MyTable
WHERE RequestDt BETWEEN @StartDt AND @EndDt

Can this be expressed in Linq (C#) and have it all run on the database?

Upvotes: 1

Views: 615

Answers (1)

Amy B
Amy B

Reputation: 110221

DateTime startDt = new DateTime(2009, 1, 1);
DateTime endDt = new DateTme(2010, 6, 1);

var query = dc.MyTables
  .Where(x => startDt <= x.RequestDate && x.RequestDt <= endDt)
  .GroupBy(x => 1)  //unsure about this group by
  .Select(g => new
  {
    FirstAvg = g.Average(x =>
      SqlMethods.DateDiffDay(x.CreatedDt, x.CompletedDt)),
    SecondAvg = g.Average(x =>
      SqlMethods.DateDiffDay(x.CreatedDt, x.ComplianceDt))
  });

var row = query.Single();

Upvotes: 3

Related Questions