Databases
StackOverflow2013
Postsdbo
PORecommend a fitting LINQ provider to me (SQL server, complex queries)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORecommend a fitting LINQ provider to me (SQL server, complex queries)
    primarykey
    data
    text
    <p>I've been using LINQ to SQL &amp; to entities for a while and am overall very happy with them. However i know of their limitations and one in particular is becoming a large issue for me. When you do a complex nested query in the form of</p> <pre><code>MyContext.SomeTable .Select(item=&gt;new{ item.SomeProperty1, item.SomeProperty2, item.NavigationProperty1 .Select(nav1=&gt; new {// retrieve some properties}), // This triggers a single query as long as don't have more than one subquery item.NavigationProperty2 .Select(nav2=&gt; new {// retrieve some properties}) // This triggers one query PER ROW in the original query }); </code></pre> <p>The providers i have tested are LINQ TO SQL / LINQ TO entities (and even worse, devart LINQConnect that fares worse and generates 1 per row on the first navigation property)</p> <p>What i get now that is generated(pseudocode):</p> <pre><code>select t1.a,t1.b,t2.c,t2.d from mytable as t1 join navproperty1table as t2 </code></pre> <p>and 1 millions (if there is 1 million results in the first set) of queries like this: <code>select t3.e,t3.f from navproperty2table as t3 where id = X</code> (X changing on X query to next element returned by first query)</p> <p>What i want:</p> <pre><code>select t1.a,t1.b,t2.c,t2.d,t3.e,t3.f from mytable as t1 join navproperty1table as t2 join navproperty2table as t3 </code></pre> <p>Now of course if there were 3 rows in the original table it wouldn't be an issue, but i have 10s of thousands to millions of rows in my tables "and" i need a much much much more complex query in a single select (i want to get a complex graph at once). Think 20 + tables with 3-6 levels of nesting accessing an additional 2-5 tables each.</p> <p>My SQL server can perfectly cope with it, i don't care for the bandwidth either, it's on an instance linked by a gigabit connection, i can't get that data in deferred manner, i actually "use" all of it immediately so it's not just laziness. Right now for performance reasons i had to split the query in many small queries and join them manually on the LINQ to object size, which gives some really nasty code for whoever maintains it but was the only actual solution i had, so overall including all the small queries and final joining, I'm at over 600 lines of unsplitable code in a single method that is totally unmaintainable.</p> <p>Are there actually "any" LINQ providers production ready today before i go and evaluated them all that work in such a mindset or am i better off coding and commercializing my own? (I'm very surprised that they don't all work that way actually, i can't see a single instance where you'd be better off with the foreach case and the ones i've tried that claim to get rid of n+1 with loadwith, don't get rid of it as they still do n+1 queries but just batch it in a single call, 1 round trip &amp; n+1 queries isn't satisfying when 1 is 10 000 then 10 000 000 and then 10 000 000 000)</p> <ul> <li>(note that I'm speculating on what exactly triggers this, but it isn't the question, no matter what triggers this "exactly" I'm sure to hit it in my current context)</li> </ul> <p>PS: Note that I'm running .NET 4.0 full profile on a windows server 2008 or higher and on SQL server 2008 or higher, a provider that doesn't support anything else would be fine, i have zero requirements for migration, portability, lower .net versions, lower sql server support etc. Migrating to even more recent versions is an option if required. I also don't have any prerequisites for modeling or advanced features, the DB is already there, i only want to query tables, so something with no modeling / views / DML / stored procedure / functions support is fine, my one and only requirement is sensible SQL generation on complex queries and object graphs</p> <p>EDIT: for clarification here is an actual example of the issue on a DB everyone can get, adventureworks</p> <p>Querying employees for each contact</p> <pre><code>Contacts .Select(cont=&gt;new { cont.EmailAddress, cont.EmailPromotion, Employees = cont.Employees .Select(emp=&gt;new { emp.Gender, emp.HireDate }).ToList() }).ToList() </code></pre> <p>Generates</p> <pre><code>SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], ( SELECT COUNT(*) FROM [HumanResources].[Employee] AS [t2] WHERE [t2].[ContactID] = [t0].[ContactID] ) AS [value] </code></pre> <p>FROM [Person].[Contact] AS [t0] LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID] ORDER BY [t0].[ContactID], [t1].[EmployeeID]</p> <p>Now querying just vendors for each Contact Contacts .Select(cont=>new { cont.EmailAddress, cont.EmailPromotion, Vendors = cont.VendorContacts.Select(vend=>new { vend.ContactTypeID, vend.ModifiedDate }).ToList() }).ToList()</p> <p>still ok:</p> <pre><code>SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[ContactTypeID], [t1].[ModifiedDate], ( SELECT COUNT(*) FROM [Purchasing].[VendorContact] AS [t2] WHERE [t2].[ContactID] = [t0].[ContactID] ) AS [value] </code></pre> <p>FROM [Person].[Contact] AS [t0] LEFT OUTER JOIN [Purchasing].[VendorContact] AS [t1] ON [t1].[ContactID] = [t0].[ContactID] ORDER BY [t0].[ContactID], [t1].[VendorID]</p> <p>Now querying both at once (triggers X row query)</p> <pre><code>Contacts .Select(cont=&gt;new { cont.EmailAddress, cont.EmailPromotion, Employees = cont.Employees .Select(emp=&gt;new { emp.Gender, emp.HireDate }).ToList(), Vendors = cont.VendorContacts.Select(vend=&gt;new { vend.ContactTypeID, vend.ModifiedDate }).ToList() }).ToList() </code></pre> <p>Generates the ugly and slow (not pasting it all for obvious reasons but you get the point): </p> <pre><code>SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], ( SELECT COUNT(*) FROM [HumanResources].[Employee] AS [t2] WHERE [t2].[ContactID] = [t0].[ContactID] ) AS [value], [t0].[ContactID] FROM [Person].[Contact] AS [t0] LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID] ORDER BY [t0].[ContactID], [t1].[EmployeeID] GO -- Region Parameters DECLARE @x1 Int = 1 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 2 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 3 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 4 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 5 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 6 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 7 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 8 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 9 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO -- Region Parameters DECLARE @x1 Int = 10 -- EndRegion SELECT [t0].[ContactTypeID], [t0].[ModifiedDate] FROM [Purchasing].[VendorContact] AS [t0] WHERE [t0].[ContactID] = @x1 GO </code></pre> <p>What i expect / would like to see generated:</p> <pre><code>SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], [t2].[ContactTypeID], [t2].[ModifiedDate] ,[t0].[ContactID] FROM [Person].[Contact] AS [t0] LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID] LEFT OUTER JOIN [Purchasing].[VendorContact] AS [t2] ON [t2].[ContactID] = [t0].[ContactID] GO </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCœur
    1. USRonan Thibaudau
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORecommend a fitting LINQ provider to me (SQL server, complex queries)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORecommend a fitting LINQ provider to me (SQL server, complex queries)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PORecommend a fitting LINQ provider to me (SQL server, complex queries)
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODoes it actually trigger one query per row, or does it generate a correlated subquery that SQL Server might optimize out to some join-esque execution plan?
      singulars
      1. PORecommend a fitting LINQ provider to me (SQL server, complex queries)
      1. USta.speot.is
    2. COIt generates a subquery (which is fine) when there is at most 1 nav property, after that it generates an actual query "per" row , which is insane, as in select bla from t1 where id = some actual number not a condition, this line repeated 1 million times
      singulars
      1. PORecommend a fitting LINQ provider to me (SQL server, complex queries)
      1. USRonan Thibaudau
    3. COI guess you're looking for a solution that produces a query with 1 join for the first `Select()` on a navigation property, followed by 1 query per subsequent `Select()` (with a WHERE clause on the parent id)? I figure you do not want one large query with an inevitable cartesian product.
      singulars
      1. PORecommend a fitting LINQ provider to me (SQL server, complex queries)
      1. USGert Arnold
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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