LINQ Join查询(表之间具有可为空的ref) [英] LINQ Join query (with nullable ref between table)

问题描述

我有3张桌子.

例如客户，公司和地址.

• 客户获得了公司的推荐.

• Client has got ref to Company.

公司对Address(开票和运输)有2个可为空的引用，因此在某些情况下Address可能不存在.

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

我需要进行连接查询，但是如果Company.BillingAddress或Company.ShippingAddress等于null，我不会得到所有数据.

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?

谢谢.

推荐答案

尝试以下代码段:

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

已添加:根据您的评论，这是您需要的一段代码.现在，即使ShippingAddressId或BillingAddressId等于null，就像SQL在SQL中一样，您也可以拥有客户和公司数据.

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

这篇关于LINQ Join查询(表之间具有可为空的ref)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！