CODEWITHSUNDEEP
linqlinq-to-sql
DotnetSparrow
DotnetSparrow

Reputation: 27996

Linq static method error

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

Upvotes: 0

Views: 1505

Answers (3)

Slauma
Slauma

Reputation: 177163

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();

Upvotes: 1

Stuart
Stuart

Reputation: 66882

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

Upvotes: 1

Jon
Jon

Reputation: 437584

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);

Upvotes: 1

Related Questions