Using Linq to query Entity Framework with Where clause and many-to-many relation

Viewed 57

I have two tables People and Ordersand a many-to-many relationship between the two using PeopleOrders.

Each order is associated with two people: Client and Salesman.

I have the following query:

var query = db.People
              .Where(u => u.Description.Equals("Client"))
              .Select(u => new {u.Id, OrderId = u.Orders.Select(p => p.Id))
})
              .ToList();

This returns a json like this:

[{"Id":1,"OrderId":[2]},{"Id":9,"OrderId":[10,11,12,13]},{"Id":14,"OrderId":[14,15]}]

The ClientID and an array of orders.

I need to invert. Orders can't be an array.

So I need OrderID associated with the ClientID. Something like this:

[{"OrderId":2,"Id":1},{"OrderId":10,"Id":9},{"OrderId":11,"Id":9},{"OrderId":12,"Id":9},{"OrderId":13,"Id":9}]

The query would be something like:

var query = db.Orders

But I need to subquery the People table, so it return only Client; otherwise, it will return a array of People like:

{"OrderId":2,"Id":[1,10]}

Thank you in advance.

2 Answers
Related