join - Linq to Entity有多个左外部联接

我试图理解LINQ到Entity中的左外部联接。例如，我有以下3个表:

公司，公司产品，产品

CompanyProduct链接到其两个父表，Company和Product。

我想返回所有公司记录和关联的CompanyProduct，无论给定产品的CompanyProduct是否存在。在Transact SQL中，我将使用左外部联接从Company表中进行以下操作:

SELECT * FROM Company AS C
LEFT OUTER JOIN  CompanyProduct AS CP ON C.CompanyID=CP.CompanyID
LEFT OUTER JOIN  Product AS P ON CP.ProductID=P.ProductID
WHERE      P.ProductID = 14 OR P.ProductID IS NULL

我的数据库有3家公司，以及2条以ProductID为14的CompanyProduct记录。因此，SQL查询的结果是预期的3行，其中2行连接到CompanyProduct和Product，另外1行仅包含Company表和null在CompanyProduct和Product表中。

那么，您如何在LINQ中编写与实体的联接以达到相似的结果呢？

我尝试了一些不同的操作，但是语法不正确。

谢谢。

最佳答案

解决了!

最终输出:

theCompany.id: 1
theProduct.id: 14
theCompany.id: 2
theProduct.id: 14
theCompany.id: 3

这是场景

1-数据库

--Company Table
CREATE TABLE [theCompany](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [value] [nvarchar](50) NULL,
 CONSTRAINT [PK_theCompany] PRIMARY KEY CLUSTERED
( [id] ASC ) WITH (
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO


--Products Table
CREATE TABLE [theProduct](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [value] [nvarchar](50) NULL,
 CONSTRAINT [PK_theProduct] PRIMARY KEY CLUSTERED
( [id] ASC
) WITH (
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO


--CompanyProduct Table
CREATE TABLE [dbo].[CompanyProduct](
    [fk_company] [int] NOT NULL,
    [fk_product] [int] NOT NULL
) ON [PRIMARY];
GO

ALTER TABLE [CompanyProduct]  WITH CHECK ADD CONSTRAINT
    [FK_CompanyProduct_theCompany] FOREIGN KEY([fk_company])
    REFERENCES [theCompany] ([id]);
GO

ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT
    [FK_CompanyProduct_theCompany];
GO

ALTER TABLE [CompanyProduct]  WITH CHECK ADD CONSTRAINT
    [FK_CompanyProduct_theProduct] FOREIGN KEY([fk_product])
 REFERENCES [dbo].[theProduct] ([id]);
GO

ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT
    [FK_CompanyProduct_theProduct];

2-数据

SELECT [id] ,[value] FROM theCompany
id          value
----------- --------------------------------------------------
1           company1
2           company2
3           company3

SELECT [id] ,[value]  FROM theProduct
id          value
----------- --------------------------------------------------
14          Product 1


SELECT [fk_company],[fk_product] FROM CompanyProduct;
fk_company  fk_product
----------- -----------
1           14
2           14

3-VS.NET 2008中的实体

alt text http://i478.photobucket.com/albums/rr148/KyleLanser/companyproduct.png
实体容器名称为“testEntities”(在模型“属性”窗口中看到)

4-代码(最终!)

testEntities entity = new testEntities();

var theResultSet = from c in entity.theCompany
select new { company_id = c.id, product_id = c.theProduct.Select(e=>e) };

foreach(var oneCompany in theResultSet)
{
   Debug.WriteLine("theCompany.id: " + oneCompany.company_id);
    foreach(var allProducts in oneCompany.product_id)
    {
        Debug.WriteLine("theProduct.id: " + allProducts.id);
    }
}

5-最终输出o​​jit_rstrong

theCompany.id: 1
theProduct.id: 14
theCompany.id: 2
theProduct.id: 14
theCompany.id: 3