1

I have a table with Time column of type DATETIME and I want to craft a piece of Linq-To-Sql code that does this:

SELECT Whatever FROM ThatTable WHERE Time > DATEADD(hour, -3, GETUTCDATE())

and the key is I want SQL Server time used, so I used the trick from here with a [Function(Name="GetUTCDate", IsComposable=true)] - attributed method in my DataContext.

My code goes like this:

context.GetTable<ThatTable>().Where(
    item => item.Time > context.GetServerUTCDate().AddHours(-3) );

and when I output the emitted SQL query I see that it has this predicate:

WHERE [t0].[Time] > DATEADD(ms,
        (CONVERT(BigInt,@p0 * 3600000)) % 86400000,
           DATEADD(day, (CONVERT(BigInt, @p0 * 3600000)) / 86400000,
              GetUTCDate()))

Here it actually makes use of GetUTCDate() but clearly it failed to make use of hour and instead emitted some magic for comparing number of hours to milliseconds and working with those.

Can I make it use hour instead of magic?

Community
  • 1
  • 1
sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • Do you definitely need the time to be based on the *database* time rather than where the code is running? – Jon Skeet Apr 09 '15 at 13:42
  • @JonSkeet Yes, I want the latest time among the stuff on the server and if it mismatches my client time I'll be requesting something else and perhaps get absolutely meaningless results. – sharptooth Apr 09 '15 at 13:43
  • When you say "client" here - where is this LINQ to SQL code running? (Commonly it would be on a server itself, in which case it may be fine to just trust that time...) – Jon Skeet Apr 09 '15 at 13:49
  • @JonSkeet The C# code runs on some random server and SQL queries run in SQL Azure which is hosted in some other random place, so I'd rather not rely on them having identical time. – sharptooth Apr 09 '15 at 13:52
  • Okay. Now, as for the SQL that you don't like - do you not like it just because it's harder to read in debug logs? Does it perform worse? Something else? – Jon Skeet Apr 09 '15 at 13:56
  • @JonSkeet It just looks scary and I have to make effort to validate it, that's the only real problem. – sharptooth Apr 09 '15 at 14:08

0 Answers0