LINQ to SQL -> Cannot access a disposed object. Object name: 'DataContext accessed after Dispose

Question

Let say I have 2 tables which is Student and School. Within my Student table I have a fkSchoolId linking to a School record. However, if I retrieve my record as below

public static List<Student> GetByType(string connString, int type)
{
    using (mydb_DataContext db = new mydb_dbDataContext(connString))
    {
        return (from t1 in db.Students
                where t1.type = type
                select t1).ToList();
    }
}

I will have the list of Student objects where I can access it in a foreach loop. But when I do as below, I will get an error when retrieving the school name.

foreach(Student student in DAL.StudentLogic.GetByType(5))
{
    string schoolName = student.School.Name;
}

System.ObjectDisposedException: 'Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'.'

May I know how can I get the foreign information stored in the return object so that I can access them? Or better way, if I can specify to load up just the school Name?

Updated: If I do as follow, it works, but not sure how much it will impact the performance. I will do a benchmark and update to this topic again next week.

public static List<Student> GetByType(string connString, int type)
{
    using (mydb_DataContext db = new mydb_dbDataContext(connString))
    {
        List<Student> students = (from t1 in db.Students where t1.type = type select t1).ToList();

        foreach(Student student in students)
        {
            student.School.Name = db.Schools.Where(q => q.SchoolId == student.fkSchoolId).FirstOrDefault().Name;
        }
    }
}

I'll be able to access to student.School.Name in my return object.

This solution will perform a separate query for each returned student. Shame to go to DB so many time.. — Gilad Green, Sep 09 '17 at 12:55
@GiladGreen I agreed.. Not the best performance.. I wonder why LINQ to SQL does not have a simple way to stat which I would like to bring back.. I tried DeferredLoadingEnabled but I'm still getting null foreign object. I do not wish to step into C#7. If I use Entity Framework, there is a .Include there, will it work with EF to solve my problem? — TPG, Sep 09 '17 at 13:11

Gilad Green · Answer 1 · 2017-09-10T05:13:38.247

Set the DeferredLoadingEnabled property to false:

Gets or sets a value that indicates whether to delay-load one-to-many or one-to-one relationships.

Therefor the related data will be retrieved when materializing the query and not retrieved in a later stage (after context was disposed)

public static List<Student> GetByType(string connString, int type)
{
    using (mydb_DataContext db = new mydb_dbDataContext(connString))
    {
        db.DeferredLoadingEnabled = false;
        return (from t1 in db.Students
                where t1.type = type
                select t1).ToList();
    }
}

However consider thinking (depends on overall design/ program requirements and load) of having the context kept open for the lifetime of the class this functions sits in (in seems like a DAL class). Then implement the IDisposable interface and dispose the context in it. (Remember that the Dispose must be called explicitly).

If all you want is the school name and you are using C# 7.0 you can use the named tuples this way:

public static List<(Student Student, string SchoolName)> GetByType(string connString, int type)
{
    using (mydb_DataContext db = new mydb_dbDataContext(connString))
    {
        return (from t1 in db.Students
                join school in db.Schoold on t1.SchoolId equals school.Id
                where t1.type = type
                select (t1, school.Name)).ToList();
    }
}

If you get compilation error CS8137 then you need to install the Nuget package of System.ValueTuple

Thanks for the suggestion. I will try it. I have just updated my question on another method that I'm trying. I will test the performance and update back to this thread. — TPG, Sep 09 '17 at 12:11
@teapeng - missed the linq to sql and not EF. Sorry. Have a look [here for setting the relationships](https://www.codeproject.com/Articles/37784/One-Many-and-One-One-relationship-using-LINQ-to-SQ) and otherwise you can perform a join to get the data — Gilad Green, Sep 09 '17 at 14:53

score 2 · Answer 2 · answered Sep 11 '17 at 12:26

With Linq2Sql you can use LoadWith, eg

using (mydb_DataContext db = new mydb_dbDataContext(connString))
{
    DataLoadOptions op = new DataLoadOptions();
    op.LoadWith<Student>(o => o.School);

    db.LoadOptions = op;
    return (from t1 in db.Students
        where t1.type = type
        select t1).ToList();
}

The one downside is that this is retrieving all columns in the school table.

LINQ to SQL -> Cannot access a disposed object. Object name: 'DataContext accessed after Dispose

2 Answers2