none
EF CodeFirst Hierarchal Collections/Relationship Chain RRS feed

  • Question

  • public class Category
    {
         public int Id {get; set;}
         public virtual ICollection<Category> Categories {get;set;}
         public virtual ICollection<Product> Products {get;set;}
         public int ParentId {get;set;}
    }
    
    public class Product
    {
         public int Id {get;set;}
         public virtual ICollection<Category> Categories {get;set;}
    }
    


    So these are the 2 models I've built(All pertinent data). What I would like to do is make it so the collection of products on a category is equal to that of all the products associated to it and the products associated to its children.

    So if we had a catergory "TVs" and it had 2 children categories ("Plasmas" and "LCDs") I would like to make sure that the collections of products for "TVs" would contain all the products of itself,"Plasmas", and "LCDs" and be able to query against that with LINQ.

    Is there a way I could represent this type of relationship with the fluid api or maybe get this to work by any other recommended means?

     

    Thanks

    Sunday, September 4, 2011 8:46 PM

Answers

  • Hello,

    that not possible with "mapping". You must write query for that. For single hierarchy level it can be something like:

    var products = from c in context.Categories  // Top category<br/>                      where c.Id == topId
                          from cc in c.Categories          // Subcategories
                          from p in cc.Products             // Products of subcategories
                          select p;
    


    For complex hierarchies it is best to use stored procedure and CTE (common table expressions) to query hierarchical data. There is currently no way to make this work with complex hierarchies in EF - this will change one EF supports table valued functions which will wrap CTE SQL query. But even with table valued function it will be possible only with EDMX because fluent-api is not able to map SQL functions at the moment.

    Best regards,
    Ladislav

    Monday, September 5, 2011 12:41 PM

All replies

  • Hello,

    that not possible with "mapping". You must write query for that. For single hierarchy level it can be something like:

    var products = from c in context.Categories  // Top category<br/>                      where c.Id == topId
                          from cc in c.Categories          // Subcategories
                          from p in cc.Products             // Products of subcategories
                          select p;
    


    For complex hierarchies it is best to use stored procedure and CTE (common table expressions) to query hierarchical data. There is currently no way to make this work with complex hierarchies in EF - this will change one EF supports table valued functions which will wrap CTE SQL query. But even with table valued function it will be possible only with EDMX because fluent-api is not able to map SQL functions at the moment.

    Best regards,
    Ladislav

    Monday, September 5, 2011 12:41 PM
  • Hi,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 8, 2011 5:51 AM
    Moderator