LINQ to Entities中DateTime.DayOfWeek的Join

Question

假设有两个表：Shifts和RANK_S_DAY。Shifts包含一个DateTime类型的ShiftDate列，而RANK_S_DAY具有一个DayOfWeek列。我需要连接(int)ShiftDate.DayOfWeek等于DayOfWeek。我知道为什么它不起作用，但我不确定该如何改变它。异常是：

"The specified type member 'DayOfWeek' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."

据我所知，LINQ无法将(int)ShiftDate.DayOfWeek转换为SQL可以理解的内容。有什么想法吗？

以下是代码：

Shifts = from s in en.Shifts
join j in en.RANK_S_JOB on s.kronos_JobPositions.JobID equals j.JOBNO
join d in en.RANK_S_DAY on (int)s.ShiftDate.DayOFWeek equals d.DAY_OF_WEEK
orderby
 d.RANK,
 j.RANK ascending
select s;

- Steve

Answer 1

LINQ to SQL（语言集成查询）

var dayOfWeekCondition = (dt => dt.DayOfWeek == dayOfWeek);

LINQ to Entities

int dow = (int)dayOfWeek + 1; // SQL Day of week
var dayOfWeekCondition = (dt => SqlFunctions.DatePart(“weekday”, dt) == dow);

---

Source:

http://blog.abodit.com/2009/07/entity-framework-in-net-4-0/

- Leniel Maccaferri

---

太棒了，这正是我在寻找的！ - Jacob

@Jacob刚刚更新了答案，包括相关的代码... :) - Leniel Maccaferri

Answer 2

using System.Data.Objects.SqlClient; //Don't forget this!!

//You can access to SQL DatePart function using something like this:

YourTable.Select(t => new { DayOfWeek = SqlFunctions.DatePart("weekday", t.dateTimeField) - 1 }); //Zero based in SQL

//You can compare to SQL DatePart function using something like this:

DateTime dateToCompare = DateTime.Today;
YourTable.Where(t => SqlFunctions.DatePart("weekday", t.dateTimeField) - 1 == dateToCompare }); //Zero based in SQL

- Ymagine First

Answer 3

看起来在这个层面上我无能为力。所以我创建了一个存储过程将两个表连接起来并导入到实体中，创建了一个函数导入返回Shifts实体。不确定这是否是最佳方法，但它有效且干净。

- Steve

Answer 4

有趣的是，在Linq-to-Sql中这个很好地工作：

from o in Orders
join c in Categories on (int) o.OrderDate.Value.DayOfWeek equals c.CategoryID
where o.OrderDate != null
select c

那个查询毫无意义——只是一些随机连接，具有适当的数据类型。（我使用的是Northwind）

- James Curran