How to add a DateTime extension method to use on EF and LINQ queries?


I´m using a very usefull extension method to ignore database milliseconds on DateTime comparasion listed here: How to truncate milliseconds off of a .NET DateTime

My code turned to be the following:

    /// <summary>
    /// Helpers to handle DateTime comparasion, as storing and retrieving form DB will change number of Ticks.
    /// </summary>
    public static class DateTimeHelper

        /// <summary>
        /// This function will truncate the datetime to the given TimeSpan for comparasion.
        /// </summary>
        /// <param name="dateTime">Datetime to truncate</param>
        /// <param name="timeSpan">Timespan to be used</param>
        /// <returns></returns>
        public static DateTime Truncate(this DateTime dateTime, TimeSpan timeSpan)
            return dateTime.AddTicks(-(dateTime.Ticks % timeSpan.Ticks));

And I wanna use it on EF and LINQ to get data from database:

HISDATASET savedObject = dbContext.HISDATASET.FirstOrDefault(record => record.DSSTARTDATETIME.Truncate(TimeSpan.FromMilliseconds(1)) == dbControl.DSSTARTDATETIME.Truncate(TimeSpan.FromMilliseconds(1)) &&
                                                             record.DSENDDATETIME.Truncate(TimeSpan.FromMilliseconds(1)) == dbControl.DSENDDATETIME.Truncate(TimeSpan.FromMilliseconds(1)));

I´m getting this error when running:

LINQ to Entities does not recognize the expression 'System.DateTime Truncate(System.DateTime, System TimeSpan)' that cannot be converted on a repository expression" (Translated).

I wanna be able to call my extension method inside my database query. How can I solve that ?

Thanks for any help....


This solved for me, but I didn´t like very much the solution... Not because the DiffSeconds usage, but because it never returns 0. So, comparing to zero does not work. I had to adapt it to accept at least 1sec difference (<=1) and them it went through.

HISDATASET savedObject = dbContext.HISDATASET.FirstOrDefault(record => EntityFunctions.DiffSeconds(record.DSSTARTDATETIME, dbControl.DSSTARTDATETIME) <= 1 &&  EntityFunctions.DiffSeconds(record.DSENDDATETIME, dbControl.DSENDDATETIME) <= 1);

There should be a better way to compare DateTime variables with database fields... Anyway, thanks for the help.

You can use one of the supplied EntityFunction


EntityFunctions.DiffMilliseconds(dateTime1, dateTime2) < someNumber

or maybe using DiffSeconds will be better

 EntityFunctions.DiffSeconds(dateTime1, dateTime2) == 0