Help in Entity Framework 4.1 Query

Question

I need help in writing a query, below i have written classes with needful properties for this example only (i have not shown DB tables that e.f. generates)

//this class will create a unique id for each location may be country,
 state or city
public  class Location
{
    public int Id { get; set; }
    public string Name { get; set; }       
    public string Discriminator{get;set;} 

    public int? ParentLocationId { get; set; }
    public Location ParentLocation { get; set; }

    public ICollection<Location> ChildLocations { get; set; }
}

Sample Locations data

Id | Name | Discriminator | ParentLocationId
1 | India | Country | null
2 | Karnatka | State | 1
3 | Maharashtra | State | 1
4 | Banglore | City | 2


//this will contain all product categories + products itself
public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Category ParentCategory { get; set; }
    public int? ParentCategoryId { get; set; }

    public ICollection<Category> ChildCategories { get; set; }
}

Sample category Data

Id | Name | ParentCategoryId
1  | Electronics | Null
2  | Mobiles | 1
3 | Apple | 2
4 | Nokia | 2
5 | I phone-4 | 3
6 | Nokia-Some Model | 4

I have used a variable of Type=User in below class but i have'nt shown 'User' class here as it doesn't contain any thing spl

public class Purchase
{
    public int Id { get; set; }

    public User User { get; set; }
    [Required]
    public int UserId { get; set; }

    public Category Category { get; set; }
    [Required]
    public int CategoryId { get; set; }

    public Location Location { get; set; }
    [Required]
    public int LocationId { get; set; }
}

Note for a successful purchase order locationId must be cityId and categoryId should be lowest in hierarchy, eg categoryId cant be Mobile, it should be iphone-4 or nokia-some-model

Sample Purchase Orders Data

Id | CategoryId | LocationId | UserId
1  | 5 | 4  | 1 
1  | 5 | 4  | 2
1  | 5 | 4  | 3

Till now every thing is working fine for me, Below is my question

Iam creating filtering mechanism where i provide 2 things locationId(this location may be ID of country,state or city) and product id(this product may be any where in hierarchy, eg this value may be id of electronics or mobile or apple or iphone) and get a list of all purchases made that satisfies this

eg: sample filters that i may make

• a> find all mobile sale in a state (pass categoryId of mobile and locationId of a particular state)
• b> find all nokia sale in a country (pass categoryId of nokia and locationId of a particular country) and so on..

I am open to any suggestions also you can let me know if there is anything that's not been properly explained in this question.

Answer 1

That are hierarchical queries which are IMHO impossible to do effectively in linq-to-entities because linq-to-entities doesn't have any support for recursion needed to navigating through hierarchy.

These queries work best with some support from database - in case of SQL Server 2005 and newer you can use CTE and hierarchical queries. CTEs can be used in database views which can in turn be mapped to entity in EF but it will still not allow you to create filtering criteria you need because view can have only static structure.

For example you can use CTE to define view returning CityId and its CountryId in single record. You can then use this view and join it to purchases and filter by CountryId. But what if you need to search by StateId? Your view currently doesn't have StateId column defined - you can add it as another column but it will only complicate everything - you will have to know if you must filter by country or by state. What if you have another level? And one more? What if you cannot say upfront how many levels do you have? That is exactly what can happen with categories. View will not help you.

This would be probably possible, if EF had support for table valued functions - you would create a table valued function for Locations (it would dynamically return all cities belonging to passed LocationId) using CTE internally and similar function for Categories and you would map that functions in EDMX and use them in joins in linq-to-entities queries. There is one problem - EF doesn't support mapping table valued functions yet - it is planned for next major release.

If you need this kind of search you need plain old SQL + CTE.