此子select的LINQ等效项

Question

"任务；表：

public class Task
{
public int ID { get; set; }
public string name { get; set; }
}

"任务历史记录"；表：

public class TaskHistory
{
public Guid ID { get; set; }
public DateTime Created { get; set; }
public DateTime LastModified { get; set; }
public string CreatedBy { get; set; }
public int TaskID { get; set; }
public TaskStatus { get; set; }
}

Sql查询：在过去30天内完成了哪些任务？

select * from Tasks 
where TaskStatus in ('Completed', 'Rejected')
and ID in (
select distinct TaskID 
from TaskHistory 
where LastModified >= DateAdd(day, -30, GETDATE())
)

Q： 此SQL查询的等效LINQ表达式是什么？

Answer 1

这个查询应该执行您想要的操作：

var statuses = new [] {"Completed", "Rejected"};
var query = 
from t in ctx.Tasks
where statuses.Contains(t.TaskStatus)
&& ctx.TaskHistory
.Any(th => th.LastModified <= DateTime.Now.AddDays(-30)
&& th.TaskID == t.ID)
select t;

Answer 2

除非我遗漏了什么，否则我认为您的查询正在执行以下操作：

IEnumerable<Task> query =
from t in tasks
join th in taskHistory on t.ID equals th.TaskID into ths
where
ths
.Where(x => x.LastModified < DateTime.Now.AddDays(-30.0))
.Any(x => new []
{
TaskStatus.Completed,
TaskStatus.Rejected,
}
.Contains(x.TaskStatus)
select t;

这很奇怪。