Design Meeting Notes - January 31, 2013

DbContext and transactions

Using an external transaction

The first scenario here involves an application that:

  • Is managing its own connection
  • is managing its own transaction
  • Wants EF to use this connection and transaction

For example:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    using (var transaction = connection.BeginTransaction())
    {
        // Do some stuff with connection and transaction

        using (var context = new MyContext(connection, contextOwnsConnection: false))
        {
            // Do EF stuff--the transaction should be used
        }

        using (var context = new MyContext(connection, contextOwnsConnection: false))
        {
            // Do more EF stuff--the transaction should be used
        }

        transaction.Commit();
    }
}

The problem to solve here is that the DbConnection API does not have a way to get the current transaction. It can only be obtained from BeginTransaction.

Possible solutions:

  • Add DbContext constructors that accept a DbTransaction. Issues:
    • The app may not have the transaction at the time the context is being constructed, especially if EF is managing connections
    • There are already a lot of constructors and we don’t want to keep adding overloads
  • Provide a method on DbContext.Database to set the transaction
    • Apps could still define derived context constructors that call the setter
    • This method can also be used if EF is managing the transaction (see below)

The second solution looks like the better option. Code would look something like this (method name may change):

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    using (var transaction = connection.BeginTransaction())
    {
        // Do some stuff with connection and transaction

        using (var context = new MyContext(connection, contextOwnsConnection: false))
        {
            context.Database.SetTransaction(transaction);
            // Do EF stuff--the transaction should be used
        }

        using (var context = new MyContext(connection, contextOwnsConnection: false))
        {
            context.Database.SetTransaction(transaction);
            // Do more EF stuff--the transaction should be used
        }

        transaction.Commit();
    }
}

Note that in this case a new context is being created and SetTransaction is being called on each context. If a context is created using the store connection and it is used without calling SetTransaction, then the behavior will be dependent on the behavior of the lower levels—most likely it will result in exceptions for all providers.

We have no general way to tell if SetTransaction is called with an invalid (e.g. disposed/committed/rolled back) transaction. The only thing we can do is let lower levels of the stack fail.

Variations of using an external transaction

A variation of the scenario above is to have EF create and manage connections, but still use an external transaction. The SetTransaction method should work for this too:

using (var context = new MyContext())
{
    context.Database.Connection.Open();

    using (var transaction = context.Database.Connection.BeginTransaction())
    {
        context.Database.SetTransaction(transaction);

        // Do some stuff with context and/or connection and/or transaction
    }
}

However, it may be useful to provide a single method that would do all these things. For example:

using (var context = new MyContext())
{
    using (var transaction = context.Database.BeginTransaction())
    {
        // Do some stuff with context and/or connection and/or transaction
    }
}

Note that there are cases with these where EF could attempt to use the transaction after it has been disposed. In general it is not possible to check that a transaction has been disposed without wrapping the transaction or requiring a notification from the app. However, many providers set the transaction to null on the connection when it is disposed, so we can use this. We can also allow SetTransaction(null) to mean stop using the transaction that I previously set and start doing your own thing.

We will continue the discussion of these and other related cases in a future meeting.

Code First: modification function mapping

This is the first part of the work to support mapping to stored procedures using Code First. This initial discussion covers the mappings that will be created by convention when an entity type is configured to use function mappings. The API to customize how the mapping happens (e.g. changing the name of the stored proc) will be covered at a later date.

Function mapping is triggered by new entity config API (name may change):

modelBuilder.Entity<Spatial_Customer>().MapToFunctions();
  • Maps all derived types to functions
  • Throws if we find a base type not mapped to functions

Store metadata

Store metadata will be created by convention. Specifically:

  • Generates Insert, Update and Delete store functions and corresponding MSL (ModificationFunctionMapping).
  • Function names of form: <entity type name>_<operation_name>
<Function Name="InsertOrderLine"
          Aggregate="false"
          BuiltIn="false"
          NiladicFunction="false“
          IsComposable="false"
          ParameterTypeSemantics="AllowImplicitConversion"
          Schema="dbo">
    <Parameter Name="Quantity" Type="smallint" Mode="In" />
    <Parameter Name="Price" Type="decimal" Mode="In" />
    <Parameter Name="Total" Type="decimal" Mode="In" />
    <Parameter Name="IsShipped" Type="bit" Mode="In" />
    <Parameter Name="ProductId" Type="int" Mode="In" />
    <Parameter Name="Sku" Type="nvarchar" Mode="In" />
    <Parameter Name="Order_OrderId" Type="int" Mode="In" />
</Function>

Mapping metadata (Insert)

  • Generates property bindings for scalar/complex properties
  • Generates result bindings for any SGP props
    • Return values must be returned through resultset
<InsertFunction FunctionName="CodeFirstDatabaseSchema.InsertOrderLine">
    <ScalarProperty Name="Quantity" ParameterName="Quantity" Version="Current" />
    <ScalarProperty Name="Sku" ParameterName="Sku" Version="Current" />
    <AssociationEnd AssociationSet="Order_OrderLines" From="Order_OrderLines_Target"
            To="Order_OrderLines_Source">
        <ScalarProperty Name="OrderId" ParameterName="Order_OrderId" Version="Current" />
    </AssociationEnd>
    <ResultBinding Name="Id" ColumnName="Id" />
</InsertFunction>

Mapping metadata (Update)

  • Generates property bindings for scalar/complex properties
  • Generates result bindings for any store-generated computed props
  • Uses original values for concurrency properties (IsConcurrencyToken())
    • This will cause introduction of the RowsAffected output parameter
    • For concurrency tokens that are not computed there will be both current and original mappings
    • Original values will always follow a naming convention ending in _original even if no current with same name
<UpdateFunction FunctionName="CodeFirstDatabaseSchema.UpdateOrderLine">
    <ScalarProperty Name="Id" ParameterName="Id" Version="Current" />
    <ScalarProperty Name="Sku" ParameterName="Sku" Version="Current" />
    <AssociationEnd AssociationSet="Order_OrderLines" From="Order_OrderLines_Target"
            To="Order_OrderLines_Source">
        <ScalarProperty Name="OrderId" ParameterName="Order_OrderId" Version="Current" />
    </AssociationEnd>
</UpdateFunction>

Mapping metadata (Delete)

  • Generates property bindings for key properties and IA FKs (original values)
  • Adds parameters for concurrency properties (IsConcurrencyToken())
    • Follow same _original naming convention we use for update
    • Introduce RowsAffected output parameter
<DeleteFunction FunctionName="CodeFirstDatabaseSchema.DeleteOrderLine">
    <ScalarProperty Name="Id" ParameterName="Id" Version="Original" />
    <AssociationEnd AssociationSet="Order_OrderLines" From="Order_OrderLines_Target"
            To="Order_OrderLines_Source">
        <ScalarProperty Name="OrderId" ParameterName="Order_OrderId" Version="Original" />
    </AssociationEnd>
    </DeleteFunction>

Many-to-many

  • Modification functions are specified via Association Set Mapping.
  • No update function
  • Generate when both ends mapped to functions
  • Name is operation_name + left entity name + right entity name
<AssociationSetMapping Name="CourseInstructor“ TypeName="SchoolModel.CourseInstructor“ StoreEntitySet="CourseInstructor">
    <ModificationFunctionMapping>
        <InsertFunction FunctionName="SchoolModel.Store.InsertCourseInstructor" >
            <EndProperty Name="Course">
                <ScalarProperty Name="CourseID" ParameterName="courseId"/>
            </EndProperty>
            <EndProperty Name="Person">
                <ScalarProperty Name="PersonID" ParameterName="instructorId"/>
            </EndProperty>
        </InsertFunction>
        <DeleteFunction FunctionName="SchoolModel.Store.DeleteCourseInstructor"> …
    </ModificationFunctionMapping>
</AssociationSetMapping>

Last edited Feb 1, 2013 at 3:56 PM by ajcvickers, version 2

Comments

AndrewPeters Feb 8, 2013 at 4:52 PM 
@camelinckx,

It will be possible for you to create your own conventions that implement custom function naming patterns. In EF6 this is possible because of the new Custom Conventions feature that let's you easily do model-wide configuration.

Regarding existing databases, Code First will work fine provided the database contains sprocs matching those configured on your model.

Cheers,
Andrew.

camelinckx Feb 8, 2013 at 4:36 PM 
The stored procedures used for EF CRUD operations might need to be dedicated for EF calling only and avoid using them elsewhere in the DAL, otherwise future changes on business logic that starts to depend on those SPs might pressure changes into them that breaks the EF CRUD calls.
Should we propose a recommended naming convention for EF stored procedures such as {schema}.EF_{Entity}_{CRUD operation}, that keeps all EF related SPs logically organized side-by-side and easily identified by DBAs and DEVs for their intended use.
Ex.
dbo.EF_Product_Select
dbo.EF_Product_Insert
dbo.EF_Product_Update
dbo.EF_Product_Delete
This shouldn’t be a hard requirement, flexible mapping/overriding of Mapped Function to SP should be allowed, but having a guideline to follow that is facilitated by the tooling would be beneficial for new systems and general adoption of EF.

camelinckx Feb 8, 2013 at 4:31 PM 
In Enterprise grade scenarios, it might not be allowed to have EF auto-create the SQL objects for schemas and stored procedures. Many reasons are commonly known: these need to be reviewed by DBAs , need to follow naming conventions specific to the domain / company, etc.
(Granted that auto-generated scripts to disk for review and manual application is helpful.)
Is the vision to implement SP support in a way that Code First with Existing Database (code centric) can utilize this?

camelinckx Feb 8, 2013 at 3:39 PM 
Hi, glad to see the first notes on Stored Procedures for Code First.
In the "Store metadata" subtitle it says "Function names of form: <entity type name>_<operation_name>" followed by an example in which starts with: "<Function Name="InsertOrderLine"...
Shouldn't this be <Function Name="OrderLine_Insert" ... ?