如何重写与EF Core LINQ连接的原始sql查询?

Question

我有这样的查询:

SELECT * 
FROM public.lifecycle_data lifecycle_data 
INNER JOIN 
(SELECT * 
FROM public.users 
WHERE id = 123) AS t1 ON lifecycle_data.reference = t1.id
WHERE updated IS NULL
ORDER BY created DESC

我想用EF Core LINQ重写这个查询，我试过了:

var users = db.LifeCycle
.Where(l => l.Updated == null)
.Join(db.Users,
l => l.Reference,
u => u.Id,
(lifeCycle, user) => new User()
{
Id = lifeCycle.Id,
FieldOne = user.FieldOne,
FieldTwo = user.FieldTwo,
Created = lifeCycle.Created
})
.Where(u => u.Id == 123)
.OrderBy(c => c.Created)
.ToList();

但是它被解释为:

SELECT 
l.id AS "Id", u.field_one AS "FieldOne", u.field_two AS "FieldTwo", l.created AS "Created"
FROM 
lifecycle.lifecycle_data AS l
INNER JOIN 
users.users AS u ON l.reference = u.id
WHERE 
FALSE
ORDER BY l.created

Answer 1

ORM的工作是从实体之间的关系生成查询，特别是join。EF(或任何其他ORM)中没有表，只有实体。DbContext不是数据库的模型，LINQ也不是SQL的替代品。

在EF Core 5及以后的版本中，等价的是一个Filtered Include，假设User有一个LifecycleData集合属性:

var users = dbContext.Users
.Include(u=>u.LifeCycleData
.Where(l=>l.Updated==null)
.OrderBy(l=>l.Created))
.Where(u=>u.d==123);