Tag Archives: entity framework

The ObjectContext instance has been disposed and can no longer be used for operations that require a connection

TL;DR: Don’t return task before disposing context, await it instead

If you’re getting an Entity Framework exception:

System.ObjectDisposedException: The ObjectContext instance has been disposed and can no longer be used for operations that require a connection
at System.Data.Entity.Core.Objects.ObjectContext.ReleaseConnection()
at System.Data.Entity.Core.Objects.ObjectContext.d__3d`1.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Core.Objects.ObjectContext.d__39.MoveNext()

in the code such as:

public Task<Order> GetOrders()
{
    using (var dbContext = new MyDbContext(connectionString))
    {
        return dbContext.Orders.ToArrayAsync()
    }
}

then it means that you’re disposing the context before the task is completed. Await it, indeed:

public async Task<Order> GetOrders()
{
    using (var dbContext = new MyDbContext(connectionString))
    {
        return await dbContext.Orders.ToArrayAsync()
    }
}

Happy awaiting!

How to generate ids programmatically using SQL Server and Entity Framework

TL;DR: create a sequence and execute stored proc to read from it

Generating ids in the database is used to be the default and near to the only one approach but now days this trend is fading away. Here’s some scenarios discovered by me recently where it doesn’t play well:
CQRS (see the comments, also Steven’s blog)
– bulk insert into many-to-many mapping table (see my previous post)

So here’s a solution. First, create a sequence (available in SQL Server 2012 or later, and in SQL Azure):

create sequence dbo.OrderId as int
start with 1
increment by 1

Then, read from it using the built-in stored procedure:

exec sys.sp_sequence_get_range @sequence_name, @range_size, @range_first_value OUT" 

And finally, execute the stored procedure programmatically. In this particular example, using Entity Framework:

 public async Task<int> GetSequenceStart(string sequenceName, int count)
{
    var sequenceNameParam = new SqlParameter
    {
        ParameterName = "@sequence_name",
        SqlDbType = SqlDbType.NVarChar,
        Direction = ParameterDirection.Input,
        Value = sequenceName
    };
    var countParam = new SqlParameter
    {
        ParameterName = "@range_size",
        SqlDbType = SqlDbType.Int,
        Direction = ParameterDirection.Input,
        Value = count
    };
    var outputParam = new SqlParameter
    {
        ParameterName = "@range_first_value",
        SqlDbType = SqlDbType.Variant,
        Direction = ParameterDirection.Output
    };

    await _dbContext.ExecuteSqlCommandAsync(
        "exec sys.sp_sequence_get_range @sequence_name, @range_size, @range_first_value OUT",
        sequenceNameParam,
        countParam,
        outputParam);

    return (int)outputParam.Value;
}

The drawbacks of this solution is that it doesn’t scale that well:
– if you’re using more than one database, you have to have one of them as a master and it will receive higher load.
– each application instance must be fully aware of such hierarchy what crosses the boundaries of horizontal scaling (aka scaling out).

Happy sequencing!

How to map linking table for many-to-many relationship using Entity Framework 6

TL;DR: use synonym.

As promised earlier to myself, I blog about interesting challenges and the ways I solved them.

The other day, me and my team faced a limitation in EF6: you can either configure a linking table for many-to-many relationship between two entities or map this table to an entity. But not both simultaneously.

For instance, for the relationship between Orders and Addresses, when one order can be linked to multiple addresses (shipping, billing, legal, etc.) and one address can be used for multiple orders.

modelBuilder.Entity<Order>
            .HasMany(x => x.Addresses)
            .WithMany(x => x.Orders)
            .Map(c => c.ToTable("OrderAddresses")
                       .MapLeftKey("OrderId")
                       .MapRightKey("AddressId"));

And in the same time you’d like to have the intermediate, linking entity to be mapped too. To query it independently or for bulk operations such as insert.

modelBuilder.Entity<OrderAddress>
            .ToTable("OrderAddresses");

This case you’ll get an exception during context configuration validation:

System.InvalidOperationException: The navigation property ‘Addresses’ declared on type ‘Order’ has been configured with conflicting mapping information.

The solution would be to create a synonym (which in my project we keep in a separate schema called syn):

create schema syn
create synonym syn.OrderAddresses for dbo.OrderAddresses

and map the intermediate entity to it:

modelBuilder.Entity<OrderAddress>
            .ToTable("OrderAddresses", "syn");

Note the overload accepting the schema name.

Now you can bulk insert into this table directly:

using (var scope = new TransactionScope())
{
    dbContext.BulkInsert(orders);
    dbContext.BulkInsert(addresses);
    dbContext.BulkInsert(orderAddresses);

    scope.Complete();
}

We’re using EntityFramework.BulkInsert but also you can try EntityFramework.Utilities that supports both bulk insert and delete. However it also has a bug and doesn’t expose an overload accepting SqlBulkCopyOptions.

Happy mapping!