How to implement a left join with some condition on second table in EF / EF Core?

Question

How to select data from table A (whole rows) join with table B when B has a Where clause?

What I need exactly is like this SQL code:

select * from HISBaseInsurs i left join (select * from HISBaseCenterCodeSends h where h.ServiceGroupID = 4 and h.CenterCode = 2) s on i.ID = s.InsurID

Result:

ID          Name                                               ID          CenterCode  ServiceGroupID InsurID     CodeSend        WebServiceAddress                                                                                    WebServicePassword                                 WebServiceUserName
----------- -------------------------------------------------- ----------- ----------- -------------- ----------- --------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           a                                                  2           2           4              1           asd6541         www.x.com                                                                                            23d                                                asda
2           b                                                  NULL        NULL        NULL           NULL        NULL            NULL                                                                                                 NULL                                               NULL
3           c                                                  NULL        NULL        NULL           NULL        NULL            NULL                                                                                                 NULL                                               NULL
4           d                                                  NULL        NULL        NULL           NULL        NULL            NULL                                                                                                 NULL                                               NULL

Now I want to have these like a list of entities. What I've done is:

list = HISBaseInsurs.Include(s => s.CenterCodeSends.Where(x => x.Center.CenterCode == 2 && x.ServiceGroup.ID == 4)).ToList();

But this solution has an exception. The exception message is:

The Include property lambda expression 's => {from HISBaseCenterCodeSend x in s.CenterCodeSends where (([x].Center.CenterCode == 2) AndAlso ([x].ServiceGroup.ID == 4)) select [x]}' is invalid. The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. '(Derived d) => d.MyProperty'. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

How I can fix this?

first of all your sql query can be written like select * from HISBaseInsurs i left join HISBaseCenterCodeSends h on s on i.ID = h.InsurID where h.ServiceGroupID = 4 and h.CenterCode = 2 and second see these stackoverflow.com/a/23558389/2343086 and stackoverflow.com/a/3413698/2343086 — abdul qayyum
@abdulqayyum As I said I want to select all rows of table A. but selection that you've written only select a row that accepts the condition. — Shaho

Moho Moho · Accepted Answer · 2019-02-20T21:28:53

Something like:

var filteredCenterCodeSends = dbContext.HISBaseCenterCodeSends
    .Include( ccs => ccs.Insur ) // assuming navigation property name
    .Where( ccs => 
        ccs.Center.CenterCode == 2 
        && ccs.ServiceGroup.ID == 4 );
        // if ccs.Insur/ID is nullable, also add `&& ccs.Insur != null`

var insurersWithFilteredCcs = dbContext.HISBaseInsurs
    .GroupJoin( filteredCenterCodeSends,
        insr => insr,
        ccs => ccs.Insur,
        (insr, ccsCollection) =>
            new 
            {
                Insur = insr,
                FilteredCenterCodeSends = ccsCollection,
            } );

How to implement a left join with some condition on second table in EF / EF Core?

1 Answers