compare the dates with linq

Question

I have this code:

   i.SpesaVitto = db.TDP_NotaSpeseSezB.Where(p => p.GiornoFine == null && 
                ((DateTime)p.Giorno).Date == ((DateTime)i.Data).Date &&
                p.MissioneID == missioneID).Sum(p => p.Costo);

If i launch it i obtain this error:

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

how can i compare the datetimes field without the hours part

thanks

Answer 1

LINQ to Entities can compare only parts of entity ("entity members"). What is type of "p.Giorno"?

I advise following: add explicit DateTime field (let's call it DateTimeField) to entity that forms elements of "TDP_NotaSpeseSezB", then compare this way:

p.DateTimeField >= ((DateTime)i.Data).Date && p.DateTimeField < ((DateTime)i.Data).Date.AddDays(1)

Any questions?

Answer 2

Check out this question it looks like the syntax error may be in your SQL, not in your LINQ. The asker in that question had written:

SQL ------
Select
 EmployeeNameColumn
From
EmployeeTable
WHERE StartDateColumn.Date <= GETDATE() //Today
SQL ------

When they should have written:

SQL ------
Select
 EmployeeNameColumn
From
EmployeeTable
WHERE StartDateColumn <= GETDATE() //Today
SQL -

Answer 3

Well this is certainly not the most efficient way to do it, but you can try something like:

i.SpesaVitto = db.TDP_NotaSpeseSezB.Where(p => p.GiornoFine == null && 
            (new DateTime((p.Giorno as DateTime).Year, (p.Giorno as DateTime).Month, (p.Giorno as DateTime).Day) == (new DateTime((i.Data as DateTime).Year, (i.Data as DateTime).Month, (i.Data as DateTime).Day) &&
            p.MissioneID == missioneID).Sum(p => p.Costo);

Depending on the requirements I would probably implement something cleaner and faster but it would depend on how the dates are stored in your database.