SQL内连接与IN语句的Linq等价语句

Question

CREATE TABLE PersonTask
(
PersonId INT NOT NULL,
WeekId INT NOT NULL,
WeekDaysTaskId INT,
WeekEndTaskId INT
)
GO
CREATE TABLE Task
(
Id INT PRIMARY KEY,
[Description] VARCHAR(250) NOT NULL
)
GO
INSERT INTO Task(Id, [Description]) VALUES(1, 'Task-01')
INSERT INTO Task(Id, [Description]) VALUES(2, 'Task-02')
INSERT INTO Task(Id, [Description]) VALUES(3, 'Task-03')
GO
INSERT INTO PersonTask(PersonId, WeekId, WeekDaysTaskId, WeekEndTaskId) VALUES(1, 1, NULL, 1)
INSERT INTO PersonTask(PersonId, WeekId, WeekDaysTaskId, WeekEndTaskId) VALUES(1, 2, 2, NULL)
INSERT INTO PersonTask(PersonId, WeekId, WeekDaysTaskId, WeekEndTaskId) VALUES(1, 3, 3, 3)
GO
SELECT 
        PT.PersonId, 
        WeekId,
        'Not on Bench' as [Status]
    FROM PersonTask AS PT
    INNER JOIN Task AS T ON T.Id IN (PT.WeekDaysTaskId, PT.WeekEndTaskId)
    WHERE WeekId = 3/*Param-1*/ and PersonId = 1/*Param-2*/

我试图写一个linq查询相当于上面的T-sql语句，但徒劳的。有人可以帮助c# Linq查询得到上面的T-SQL语句吗?

Answer 1

必须是这样的(如果你有一个personTaskQueryable和taskQueryable):

from pt in personTaskQueryable
from t in taskQueryable
where (t.Id == pt.WeekDaysTaskId || t.Id == pt.WeekEndTaskId)
    && pt.WeekId == 3 && pt.PersonId == 1
select new { pt.PersonId, pt.WeekId, Status = "Not on Bench" }

我没有尝试，因为我直接在答案文本框中编码，但是Linq文档说你不能用"join"操作符做这样的事情，因为它只支持等量连接。

解决方案是首先构建笛卡尔积并从那里限制结果集。