Linq query with month difference between 2 dates

Question

I have 2 Field FromDate and ToDate in database. I want the result LINQ query for records that have month difference of 2 dates with some dynamic value.

Actually I have one database table as EmploymentHistory with field NameOfCompany as nvarchar(50), ProfileID as bigint, EmoploymentFrom as datetime, EmploymentUpto as datetime, IsCurrentEmployer as bit.

ProfileID is reference key of Profile I want to generate a query for all profiles by experience in month.

if IsCurrentEmployer is true then EmploymentUpto is null.

Solution 1

If you're under .NET 4+ you could try EntityFunctions.DiffMonths utility method e.g.:

var query = query
            .Where(i=> 
                 EntityFunctions.DiffMonths(i.FromDate, i.ToDate) == 2
            );

Solution 2

Asuming you are using entity framework.. u may

Query(q => q.ToDate < DateTime.Today && q.FromDate > DateTime.Today.AddMonths(-2))

Query is any Iqueryable.. could be select, first, firstOrDefault.. of course you can change DateTime.Today to yours params.

Comments

[Flexicoder]

EntityFunctions has now been replaced with DbFunctions