CODEWITHSUNDEEP
c#.netlinq
Oskar Persson
Oskar Persson

Reputation: 1

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

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.

Upvotes: 0

Views: 79

Answers (1)

Tintow
Tintow

Reputation: 358

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
    }
}

Upvotes: 1

Related Questions