CODEWITHSUNDEEP
sqlentity-frameworkentity-framework-4
ShevninAnton
ShevninAnton

Reputation: 159

How to create multiple Left Join in Entity framework

I have sql query:

FROM tbl_UploadFile AS AllFiles 
LEFT JOIN tbl_FirstFile AS firstFiles ON AllFiles.Guid=firstFiles.[FileId]
LEFT JOIN tbl_SecondFile AS secondFiles ON AllFiles.Guid=secondFiles.[FileId]
LEFT JOIN tbl_ThirdFile AS thirdFiles ON AllFiles.Guid=thirdFiles.[FileId]
WHERE firstFiles.[FileId] is NULL AND secondFiles.[FileId] is NULL AND thirdFiles.[FileId] is NULL;

This query return guid from tbl_UploadFile, if this guid doen't use on tbl_FirstFile, tbl_SecondFile and tbl_ThirdFile. It is work fine.

I try to write on Entity Framework (using GroupJoin), but I can make GroupJoin only for tbl_FirstFile:

 var unlinkedFiles = context
    .Set<DbUploadFile>()
    .GroupJoin(
    context.Set<DbFirstFile>(),
    file => file.Guid,
    clFile => clFile.FileId,
    (file, files) => new
    {enter code here
    FileGuid = file.Guid,
    Children = files.Count()
    })
    .Where(x => x.Children == 0)
    .ToList();

How write multiples GroupJoin for all tables?

Upvotes: 1

Views: 3216

Answers (1)

Arion
Arion

Reputation: 31249

I am not sure about your table names. But you could do something like this:

var result= (
        from AllFiles in context.tbl_UploadFile
        from firstFiles in context.tbl_FirstFile
            .Where(w=>AllFiles.Guid=w.FileId).DefaultIfEmpty()
        from secondFiles in context.tbl_SecondFile
            .Where(w=>AllFiles.Guid=w.FileId).DefaultIfEmpty()
        from thirdFiles in tbl_ThirdFile
            .Where(w=>AllFiles.Guid=w.FileId).DefaultIfEmpty()
        where firstFiles.FileId== null
        where secondFiles.FileId == null  
        where thirdFiles.FileId == null
        select AllFiles
    ).ToList();

It is much cleaner then doing a GroupJoin I think

Upvotes: 4

Related Questions