Subsonic 3 - SimpleRepository

Question

I am playing around with Subsonic 3's simple repository and am hitting walls in understanding how to deal with foreign keys...

If I have a product object containing

int ID; 

string name; 

string description; 

Category category; 

int categoryID (this one is just to persist the product's categoryID to the DB)

and a category object containing 

int ID; 

string name;

How can I use the repository to bring back a list of all products with their category object instantiated?

At the moment I have written a linq query which joins on product.categoryID = category.ID which is all well and good, but when I .ToList() the results of this query, the product's Category isn't instantiated.

Is there a way to do this, or do I have to manually instantiate the Category for each product?

Thanks,

Paul

Softhearted

Answer 1

you need to get linq to populate it,
using something like
var query = from product in repo.All(Product)
join categoryItem in repo.All(Category)
on product.CategoryId equals categoryItem.Id
select new {
ID = product.ID,
name = product.name,
description = product.description,
categoryId= product.CategoryId
category = categoryItem
};

Zingg

This sounds like the answer... gonna try it tonight then let you know fella Cheers man – Softhearted

Hi Podge, thanks for your help. In order to recast this to a list of products, will I have to manually step through the list and instantiate a new product object for each result ( which is currently of type [anonymous type]) – Softhearted

There is no simple way I know of, except for manually assigning it as you are saying. The anonymous type is read only to in C#. The other option is to load the Category separately and iterate through the products. You can cache the Category table and then you are only hitting the database once. Sorry it is not the answer you want. – Zingg

Something like this List<Product> prods = repo.All<Product>().ToList(); List<Category> categories = (from product in repo.All<Product>() join category in repo.All<Category>() on product.categoryId equals category.Id select category).Distinct().ToList(); foreach (Product prod in prods) { prod.category = categories.Find(c => c.Id == prod.categoryId ); } – Zingg

TheVillageIdiot, you should really create a new question thread. Anyway what error message are you getting? Also what version of Subsonic have you got? Podge – Zingg