What's wrong with this linqTOsql self referencing object mapping?

Question

Hey there,

I'm trying to create a self referencing object using linqTOsql mapping. So far, I am definitely in over my head. Here's the code I have:

[Table]
public class Category
{
    [Column(IsPrimaryKey=true, IsDbGenerated=true, AutoSync=AutoSync.OnInsert)]
    public Int64 catID { get; set; }
    public Int64 parentCatID { get; set; }
    public string catName { get; set; }
    public string catDescription { get; set; }

    internal EntityRef<IEnumerable<Category>> _category;
    [Association(ThisKey = "parentCatID", Storage = "_category")]
    public IEnumerable<Category> category {
        get { return _category.Entity; }
        set { _category.Entity = value; }
    }
}

My fakeRepository is defined like this:

// Fake hardcoded list of categories
private static IQueryable<Category> fakeCategories = new List<Category> {
    new Category { catID = 1, parentCatID = 0, catName = "Root", catDescription = "" },
    new Category { catID = 2, parentCatID = 1, catName = "Category w/subs", catDescription = "" },
    new Category { catID = 3, parentCatID = 1, catName = "Category no subs but now has subs", catDescription = "" },
    new Category { catID = 4, parentCatID = 2, catName = "Zub Cat", catDescription = "" },
    new Category { catID = 5, parentCatID = 2, catName = "Sub Cat", catDescription = "" },
    new Category { catID = 6, parentCatID = 0, catName = "Another Root", catDescription = "" },
    new Category { catID = 7, parentCatID = 0, catName = "Ze German Root", catDescription = "" },
    new Category { catID = 8, parentCatID = 3, catName = "Brand new cats", catDescription = "" },
    new Category { catID = 9, parentCatID = 8, catName = "Brand new cats sub", catDescription = "" },
}.AsQueryable();

I pass Category to the view like this:

public ActionResult CategoryTree()
{
    IQueryable<Category> cats = genesisRepository.Category
                                                 .Where(x => x.parentCatID == 0)
                                                 .OrderBy(x => x.catName);
    return View(cats);
}

The problem that I'm running into is that all of this compiles, but I don't get anything beyond the root categories. Model[0].category is returning null.

What is wrong with my self-referencing object?

Edit

I wonder if it's not working because I don't have a real linq-to-sql data context in my fakeRepository. If that's the case, is there a way around that? Can I can get this to work without a connection to a database?

Answer 1

It is supposed to be null. You are getting all categories where the ParentId = 0 ... and you don't have a child with an Id of 0. So that seems right to me.

It is not showing any subcategories because it has no subcategories to show. Try this:

    IQueryable<Category> cats = genesisRepository.Category
                                                 .Where(x => x.parentCatID != 0)
                                                 .OrderBy(x => x.catName);

The parentCatId needs to point to a valid CatId for it to be a subcategory. This query should get you all the categories that are subcategories.

Answer 2

Yeah, you hit the nail on the head. It's not working because you're using a fake repository.

Linq-to-Sql does all the wiring up for you and sets the related collections based on the properties (& their attributes) that you setup in your model.

I don't know how to accomplish this without a connection to the database because internal EntityRef<IEnumerable<Category>> _category; is completely foreign to me - I'm more of a POCO model type of guy.

After a quick google, I found this - How to: Map Database Relationships (LINQ to SQL)

Could you change your model to read:

[Column(IsPrimaryKey=true, IsDbGenerated=true, AutoSync=AutoSync.OnInsert)]
public Int64 CatId { get; set; }
[Column]
public Int64 ParentCatId { get; set; }
[Column]
public string CatName { get; set; }
[Column]
public string CatDescription { get; set; }

private EntitySet<Category> _ChildCategories;
[Association(Storage = "_ChildCategories", OtherKey = "ParentCatId")]
public EntitySet<Category> ChildCategories
{
    get { return this._ChildCategories; }
    set { this._ChildCategories.Assign(value); }
}

private EntityRef<Category> _ParentCategory;
[Association(Storage = "_ParentCategory", ThisKey = "ParentCatId")]
public Category ParentCategory
{
    get { return this._ParentCategory.Entity; }
    set { this._ParentCategory.Entity = value; }
}

Now because your ChildCategories is of type EntitySet<Category> (which inherits from IList<T>) you should be able to wire fake relationships up yourself.

So you could do something like this:

private static IQueryable<Category> GetFakeCategories()
{
    var categories = new List<Category> {
        new Category { CatId = 1, ParentCatId = 0, CatName = "Root", CatDescription = "" },
        new Category { CatId = 2, ParentCatId = 1, CatName = "Category w/subs", CatDescription = "" },
        //Blah
        new Category { CatId = 8, ParentCatId = 3, CatName = "Brand new cats", CatDescription = "" },
        new Category { CatId = 9, ParentCatId = 8, CatName = "Brand new cats sub", CatDescription = "" }
    };

    //Loop over the categories to fake the relationships
    foreach (var category in categories)
    {
        category.ChildCategories = new EntitySet<Category>(); //new up the collection
        foreach (var subLoopCategory in categories)
        {
            if (category.ParentCatId == subLoopCategory.CatId)
                category.ParentCategory = subLoopCategory;

            if (category.Id == subLoopCategory.ParentCatId)
                category.ChildCategories.Add(subLoopCategory);
        }
    }
    return categoies.AsQueryable();
}

It works in my head at least... :-)

HTHs,
Charles

EDIT: Re: Comment below about a null reference on _childCategories.

You could change the model to look like:

private EntitySet<Category> _ChildCategories = new EntitySet<Category>();