将带有派生表的多表sql连接转换为Linq
将带有派生表的多表sql连接转换为Linq
提问于 2018-01-13 05:08:59
我有一个工作的SQL查询，如下所示。我想创建一个等价的Linq查询。没有太多的成功。
select u.Id as userId, u.CustomerId, a.Id as appId, a.Created as appCreated
from EO_AppUsers u
left join EO_Policies p on (u.Id = p.AppUser_Id)
CROSS APPLY (
select TOP 1 id, created, AppUser_Id
from EO_Applications d
WHERE d.AppUser_id = u.id
order by Created desc ) a
CREATE TABLE [dbo].[EO_AppUsers] (
[Id] NVARCHAR (128) NOT NULL
CREATE TABLE [dbo].[EO_Policies] (
[Id] NVARCHAR (128) NOT NULL,
[AppUser_Id] NVARCHAR (128) NULL,
CREATE TABLE [dbo].[EO_Applications] (
[Id] NVARCHAR (128) NOT NULL,
[Created] DATETIME2 (7) NOT NULL,
[AppUser_Id] NVARCHAR (128) NULL,EO_AppUsers有一个EO_Policie%s和零个或多个EO_Applications。
SQL创建一个包含所有EO_AppUsers和相关EO_Policies以及最新/最新EO_Applications的报告
下面的代码处理返回EO_AppUsers和EO_Policies，但缺少EO_Applications。它需要来自上述SQL查询的CROSS APPLY。
var q = (from u in dbContext.EO_AppUsers
join p in dbContext.EO_Policies on u.Id equals p.AppUser.Id
//join a in dbContext.EO_Applications on u.Id equals a.AppUser.Id
select new
{
/// return fields from EO_AppUsers, EO_Policies and EO_Applications
});回答 1
Stack Overflow用户
回答已采纳
发布于 2018-01-13 06:06:37
您可以尝试这个linq查询。
from u in EO_AppUsers
join p in EO_Policies on u.Id equals p.AppUser_Id into pl
from lp in pl.DefaultIfEmpty()
from a in EO_Applications.Where(d => d.AppUser_Id == u.Id)
.OrderByDescending(d => d.Created)
.Take(1)
.DefaultIfEmpty()
select new { userId = u.Id,
u.CustomerId,
appId = a.Id,
appCreated = a.Created,
polId = lp.Id
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48234022
复制相关文章
相似问题
腾讯云开发者
