How to write this SQL query as a LINQ statement in .NET Core (C#)?

Question

I'm trying to join three tables together, but I just can't get my LINQ statements to get the data I want to. My SQL query that I want to replicate is like this:

SELECT TOP 5 SUM(Grade) AS 'TotalGrade', COUNT(Restaurants.Name) AS 'NumberOfVisits', Restaurants.Name FROM Lunches
  JOIN dbo.LunchRestaurant ON Lunches.LunchId = LunchRestaurant.LunchId
  JOIN Restaurants ON Restaurants.RestaurantId = LunchRestaurant.RestaurantId
  GROUP BY Restaurants.Name

The LINQ statement I currently have is:

    var q = from l in lunchContext.Lunches
            join lr in lunchContext.LunchRestaurant on l.LunchId equals lr.LunchId
            join r in lunchContext.Restaurants on lr.RestaurantId equals r.RestaurantId
            select new { l.Grade, r.RestaurantId};

But with this one, I can't get in my group by statement or the aggregate functions no matter how I try. Do you have any suggestions on what to do? There also doesn't seem to be a way to write raw SQL statements when dealing with several tables, at least not easily done in EF Core if I'm not mistaken. Otherwise that'd be an option too.

Use navigation properties, no joins.

Gert Arnold
– Gert Arnold

2020-03-29 12:02:48 +00:00
Commented Mar 29, 2020 at 12:02 — Gert Arnold
– Gert Arnold, Commented Mar 29, 2020 at 12:02

Tintow · Accepted Answer · 2020-03-29 11:15:39Z

1

I think you're looking for linq grouping. From memory, it would be something like;

          var q = from l in lunchContext.Lunches
        join lr in lunchContext.LunchRestaurant on l.LunchId equals lr.LunchId
        join r in lunchContext.Restaurants on lr.RestaurantId equals r.RestaurantId
        group l by lr.RestaurantId into g
        select new { Id = g.Key,  Grade =g.Sum(x=>x.Grade)};

If you do need to go down the raw SQL route then you can levarage ADO.Net like this

using (var command = lunchContext.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "{Your sql query here}";
    context.Database.OpenConnection();
    using (var result = command.ExecuteReader())
    {
        // do something with result
    }
}

edited Mar 29, 2020 at 11:15

answered Mar 28, 2020 at 22:28

Tintow

3581 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oskar Persson Over a year ago

I can't seem to get access to the grade property/column in my anonymous object. Looks like I only have access to the middle tables keys :(

Tintow Over a year ago

Assuming that 'Grade' is in the Lunches table, I've updated the answer to get the values from there.

Collectives™ on Stack Overflow

How to write this SQL query as a LINQ statement in .NET Core (C#)?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related