{"id":744,"date":"2012-05-22T01:10:43","date_gmt":"2012-05-21T15:10:43","guid":{"rendered":"http:\/\/ntsblog.homedev.com.au\/?p=744"},"modified":"2013-02-03T00:31:52","modified_gmt":"2013-02-02T13:31:52","slug":"linq-entity-join-query-entity-framework","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2012\/05\/22\/linq-entity-join-query-entity-framework\/","title":{"rendered":"Linq for Entity Join Query &#8211; Entity Framework"},"content":{"rendered":"<div id=\"ntsbl-534503728\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><h2>The Problem<\/h2>\n<p>You have a parent\/child table structure in your database that looks like the table structure outlined below.<\/p>\n<p>You have used the EntityFramework or Linq to SQL to build your data access layer.<\/p>\n<p>Now you want to write a method using linq as follows:<\/p>\n<p>[csharp]<br \/>\npublic List&lt;Child&gt; GetChildren(string parentName)<br \/>\n[\/csharp]<\/p>\n<h2>The database<\/h2>\n<p>[sql]<br \/>\nCreate Table Parent (<br \/>\n  Id int identity(1,1) NOT NULL,<br \/>\n  ParentName varchar(50) NOT NULL,<br \/>\n  CONSTRAINT PK_Parent Primary Key Clustered (<br \/>\n    Id Asc<br \/>\n  )<br \/>\n);<\/p>\n<p>Create Table Child (<br \/>\n   Id int identity(1,1) NOT NULL,<br \/>\n   ParentId int NOT NULL,<br \/>\n   ChildName varchar(50) NOT NULL,<br \/>\n   CONSTRAINT PK_Child Primary Key Clustered (<br \/>\n    Id Asc<br \/>\n  )<br \/>\n)<br \/>\n[\/sql]<\/p>\n<p>Obviously I would have foreign keys between parent and child.. you get the idea, this is your basic run of the mill one to many, parent child relationship.<\/p>\n<h2>In SQL<\/h2>\n<p>To get a list of all the children in sql your query would look something like:<br \/>\n[sql]<br \/>\nselect c.*<br \/>\nfrom Parent p<br \/>\n    inner join Child c on c.ParentId = p.Id<br \/>\nWhere p.ParentName = @searchString &#8212; where @searchString is a variable<br \/>\n[\/sql]<\/p>\n<h2>The Method<\/h2>\n<p>I looked around the web to find a simple example of how to do this and I could not find one, that explained how to do an easy join syntax between two tables.<\/p>\n<p>After much trial and error I came up with this:<\/p>\n<p>[csharp]<\/p>\n<p>public List&lt;Child&gt; GetChildren(string parentName)<br \/>\n{<br \/>\n    List&lt;Child&gt; children = null;<\/p>\n<p>    using (FrameworkDb dbContext = new FrameworkDb())<br \/>\n    {<br \/>\n        children = (from c in dbContext.Children<br \/>\n                    join p in dbContext.Parent.Where(p =&gt; p.ParentName == parentName)<br \/>\n                    on new { PID = c.ParentId } equals new { PID = p.Id }<br \/>\n                    select c).ToList();<br \/>\n    }<\/p>\n<p>    return children;<br \/>\n}<\/p>\n<p>[\/csharp]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Problem You have a parent\/child table structure in your database that looks like the table structure outlined below. You have used the EntityFramework or [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5,6],"tags":[],"class_list":["post-744","post","type-post","status-publish","format-standard","hentry","category-c","category-code"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=744"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/744\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}