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

Question

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....

[EDIT - FINAL CODE]

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.

score 2 · Accepted Answer · answered Nov 27 '13 at 14:37

2

You can use one of the supplied EntityFunction

DiffMilliseconds

EntityFunctions.DiffMilliseconds(dateTime1, dateTime2) < someNumber

or maybe using DiffSeconds will be better

 EntityFunctions.DiffSeconds(dateTime1, dateTime2) == 0

answered Nov 27 '13 at 14:37

Aducci

26,101
8
63
67

1

And in EF6 - from https://entityframework.codeplex.com/wikipage?title=Updating%20Applications%20to%20use%20EF6 System.Data.Objects.EntityFunctions => System.Data.Entity.DbFunctions (Note that the class has been renamed; a class with the old name still exists and works, but it now marked as obsolete.) – Colin Nov 27 '13 at 14:46
Forgot to mention that I´m using Oracle. I can´t find System.Data.Objects to include. Shall I install something other than EF ? – Mendes Nov 27 '13 at 15:47
@Mendez - Oracle shouldn't matter. As per Colin's comment, if you are using EF6, you will need to follow his comment. Hope that helps. – Aducci Nov 27 '13 at 16:03
I had to add reference to System.Data.Entity, then the functoin compilet, but it´s not working yet... – Mendes Nov 27 '13 at 16:24
@Mendez - What is not working? Updating your question with your new code will help troubleshooting – Aducci Nov 27 '13 at 16:35
Not returning any register: HISDATASET savedObject = dbContext.HISDATASET.FirstOrDefault(record => EntityFunctions.DiffSeconds(record.CREATEDATETIME, dbControl.CREATEDATETIME) == 0); – Mendes Nov 27 '13 at 16:40
@Mendez - What is dbControl.CREATEDATETIME? Where do you assign a value to it? I would try some of the other `diff` functions like DiffHours just to test if you get something back. – Aducci Nov 27 '13 at 16:45
Thanks Aducci for the help. I´ll upvote as you gave me the way to solve the problem. See EDIT post for the final solution (compare to at least 1 sec). – Mendes Nov 27 '13 at 19:31
@Mendez - Thank you, glad you solved it. You can always post your solution as an answer and accept it. This will help other people in the future – Aducci Nov 27 '13 at 19:34

score 0 · Answer 2 · answered Nov 27 '13 at 14:33

0

Sadly you can not, EF have no way to understand your .NET code and to generate SQL which will give the same result. Every expression used in LINQ to Entites must be translated to SQL and only simple ones are supported by EF.

answered Nov 27 '13 at 14:33

Piotr Auguscik

3,651
1
22
30

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

2 Answers2