CODEWITHSUNDEEP
asp.net-mvc-4linq-to-entitiesentity-framework-5
SQLGrinder
SQLGrinder

Reputation: 383

LINQ to EF Using a Collection in a Where Clause

I have a main VendorProfile table and a 1-many VendorHistory table that contains status codes and date stamps. The query below works at retrieving only the latest status (status code and date) for each vendor. However, the view allows the user to select checkboxes of any of the status codes to filter the view. So I need to add a where clause that matches ANY of the checkbox StatusSelections.

Model Diagram

    public IEnumerable<BrowseStatusModel> BrowseByStatus(int[] StatusSelections)
    {
        IQueryable<BrowseStatusModel> query = _db.VendorProfiles
            .Include("VendorStatusHistory")
            .Include("StatusCodes")
            .Select(s => new BrowseStatusModel
            {
                ProfileID = s.ProfileID,
                Name = s.Name,
                CompanyName = s.CompanyName,
                CompanyDBA = s.CompanyDBA,
                DateCreated = s.DateCreated,
                Status = s.VendorStatusHistories.OrderByDescending(o => o.DateCreated).FirstOrDefault().Id,
                StatusDate = s.VendorStatusHistories.OrderByDescending(o => o.DateCreated).FirstOrDefault().DateCreated
            })
            .OrderBy(x => x.ProfileID);

        foreach (int status in StatusSelections)
        {
            query = query.Where(x => x.Status == status);
        }
        return query;
    }

The above foreach loop works but, unfortunately creates AND condition where ALL selections must be true instead of ANY. I figured I would have to use a where clause with the following in some way but have been unsuccessful at the correct syntax.

.AsQueryable().Any();

Upvotes: 0

Views: 692

Answers (1)

Matthew Fotzler
Matthew Fotzler

Reputation: 640

Use contains in the place of that foreach loop

query = query.Where(x => StatusSelections.Contains(x.Status))

Upvotes: 2

Related Questions