Using Where clause in Group Join

Question

Please Consider these 2 tables:

CategoryID            CategoryName            CategoryModel
-----------------------------------------------------------
1                     Book                       1
2                     Shoe                       2
3                     Glass                      1 

and

SubCategoryID      SubCategoryName    CategoryID     SubCategoryModel    OtherColumn1       OtherColum2
---------------------------------------------------------------------
1                  Book1              1                   3
2                  Book2              1                   1
3                  Shoe1              2                   2
4                  Shoe2              2                   2

I want such this query:

from a in Category
join b in SubCategory
on a.CategoryID equals b.CategoryID into grpDetail
where a.CategoryModel != b.SubCategoryModel     <----------
select new 
{
     Id = a.CategoryID,
     Count1 = grpDetail.Count(o=>o.OtherColumn1 == 1),
     ...
}

the problem id I can't access to b in above specifies line. How can I write this query?

Thanks

Answer 1

There is a straightforward one to many relation between Categories and SubCategories: every Category has zero or more SubCategories; every SubCategory belongs to exactly one Category, namely the Category that the foreign key SubCategory.CategoryId refers to.

You want to join Category and SubCategory on this foreign key. You don't want all Category-SubCategory combinations that match, you want only those where Category.CategoryModel is not equal to SubCategory.SubCategoryModel.

From the remaining records, you want to select several properties. I don't see property GrpDetail in your classes, so I don't know what you want.

Luckily you mention that your problem is in the Where:

var result = Categories.Join(SubCategories, // join tables Categories and SubCategories
    category => category.Id,                // from every category take the Id,
    subCategory => subCategory.CategoryId,  // from every subCategory take foreign key CategoryId

    (category, subCategory) => new          // when they match make one new object
    {
       // we need at least Category.CategoryModel and SubCategory.SubCategoryModel
       CategoryModel = category.CategoryModel,
       SubCategoryModel = subCategory.SubCategoryModel,

       // Select other Category properties that you plan to use:
       CategoryId = category.Id,
       ...

       // Select other SubCategory properties that you plan to use:
       ...
})
// we don't want all combinations, only those where
// CategoryModel is not equal to SubCategoryModel
.Where(joinResult => joinResult.CategoryModel != joinResult.SubCategoryModel)

// from the remaining combinations calculate the final result
.Select(joinResult => new
{
    Id = joinResult.CategoryId,
    Count1 = ... // sorry, don't know what property grpDetail does
    ...
});