What is the way to make a select on a nullable foreign key field in Entity Framework?

Question

Hi,

I have a category table which has a foreign key to it self by a nullable parentId field. In Entity-Framework when a relation is created, the entity is generated without any relation fields. I mean, in my example when I created a parentId-Id relation in Category table, the generated Category Entity will have an int typed Id property and a Category typed ParentCategory property and no ParentId property. And this makes my queries harder.

So, I have a trouble when I want to select the subcategories of a category. I use the method below for that;

public IEnumerable<ICategory> GetSubCategories(long? categoryId)
{
    var subCategories = this.Repository.Category.Where(c => c.ParentCategory.Id == categoryId)
                .ToList().Cast<ICategory>();

    return subCategories;
}

But this does not work, when I want to select the root categories. What is the way of doing this?

By the way, I wonder if there is a way to generate entities like in Linq to Sql, with an int typed Id property, an int typed ParentId and a Category typed ParentCategory property.

Answer 1

To select the root categories:

var rootCategories = this.Repository.Category.Where(c => c.ParentCategory == null).ToList().Cast<ICategory>();

To generalize the code to work with both sub & root categories:

var rootCategories = this.Repository.Category.Where(c => categoryId == null ? c.ParentCategory == null : c => c.ParentCategory.Id == categoryId).ToList().Cast<ICategory>();