CODEWITHSUNDEEP
c#entity-framework
Percy
Percy

Reputation: 3125

Entity Framework 6 selecting items based on a field in a child item

EF6 MVC5

I have an Orders table. Each order has a Customer.

I am trying to seach for Orders that have a customer with a surname that contains a string.

So one Order has one Customer but a Customer can have many Orders.

I can't quite get what I need. I'm assuming it's better (more efficient?) to get the customer first then get the orders, rather than get all orders and then filter on Customer - so here are my attempts:

List<Order> orders = new List<Order>();
orders = db.Contacts.Where(c => c.NameLast.Contains(surnameContains)).Select(c => c.Orders).ToList();

The above gives the error:

Cannot implicitly convert type 'System.Collections.Generic.List>' to 'System.Collections.Generic.List'

So I'm thinking the above is returning a List of List of Orders - which makes sense - a List of Customers and each Customer contains a List of Orders. I think maybe I can addRange:

orders.AddRange(db.Contacts.Where(c => c.NameLast.Contains(surnameContains)).Select(c => c.Orders));

This doesn't work either - error=

Argument 1: cannot convert from 'System.Linq.IQueryable>' to 'System.Collections.Generic.IEnumerable'

I'd be really grateful for any pointers to help with this. The result I would like is a List of Order.

Upvotes: 0

Views: 41

Answers (1)

faflo10
faflo10

Reputation: 386

In your Orders table, do you have a Customer/Contact field? If yes, you can test the following :

List<Order> orders = new List<Order>();
orders = db.Orders.Where(o => o.Customer.NameLast.Contains(surnameContains)).ToList();

If in your Orders table, you just have a Customer/Contact ID, you can test the following :

List<int> ListeID = db.Contacts.Where(c => c.NameLast.Contains(surnameContains)).Select(c => c.ID).ToList();
List<Order> orders = new List<Order>();
orders = db.Orders.Where(o => ListeID.Contains(o.CustomerID)).ToList();

or something along those lines.

Upvotes: 1

Related Questions