CODEWITHSUNDEEP
c#linq
Sith2021
Sith2021

Reputation: 3716

Filtering by plain date SQL to Linq syntax

I have the following SQL and result:

SELECT EventTime FROM Incidents
-- RESULT --
2015-10-20 00:00:00.000
2015-10-20 05:00:00.000
2015-10-20 05:50:00.000
2015-10-20 07:00:00.000
2015-10-20 09:00:30.000
2015-10-21 05:00:00.000
2015-10-21 06:10:00.000
2015-10-22 09:10:00.000

I use the following SQL to filter by plain date (SQL 2014):

SELECT DISTINCT CONVERT (Date, [EventTime]) AS EventDate FROM Incidents
-- RESULT --
EventDate
2015-10-20
2015-10-21
2015-10-22

So very easy in SQL. For LINQ I try the logic:

var q = db.TimeLines.Select(x => x.Time.Date).Distinct();
// show it
q.ToList().ForEach(x => { Console.WriteLine("EventDate: {0}", x); });

Breaks with: Additional information: The specified type member 'Date' is not supported in LINQ...

...How to write the second SQL in LINQ?

Upvotes: 0

Views: 71

Answers (1)

user1641172
user1641172

Reputation:

If you change the query into a list before using operators that aren't translatable to sql, then it will work.

var q = db.TimeLines.ToList.Select(x => x.Time.Date).Distinct();
// show it
q.ForEach(x => { Console.WriteLine("EventDate: {0}", x); });

But you need to cast as a list before you get the date portion of the date, not after.

EDIT:

In this case, i'm not sure you will get any performance increase at all, but as mentioned in the comment below by juharr, use AsEnumerable if you want the query to remain lazy.

var q = db.TimeLines.AsEnumerable.Select(x => x.Time.Date).Distinct();

EDIT 2:

If you want to run the query entirely on the SQL server, you can use DbFunctions.TruncateTime as a translatable function:

var q = db.TimeLines.Select(x => DbFunctions.TruncateTime(x.Time)).Distinct();

EDIT 3: Also, Sergey is right about my first example, you should select just what you want before dumping into memory if doing it this way:

var q = db.TimeLines.Select(x => x.Time).AsEnumerable.Select( x => x.Date).Distinct();

Upvotes: 1

Related Questions