none
Self Referencing table RRS feed

  • Question

  • I have a table

    CatategoryID int,
    Name varchar(50),
    ParentCategoryID int,
    IsDeleted bit

    1  "Root"  null  0
    2  "Sub Category 1"  1  0
    3  "Sub Category 2"  1  1

    Basically root categories have ParentCategoryID as null.

    to get root categories i use:
    using(TestDataContext dc = new TestDataContext())
    {
        var rootCategories = (from c in dc.Category
                     where c.ParentCategory == null
                     && IsDeleted == false
                     select c).ToList();

        //Calling function to loop thru all categories and load child categories
            LoopCategories(rootCategories);

    }

    private void LoopCategories(IEnumerable<Category> categories)
    {
        foreach (Category c in categories)
            {
                if (c.Categories != null)
                     LoopCategories(c.Categories);
            }
    }

    rootCategories populated correctly,but when calling LoopCategories,it ignore IsDeleted statement.
    Question,how to make it work???


    Also, if i just return rootCategories,without looping first,i get an error when i access Categories property
    that DataContext already Disposed. Any solution for this?

    Thanks for your help
    Monday, June 9, 2008 5:26 PM

Answers

  • The DataContext does take memory but is a necessary overhead if you wish to let the objects go back to the database to query for more information such as discovering relationships or making changes to them.

     

    In order you don't keep it around indefinitely and use a lot of memory you should create and dispose it for each logical unit of work.

     

    To make your queries work you could extend the class to have an additional property such as:

     

    public IEnumerable<Category> ActiveCategories

    {

      get

      {

        return from c in this.Categories where c.IsDeleted == false select c;

      }

    }

     

    [)amien

    Monday, June 9, 2008 9:43 PM
    Moderator
  • If you want to apply a filter that will apply when sub-collections are loaded, use DataLoadOptions.

     

    DataLoadOptions options = new DataLoadOptions();

    options.AssociateWith<Category>(c => c.Categories.Where(c2 => !c2.IsDeleted));

     

    using (TestDataContext dc = TestDataContext())

    {

        dc.LoadOptions = options;

        ...
    }

     

     

     

    Tuesday, June 10, 2008 3:01 PM
    Moderator

All replies

  • Because you are accessing the c.Categories property in LoopCategories it will load all the child relationships without checking the child's IsDeleted property.

     

    The DataContext error is being caused by the using() keyword wrapped around the DataContext declaration so it will be disposed the moment that block finishes.  Removing the using clause will let you access the DataContext after this block (loading child properties requires the DataContext is still around).

     

    [)amien
    Monday, June 9, 2008 6:23 PM
    Moderator
  • But how to make child relationship load with checking for IsDeleleted?

    And if im not disposing DataContext,will it waist memory and create more work for GC to dispose it later???
    Monday, June 9, 2008 8:47 PM
  • What you need to do indeed is

     

    Code Snippet

    using(SomethingContext ....)

    {

       .....

      ....

      return query.ToList();

     

    }

     

     

     

    Using statement means that any object associated will be disposed after going out of its scope. And by doing a return, you already are exiting from that scope so GC will dispose it in the next iteration with no problem.

     

    Monday, June 9, 2008 9:36 PM
  • The DataContext does take memory but is a necessary overhead if you wish to let the objects go back to the database to query for more information such as discovering relationships or making changes to them.

     

    In order you don't keep it around indefinitely and use a lot of memory you should create and dispose it for each logical unit of work.

     

    To make your queries work you could extend the class to have an additional property such as:

     

    public IEnumerable<Category> ActiveCategories

    {

      get

      {

        return from c in this.Categories where c.IsDeleted == false select c;

      }

    }

     

    [)amien

    Monday, June 9, 2008 9:43 PM
    Moderator
  • Sidar,
    your solution is not helping at all
    if i put return categories inside using,
    im still getting
    Cannot access a disposed object.
    Object name: 'DataContext accessed after Dispose.'.
    Tuesday, June 10, 2008 2:14 AM
  • I would try removing the using clause entirely and just let the object create/go out of scope normally.

    Can you post the code sample you are now trying?

    [)amien
    Tuesday, June 10, 2008 3:25 AM
    Moderator
  • Damien,this is a code i was trying

    function IEnumerable<Category> GetRootCategory()
    {
    using(TestDataContext dc = new TestDataContext())
    {
        var rootCategories = (from c in dc.Category
                     where c.ParentCategory == null
                     && IsDeleted == false
                     select c).ToList();

    return rootCategories;
    }
    }


    in my page,im calling this function to populate a tree control with root/child

    var categories =
    GetRootCategory();
    foreach(Category category in categories)
    {
    if (category.Categories.Count != 0) //this line give an error if im using 'using' syntax,without 'using' its working fine
    {
    //here i add child nodes
    }
    }

    Thank you
    Tuesday, June 10, 2008 2:47 PM
  • If you want to apply a filter that will apply when sub-collections are loaded, use DataLoadOptions.

     

    DataLoadOptions options = new DataLoadOptions();

    options.AssociateWith<Category>(c => c.Categories.Where(c2 => !c2.IsDeleted));

     

    using (TestDataContext dc = TestDataContext())

    {

        dc.LoadOptions = options;

        ...
    }

     

     

     

    Tuesday, June 10, 2008 3:01 PM
    Moderator
  • Matt thanks!!!
    Thats the most clean solution.
    But again,if i do it with 'using'
    same error:
    Cannot access a disposed object.
    Object name: 'DataContext accessed after Dispose.'.

    But now i removed all 'using; and it works perfect!!!!

    Thank you all!!!
    Tuesday, June 10, 2008 10:17 PM