DATEDIFF (in months) in linq

Question

select col1,col2,col3 from table1 
where(DATEDIFF(mm, tblAccount.[State Change Date], GETDATE()) <= 4

I want to convert this sql query to LINQ. but I dont know any DateDiff alternative in LINQ. can you please suggest me?

Answer 1

Putting aside your original question for a moment, in your query you use:

where(DATEDIFF(mm, tblAccount.[State Change Date], GETDATE()) <= 4

This query would always cause a full table scan, since you're comparing the result of a function call against a constant. It would be much better if you calculate your date first, then compare your column value against the calculated value, which would allow SQL to use an index to find the results instead of having to evaluate every record in your table.

It looks like you're trying to retrieve anything within the past 4 months, so in your application code, try calculating the date that you can compare against first, and pass that value into your Linq2Entities expression:

DateTime earliestDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-4);
var results = from t in context.table1
              where t.col3 >= earliestDate
              select t;

Answer 2

In EF6, the class to use is DbFunctions. See, for example, DbFunctions.DiffMonths.