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

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.

From stackoverflow

• 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>();
  

  yapiskan : When c.ParentCategory is null this will throw NullReferenceException.

  Buu Nguyen : You should check which object is null in the thrown exception and post here, together with the code you use.

  yapiskan : I change my data access method to linq2sql. So, I can't give you the code. But I could say that there are some categories which has no parent! So, that will make this query to throw a NullReferenceException for that categories.

  Buu Nguyen : well, the c.ParentCategory == null is to select those category without no parent. Should not throw exception there. Anyway, since you don't have the code, I don't know if I could help...