CODEWITHSUNDEEP
c#mysqlsqllinqentity-framework
user535617
user535617

Reputation: 644

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.

Upvotes: 3

Views: 21324

Answers (4)

Roshna Omer
Roshna Omer

Reputation: 721

this answer helped me for the exact day number, not in-between difference:

Found it on forums.asp.net

Here's a sample showing one way to get all employees with a DOB between now and 14 days from now...

var employeesWithBirthday = 
    from emp in dc.Employees
    let BirthdayDiff = (new DateTime(DateTime.Now.Year, emp.BirthDate.Month, emp.BirthDate.Day) - DateTime.Now).TotalDays 
    where BirthdayDiff >= 0 && BirthdayDiff <= 14
    select emp

; ...although, be aware that a query like that will do a table scan (can't use any indexes)...

Upvotes: 0

Rohidas Kadam
Rohidas Kadam

Reputation: 446

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

Its not working in my scenario

Upvotes: 4

Mike Peterson
Mike Peterson

Reputation: 1099

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;

Upvotes: 11

Colin
Colin

Reputation: 22595

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

Upvotes: 2

Related Questions