Databases
StackOverflow2013
Postsdbo
POLinq and EntityFramework 4 with multiple inner joins with nested subqueries
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq and EntityFramework 4 with multiple inner joins with nested subqueries
    text
    copied!<p>I'm trying to write Linq query on this Products table based on FacetTypes that need to be grouped by their Facet.</p> <p>This is the table structure: <img src="https://i.stack.imgur.com/OdDSu.png" alt="enter image description here"></p> <p>I get passed an array of facetTypeIds, say 9, 6, 52</p> <pre><code>FacetTypeId 9 has a name of "160" and is a Facet of "Size" FacetTypeId 6 has a name of "157" and is a Facet of "Size" FacetTypeId 52 has a name of "Cool Brand" and is a Facet of "Brand" </code></pre> <p>They need to be constructed into a query that joins based on the facet, like this:</p> <pre><code>select * from products p inner join (select productId from productFacets where facetTypeId in (9, 6)) p1 on p1.productId = p.productId inner join (select productId from productFacets where facetTypeId in (52)) p2 on p2.productId = p.productId </code></pre> <p>The outcome is a result set that says:</p> <p>Get me Products which have brand "Cool Brand" and Sizes of (160 or 157)</p> <p>How would I go about creating a linq query that will dynamically build this?</p> <p>I'm kinda stuck as to how this would be formed in linq.</p> <p><strong>EDIT:</strong></p> <p>This is the code I've sort of come up with however it feels pretty inefficient. </p> <pre><code>MyDbContext _context; // Groups FacetTypeIds by Facet into int lists Dictionary&lt;int, List&lt;int&gt;&gt; createFacetGroup(int[] facetTypeIds) { var facets = new Dictionary&lt;int, List&lt;int&gt;&gt;(); var facetTypes = from ft in _context.FacetTypes where facetTypeIds.Contains(ft.FacetTypeId) select ft; foreach (var facetType in facetTypes) { if (facets.ContainsKey(facetType.Facet.FacetId)) facets[facetType.Facet.FacetId].Add(facetType.FacetTypeId); else facets.Add(facetType.Facet.FacetId, new List&lt;int&gt; { facetType.FacetTypeId }); } return facets; } public List&lt;Product&gt; FindProductsByGroupedFacetTypeIds(int[] facetTypeIds) { var groupedFacetTypeIds = createFacetGroup(facetTypeIds); // this seem very inefficient but ToList needs to be called // otherwise the results products in the foreach loop dont end // up with the correct result set var products = _context.Products.ToList(); foreach (var facetTypeIdGroup in groupedFacetTypeIds) { var facetTypeIdGroupArray = facetTypeIdGroup.Value.ToArray(); products = (from p in products where p.FacetTypes.Any(x =&gt; facetTypeIdGroupArray.Contains(x.FacetTypeId)) select p).ToList(); } return products; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload