SQL Inner with 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*/

我正在尝试编写一个与上面的T-sql语句等价的linq查询，但徒劳无功。谁可以帮助SQL查询得到上面的T- C#语句。

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" }

我没有尝试，因为我直接在answer文本框中编码，但Linq文档说明您不能使用"join“运算符执行此类操作，因为它只支持equijoins。

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