LINQ to SQL - Restrict Relationship by Condition (One to Many) (1 - M)

Question

OK, first off, I'm brand new to LINQ2SQL so excuse my ignorance.

I've done some searches, but after 1 hour, I decided to come here.

Here goes:

I'm trying to do something that I think is very simple. It could just be that I don't understand. I'm using LINQ 2 SQL, via the VS designer. I have 2 tables: Clients and Categories. A client can have multiple categories.

I have a very simple query to find clients:

Client c = db.Clients.SingleOrDefault(client => client.ID == id);

What I want to do is modify this so that the collection: c.Categories, will only contain a subset of that client's categories.

This is what I've tried:

Client c = db.Categories.Where(cat => cat.IsActive == true).Select(cat.Clients).SingleOrDefault(client => client.ID == id);

I get an error reporting that more than one client is being returned.

1. Am I missing something? Is this not something that LINQ is designed to do?

2. Am I supposed to use the first query then do another query specifically on Categories when I need that list??

   c.Categories.Categories.Where(cat => cat.IsActive == true)

Thank you in advance.

Answer 1

SingleOrDefault() is for when there's ONLY one result. It seems like there's more than one record that has that id. Try using FirstOrDefault() instead.

Client c = db.Categories.Where(cat => cat.Name == "Name").Select(cat.Clients).FirstOrDefault(client => client.ID == id);

Answer 2

The problem is that the Categories.Where part in combination with the Select, is returning a collection of collections. What you may need to do is either use SelectMany, or use Single (or SingleOrDefault, or First or FirstOrDefault) instead of Where.

Examples:

Client c = db.Categories.Where(cat => cat.IsActive)
                        .SelectMany(cat.Clients)
                        .SingleOrDefault(client => client.ID == id);

or

Client c = db.Categories.Single(cat => cat.IsActive)
                        .Clients
                        .SingleOrDefault(client => client.ID == id);