Subtract two dates with 'let' variable then average()?

Question

Attempting to subtract two dates from one another to figure out the number of days, then execute .Average() on the 'let' variable avgConversion.

I encounter the following error; LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)' method, and this method cannot be translated into a store expression.

var averageConversion =
(
    from r in db.Registrations
    where
        (bu == "All" || r.BusinessUnit.Equals(bu)) &&
        (region == "All" || r.Region.Equals(region)) &&
        (startDate == null || r.StartDate >= startDate) &&
        (endDate == null || r.EndDate <= endDate) &&
        !r.RegistrationStatus.Equals("Cancelled") &&
        !r.Status.Equals("Cancelled")
    let avgConversion = r.StartDate.Value.Subtract(r.RegistrationDate.Value).Days
    select avgConversion
).Average();

Thanks to Enigma, this is what ended up solving the problem.

var dates = (from r in db.Registrations
                                 where
                                      (bu == "All" || r.BusinessUnit.Equals(bu)) &&
                                      (region == "All" || r.Region.Equals(region)) &&
                                      (startDate == null || r.StartDate >= startDate) &&
                                      (endDate == null || r.EndDate <= endDate) &&
                                      !r.RegistrationStatus.Equals("Cancelled") &&
                                      !r.Status.Equals("Cancelled")
                                 select new
                                 {
                                     r.RegistrationDate,
                                     r.StartDate
                                 }).ToList();

        var avgConversion = (from d in dates
                             let AvgConversion = d.StartDate.Value.Subtract(d.RegistrationDate.Value).Days
                             select AvgConversion).Average();

1

What's your question? – Enigmativity Oct 22 '15 at 01:12

score 1 · Accepted Answer · answered Oct 22 '15 at 01:20

Remember that Entity Framework translates a subset of all possible LINQ statements into SQL. If you use methods or functions that can't be translated the you get the "method cannot be translated into a store expression" error.

Instead you should pull your data into memory before doing the calculation so that you can run the full LINQ expression.

Like this:

var averageConversion =
(
    from r in db.Registrations
    where
        (bu == "All" || r.BusinessUnit.Equals(bu)) &&
        (region == "All" || r.Region.Equals(region)) &&
        (startDate == null || r.StartDate >= startDate) &&
        (endDate == null || r.EndDate <= endDate) &&
        !r.RegistrationStatus.Equals("Cancelled") &&
        !r.Status.Equals("Cancelled")
    select new
    {
        r.StartDate,
        r.RegistrationDate
    }
)
    .ToArray()
    .Select(r => r.StartDate.Value.Subtract(r.RegistrationDate.Value).Days)
    .Average();

Thank you, I followed your logic but broke it out a bit more. — JReam, Oct 22 '15 at 01:28

Subtract two dates with 'let' variable then average()?

1 Answers1