EF 以时间字段为查询条件

 

EF 以时间字段为查询条件

            using (var fr = new FxTrContext("wscConn"))//use 领域
            {

                if (fr.FxDailySet.FirstOrDefault() != null)//表中不为空
                {
                    var sss = list[0].DealDate.Date;//list为传入modelList
                    List<ForexDailyModel> dailyList = fr.FxDailySet.Where(x => DbFunctions.TruncateTime(x.DealDate) == sss).ToList();
//DbFunctions需要引用
                    if (dailyList != null)
                    {

                        fr.FxDailySet.RemoveRange(dailyList);
                    }
                    fr.FxDailySet.AddRange(list);

                    fr.SaveChanges();
                    fr.Dispose();
                }

            }

有两点比较有趣的地方：

1、在传入EF的lambda之前，先计算出来时间格式（DateTime)类型的数据，包括Now等类似的函数，赋值给变量，再把变量传入  也就是上面代码中的sss

2、如果用X=>X.dealDate ==sss 也是不行的。与字符串格式的值作为条件的对比不同，需要用到 DbFunctions.TruncateTime(x.DealDate) == sss  作为条件。

 

引用：https://stackoverflow.com/questions/14601676/the-specified-type-member-date-is-not-supported-in-linq-to-entities-only-init

Using this code in Entity Framework I receive the following error. I need to get all the rows for a specific date, DateTimeStart is of type DataType in this format 2013-01-30 12:00:00.000

Code:

 var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
                    .Where(x =>  x.DateTimeStart.Date == currentDateTime.Date);

---

Error:

base {System.SystemException} = {"The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."}

Any ideas how to fix it?

linqentity-framework-4linq-to-entities

Share

Improve this question

Follow

edited Feb 15 '20 at 0:12

 

Selim Yildiz

4,09266 gold badges1414 silver badges2525 bronze badges

asked Jan 30 '13 at 10:25

 

GibboK

62.8k127127 gold badges374374 silver badges603603 bronze badges

• I am able to use x.DateTimeStart.Date in EF Core 2.1.1 – Kirsten Greed Aug 1 '19 at 19:54 

Add a comment

10 Answers

ActiveOldestVotes

280

 

DateTime.Date cannot be converted to SQL. Use EntityFunctions.TruncateTime method to get date part.

var eventsCustom = eventCustomRepository
.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
.Where(x => EntityFunctions.TruncateTime(x.DateTimeStart) == currentDate.Date);

UPDATE: As @shankbond mentioned in comments, in Entity Framework 6 EntityFunctions is obsolete, and you should use DbFunctions class, which is shipped with Entity Framework.

Share

Improve this answer

Follow

edited Mar 8 '16 at 8:44

 

Mr. Flibble

24.8k2121 gold badges6565 silver badges9696 bronze badges

answered Jan 30 '13 at 10:29

 

Sergey Berezovskiy

214k3333 gold badges390390 silver badges415415 bronze badges

• 1

  I have to slight modify your version .Where(x => EntityFunctions.TruncateTime(x.DateTimeStart) == currentDate.Date); let me know your thougs – GibboK Jan 30 '13 at 10:46 

• 1

  I hope you do not mind I have edit your answer adding .date if you agree, so just for reference :-) thanks for your support, I really appreciate it – GibboK Jan 30 '13 at 11:16

• 1

  @GibboK sure, no problem :) That was just for purpose of formatting long string. – Sergey Berezovskiy Jan 30 '13 at 11:20

• 71

  EntityFunctions is obsolete, instead use DbFunctions.TruncateTime method – shankbond May 6 '14 at 11:26

• 1

  The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – SAR May 7 '17 at 10:14

Show 1 more comments

 

Report this ad

85

 

You should now use DbFunctions.TruncateTime

var anyCalls = _db.CallLogs.Where(r => DbFunctions.TruncateTime(r.DateTime) == callDateTime.Date).ToList();

Share

Improve this answer

Follow

edited Sep 6 '17 at 13:21

 

Soner Gönül

90.4k9999 gold badges181181 silver badges319319 bronze badges

answered Mar 12 '14 at 21:50

 

WaZ

1,53733 gold badges1818 silver badges2727 bronze badges

• see: stackoverflow.com/questions/23911301/… – juFo May 4 '15 at 8:32

• The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – SAR May 7 '17 at 10:16

引用：https://www.cnblogs.com/lwqlun/p/11933042.html

在组合查询表达式树的阶段，EF/EF Core只会去组合表达式，而不会去尝试计算表达式的值，所以这个阶段DateTime.Now.Date的值并没有被计算出来， 在进入正常查询阶段的时候， EF/EF Core会尝试将查询表达式树翻译成SQL脚本，这时候由于我们的EF Provider是MySql Provider， 恰巧DateTime.Now可以翻译成Mysql的内置函数CURRENT_TIMESTAMP()， 所以这里EF/EF Core就跳过了表达式值的计算，直接将其翻译成了对应的内置函数，所以导致生成的SQL查询和我们的预期有偏差。

那么我们该如何解决这个问题呢？

解决方案

经过了以上的思考，其实解决这个问题也就很简单了，我们可以将DateTime.Now.Date先计算出来，保存在一个变量中，然后将这个变量传入查询中。

 

var today = DateTime.Now.Date;

var query = DbContext.CRM_Note_Reminders
     .Include(x => x.CRM_Note)
     .Where(x => !x.CRM_Note.Is_Deleted 
             && !x.Is_Deleted
             && x.Reminder_Date.Date <= today)
     .ToList();