The specified type member 'Date' is not supported in LINQ to Entities.(Use Sqlie)

Question

> System.NotSupportedException:“The specified type member 'Date' is not

supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.”

I use EntityFramework6 to Query from Sqlie,there is some worng about dateTime.

var test = DateTime.Now.Date;
var maxEntity=_baseService.GetList<TestNoMapping>(o => o.LastChangeTime.Date == test)
            .OrderByDescending(o => o.SampleId)
            .FirstOrDefault();
public class BaseService
{
    private readonly BaseContext _baseContext = new BaseContext();


    public List<T> GetList<T>(Expression<Func<T, bool>> where) where T : class
    {
        return _baseContext.Set<T>().Where(where).ToList();
    }

the DbFunctions.TruncateTime I have try,But it doesn't work

Can you provide your code with DbFunctions.TruncateTime that is not working? — Manprit Singh Sahota, Jul 27 '17 at 07:55
Maybe you can check this post https://stackoverflow.com/questions/11597373/the-specified-type-member-date-is-not-supported-in-linq-to-entities-exception — Erick Gallani, Jul 27 '17 at 07:55
Same issue as https://stackoverflow.com/questions/33138124/cant-compare-dates-using-sqlite-with-ef6. Unfortunately unresolved. — Ivan Stoev, Jul 27 '17 at 08:03
Add following line to constructor of your DbContext so that you can get exact query which SQL is executing. `Database.Log = s => Trace.WriteLine(s);` — Manprit Singh Sahota, Jul 27 '17 at 08:30

score 1 · Accepted Answer · answered Jul 27 '17 at 08:44

The best you can do here is replace the condition by a range test: date between last midnight and midnight today:

var minDate = DateTime.Today;
var maxDate = minDate.AddDays(1);
var maxEntity = _baseService
    .GetList<TestNoMapping>(o => o.LastChangeTime >= minDate
                              && o.LastChangeTime < maxDate)
    .OrderByDescending(o => o.SampleId)
    .FirstOrDefault();

The reason is that it's never a good idea to convert a database value before comparing it: it disables any indexes on the column. (See sargable).

Manprit Singh Sahota · Answer 2 · 2017-07-27T08:00:40.597

0

You have to use DbFunctions.TruncateTime method of Entity framework to match date portion as Linq will not recognize Date property.

Can you try below code?

var test = DateTime.Now;
var maxEntity=_baseService.GetList<TestNoMapping>(o => DbFunctions.TruncateTime(o.LastChangeTime.Date) == DbFunctions.TruncateTime(test))
            .OrderByDescending(o => o.SampleId)
            .FirstOrDefault();

edited Jul 27 '17 at 08:00

answered Jul 27 '17 at 07:55

Manprit Singh Sahota

1,279
2
14
37

HuangKai already wrote in his question: the DbFunctions.TruncateTime I have try,But it doesn't work – Mighty Badaboom Jul 27 '17 at 07:59
Added sample code. Can you try this? – Manprit Singh Sahota Jul 27 '17 at 08:01
@peter thanks,Program throw exception "SQL logic error or missing database no such function: TruncateTime" – HuangKai Jul 27 '17 at 08:26
@Manprit Singh Sahota the err is "LastChangeTime] >= '2017/7/27 0:00:00'" ,I changed to[LastChangeTime] >= '2017-07-27 00:00:00' ，but i don't know how to change the code – HuangKai Jul 27 '17 at 09:16
Please provide the expected query and query that is actually executing so that I can review. – Manprit Singh Sahota Jul 27 '17 at 15:44

Mighty Badaboom · Answer 3 · 2017-07-27T08:08:01.883

0

As you see in the exception: Date is not supported in LINQ to Entities

You should use

o => o.LastChangeTime.Year == test.Year &&
     o.LastChangeTime.Month == test.Month &&
     o.LastChangeTime.Day == test.Day &&

instead of

o => o.LastChangeTime.Date == test

Edit

And change

var test = DateTime.Now.Date;

to

var test = DateTime.Now;

edited Jul 27 '17 at 08:08

answered Jul 27 '17 at 07:56

Mighty Badaboom

6,067
5
34
51

I'll try to do as you say，Program throw exception System.InvalidCastException:“指定的转换无效。” – HuangKai Jul 27 '17 at 08:06
@HuangKai Edited my question; have a look at it. – Mighty Badaboom Jul 27 '17 at 08:08
，I uploaded a picture about the exception – HuangKai Jul 27 '17 at 08:19

The specified type member 'Date' is not supported in LINQ to Entities.(Use Sqlie)

3 Answers3