user535617 user535617 - 6 months ago 152
MySQL Question

Date Difference Logic in LINQ

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.

Answer

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;