Databases
StackOverflow2013
Postsdbo
POHow to write this SQL query in Entity Framework?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to write this SQL query in Entity Framework?
    primarykey
    data
    text
    <p>I have this query that I want translated pretty much 1:1 from Entity Framework to SQL:</p> <pre><code>SELECT GroupId, ItemId, count(*) as total FROM [TESTDB].[dbo].[TestTable] WHERE GroupId = '64025' GROUP BY GroupId, ItemId ORDER BY GroupId, total DESC </code></pre> <p>This SQL query should sort based on the number occurrence of the same <code>ItemId</code> (for that group).</p> <p>I have this now:</p> <pre><code>from x in dataContext.TestTable.AsNoTracking() where x.GroupId = 64025 group x by new {x.GroupId, x.ItemId} into g orderby g.Key.GroupId, g.Count() descending select new {g.Key.GroupId, g.Key.ItemId, Count = g.Count()}; </code></pre> <p>But this generates the following SQL code:</p> <pre><code>SELECT [GroupBy1].[K1] AS [GroupId], [GroupBy1].[K2] AS [ItemId], [GroupBy1].[A2] AS [C1] FROM ( SELECT [Extent1].[GroupId] AS [K1], [Extent1].[ItemId] AS [K2], COUNT(1) AS [A1], COUNT(1) AS [A2] FROM [dbo].[TestTable] AS [Extent1] WHERE 64025 = [Extent1].[GroupId] GROUP BY [Extent1].[GroupId], [Extent1].[ItemId] ) AS [GroupBy1] ORDER BY [GroupBy1].[K1] ASC, [GroupBy1].[A1] DESC </code></pre> <p>This also works but is a factor 2 slower than the SQL I created.</p> <p>I've been fiddling around with the linq code for a while but I haven't managed to create something similar to my query.</p> <p>Execution plan (only the last two items, the first two are identical):</p> <pre><code>FIRST: |--Stream Aggregate(GROUP BY:([Extent1].[ItemId]) DEFINE:([Expr1006]=Count(*), [Extent1].[GroupId]=ANY([TESTDB].[dbo].[TestTable].[GroupId] as [Extent1].[GroupId]))) |--Index Seek(OBJECT:([TESTDB].[dbo].[TestTable].[IX_Group]), SEEK:([TESTDB].[dbo].[TestTable].[GroupId]=(64034)) ORDERED FORWARD) SECOND: |--Stream Aggregate(GROUP BY:([TESTDB].[dbo].[TestTable].[ItemId]) DEFINE:([Expr1007]=Count(*), [TESTDB].[dbo].[TestTable].[GroupId]=ANY([TESTDB].[dbo].[TestTable].[GroupId]))) |--Index Seek(OBJECT:([TESTDB].[dbo].[TestTable].[IX_Group] AS [Extent1]), SEEK:([Extent1].[GroupId]=(64034)) ORDERED FORWARD) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThe Cookies Dog
    1. USThe Cookies Dog
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to write this SQL query in Entity Framework?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to write this SQL query in Entity Framework?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy do you not use the TOP functionality in LINQ? In Lambdas at least it exists (top, skip functions). Your speed difference is pulling all elements - instead of only top 1000 - possibly. EF version? They work on making better SQL.
      singulars
      1. POHow to write this SQL query in Entity Framework?
      1. USTomTom
    2. CO@ TomTom, I know it exists, I need all data but the query with TOP was just a quick test from myself. I removed it from the question so please ignore :)
      singulars
      1. POHow to write this SQL query in Entity Framework?
      1. USThe Cookies Dog
    3. COHm, smells like bad SQL - which EF is known for. Not sure a lot can be done. Which EF Version you use? it gets better with 5 adand mabe 6, otherwise you can ask on the EF side on codeplex and the develoeprs may jump in.
      singulars
      1. POHow to write this SQL query in Entity Framework?
      1. USTomTom
 

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