Date Difference Logic in LINQ

Question

I'm attempting to access data from two different database tables and then join them together on two fields using LINQ in C#. I believe that I have a logically sound overall working approach. Part of the problem I'm running into is that I'm filtering the data from both tables prior to joining them, because the tables have far too much data and it would cause a crash.

The main problem is that for one of the tables I need to pull only data that has a timestamp (column) value of today. The timestamp value is of type System.DateTime?.

I've tried a few different ways:

        DateTime? currentDate = System.DateTime.Now;

        var second_data = (from b in this.database.table
            where EntityFunctions.DiffDays(b.timeStamp.Value, currentDate) == 0
            select b);

I'm under the impression this doesn't work because there's no function in the database to handle it. Inner Exception: '{"FUNCTION database.DiffDays does not exist"}'

        var second_data = (from b in this.database.table
            where b => b.timeStamp.Value.Date == DateTime.Now.Date
            select b);

This doesn't work because 'The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.'

        var second_data =
            this.database.table.Where(sd => sd.timeStamp.Value.Date == DateTime.Now.Date);

But this again fails because of the use of .Date.

Unfortunately, because I don't have the memory to hold all that data, the possibility of of pulling all the data first and then running date logic on it is out of the question. If anyone could give any insight on how I might be able to solve this problem it would be greatly appreciated, thank you.

Solution 1

To get rows from the table that are only for today (or a specific date range), you could simply do this. The nice thing about this approach is that it works for both cases of a specific date or a date range.

// specify date range (without time)    
DateTime currentDate = System.DateTime.Now.Date;
DateTime nextDate = currentDate.AddDays(1);

var second_data = from b in this.database.table 
                  where b.timeStamp.Value >= currentDate 
                  and b.timeStamp.Value < nextDate
                  select b;

Solution 2

query = query.Where(c=>  DbFunctions.DiffDays(c.ToDate, DateTime.Now) < 30);

Its not working in my scenario

Solution 3

I'm using Sql Server and I get your first method to work if I remove the call to timestamp.Value. I don't think your version compiles because DiffDays takes nullable DateTimes for both parameters.

DateTime? currentDate = System.DateTime.Now;

var second_data = (from b in this.database.table
    where EntityFunctions.DiffDays(b.timeStamp, currentDate) == 0
    select b);

The other thing I note is that I get a warning:

'System.Data.Entity.Core.Objects.EntityFunctions' is obsolete: 'This class has been replaced by System.Data.Entity.DbFunctions.'

So if it still doesn't work in MySql you could try DbFunctions instead