CODEWITHSUNDEEP
c#linqcollections
tett
tett

Reputation: 605

How to query ICollection<T>?

I have two models, where both of them include an ICollection<T> object, something like this:

public class Business : ApplicationUser
{
    ...
    public virtual ICollection<Subcategory> Subcategories { get; set; }
    ...
}

public class Request
{
    ....
    public virtual ICollection<Subcategory> Subcategories { get; set; }
    ...
}

I want to query for the businesses and get the businesses whose subcategories match the ones in request (even one match is enough). I have something like this, which I wrote just as a sample, and of course it doesn't work (request is of type Request):

foreach (var subcategory in request.Subcategories)
{
    var businesses = db.Users
        .OfType<Business>()
        .Where(b => b.Subcategories
            .Where(s => s.SubcategoryName == subcategory.SubcategoryName));
}

So, for example, let's say we have two businesses, from which the first one has Football and Basketball as subcategories and the second one has Basketball and Tennis. Then, let's say our user selects both Football and Tennis subcategories, so my query should return both businesses, because first one includes Football, and the second one includes Tennis.

So, not every single subcategory should match, even one match is enough for accepting the business as a result. How can I achieve it? If you can provide the code I would be glad.

Here is also my subcategory model, in case you need it:

public class Subcategory
{
    public int SubcategoryID { get; set; }

    public string SubcategoryName { get; set; }
}

Upvotes: 4

Views: 22242

Answers (2)

Robert Harvey
Robert Harvey

Reputation: 180908

Look at the signatures of your Linq methods. In particular, Where

Enumerable.Where<TSource> Method (IEnumerable<TSource>, Func<TSource, Boolean>)

expects a predicate that returns a boolean value. Where returns a collection, so you can't plug it into a Where clause expecting a Func that returns a boolean; that's not going to work.

What you probably need is something like this:

var businesses = db.Users.OfType<Business>()
    .Where(b => b.Subcategories
    .Any(s => s.SubcategoryName == subcategory.SubcategoryName));

Notice the use of Any. Any returns a boolean value, indicating whether or not a record was found matching your predicate s => s.SubcategoryName == subcategory.SubcategoryName

Upvotes: 9

chipples
chipples

Reputation: 195

I would do this:

var businesses = db.Users
    .OfType<Business>()
    .Where(b => b.Subcategories.Intersect(request.Subcategories)
    .Any());

Breaking this down:

b.Subcategories.Intersect(request.Subcategories);

Would return a collection of those categories that exist in b.Subcategories and request.Subcategories.

Therefore

b.Subcategories.Intersect(request.Subcategories).Any()

Would return true if there ARE any categories that exist in both, and false if it doesn't.

And so finally:

db.Users.OfType<Business>()
        .Where(b => b.Subcategories.Intersect(request.Subcategories).Any());

Would return any business for which the previous statement returns true, that is, any business with subcategories matching the subcategories of the request.

Upvotes: 4

Related Questions