Search code examples
sqldatabasebreeze

junction tables in Breeze JS many to many. How to get data.results


I'm working on my first Breeze Angular project (.NET). I'm slowly getting there but for the last 2 days I've been stuck with the following problem.

I have 3 Tables.

[Products] 1>m [ProductCategory] m<1 [Categorys]

ProductCategory is a Junction/Link table that has ProductID and CategoryID as Foreign keys. Very simple.

I need to generate a list of Categorys where ProductId = 3

any Idea how I do this with Breeze EntityQuery?

What would the Breeze Query look like? This is as far as I have got.

 var query = breeze.EntityQuery 
.from("Categorys") 
.expand(???)
.where("ProductCategory.ProductID", "==", 3);

All three tables are correctly Modeled eg

public class Product_Category
{
    [Key]
    public int ID { get; set; }
    [Required]
    public int ProductID { get; set; }
     [Required]
    public int CategoryID { get; set; }
    [ForeignKey("CategoryID")]
    public Category Categories { get; set; }

    [ForeignKey("ProductID")]
    public Product Products { get; set; }

}

The simple SQL statement would look like:-

     SELECT  ProductCategory.ProductID, ProductCategory.CategoryID,
     Categorys.CategoryName FROM            
                       ProductCategory INNER JOIN
                              Categorys ON 
ProductCategory.SubCategoryID = Categorys.SubCategoryID 
WHERE    (ProductCategory.ProductID = 3)

I realise this is a very common requirement but try as I might I cannot find a solution.

UPDATE

Thanks to Dominic here is a solution to the above. In case anybody else is as thick as me.

BREEZE

 var query = breeze.EntityQuery
.from("Products_Categories")
.expand("Categories")
.where("ProductID", "==", parseInt(prodid));

Angular View:

<div data-ng-repeat="item in ProductCategory">
    <div data-ng-repeat="sub in item.Categories">
        <span ng-show="sub.CategoryName.length">{{sub.CategoryName}}</span>
    </div>
</div>

Solution

  • I don't think you can do query like

    var query = breeze.EntityQuery 
    .from("Categorys") 
    .expand(???)
    .where("ProductCategory.ProductID", "==", 3);
    

    But I'm sure you can do query like

    var query = breeze.EntityQuery
    .from("ProductCategory")
    .expand("Categories")
    .where("ProductId", "==", 3);
    

    Then you would have to iterate through ProductCategories and then through Categories within. I'm not 100% sure of the syntax because for some strange reason expand on client side didn't work for me in the past and got used to writing .Include in breezeController, but logic should be the same.