LINQ Join query (with nullable ref between table)

c# entity-framework linq nullable

Question

I own three tables.

Client, Company, and Address are a few examples.

  • Client provided reference to business.

  • Due to the company's two nullable references to the address (Billing and Shipping), the address may occasionally not exist.

I must create a join query, but in the eventCompany.BillingAddress or Company.ShippingAddress equals null I don't receive all the data.

I tried it, but the answer is incorrect:

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

Would you kindly guide me through creating a join query or provide some assistance?

Thanks.

1
3
6/6/2015 11:18:59 AM

Accepted Answer

Try out the following snippet of code:

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: Here is the snippet of code you require in accordance with your remarks. Your Client and Company data are now accessible even ifShippingAddressId or BillingAddressId equal null much whatLeft Join do inSQL .

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
            });
6
11/13/2012 11:02:31 AM

Popular Answer

You must intend to create an outer join. Here is an example using the "Northwind" database for customers and orders to demonstrate how to do it:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow