SQL Server query to C# Linq-CodePudding

I'm currently having a rough time converting my SQL query to LINQ for a school project I'm using WPF and Entity Framework

here is my SQL query (working exactly as I expect)

select IngrediantName,sum(IngrediantQuantity) as Quantity, IngrediantMeasurementUnit from Users
join Shopping_List
on Users.UserID = Shopping_List.ShoppingListID
join List_Item
on List_Item.ShoppingListID = Shopping_List.ShoppingListID
join Ingrediant
on Ingrediant.IngrediantID = List_Item.IngrediantID
where Users.UserID = 1
group by IngrediantName,IngrediantMeasurementUnit

Here is the query that I have so far

var query = from user in dbContext.Users
                        join shoppingList in dbContext.ShoppingLists on user.UserId equals shoppingList.UserId
                        join listItem in dbContext.ListItems on shoppingList.ShoppingListId equals listItem.ShoppingListId
                        join ingrediant in dbContext.Ingrediants on listItem.IngrediantId equals ingrediant.IngrediantId
                        where currentUserNumber == user.UserId
                        
                        select new
                        {
                            name = ingrediant.IngrediantName,
                            quantity = ingrediant.IngrediantQuantity,
                            unit = ingrediant.IngrediantMeasurementUnit,
                        };

Here is what i try so far

            var query = from user in dbContext.Users
                        join shoppingList in dbContext.ShoppingLists on user.UserId equals shoppingList.UserId
                        join listItem in dbContext.ListItems on shoppingList.ShoppingListId equals listItem.ShoppingListId
                        join ingrediant in dbContext.Ingrediants on listItem.IngrediantId equals ingrediant.IngrediantId
                        where currentUserNumber == user.UserId
                        group ingrediant by ingrediant.IngrediantQuantity into x
                        
                        select new
                        {
                            name = x.GroupBy(x => x.IngrediantName),
                            quantity = x.Sum(x => x.IngrediantQuantity),
                            unit = x.GroupBy(x => x.IngrediantMeasurementUnit),
                        };

this one return the following error wiches doesn't tell much

Argument type 'System.Linq.IQueryable1[System.Linq.IGrouping2[System.String,Microsoft.EntityFrameworkCore.Query.TransparentIdentifierFactory TransparentIdentifier2[Microsoft.EntityFrameworkCore.Query.TransparentIdentifierFactory TransparentIdentifier2

If someone could point me in the right direction I would appreciate it, if you need more info I will provide it for sure

Thanks

CodePudding user response：

UPDATE

I got this working here the answers to the question for anyone else

var query = 
    from user in dbContext.Users
    join shoppingList in dbContext.ShoppingLists 
        on user.UserId equals shoppingList.UserId
    join listItem in dbContext.ListItems 
        on shoppingList.ShoppingListId equals listItem.ShoppingListId
    join ingrediant in dbContext.Ingrediants 
        on listItem.IngrediantId equals ingrediant.IngrediantId
    where currentUserNumber == user.UserId
    group ingrediant by new { name = ingrediant.IngrediantName, unit = ingrediant.IngrediantMeasurementUnit } into x
    select new
    {
        name = x.Key.name,
        quantity = x.Sum(x => x.IngrediantQuantity),
        unit = x.Key.unit,
    };

if you look at the group line group ingrediant by new { name = ingrediant.IngrediantName, unit = ingrediant.IngrediantMeasurementUnit } into x

from my understanding you use the new to create a new selector then you can use x.key.name and x.key.unit where name and unit are simply some variable