0

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?

Loofer
  • 6,841
  • 9
  • 61
  • 102
Sith2021
  • 3,245
  • 30
  • 22
  • are you using LINQ to SQL or Entity Framework? – Sergey Berezovskiy Oct 22 '15 at 15:16
  • For LINQ-to-SQL, see [this](http://stackoverflow.com/questions/11663523/using-built-in-sql-functions-in-a-linq-query). For Entity Framework, see [this](http://stackoverflow.com/questions/12412231/how-do-i-perform-date-part-comparison-in-ef). – Anton Gogolev Oct 22 '15 at 15:17
  • Check out [EntityFunctions.TruncateTime](https://msdn.microsoft.com/en-us/library/dd395596(v=vs.110).aspx) – juharr Oct 22 '15 at 15:18
  • The correct Linq try is: var q = db.Incidents.Select(x => x.EventTime.Date).Distinct(); – Sith2021 Oct 22 '15 at 15:19
  • If you change it into a list before getting the datepart it will work, db.TimeLines.ToList().Select(x => x.Time.Date).Distinct(); –  Oct 22 '15 at 15:20
  • Yes Nick. But this populate the result before the apply filter... the table can have many records – Sith2021 Oct 22 '15 at 15:27
  • You aren't applying any filters? If you want to apply criteria before working with the list in memory, use a .Where before the .ToList –  Oct 22 '15 at 15:42
  • would be nice if you marked my answer as the answer, considering you have indicated it worked for you.. –  Nov 06 '15 at 13:30

1 Answers1

1

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();