join - right - 具有多個左外連接的Linq to Entity




linq right join (5)

LEFT OUTER JOIN通過使用Entity Framework中的GroupJoin完成:

msdn.microsoft.com/en-us/library/bb896266.aspx

我試圖了解LINQ to Entity中的左外連接。 例如,我有以下3個表:

公司,公司產品,產品

CompanyProduct鏈接到其兩個父表Company和Product。

我想要返回所有公司記錄和關聯的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個CompanyProduct記錄與ProductID 14相關聯。因此,SQL查詢的結果是預期的3行,其中2行連接到CompanyProduct和Product,1個只有Company表和null在CompanyProduct和Product表中。

那麼如何在LINQ to Entity中編寫相同類型的連接來實現類似的結果呢?

我嘗試了一些不同的東西,但無法正確的語法。

謝謝。


它應該是這樣的......

var query = from t1 in db.table1
    join t2 in db.table2
    on t1.Field1 equals t2.field1 into T1andT2
    from t2Join in T1andT2.DefaultIfEmpty()


    join t3 in db.table3
    on t2Join.Field2 equals t3.Field3 into T2andT3
    from t3Join in T2andT3.DefaultIfEmpty()
    where t1.someField = "Some value" 
    select 
    {
        t2Join.FieldXXX
        t3Join.FieldYYY


    };

這就是我做的....


正常組連接表示左外連接。 嘗試這個:

var list = from a in _datasource.table1
           join b in _datasource.table2
           on a.id equals b.table1.id
           into ab
           where ab.Count()==0
           select new { table1 = a, 
                        table2Count = ab.Count() };

該示例為您提供了table1中沒有對table2的引用的所有記錄。 如果省略where句子,則獲得table1所有記錄。


解決了!

最終產出:

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中的實體

替代文字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 - 最終輸出

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

那個怎麼樣(你的實體設計師在公司和產品之間確實存在多對多的關係,不是嗎?):

from s in db.Employees
where s.Product == null || s.Product.ProductID == 14
select s;

實體框架應該能夠確定要使用的連接類型。