Dmitry Stepanov
Dmitry Stepanov

Reputation: 2924

Getting objects from a table for a given condition for the field in another table

Let's say I have two tables. The first one is Models that has Id, MachineType, BrandId and ModelName columns, the second table is Brands with Id and BrandName columns.

I need to write a method for my repository that returns all brands for a given MachineType. If I'd have had a column MachineType in the Brands table, it'd have been pretty simple:

    public IEnumerable<Brand> GetBrandByType(MachineTypeEnum type)
    {
        return _context.Brands.Where(x => x.MachineType == type).AsEnumerable();
    }

But how to do it in my case?

Upvotes: 0

Views: 30

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

If you have navigation properties defined on your entities:

_context.Brands.Where(b => b.Models.Any(m => m.MachineType == type))
        .AsEnumerable();

Otherwise

from b in _context.Brands
join m in _context.Models
     on b.Id equals m.BrandId into g   
where g.Any(m => m.MachineType == type)
select b

Upvotes: 2

Related Questions