CODEWITHSUNDEEP
databaselinqentity-frameworkentity-framework-4.1
1110
1110

Reputation: 6839

How to get top most parent in self reference object

UPDATE This is my comment from bellow that I have discused with @Slauma:

Because I need to get all locations that are connected to passed root category. As you can see if I pass 2 and some location have category 44 and 44 is child of 32 which is child of 2 I need to get this location. LocationCategory is N:N table in database between Locations and PlaceCategories. Not important but can give a better picture. I have a map and markers on that map. I can click on Education(id:2) link and I need to get all markers where root of location category is '2' (like on foursquare.com map)

I have a self reference table in database. So I created the following object:

public class PlaceCategory
    {
        public int PlaceCategoryId { get; set; }
        public string Name{ get; set; }
        public int? ParentId { get; set; }

        public virtual PlaceCategory Parent { get; set; }
        public virtual ICollection<PlaceCategory> Children { get; set; }

        public string Icon { get; set; }
    }

Because Location object can have multiple categories I have LocationCategory object:

public class LocationCategory
    {
        [Key, Column(Order = 1)]
        public int LocationId { get; set; }
        [Key, Column(Order = 2)]
        public int PlaceCategoryId { get; set; }
        public Guid UserId { get; set; }
        public DateTime CreatedOnDate { get; set; }
        public bool IsPrimary { get; set; }

        public virtual Location Location { get; set; }
        public virtual PlaceCategory PlaceCategory { get; set; }
        public virtual User User { get; set; }
    }

Location object have:

public class Location
{
    ...
    public virtual ICollection<LocationCategory> LocationCategories { get; set; } 
    ...

In database in self reference table I have:

root: Education (id:2, parentId:null)
child1: School(id:32, parentId:2) 
child2: Elementary(id:42,parentId:32), High(id:43,parentId:32), Higher(id:44,parentId:32) etc.

I have to take list of locations based on passed root category.

var model = locationRepository.GetLocationss().Where(x => x.LocationCategories???); // but it's a list and don't know how to check top most parent here?

So if I pass '2' I should get all items which have category 2,32,42,43,44

Upvotes: 2

Views: 984

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

This is not supported by entity framework unless you add rootCategoryId to every PlaceCategory and filter on that property when retrieving locations but this approach will fail once you have deeper nesting and you may require to get all locations for some category which is not root (but have its own parent). In such case storing the root will not help.

The generalization of this problem is called hierarchical or recursive query. That is the query which can traverse hierarchy and get all required nested records. It is possible to do that with SQL by using Common Table Expression alias CTE (requires SQL Server 2005 or newer). You can create such query and execute it directly by dbContext.Database.SqlQuery.

In case of EF 5.0 with .NET 4.5 and EDMX (database first) you would be also able to implement the query as Table Valued Function in SQL Server, map it in EDMX and use it in Linq queries.

Upvotes: 3

Related Questions