CODEWITHSUNDEEP
linqwhere-clause
user1011627
user1011627

Reputation: 1811

Linq where IN using method syntax

I have been struggling trying to get this one to work and my brain is shot...hoping someone can help out.

I have a table called Company which is the main entity returned from the query. The company table is linked to a table called Category through the table CompanyCategory. The category table has 2 fields that are relevant here: Name and Meaning. These fields contain the same data on a row...ie...Name = "Marketing" and Meaning = "MARKETING". This allows me to query by Meaning, while the display name could potentially change in the future. I will always query the category table by Meaning, and then display the Name.

Since a company can contain 1 to many categories, there are times when I want to return all companies that have a set of categories....say...all companies that are tied to marketing or public relations. Here is the query that I have right now that returns all companies with only the data I want.

IQueryable<ICompanyModel> q = base.Context.Set<KD.CompanyAdventures.Data.Implementation.Company>()
    .Include(x => x.Address.City.FirstAdminDivision)
    .Include(x => x.CompanyBioInfoes)
    .Select(x => new CompanyModel
    {
        CompanyId = x.CompanyId,
        Name = x.Name,
        BusinessPhone = x.BusinessPhone,
        PrimaryEmail = x.PrimaryEmail,
        WebsiteUrl = x.WebsiteUrl,
        LogoUrl = x.LogoUrl,
        Address = new AddressModel
        {
            AddressId = x.AddressId,
            Address1 = x.Address.Address1,
            Address2 = x.Address.Address2,
            PostalCode = x.Address.PostalCode,
            City = new CityModel
            {
                CityId = x.Address.City.CityId,
                Name = x.Address.City.Name,
                FirstAdminDivision = new FirstAdminDivisionModel
                {
                    FirstAdminDivisionId = x.Address.City.FirstAdminDivision.FirstAdminDivisionId,
                    Name = x.Address.City.FirstAdminDivision.Name
                }
            }
        }
    });

I am passing a List<string> to the method (GetCompanies) that has this LINQ query and I need to filter the companies that are returned by that list of category meanings that are being passed in. I have been able to get this to work in a test with 2 simple lists using the following (where list 1 = all employees (my wife and kids names) and list 2 is just the names of the my kids):

IQueryable<string> adultEmployees = employees.Where(emp => !kids.Contains(emp.ToString())).AsQueryable();

But I am not able to get this to work with the company and category example as I can't figure out how to drill down to the meaning with complex objects....ie...not list of strings.

Object classes used by the LINQ query look like the following:

CompanyModel [ CompanyId, CompanyName, List<CategoryModel> ]
CategoryModel [ CategoryId, Name, Meaning ]

Any help is greatly appreciated.

Upvotes: 0

Views: 156

Answers (1)

har07
har07

Reputation: 89285

Assume that meanings is a list containing "marketing" and "public relations". If I understand this correctly, you can get companies having CategoryModels's Meaning equals "marketing" or "public relations" like so :

companies.Where(c => c.CategoryModels.Any(cm => meanings.Contains(cm.Meaning)))

Upvotes: 1

Related Questions