CODEWITHSUNDEEP
c#entity-frameworklinq
Mou
Mou

Reputation: 16322

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

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

Upvotes: 0

Views: 4422

Answers (1)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62498

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,

Upvotes: 2

Related Questions