LINQ Unable to get distinct value from many to many Entity Framework

Question

I am using linq to query many to many relationship

I have those tables/entities :

Each order has multiple products and I want to get distinct order with products.

Here is my query:

var query = (from order in db.Order
             join orderproduct in db.OrderProduct
                         on order.orderId equals orderproduct.OrderId
             join product in db.Product
                         on orderproduct.ProductId equals product.productId
             select new 
                    { order.orderId, 
                      order.name,
                      product.productId, 
                      product.productName, 
                      product.price 
                    }).Distinct().ToList();

This is the result :

I want to get only name "Jane" with 2 products. The result is showed 2 records with same name "Jane".

How to get one record name "jane" and 2 products?

Thanks for your help.

Answer 1

If you want to return a structure containing the customer's name and its ordered products, I recommend to group the products by order.orderName (not order.orderId) and to load the result into a dictionary:

var query = from order in db.Order
            join orderproduct in db.OrderProduct on order.orderId equals orderproduct.OrderId
            join product in db.Product on orderproduct.ProductId equals product.productId
            group product by order.orderName into orderGroup
            select orderGroup;

var orderedProductsByName = query.ToDictionary(name => name.Key, products => products.ToList());

Answer 2

use the following:

var query = (from order in db.Order
                         join orderproduct in db.OrderProduct
                         on order.orderId equals orderproduct.OrderId
                         join product in db.Product
                         on orderproduct.ProductId equals product.productId
                         select new {order.orderId,order.name,product.productId,product.productName,product.price}).GroupBy(order=>order.orderId).ToList();