Is there any alternative to DateTime.Ticks or DateTime.TimeOfDay in Linq-To-Entities?

Question

Hi there!

I'm writing a log parser for an asp.net mvc2 web application. I use Entity framework as a model and logging is done both using my manual engine together with SqlServer2008 CDC feature.

When inserting, or editing a row in a database, the action is being logged. However, there's a small lag between occuring changes in actual table and logging those changes. I need to display details from CDC tables, when user clicks on some of them. Because of before mentioned lag, I can not compare equivalence of two DateTime values. I want to allow lag of 2000 miliseconds. Easiest way I know is using Ticks, or TimeOfDay and comparing absolute value of their values subtracted, but damned Linq-To-Entities does not allow these two properties.

Here's the simple function I'm having problems with...

        public static List<dbo_Object_CT> GetLogDetails (Int32 objectID, DateTime? actionDateAndTime)
    {
        ObjectEntities oe = new ObjectEntities();

        var mylogdetails = (from objectLog in oe.dbo_Object_CT
                     join ltm in oe.lsn_time_mapping on objectLog.C___start_lsn equals ltm.start_lsn
                     where (objectLog.Id == objectID)
                     && ((actionDateAndTime == null) ? true : (Math.Abs(actionDateAndTime.Value.Ticks - ltm.tran_begin_time.Value.Ticks) < 2000))
                     select objectLog).ToList();

        return mylogdetails;
    }

I know I could manually do "where" clause, but it would be big, ugly and slow. Does anyone have better sugestions?

Answer 1

What about

((TimeSpan) (actionDateAndTime.Value - ltm.tran_begin_time.Value)).Milliseconds > 2000 

Produces SQL something like

(CONVERT(Int,(CONVERT(BigInt,(CONVERT(BigInt,(((
 CONVERT(BigInt,DATEDIFF(DAY, t1, t2))) 
 * 86400000) 
 + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, t1, t2), t1), t2)) 
 * 10000)) / 10000)) % 1000)) 

Don't know how to get LINQ to produce sql like

 DATEDIFF (MS , actionDateAndTime , ltm.tran_begin_time)  

Answer 2

Take a look at EF Canonical functions and SQL Server-specific functions. Here is a couple of guides on how to use these functions:
How to: Call Canonical Functions (LINQ to Entities)
How to: Call Database Functions (LINQ to Entities)
Here you can find some information concerning the EntityFunctions class.