Linq static method error

Question

I have created following function to get dates difference:

    public static double MonthsDifference(DateTime dtStart, DateTime dtNow)
    {
        DateTime start = dtStart;
        DateTime end = dtNow;
        int compMonth = (end.Month + end.Year * 12) - (start.Month + start.Year * 12);
        double daysInEndMonth = (end - end.AddMonths(1)).Days;
        double months = compMonth + (start.Day - end.Day) / daysInEndMonth;
        return months;
    }

I am using it in my LINQ query

var query = from c in context.AccountOwners.Where( MonthsDifference(p.Account.StateChangeDate,DateTime.Now) < 4 )
                        select c;
            return query.Count();

but it is giving error:

LINQ to Entities does not recognize the method 'Double MonthsDifference(System.DateTime, System.DateTime)' method, and this method cannot be translated into a store expression.

Please suggest solution

firstly. you can create and use functions in a linq to entities call. however, from my findings it only works if the method is declared in the same class you are trying to use it. — Michael, May 24 '14 at 01:39
second. use the built in entity functions for date/time functions. — Michael, May 24 '14 at 01:39

score 1 · Answer 1 · answered Apr 01 '11 at 16:45

The MonthsDifference function cannot be mapped to SQL, which is why you are getting this error. You need to either rewrite the query expression without any calls to your own methods to do what you want (which may be impossible -- I don't know exactly which native database functions LINQ to Sql supports), or fetch the result set and do the filtering locally.

The latter approach would go like this:

var count = context.AccountOwners.ToArray()
       .Count(o => MonthsDifference(p.Account.StateChangeDate,DateTime.Now) < 4);

score 1 · Answer 2 · edited May 23 '17 at 12:19

If you want to do this in the Linq then you need to inline this method so that Linq2Sql can translate it into sql.

So I think you'll need something like:

var start = DateTime.Now;
var query = from c in context.AccountOwners
            let accountDate = c.Account.StateChangeDate
            let diff = (start.Month + start.Year * 12) - (accountDate.Month + accountDate.Year * 12) + ...
            where diff < 4
            select c;

return query.Count();

Linked to Months difference between dates

score 1 · Answer 3 · answered Apr 01 '11 at 17:28

In LINQ to Entities you can use Entity functions:

using System.Data.Objects;

var query = from c in context.AccountOwners.Where(EntityFunctions.DiffMonths(
                           p.Account.StateChangeDate,DateTime.Now) < 4 )
            select c;
return query.Count();

Linq static method error

3 Answers3