pathDongle pathDongle - 1 year ago 120
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

date
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 })
.Distinct().ToListAsync();


x.OptionTime
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 
                          })
                         .Distinct()
                         .ToListAsync();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download