DATEDIFF (in months) in linq

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?

You're looking for SqlMethods.DateDiffMonth.

EDIT: In EF4, use SqlFunctions.DateDiff.

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;

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

I solved this problem in another manner: I calculated the date from code:

theDate = Date.Now.AddMonths(-4)

And in EF change the condition:

tblAccount.[State Change Date] < theDate