Stored procedure to linq - specific SP

Question

I'm trying to convert the following SP to linq, but it's giving me different results and I can't seem to figure out why...

Here's the SP in sql:

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)

and here is my linq code:

 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
                    });

could you please help me? The two linq ones are giving me equal results, but not the same with the original sp... Thanks in advance :)

Answer 1

For Left Outer Join the trick is to use join ... into then use DefaultIfEmpty() on grouping in LINQ result:

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
                    });

And this is reference for Left Outer Join using LINQ.

Hope this helps.