Stack Exchange
Complex Linq Query with multiple joins
I am not really familiar with Linq and I need to create a query to get the following data:
Tables: 
Product
Id,
GroupId,
ProductCode,
SpecificCode
...
ProductGroup
Id,
Status
...
Rule 
Id,
GroupId,
ProductCode,
SpecificCode
...
Parameters:
ProductCode:string,
SpecificCode:string
Requirement:
1.Get all ProductGroups where Status = Active, and which is associated with at least one Product with ProductCode and SpecificCode matching the parameters.
2.Get all rules assocated with these groups.
3.Filter out the rules that don't have a matching Productcode + SpecificCode on the rule itself.


I have written it out in multiple steps because that helps me understand the process. It may be possible in one query - I don't know. TBH I don't really care if I need multiple queries, I just need it to get the correct result set, with no duplicates (which is where I am really struggling).
Many Thanks!

you might want to provide some sample data so people get a better understanding of what you mean by no duplicate data

        from pg in ProductGroup        
        where pg.status == "active" 
        select new
        {
        ProductGroup = pg,
        Products = (from p in Product
              && p.GroupId = pg.Id  
              && p.productcode == inputproductcode 
              && p.specificcode == inputspcode).ToList(),

        Rules = pg.Rules.Where(r=>r..productcode == inputproductcode && r.specificcode == inputspcode).ToList()
        }

Something like this should work. 

Try something like that, it's weird though, that property should be set up.

What about this? Assuming your properties are Products, ProductGroups and Rules:
var productGroups = (from p in Products
                     join pg in ProductGroups on p.GroupId equals pg.Id
                     where pg.Status = "Active"
                     select new
                            {
                                p.ProductCode,
                                p.SpecificCode,
                                Rules = Rules.Where(r => r.ProductCode == p.ProductCode
                                    && r.SpecificCode == p.SpecificCode).ToList()
                            })
                     .ToList();


ProductGroup doesn't have ProductCode and SpecificCode peoperties

Ah okay, what links a Product to a ProductGroup?

When Product.GroupId == ProductGroup.Id

That's getting close, but I think it ignores requirement 2 - I think it should have another where call - .Where(r => r.GroupId == p.GroupId)

Also, I think there is a Distinct needed somewhere, as it returns a lot of duplicates (lets say rules 1 and 2 are correctly returned) - it shows multiple instances of these as a list of lists - [[1,2][1,2][1,2]...] - I just need [1,2]. I could call First() on the result I suppose but I wonder if that would jut be a hack.
