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>
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.