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

<p>Company, CompanyProduct, Product</p>

<p>The CompanyProduct is linked to its two parent tables, Company and Product.</p>

<p>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: </p>

<pre><code>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
</code></pre>

<p>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. </p>

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

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

<p>Thanks.</p>

[{'answer_id': 78714, 'author': 'dimarzionist', 'author_id': 10778, 'author_profile': 'https://Stackoverflow.com/users/10778', 'pm_score': 0, 'selected': False, 'text': '<p>Please try something like this:</p>\n\n<pre><code>from s in db.Employees\njoin e in db.Employees on s.ReportsTo equals e.EmployeeId\njoin er in EmployeeRoles on s.EmployeeId equals er.EmployeeId\njoin r in Roles on er.RoleId equals r.RoleId\nwhere e.EmployeeId == employeeId &amp;&amp;\ner.Status == (int)DocumentStatus.Draft\nselect s;\n</code></pre>\n\n<p>Cheers!</p>\n'}, {'answer_id': 175482, 'author': 'liggett78', 'author_id': 19762, 'author_profile': 'https://Stackoverflow.com/users/19762', 'pm_score': 0, 'selected': False, 'text': "<p>What about this one (you do have a many-to-many relationship between Company and Product in your Entity Designer, don't you?):</p>\n\n<pre><code>from s in db.Employees\nwhere s.Product == null || s.Product.ProductID == 14\nselect s;\n</code></pre>\n\n<p>Entity Framework should be able to figure out the type of join to use.</p>\n"}, {'answer_id': 175636, 'author': 'KyleLanser', 'author_id': 12923, 'author_profile': 'https://Stackoverflow.com/users/12923', 'pm_score': 4, 'selected': False, 'text': '<p>Solved it!</p>\n\n<p><strong>Final Output:</strong></p>\n\n<pre><code>theCompany.id: 1  \ntheProduct.id: 14  \ntheCompany.id: 2  \ntheProduct.id: 14  \ntheCompany.id: 3  \n</code></pre>\n\n<hr>\n\n<p><strong>Here is the Scenario</strong></p>\n\n<p><strong>1 - The Database</strong></p>\n\n<pre class="lang-sql prettyprint-override"><code>--Company Table\nCREATE TABLE [theCompany](\n    [id] [int] IDENTITY(1,1) NOT NULL,\n    [value] [nvarchar](50) NULL,\n CONSTRAINT [PK_theCompany] PRIMARY KEY CLUSTERED \n( [id] ASC ) WITH (\n    PAD_INDEX  = OFF, \n    STATISTICS_NORECOMPUTE  = OFF, \n    IGNORE_DUP_KEY = OFF, \n    ALLOW_ROW_LOCKS  = ON, \n    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\n) ON [PRIMARY];\nGO\n\n\n--Products Table\nCREATE TABLE [theProduct](\n    [id] [int] IDENTITY(1,1) NOT NULL,\n    [value] [nvarchar](50) NULL,\n CONSTRAINT [PK_theProduct] PRIMARY KEY CLUSTERED \n( [id] ASC\n) WITH (    \n    PAD_INDEX  = OFF, \n    STATISTICS_NORECOMPUTE  = OFF, \n    IGNORE_DUP_KEY = OFF, \n    ALLOW_ROW_LOCKS  = ON, \n    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\n) ON [PRIMARY];\nGO\n\n\n--CompanyProduct Table\nCREATE TABLE [dbo].[CompanyProduct](\n    [fk_company] [int] NOT NULL,\n    [fk_product] [int] NOT NULL\n) ON [PRIMARY];    \nGO\n\nALTER TABLE [CompanyProduct]  WITH CHECK ADD CONSTRAINT\n    [FK_CompanyProduct_theCompany] FOREIGN KEY([fk_company]) \n    REFERENCES [theCompany] ([id]);\nGO\n\nALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT \n    [FK_CompanyProduct_theCompany];\nGO\n\nALTER TABLE [CompanyProduct]  WITH CHECK ADD CONSTRAINT \n    [FK_CompanyProduct_theProduct] FOREIGN KEY([fk_product]) \n REFERENCES [dbo].[theProduct] ([id]);\nGO\n\nALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT \n    [FK_CompanyProduct_theProduct];\n</code></pre>\n\n<p><strong>2 - The Data</strong></p>\n\n<pre><code>SELECT [id] ,[value] FROM theCompany\nid          value\n----------- --------------------------------------------------\n1           company1\n2           company2\n3           company3\n\nSELECT [id] ,[value]  FROM theProduct\nid          value\n----------- --------------------------------------------------\n14          Product 1\n\n\nSELECT [fk_company],[fk_product] FROM CompanyProduct;\nfk_company  fk_product\n----------- -----------\n1           14\n2           14\n</code></pre>\n\n<p><strong>3 - The Entity in VS.NET 2008</strong>  </p>\n\n<p><a href="http://i478.photobucket.com/albums/rr148/KyleLanser/companyproduct.png" rel="nofollow noreferrer">alt text http://i478.photobucket.com/albums/rr148/KyleLanser/companyproduct.png</a><br>\nThe Entity Container Name is \'testEntities\' (as seen in model Properties window)  </p>\n\n<p><strong>4 - The Code (FINALLY!)</strong></p>\n\n<pre class="lang-c# prettyprint-override"><code>testEntities entity = new testEntities();\n\nvar theResultSet = from c in entity.theCompany\nselect new { company_id = c.id, product_id = c.theProduct.Select(e=&gt;e) };\n\nforeach(var oneCompany in theResultSet)\n{\n   Debug.WriteLine("theCompany.id: " + oneCompany.company_id);\n    foreach(var allProducts in oneCompany.product_id)\n    {\n        Debug.WriteLine("theProduct.id: " + allProducts.id);\n    }\n}\n</code></pre>\n\n<p><strong>5 - The Final Output</strong></p>\n\n<pre><code>theCompany.id: 1  \ntheProduct.id: 14  \ntheCompany.id: 2  \ntheProduct.id: 14  \ntheCompany.id: 3  \n</code></pre>\n'}, {'answer_id': 1511650, 'author': 'StriplingWarrior', 'author_id': 120955, 'author_profile': 'https://Stackoverflow.com/users/120955', 'pm_score': 3, 'selected': False, 'text': '<p>You\'ll want to use the Entity Framework to set up a many-to-many mapping from Company to Product.  This will use the CompanyProduct table, but will make it unnecessary to have a CompanyProduct entity set in your entity model.  Once you\'ve done that, the query will be very simple, and it will depend on personal preference and how you want to represent the data.  For example, if you just want all the companies who have a given product, you could say:</p>\n\n<pre><code>var query = from p in Database.ProductSet\n            where p.ProductId == 14\n            from c in p.Companies\n            select c;\n</code></pre>\n\n<p>or</p>\n\n<pre><code>var query = Database.CompanySet\n            .Where(c =&gt; c.Products.Any(p =&gt; p.ProductId == 14));\n</code></pre>\n\n<p>Your SQL query returns the product information along with the companies.  If that\'s what you\'re going for, you might try:</p>\n\n<pre><code>var query = from p in Database.ProductSet\n            where p.ProductId == 14\n            select new\n            {\n                Product = p,\n                Companies = p.Companies\n            };\n</code></pre>\n\n<p>Please use the "Add Comment" button if you would like to provide more information, rather than creating another answer.</p>\n'}, {'answer_id': 3529660, 'author': 'Martin', 'author_id': 426177, 'author_profile': 'https://Stackoverflow.com/users/426177', 'pm_score': 1, 'selected': False, 'text': "<p>The normal group join represents a left outer join. Try this:</p>\n\n<pre><code>var list = from a in _datasource.table1\n           join b in _datasource.table2\n           on a.id equals b.table1.id\n           into ab\n           where ab.Count()==0\n           select new { table1 = a, \n                        table2Count = ab.Count() };\n</code></pre>\n\n<p>That example gives you all records from <code>table1</code> which don't have a reference to <code>table2</code>.\nIf you omit the <code>where</code> sentence, you get all records of <code>table1</code>.</p>\n"}, {'answer_id': 4054699, 'author': 'Mitch', 'author_id': 491618, 'author_profile': 'https://Stackoverflow.com/users/491618', 'pm_score': 2, 'selected': False, 'text': '<p>LEFT OUTER JOINs are done by using the GroupJoin in Entity Framework:</p>\n\n<p><a href="http://msdn.microsoft.com/en-us/library/bb896266.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/bb896266.aspx</a></p>\n'}, {'answer_id': 5351728, 'author': 'Deepak ', 'author_id': 665993, 'author_profile': 'https://Stackoverflow.com/users/665993', 'pm_score': 3, 'selected': False, 'text': '<p>IT should be something like this....</p>\n\n<pre><code>var query = from t1 in db.table1\n    join t2 in db.table2\n    on t1.Field1 equals t2.field1 into T1andT2\n    from t2Join in T1andT2.DefaultIfEmpty()\n\n\n    join t3 in db.table3\n    on t2Join.Field2 equals t3.Field3 into T2andT3\n    from t3Join in T2andT3.DefaultIfEmpty()\n    where t1.someField = "Some value" \n    select \n    {\n        t2Join.FieldXXX\n        t3Join.FieldYYY\n\n\n    };\n</code></pre>\n\n<p>This is how I did....</p>\n'}]
['https://Stackoverflow.com/questions/78468', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']