Linq to Entity with multiple left outer joins

Asked 2008-09-16T23:54:57.933

I am trying to understand left outer joins in LINQ to Entity. For example I have the following 3 tables:

Company, CompanyProduct, Product

The CompanyProduct is linked to its two parent tables, Company and Product.

I want to return all of the Company records and the associated CompanyProduct whether the CompanyProduct exists or not for a given product. In Transact SQL I would go from the Company table using left outer joins as follows:

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

My database has 3 companies, and 2 CompanyProduct records assocaited with the ProductID of 14. So the results from the SQL query are the expected 3 rows, 2 of which are connected to a CompanyProduct and Product and 1 which simply has the Company table and nulls in the CompanyProduct and Product tables.

So how do you write the same kind of join in LINQ to Entity to acheive a similiar result?

I have tried a few different things but can't get the syntax correct.

Thanks.

join linq-to-entities outer-join

Edit Report

2 Answers

Solved it!

Final Output:

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

Here is the Scenario

1 - The Database

--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 - The Data

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

SELECT [id] ,[value]  FROM theProduct
id


                        
                        
                            answered 2008-10-06T18:39:27.007


            
            
                
                    
                        
                            
                            
                        
                        1
                        
                            
                            
                        
                        
                    
                    
                        
                        The normal group join represents a left outer join. Try this:

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


That example gives you all records from table1 which don't have a reference to table2.
If you omit the where sentence, you get all records of table1.

                        
                        
                            answered 2010-08-20T09:29:06.440



        
        
            Your Answer