CODEWITHSUNDEEP
c#sqllinqentity-frameworklambda
DontFretBrett
DontFretBrett

Reputation: 1165

Entity Framework Lambda Optimization

I have some code querying EF entities and it's taking 1.5-1.7 seconds. Should I not use linq or lambda to produce these calculations? A view maybe? Or if someone can provide a more efficient solution, I'd appreciate it! The big delay happens when I'm referencing CurrentMonthBookedDollars in an MVC view:

string[] IgnoreBDOs = { "", ".", ",", "00", "092", "120", "zz", "zzz", "zzzz", "yyy" };

IQueryable<LOAN> Production
{
    get
    {
        return Db.LOAN.Where(n => IgnoreBDOs.Contains(n.TEAM.BDO.ToLower()) == false
        && n.LOAN_TYPE.Length > 0);
    }
}

public decimal CurrentMonthBookedDollars
{
    get
    {
        return Production
        .Where(p => p.WORKFLOW.NOTE_DATE.HasValue
            && p.WORKFLOW.NOTE_DATE.Value.Year == DateTime.Today.Year
            && p.WORKFLOW.NOTE_DATE.Value.Month == DateTime.Today.Month)
        .Select(p => p.LOAN_AMT ?? 0)
        .DefaultIfEmpty()
        .Sum(amt => amt);
    }
}

*Edit: I also wanted to mention CurrentMonthBookedDollars results in 39,576 reads when I run a profiler on the SQL database.

Upvotes: 3

Views: 490

Answers (1)

Erik Philips
Erik Philips

Reputation: 54618

This is a non-sargable query because of :

    .Where(p => p.WORKFLOW.NOTE_DATE.HasValue
        && p.WORKFLOW.NOTE_DATE.Value.Year == DateTime.Today.Year
        && p.WORKFLOW.NOTE_DATE.Value.Month == DateTime.Today.Month)

instead you should use:

var start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
var end = start.AddMonth(1);

    .Where(p => p.WORKFLOW.NOTE_DATE.HasValue
        && p.WORKFLOW.NOTE_DATE.Value >= start
        && p.WORKFLOW.NOTE_DATE.Value < end)

Depending on how the database was defined (collation type) the following may either be non-sargable as well or not necessary:

Db.LOAN.Where(n => IgnoreBDOs.Contains(n.TEAM.BDO.ToLower()) == false ...

If your collation is Case-Insensitive (the following example is Sql-server) then there is no point using .ToLower():

enter image description here

Upvotes: 6

Related Questions