LINQ Query to join three tables

Question

I need a help in LINQ Query for the below.

 public interface IBrand
 {
      int BrandId { get; set; }
      IEnumerable<IBuyingAgency> BuyingAgencies { get; set; }
 }

 public interface IBuyingAgency
 {
      int BuyingAgencyId { get; set; }
 }

 public interface IClientGroup
 {
      IBuyingAgency BuyingAgency { get; set; }
      int ClientGroupId { get; set; }
 }


1).  var brands         = LoggedInUserHelper.GetUser().GetBrands(roles); // returns IEnumerable<Tuple<IBrand, string>>
2).  var buyingAgencies = LoggedInUserHelper.GetUser().GetBuyingAgencies(roles); //IEnumerable<IBuyingAgency>
3).  var clientGroups   = LoggedInUserHelper.GetUser().GetClientGroups(roles); //IEnumerable<IClientGroup>


 function IEnumerable<IClientGroup> GetClientGroups( List<int> BrandIds)
 {
     var brands         = LoggedInUserHelper.GetUser().GetBrands(roles); // returns IEnumerable<Tuple<IBrand, string>> 
     var buyingAgencies = LoggedInUserHelper.GetUser().GetBuyingAgencies(roles); //IEnumerable<IBuyingAgency>
     var clientGroups   = LoggedInUserHelper.GetUser().GetClientGroups(roles); //IEnumerable<IClientGroup>

  var lstBrandagencies = brands.Where(brand => BrandIds.Contains(brand.Item1.BrandId) &&  brand.Item1.BuyingAgencies.Any( ba => buyingAgencies.Contains(ba.BuyingAgencyId))).SelectMany(brand => brand.Item1.BuyingAgencies);

   var buyingAgencyIDs = lstBrandagencies.Select(b => b.BuyingAgencyId);

      clientGroups = clientGroups.Where(cg => buyingAgencyIDs.Contains(cg.BuyingAgency.BuyingAgencyId));

      return Mapper.Map<IEnumerable<IClientGroup>>(clientGroups.ToList());    

 }

I wrote the above function but not working, it gets all the clientgroups instead of filtering

I wants to write a query to get all ClientGroups whch satisfies the below condition

1. retrieve the brand from brands ( above ) that matches the list of brandId's passing in as parameter
2. Than get all the buyingAgencies under brands (1) above which matches with the id's of (2) above 
3. Finally get all clientgroups which matches with the buyingAgency retrieving in step (2) 

Please can you help.

Answer 1

you are not filtering from your source 2) in this line

var buyingAgencyIDs = lstBrandagencies.Select(b => b.BuyingAgencyId);

just projecting from the previous query.

If I understood correctly you want to do this.

   var lstBrandagencies =  (from a in brands
                            where BrandIds.Contains(a.Item1.BrandId )
                            select a).SelectMany (b => b.Item1.BuyingAgencies )
                                     .Select (b => b.BuyingAgencyId );

   var buyingAgencyIDs =  from a in buyingAgencies
                          where  lstBrandagencies.Contains(a.BuyingAgencyId )                        
                          select a.BuyingAgencyId;

   var clientGroupsResult = clientGroups.Where(cg => buyingAgencyIDs.Contains(cg.BuyingAgency.BuyingAgencyId));