pathDongle pathDongle - 2 years ago 182
C# Question

LINQ distinct items by Date part only from DateTime field in Select clause

I'm struggling to get the syntax correct for the following.

I'm trying to get a distinct list of optionNames by

and not datetime. The following LINQ query gets me the distinct list by datetime only

var optionList = await db.OptionsStore.Select(x => new { x.OptionTime, x.OptionName })

is a DateTime type. I know how to use the date part only in a where clause but not the select clause. Changing x.OptionTime to x.OptionTime.Date gives this error:

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

Is this possible to convert the datetimes to date in the query or would I need to process it afterwards?

Answer Source

Use EntityFunctions.TruncateTime() for that (DateTime.Date can't be converted to SQL):

var optionList = await db.OptionsStore
                         .Select(x => new 
                              OptionTime = EntityFunctions.TruncateTime(x.OptionTime), 
                              OptionName = x.OptionName 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download