LINQ Join query (with nullable ref between table)

Question

I have got 3 table.

For instance Client, Company and Address.

Client has got ref to Company.
Company has got 2 nullable refs to Address (Billing and Shipping), so Address may not exist in some case.

I need make join query, but in case when Company.BillingAddress or Company.ShippingAddress equals null, I don't get all data).

I tried it (but it's wrong query):

var res = (from client in context.Clients
    join clientCompany in context.Companies 
    on client.ClientCompanyId equals clientCompany.Id

    into clientCompanyJoin

    from company in clientCompanyJoin
    join addressBilling in context.Addresses
    on company.BillingAddressId equals addressBilling.Id

    join addressShipping in context.Addresses
    on company.ShippingAddressId equals addressShipping.Id

    select new
    {
        Client = client,
        Company = company,
        BillingAddress = ???????
        ShippingAddress = ???????
    }
);

Could you please help me to make a join query or explain how to do it?

Thanks.

Join what to what? What should be the structure of the returned type? Can you post some code or some pseudo-code? — LightStriker, Nov 12 '12 at 10:19

Furqan Safdar · Accepted Answer · 2012-11-13T11:02:31.537

Try this piece of code snippet:

var res = (from client in context.Clients
            join clientCompany in context.Companies 
            on client.ClientCompanyId equals clientCompany.Id
            into clientCompanyJoin
            from company in clientCompanyJoin
            join addressBilling in context.Addresses
            on company.BillingAddressId equals addressBilling.Id
            where !String.IsNullOrEmpty(addressBilling.Address)
            join addressShipping in context.Addresses
            on company.ShippingAddressId equals addressShipping.Id
            where !String.IsNullOrEmpty(addressShipping.Address)
            select new
            {
                Client = client,
                Company = company,
                BillingAddress = addressBilling.Address,
                ShippingAddress = addressShipping.Address
            });

ADDED: As per your comments, here is the piece of code snippet you need. You can now have your Client and Company data even if ShippingAddressId or BillingAddressId equal null like what Left Join do in SQL.

var res = (from client in context.Clients
            join company in context.Companies 
            on client.ClientCompanyId equals company.Id
            join addressBilling in context.Addresses
            on company.BillingAddressId equals addressBilling.Id 
            into addrBillingGroup
            from gAddrBilling in addrBillingGroup.DefaultIfEmpty() // left join
            join addressShipping in context.Addresses
            on company.ShippingAddressId equals addressShipping.Id 
            into addrShippingGroup
            from gAddrShipping in addrShippingGroup.DefaultIfEmpty() // left join
            select new
            {
                Client = client,
                Company = company,
                BillingAddress = 
                    gAddrBilling == null ? null : gAddrBilling.Address,
                ShippingAddress = 
                    gAddrShipping == null ? null : gAddrShipping.Address
            });

it's does not work. I don't understand some things (for instance where !String.IsNullOrEmpty(addressBilling.Address)) and addressBilling does not contain Address field. — Serhii Kyslyi, Nov 12 '12 at 10:44
I think problem in these places "on company.BillingAddressId equals addressBilling.Id" because in some case BillingAddressId and ShippingAddressId equal NULL — Serhii Kyslyi, Nov 12 '12 at 10:45
Do you want to filter the records where BillingAddressId and ShippingAddressId equal NULL? Secondly, if BillingAddressId and ShippingAddressId is of integer type then how can it be NULL? — Furqan Safdar, Nov 12 '12 at 11:20
Yes, if ShippingAddressId or BillingAddressId equal null, I still need the Customer and Company data, but without Addresses (it should be null as left join result in sql). — Serhii Kyslyi, Nov 12 '12 at 11:36
In my model ShippingAddressId and BillingAddressId are nullable int fields. — Serhii Kyslyi, Nov 12 '12 at 11:36

score 2 · Answer 2 · answered Nov 12 '12 at 10:26

I guess you want to make an outer join. Here is an example how to do it on the "Northwind" database for customers and orders:

var ctx = new NorthwindDataContext();
var customers = from c in ctx.Customers
    join o in ctx.Orders
    on c.CustomerID equals o.CustomerID into inJoin
    from outJoin in inJoin.DefaultIfEmpty()
    orderby c.CustomerID, outJoin.OrderID
    select new
    {
        c.CustomerID,
        c.CompanyName,
        OrderID = (int?)outJoin.OrderID,
        OrderDate = (DateTime?)outJoin.OrderDate
    };

LINQ Join query (with nullable ref between table)

2 Answers2

Linked