sql query help in .Net MVC5

Question

This seems so basic and I can't for the life of me figure it out.

Working with .net MVC5 I have a route /{categoryName}/ and I am trying to get all subcategories in a given {categoryName} to display.

Right now I have and am returning the subcategory id numbers:

var subCategoriesInCategory =
  from cat in db.Categories
  join subcat in db.SubCategories
  on cat.CategoryId equals subcat.CategoryId
  where (cat.CategoryName == categoryName)
  select (subcat);

I'm sure this is basic for someoone proficient in SQL, but ...

Thanks

Adam Zuckerman · Accepted Answer · 2014-02-13 22:18:38Z

1

Try looking at this from the subcategory angle instead of the category.

var subCategoriesInCategory =
    from subcat in db.SubCategories
    where subcat.CategoryId == somevalue
    select subcat;

You would need to replace somevalue with the CategoryID value of the matching Category.

Or you could bypass the join clause by doing this:

var subCategoriesInCategory =
    from subcat in db.SubCategories
    where subcat.CategoryId == (from cat
        in db.Categories
        where cat.CategoryName == categoryName
        select cat.CategoryId
    ).FirstOrDefault()
    select subcat;

edited Feb 13, 2014 at 22:18

answered Feb 13, 2014 at 22:06

Adam Zuckerman

1,6411 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Brown · Accepted Answer · 2014-02-13 22:12:08Z

0

It looks OK. Try a category you know has subcats

var subCategoriesInCategory =
  (from cat in db.Categories
  join subcat in db.SubCategories
  on cat.CategoryId equals subcat.CategoryId
  where (cat.CategoryName == "ExistingCategory")
  select subcat).FirstOrDefault();
if (subCategoriesInCategory == null ) 
{
   // somethings wrong !
}

or if you just want the subCategory IDs

 ...  select new { subcat.ID }

answered Feb 13, 2014 at 22:12

Tom Brown

8331 gold badge13 silver badges21 bronze badges

Collectives™ on Stack Overflow

sql query help in .Net MVC5

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related