conversion - SQL to LINQ

Question

How can the following be accomplished using LINQ

SELECT r.BrandID 
FROM dbo.Items AS r
JOIN Brands AS d ON r.BrandID = d.BrandID
WHERE CategoryID IN (SELECT CategoryID 
                     FROM dbo.Categories
                     WHERE Name = 'Bread - Bakery')

Code for Brand class:

public class Brand
{
    public int BrandID { get; set; }
    [DisplayName("Brand Name")]
    public string Name { get; set; }
    public virtual List<Category> Categories { get; set; }
    public virtual List<Item> Items { get; set; }
}

Code for Item class:

public class Item
{
    [Key]
    public int ItemID { get; set; }
    public virtual Category Category { get; set; }
    public virtual Brand Brand { get; set; }
    public int CategoryID { get; set; }
    public int BrandID { get; set; }
}

code for Category class:

public class Category
{
    [Key]
    public int CategoryID { get; set; }
    [DisplayName("Category Name")]
    public virtual string Name { get; set; }
    public virtual List<Brand> Brands { get; set; }
    public virtual List<Item> Items { get; set; }
}

Answer 1

Hm, pity you didn't write your requirements, now I have to guess what they are from your SQL code.

So you have a database with Brands, Items and Categories. Every Item has a Category, every Category can be used by zero or more Items: a one-to-many relation

Every Item is of a certain Brand, Every Brand can have zero or more items: also a straightforward one-to-many relation.

Finally every Brand has zero or more Categories, every Category has zero or more Brands: many-to-many

Now you take your collection of Items, you only want to keep those Items that have a Category with a Name that equals Bread - Bakery. From the remaining items you want all their BrandIds.

The requirement would be: "Give me the BrandIds of all Items that have a Category with a Name that equals 'Bread - Bakery`.

If you use entity framework, it is usually easier if you use the virtual ICollection instead of doing the join yourself. Entity framework knows the relations between the tables and will compose the proper joins for it.

var result = myDbContext.Items                             // from the collection of Items
    .Where(item => item.Category.Name == "Bread - Bakery") // keep only those with a Category
                                                           // that has a Name equal to ...
    .Select(item.BrandId);                        // from the remaining items select the BrandId

If you really want, and you can convince your project leader that entity framework can't be trusted to do the proper joins you can do the join yourself:

// get the sequence of categories that I'm interested in:
var breadBakeryCategories = myDbContext.Categories
    .Where(category => category.Name == "Bread - Bakery");

// join the items with these categories
// and select the BrandIds
var requestedBrandIds= myDbContext.Items
    .Join(breadBakeryCategories,
    item => item.CategoryId,           // from every Item take the CategoryId,
    category => category.CategoryId,   // from every Category take the CategoryId
    (item, category) => item.BrandId);   // when they match, select the BrandId

TODO: consider concatenating this into one big ugly LINQ statement.

Remark 1

You do realize that your result might have the same BrandIds several times, don't you?

If you don't want that, start with the Brands:

var result = myDbContext.Brands
.Where(brand => brand.Items.Select(item => item.Category.Name)
                .Where(name => name == "Bread - Bakery")
                .Any())
.Select(brand => brand.brandId);

In words: from the collection of Brands, keep only those Brands that have at least one Category with a name equal to "Bread - Bakery". From the remaining Brands select the BrandId.

** Remark 2 **

Why are your one-to-many Lists instead of ICollections? Are you sure that brand.Categories[4] has a proper meaning?

var result = myDbContext.Brands
    .Where(brand => brand.Category[4].Name == "Bread - Bakeries");

Your compiler won't complain, but you'll get runtime errors.

Consider using virtual ICollection<...> for your one-to-many and many-to-many relations. This way you'll have exactly the functionality you expect with a database table, and your compiler will complain if you try to use functionality that can't be translated into SQL

Answer 2

dbContext.Items
  .Where(x => x.Category.Name.Equals("Bread - Bakery"))
  .Select(x => x.BrandID);

I am not sure why you need to use below join. It seems that it is not needed (unless intentionally inner joined with brands to remove non-matching records from items)

JOIN Brands AS d ON r.BrandID = d.BrandID