-2

I have a SQL Server 2016 table of events where the event date and start time are stored in separate columns. I'm trying to write a query to identify which of a provided list of DateTimes already exist in the table so I need to add or combine the EventDate and StartTime columns before doing the comparison like this:

public List<DateTime> DoEventsExist(List<DateTime> dateBatch)
{
    try
    {
        DBContext.Events.Where(ce => dateBatch.Contains(ce.EventDate.Add(ce.StartTime));
    }
    catch (Exception ex)
    {
    }
}

But this, of course, would give an exception with the message:

LINQ to Entities does not recognize the method 'System.DateTime Add(System.TimeSpan)' method, and this method cannot be translated into a store expression.

In this case the columns are of Date and Time types respectively (so the .NET types are DateTime and TimeSpan), but I'd also like to know how to accomplish this if they were both DateTime2 types where one contained a date at midnight and the other contained an irrelevant date with the correct time.

SqlFunctions.DateAdd would probably work but it would make my code tightly coupled with MS SQL Server, which I don't want.

Note There is a similar question, however it does not ask about combining two DateTimes. Additionally it is not specific to EF 6, its answer predates EF 6, and the answer does not work so the whole question is useless to SO.

xr280xr
  • 12,621
  • 7
  • 81
  • 125
  • You said *"I believe this will give me an error"*. Does that mean you haven't tried it? – Rufus L Jan 14 '20 at 23:01
  • 3
    Use `SqlFunctions.DateAdd`? – NetMage Jan 14 '20 at 23:01
  • Does this answer your question? [Add Datetime and time in Entity Framework from separate column](https://stackoverflow.com/questions/37452142/add-datetime-and-time-in-entity-framework-from-separate-column) – NetMage Jan 14 '20 at 23:05
  • @Rufus Yes, I have not tried this code. I know it will give an error, didn't know the exact text of the error. – xr280xr Jan 14 '20 at 23:32
  • @NetMage - That might work except it would couple my data layer with SQL Server. I saw your link. Maybe a version difference but I can't even make it work. "'TotalSeconds' is not supported in LINQ to Entities" as I expected. See my note added above. – xr280xr Jan 14 '20 at 23:53
  • `EntityFunctions` became `DbFunctions` in EF 6 and expose `CanonicalFunctions` which are supported by all EF data providers. – NetMage Jan 15 '20 at 00:46
  • If you want to query the table for StartTime and EventTime are in the provided collection then you should used `dateBatch.Contains(ce.EventDate) || dateBatch.Contains(ce.StartTime)` – Chetan Jan 15 '20 at 01:39
  • @ChetanRanpariya That won't work. In the case of a `Time` type, `StartTime` is a `TimeSpan` which dateBatch cannot contain. But the real problem is that it needs to match `StartTime` AND `EventDate` to the same `dateBatch` item. – xr280xr Jan 21 '20 at 16:07
  • @NetMage but `DbFunctions` does not have a `DateAdd` method. Please post an answer if you see a way to solve the problem – xr280xr Jan 21 '20 at 19:50
  • You don't use `DateAdd`, you use `AddSeconds` or something similar: see [this answer](https://stackoverflow.com/a/34346061/2557128). – NetMage Jan 22 '20 at 18:55
  • I think that may work for the `DateTime`/`DateTime2` case but it doesn't work for a `Date` column: "The datepart millisecond is not supported by date function dateadd for data type date." `Date` has no time so you can't add time to it. DateAdd won't convert it to a `DateTime`. – xr280xr Jan 23 '20 at 00:15

1 Answers1

0

Given you have a list of datetime values, that style of query really isn't going to get you where you want to be.

As you suggest, change it to a TVP.

You can pass a TVP to a procedure as also suggested, but TVPs can also be passed to adhoc SQL queries as parameters.

Greg Low
  • 1,526
  • 1
  • 4
  • 4
  • Can you expand on "that style of query really isn't going to get you where you want to be"? It will have a limit for SQL Server of 2100 dates per query (which I'd probably never hit, but I chunked it) but [https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine](this) mentions TVPs are performant up to only 1000 rows. So the style seems OK unless there's just no way to combine the date and time in the query, which it seems may be true. – xr280xr Jan 14 '20 at 23:47
  • You can definitely combine the date and time in the query. Take a look at DATETIMEFROMPARTS and associated functions. (No idea about using that from EF) But you either need to pass a TVP or call the query row by row (bad). TVPs are best for smaller numbers of rows. If you're really needing performance, why not just: a) truncate a staging table, b) in your query, insert all the values into that table, then c) execute a single bulk query to get the results? At least then you could index all the tables, etc. involved properly. (2100 dates is a lot to pass as a parameter). – Greg Low Jan 16 '20 at 00:07