Skip to content

Codepolice

  • ⤫

Join and Group By with LINQ to Entities

Posted by Judy Alvarez Posted on February 25, 2022March 3, 2022
0

I have banged my head against some LINQ to Entities stuff today. It started out when I was doing a query to get some stats out of my forum. I should point out that I’m a newbe with LINQ to Entities and the Entity Framework so if I have done anything stupid here you are free to mock me in the comments.

var query = (from s in db.ForumStatsSet
where s.LogDate >= date1 && s.LogDate <= date2
group s by new {
  s.Topic.topicID, s.Topic.subject,
  s.Topic.datum, s.Topic.Forum.forumID,
  s.Topic.Forum.forumName,
  s.Topic.Forum.ForumGroup.name } into g
orderby g.Count() descending
select new TopicStatsData
  {
  TopicId = g.Key.topicID,
  Count = g.Count(),
  Subject = g.Key.subject,
  ForumId = g.Key.forumID,
  ForumName = g.Key.forumName,
  ForumGroupName = g.Key.name
});

This code looked fine to me. It’s kind of massive and has a lot of joins but it should be used in an admin interface so i did not care that much. But it turned out that it timeout every time I ran it. I had a look at the SQL that was generated and that was not a pretty sight. A massive amount of subqueries and left outer joins.

I posted a question on the new and really really good code community Stackoverflow and got the tip that I should join the tables before group by clue. And that is what I tried to do.

The problem was that I got this error all the time.

from s in DB.ForumStatsSet
from t in s.Topic

Error:
An expression of type ‘WhoaAdmin.Models.Topic’ is not allowed in a subsequent from a clause in a query expression with source type ‘System.Data.Objects.ObjectQuery<WhoaAdmin.Models.ForumStats>’. Type inference failed in the call to ‘SelectMany’.

I tried to google that error and I got nothing. I realized that maybe you have to do the joins in the correct order and that was it and I ended up with this query.

var query = (from fg in db.ForumGroupSet
from f in fg.Forums
from t in f.Topics
from s in t.ForumStats
where s.LogDate >= date1 && s.LogDate <= date2
group s by new { t.topicID,
  t.subject,
  t.datum,
  f.forumID,
  f.forumName,
  fg.name } into g
orderby g.Count() descending
select new TopicStatsData
{
 TopicId = g.Key.topicID,
 Count = g.Count(),
 Subject = g.Key.subject,
 ForumId = g.Key.forumID,
 ForumName = g.Key.forumName,
 ForumGroupName = g.Key.name
});

The SQL I ended up with looks kind of awful but probably the SQL engine does something like this “under the hood” on regular SQL group by to. At least it is kind of fast. The big difference in the generated SQL is that it has replaced all “LEFT OUTER JOINS” with “INNER JOINS” and they are of course faster.

Please leave a comment.

Categories: JavascriptTagged: .net core mvc interview questions, .net core vs .net mvc, angularjs asp net mvc, asp .net core mvc, asp net core mvc modal popup partial view, asp net mvc 3, asp net mvc 3.0, asp net mvc authentication, asp net mvc crud, asp net mvc dependency injection, asp net mvc development, asp net mvc generate pdf from view, asp net mvc grid, asp net mvc interview questions and answers for experienced, asp net mvc interview questions and answers pdf, asp net mvc pdf, asp net mvc projects, asp net mvc route, asp net mvc session management, asp net mvc shopping cart example, asp net mvc tutorial step by step pdf, asp net mvc upload file, asp. net mvc interview questions, dot net mvc interview questions, hire asp .net mvc developer, mvc .net core, mvc dot net, mvc example in asp net, net mvc architecture, professional asp net mvc 5

Post navigation

Previous Previous post: Using JQuery Validation plugin with ASP.NET
Next Next post: Group By with LINQ

Related Posts

  • Playwright now offers a UI mode

    #​631 — March 24, 2023 Read on the Web JavaScript Weekly Speeding Up the JavaScript Ecosystem: npm Scripts — The latest in what has been a fascinating series on finding ‘low hanging fruit’ when it comes to performance in the JavaScript world. The author explains it best himself: “‘npm scripts’ are executed by JavaScript developers

    Posted by Posted on March 24, 2023
    0
  • Everyone’s coming for Node.js this week

    #​479 — March 23, 2023 Read on the Web 🔒  npm Granular Access Tokens Now Generally Available — The granular access token feature on the npm registry is now generally available, allowing you to restrict token access to specific packages, set expiration dates, limit access by IP range, and more. GitHub Automatic npm Publishing with GitHub

    Posted by Posted on March 23, 2023
    0
  • Transformers: JavaScript in Disguise

    #​630 — March 17, 2023 Read on the Web JavaScript Weekly 🤖  Transformers.js: Running ML Models in the Browser — Transformers are a type of machine learning model often used for natural language or visual processing and while running such models directly in the browser is in its infancy, Transformers.js opens up some ML models

    Posted by Posted on March 17, 2023
    0
  • Shell-free scripting from Node

    #​478 — March 16, 2023 Read on the Web Shell-Free Scripts with Execa 7.1 — Execa is a popular process execution library for Node and the latest version includes an interesting $ method feature for writing zx-style scripts with it, making it even more useful for shell scripting style usecases. ehmicky Turbowatch: File Change Detector and

    Posted by Posted on March 16, 2023
    0
  • New JavaScript features of the past few years

    #​629 — March 10, 2023 Read on the Web JavaScript Weekly JavaScript Features from the Past Few Years — Packed with examples, this post tackles the changes and tweaks to JavaScript and TypeScript over the past several years (some as far back as ES6/ES2015, like tagged template literals). Linus Schlumberger Astro’s 2023 Web Framework Performance Report —

    Posted by Posted on March 10, 2023
    0
  • Taking flight with Feathers 5

    #​477 — March 9, 2023 Read on the Web Feathers 5: The API and Real-Time App Framework — Feathers isn’t as well known as Nest or Fastify, say, but it’s a powerful and mature option if you want to spin up a Node CRUD app tied to a database and now it’s “TypeScript all the

    Posted by Posted on March 9, 2023
    0
Judy Alvarez

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Codepolice

  • Github
  • Atlassian
  • Flatlogic
  • Xero
  • Jetbrains
  • Figma
  • Playwright now offers a UI mode
  • Everyone’s coming for Node.js this week
  • Transformers: JavaScript in Disguise
  • Shell-free scripting from Node
  • New JavaScript features of the past few years
https://flatlogic.com/generator
COPYRIGHT © 2023 - Codepolice