C# LINQ: DbFunctions.TruncateTime throw error This function can only be invoked from LINQ to Entities

Question

here is my code.

    List<NPSEntity> Data = null; ;
    using (var db = new NPSDbContext())
    {
        Data = (from n in db.NPSDatas
                    orderby n.AddDate, n.CountryCode
                    where DbFunctions.TruncateTime(n.NPSDate) >= StartDate.Date && DbFunctions.TruncateTime(n.NPSDate) <= EndDate
                    select n).ToList();
    }

    NPSData = Data.Select(n => new NPSEntity
    {
        NPSDate = DbFunctions.TruncateTime(n.NPSDate),
        NPSAmount = n.NPSAmount,
        AddDate = n.AddDate,
        ModDate = n.ModDate,
        UserID = n.UserID,
        CountryCode = n.CountryCode
    }).ToList();

    if (NPSData!=null)
    {
        CountryCodes = NPSData.Select(e => e.CountryCode).Distinct();
        CountryCount = CountryCodes.Count();
    }

this line throwing error NPSDate = DbFunctions.TruncateTime(n.NPSDate), This function can only be invoked from LINQ to Entities

my objective is to remove time portion from NPSDate NPSDate = DbFunctions.TruncateTime(n.NPSDate) that is why i used DbFunctions.TruncateTime function.

tell me how could i fix this problem or tell me how could i remove time portion from NPSDate when fetching data from db. my NPSDate property look like public DateTime? NPSDate { get; set; } that is why i can not use NPSDate.Date

please guide me. thanks

score 2 · Answer 1 · answered Feb 13 '17 at 13:26

2

The error is self explanatory enough, that you cannot use DbFunctions in any other context, the context should only be Linq to Entities and when you call ToList() on an IQueryable<T>, the query gets executed and results are brought in memory, you would need to use Date property of DateTime if truncating the time is needed here like:

NPSData = Data.Select(n => new NPSEntity
{
    NPSDate = n.NPSDate.Date,

answered Feb 13 '17 at 13:26

Ehsan Sajjad

61,834
16
105
160

i can not use `n.n.NPSDate.Date.Date` because my n.NPSDate property is nullable type `public DateTime? NPSDate { get; set; }` so i can not use Date property. – Mou Feb 13 '17 at 13:34
then: `n.NPSDate.Value.Date`, more better is to check for null : `n.NPSDate.HasValue ? n.NPSDate.Value.Date ? null ` – Ehsan Sajjad Feb 13 '17 at 13:49

C# LINQ: DbFunctions.TruncateTime throw error This function can only be invoked from LINQ to Entities

1 Answers1