StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I was that worried about the crazy SQL, I just wouldn't do any of the grouping in the database. I would first query all of the data I needed by finishing it off with a ToList() while using the Include function to load all the data in a single select.</p> <p>Here's my final result:</p> <pre><code>var list = from o in _entities.orderT.Include("personT") .Where(p => p.personT.person_id == person_id && p.personT.created >= fromTime && p.personT.created <= toTime).ToList() group o by new { o.name, o.personT.created.Year, o.personT.created.Month, o.personT.created.Day } into g orderby g.Key.name select new { g.Key, count = g.Sum(x => x.price) }; </code></pre> <p>This results in a much simpler select:</p> <pre><code>SELECT 1 AS [C1], [Extent1].[order_id] AS [order_id], [Extent1].[name] AS [name], [Extent1].[created] AS [created], [Extent1].[price] AS [price], [Extent4].[person_id] AS [person_id], [Extent4].[first_name] AS [first_name], [Extent4].[last_name] AS [last_name], [Extent4].[created] AS [created1] FROM [dbo].[orderT] AS [Extent1] LEFT OUTER JOIN [dbo].[personT] AS [Extent2] ON [Extent1].[person_id] = [Extent2].[person_id] INNER JOIN [dbo].[personT] AS [Extent3] ON [Extent1].[person_id] = [Extent3].[person_id] LEFT OUTER JOIN [dbo].[personT] AS [Extent4] ON [Extent1].[person_id] = [Extent4].[person_id] WHERE ([Extent1].[person_id] = @p__linq__1) AND ([Extent2].[created] >= @p__linq__2) AND ([Extent3].[created] <= @p__linq__3) </code></pre> <p>Additionally, with the example data provided, SQL Profiler only notices a 3 ms increase in duration of the SQL call.</p> <p>Personally, I think that anyone that whines about not liking the output SQL of an ORM layer should go back to using Stored Procedures and Datasets. They simply aren't ready to evolve yet, and need to spend a few more years in the proverbial oven. :)</p>

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