c# - 存储过程到 linq - 特定的 SP

Question

我正在尝试将以下 SP 转换为 linq，但它给了我不同的结果，我似乎无法弄清楚为什么......

这是sql中的SP：

ALTER PROCEDURE [dbo].[XPTO]
    @resourceName varchar(200),
    @resourceType varchar(10),
    @culture varchar(2),
    @restricted bit,
    @draft bit = 0,
    @tenantId bigint
AS
    select *,
    resourceDocument.resourceDocument as Document   
from 
    Resource inner join
    ResourceType on Resource.resourceTypeId = ResourceType.resourceTypeId left outer join
    ResourceDocument on resourceDocument.resourceId = Resource.resourceId
    left outer join [language] as l on l.languageId=resourceDocument.languageId
where 
    Resource.tenantId=@tenantId and
    Resource.name = @resourceName and ResourceType.abbreviation = @resourceType and
    ((@restricted = 1 and Resource.accessLevel = 2) or @restricted <> 1) and
    (@culture is null or l.abbreviation = @culture)

这是我的 linq 代码：

 var stuff = from res in db.Resource
                            join resType in db.ResourceType
                            on res.Type.Id equals resType.Id
                            join resDocument in db.ResourceDocument.DefaultIfEmpty()
                            on res.Id equals resDocument.Resource.Id
                            join lang in db.Languages.DefaultIfEmpty()
                            on resDocument.Language.Id equals lang.Id
                            where res.Tenant.Id == tenantId
                            && res.Name == resourceName
                            && resType.Abbreviation == resourceType
                            && ((restricted == true && res.AccessLevel.Id == 2) || restricted != true)
                            && culture == null || lang.Abbreviation == culture
                            select new
                            {
                                res,
                                resType,
                                resDocument,
                                lang,
                                Document = resDocument.Resource.ResourceDocument
                            };

    var lambda = db.Resource
                    .Join(db.ResourceType, a => a.Type.Id, b => b.Id,
                    (a, b) => new { a, b })
                    .Join(db.ResourceDocument, c => c.a.Id, d => d.Resource.Id,
                    (c, d) => new { c, d }).DefaultIfEmpty()
                    .Join(db.Languages, e => e.d.Language.Id, f => f.Id,
                    (e, f) => new { e, f }).DefaultIfEmpty()
                    .Where(x => x.e.c.a.Tenant.Id == tenantId
                        && x.e.c.a.Name == resourceName
                        && x.e.c.a.Type.Abbreviation == resourceType
                        && ((restricted == true && x.e.c.a.AccessLevel.Id == 2) || restricted != true)
                        && culture == null || x.f.Abbreviation == culture)
                    .Select(m => new
                    {
                        m.e.c.a,
                        m.e.c,
                        m.e,
                        Document = m.e.c.a.ResourceDocument
                    });

请你帮助我好吗？这两个 linq 给了我相同的结果，但与原来的 sp 不一样......在此先感谢 :)

标签： c#sqllinq

Answer 1

Left Outer Join诀窍是在结果中使用join ... into thenDefaultIfEmpty()分组LINQ：

var stuff = from res in db.Resource
                    join resType in db.ResourceType
                    on res.Type.Id equals resType.Id
                    join resDocument in db.ResourceDocument
                    on res.Id equals resDocument.Resource.Id into resourceGroup
                    from resGroup in resourceGroup.DefaultIfEmpty()
                    join lang in db.Languages
                    on resDocument.Language.Id equals lang.Id into languageGroup
                    from langGroup in languageGroup.DefaultIfEmpty()
                    where res.Tenant.Id == tenantId
                    && res.Name == resourceName
                    && resType.Abbreviation == resourceType
                    && ((restricted == true && res.AccessLevel.Id == 2) || restricted != true)
                    && culture == null || lang.Abbreviation == culture
                    .Select(m => new
                    {
                        //select what you need here
                    });

这是使用LINQ的参考。Left Outer Join

希望这可以帮助。