LINQ: Search/Filter data from multiple table

Question

I have three tables,

Table:1

 UserId  UserName     ...   
  1      Jhon        ...
  2      Ashley      ...
  3      Alexa       ... 
  4      Krish       ...

Table:2

BrandId UserId  BrandName        
 1       1       BMW
 2       1       Citroen
 3       2       Audi
 4       4       Peugeot

Table:3

CountryId UserId  CountryName        
 1          3       Austria

Now what I want if user search items by Brands = ["BMW", "Audi"] & Countries= ["India","Romania"] then it should show the user result like :

 UserId  UserName     ...   
  1      Jhon        ...
  2      Ashley      ...

or search items by only Countries= ["India","Austria"] then result should be :

 UserId  UserName     ...   
   3      Alexa       ...

I have tried like following C#-LINQ, But I am always getting all user data. It's not filtering based on the given Brand or Country input. So it should give the data if either Brands or Countries match with DB data. It's not giving properly even if I remove the "into" from the LINQ part. Where Did I go wrong?

 var users = new List<Users>();
 users = _DbContext.Users.Where(x => x.Roles == model.Role).ToList();
 users = (from p in users
                         join b in _DbContext.UserBrands.Where(x => model.arBrands.Contains(x.BrandName)).ToList() on p.UserSequence equals b.UserId into bg
                         join c in _DbContext.UserCountries.Where(x => model.arCountries.Contains(x.CountryName)).ToList() on p.UserSequence equals c.UserId into cg
                         select p).ToList();

And my Sample Payload,

{
 "arBrands": ["Citroen","Peugeot"],
 "arCountries":["India","Romania"],
 "Role":"All Roles"
}

Entities:

public class UserBrands
{
    [Key] public virtual int UserBrandsId { get; set; }
    public virtual int UserId { get; set; }
    public virtual int BrandId { get; set; }
    public virtual string BrandName { get; set; }
}

public class UserCountries
{
    [Key] public virtual int UserCountriesId { get; set; }
    public virtual int UserId { get; set; }
    public virtual int CountryId { get; set; }
    public virtual string CountryName { get; set; }
}

public class UsersModel
{
    public string[] arCountries { get; set; }
    public string[] arBrands { get; set; }
    [Required]
    public string Role { get; set; }
    public List<LeafLet> features { get; set; }
}

public class Users : BaseEntity
{
    [Key]
    public virtual int UserSequence { get; set; }
    public virtual string UserName { get; set; }
    public virtual string Countries { get; set; }
    public virtual string Brands { get; set; }
    public virtual string Email { get; set; }
    public virtual string Latitude { get; set; }
    public virtual string Longitude { get; set; }
    public virtual string ProjectRole { get; set; }
    public virtual string Roles { get; set; }
}

Answer 1

You will need to clean up your models a bit, and add navigation properties to let EF perform the joins for you.

    public class Users
    {
        [Key]
        public int Id { get; set; }
        public string UserName { get; set; }
        public string Email { get; set; }
        public string Role { get; set; }

        //Only use the virtual keyword for navigating properties to other entities.
        public virtual IEnumerable<UserBrand> UserBrands {get; set;}
        public virtual IEnumerable<UserCountry> UserCountries {get; set;}
    }

    public class UserBrand
    {
        [Key] 
        public int Id { get; set; }
        public int UserId { get; set; }
        public int BrandId { get; set; }

        [ForeignKey("UserId")] // lets Entity Framework know how to map the navigation.
        public virtual User User { get; set; }

        [ForeignKey("BrandId")]
        public virtual Brand Brand { get; set; }
    }


    public class Brand
    {   
        [Key]
        public int Id { get; set; }  
        public string BrandName { get; set; }
    }


    public class UserCountry
    {
        [Key] 
        public int Id { get; set; }
        public int UserId { get; set; }
        public int CountryId { get; set; }

        [ForeignKey("UserId")]
        public virtual User User { get; set; }

        [ForeignKey("CountryId")]
        public virtual Country Country { get; set; }
    }

    public class Country
    {
        [Key] 
        public int Id { get; set; }
        public string CountryName { get; set; }
    }

Your LINQ query will then look something like this:

 var users = _DbContext.Users
                 .Where(u => u.Role == model.Role)
                 .Select(u => new { 
                      UserName = u.UserName,
                      Email = u.Email,
                      Role = u.Role,
                      BrandNames = u.UserBrands.Select(ub => ub.Brand.BrandName),
                      Countries = u.UserCountries.Select(uc => uc.CountryName)
                  }).ToList();

You can add more filters to the Where clause using the navigation properties:

 var users = _DbContext.Users
                 .Where(u => u.Role == model.Role 
                       && u.UserBrands.Any(ub => 
                              ub.Brand.BrandName == "Brand X") //Filter for brand name.
                       && u.UserCountries.Any(uc => 
                              uc.Country.CountryName == "United States") //Filter for brand name.

                 .Select(u => new { 
                      UserName = u.UserName,
                      Email = u.Email,
                      Role = u.Role,
                      BrandNames = u.BrandNames,
                      Countries = u.Countries
                           })
                 .ToList();