Entity framework SqlQuery failing with no reason (ObjectDisposedException)

I have a method where two SQL queries take place, for example:

public async Task<Result> SomeMethod(string p1, string p2)
{
    Context.Database.CommandTimeout = 60;

    var param1 = new SqlParameter("@param1", SqlDbType.VarChar, 8);
    param1.Direction = ParameterDirection.Output;
    var param2 = new SqlParameter("@param2", SqlDbType.VarChar, 8);
    param2.Direction = ParameterDirection.Output;
    var param3 = new SqlParameter("@param3", SqlDbType.BigInt);
    param3.Value = DBNull.Value;
    var param4 = new SqlParameter("@param4", SqlDbType.BigInt);
    param4.Value = DBNull.Value;

    var result1 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
                new SqlParameter("@param1", p1), param1, param2
                ).FirstOrDefaultAsync();

    var result2 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
               new SqlParameter("@param1", p2), param3, param4
               ).FirstOrDefaultAsync();

    return new Result { Result1 = result1, Result2 = result2 };
}

Don’t mind variable names that’s just an example. The problem is that the second query doesn’t execute and throws error:

“System.ObjectDisposedException: ‘The ObjectContext instance has been
disposed and can no longer be used for operations that require a
connection.’”

Both queries are working. If I swap queries the second one is always failing. I have no idea why the second query is not working. I’ve googled the error and found out that it usually appears when you try to assign query result to some entity and use it outside the context scope what fails because of lazy-load of EF but here the string is returned form the procedure, not entity. I see no reasons why context should dispose after the first query.

Please ask me for any details.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Normally this error is associated with lazy loading which has no bearing in this usage. My suspicion is the async nature of the code.

First try making the first call synchronous:

var result1 = Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
            new SqlParameter("@param1", p1), param1, param2
            ).FirstOrDefault();

var result2 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
           new SqlParameter("@param1", p2), param3, param4
           ).FirstOrDefaultAsync();

If that works, switch to make the second call synchronous:

var result1 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
            new SqlParameter("@param1", p1), param1, param2
            ).FirstOrDefaultAsync();

var result2 = Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
           new SqlParameter("@param1", p2), param3, param4
           ).FirstOrDefault();

I’d expect if the first worked, the second would still fail. Then try adding a “dummy” call ahead of the first:

var dummy2 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
           new SqlParameter("@param1", p2), param3, param4
           ).FirstOrDefaultAsync();

var result1 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
            new SqlParameter("@param1", p1), param1, param2
            ).FirstOrDefaultAsync();

var result2 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
           new SqlParameter("@param1", p2), param3, param4
           ).FirstOrDefaultAsync();

If this fails reliably on the result1 execution then it would seem that SqlQuery may have a bug, potentially threading related. If this is the case, or in doubt, you should be able to work around this by explicitly scoping your DbContext for each call:

var param1 = new SqlParameter("@param1", SqlDbType.VarChar, 8);
param1.Direction = ParameterDirection.Output;
var param2 = new SqlParameter("@param2", SqlDbType.VarChar, 8);
param2.Direction = ParameterDirection.Output;
var param3 = new SqlParameter("@param3", SqlDbType.BigInt);
param3.Value = DBNull.Value;
var param4 = new SqlParameter("@param4", SqlDbType.BigInt);
param4.Value = DBNull.Value;

string result1, result2;

using (var context = ContextFactory.Create())
{
    result1 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
            new SqlParameter("@param1", p1), param1, param2
            ).FirstOrDefaultAsync();
}
using (var context = ContextFactory.Create())
{
    result2 = await Context.Database.SqlQuery<string>("[Some_Procedure] @param1,@param2,@param3",
           new SqlParameter("@param1", p2), param3, param4
           ).FirstOrDefaultAsync();
}
return new Result { Result1 = result1, Result2 = result2 };

Where ContextFactory is a new dependency injected into this service that can create a new DbContext.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0