Parent/Child tables not referencing in LINQ or am I just wrong?

Question

I have a categories table "Category" [ID, Title] and a Parent/Child table "CategoryParent" [ID,CategoryID,ParentCategoryID] where a many-to-many tree is represented by the parent of a category being indicated using the ParentCategoryID field in the join table.

I thought this would be a simple structure to use to retrieve root categories (category entries which have NULL as a ParentCategoryID value in the join table) and child categories (by parent id.)

However, none of my attempts to write a LINQ2SQL statement to get a list of Category objects by their parent ID in the join table have produced anything compilable.

I would post some code, but none of it is either (a)complete or (b)sensible - in any terms at all.

How should one go about this?

My join table looks like this:

CategoryParent
---
ParentCategoryID [int] (PK)
CategoryID [int] FK
CategoryParentID [int] FK

My data (category) table looks like this:

Category
---
CategoryID [int] PK
Title [nvarchar]

There are two relationships:

Category.CategoryID 1->* CategoryParent.CategoryID
Category.CategoryID 1->* CategoryParent.ParentCategoryID

I would like to provide either NULL or a CategoryID and get back all the Category table rows which have it as a parent.

Answer 1

Ok, well, it looks like what I should have been doing is a fairly basic join using LINQ, rather than relying on the join objects in the context class. Something like:

var props = from i in context.Prices
    join e in context.PricingEntities on i.EntityID equals e.EntityID
    join l in context.PricingEntityProperties on e.EntityID equals l.EntityID
    join p in context.PricingProperties on l.PropertyID equals p.PropertyID
    where i.InstanceID == instanceId
    select p;