Handling Entity Framework Stored Procedure ResultSets Polymorphically

• Download source files - 318.9 KB

Introduction

Microsoft’s Entity Framework (EF) has long offered many opportunities to build reusable or customizable data access repositories using polymorphism in the conceptual model, i.e., TPH, TPT, or TPC inheritance. Using these techniques, business logic can be developed that is agnostic to whether or not the data access layer presents a specific entity or one of its subclasses, a technique that is invaluable, for instance, in creating extensible SDKs. These techniques, however, are restricted currently to operations that return IQueryable<T>, which includes table-valued-function operations but not stored procedures. Stored procedure operations instead return ObjectResult<T>. In part, this is a reasonable restriction because the stored procedure can be modeled as returning a Complex Type, which has restricted usages. However, EntityObjects or POCOs are not precluded as the Type returned from the stored procedure.

Until Entity Framework 6.1, working with stored procedures in Code First implementations has been tedious at best, particularly if they return multiple resultsets. With 6.1, custom conventions can be developed that greatly ease the mechanics. One such convention, CodeFirstStoreFunctions, available as open source or as a .nuget package, is leveraged extensively here. The CodeFirstStoreFunctions.FunctionsConvention is described in detail by the author in a series of blog posts^1,2,3; a brief operational summary follows, as well as the usage pattern adopted herein.

Subclasses use FunctionsConvention’s constructor to provide the default database schema to which the procedures apply as well as a Type containing the methods that Entity Framework uses to invoke the stored procedures. In this article, this Type is a class into which the applicable DbContext is constructor injected. The Type’s stored procedure related methods are decorated with both a DbFunctionAttribute and a CodeFirstStoreFunctions.DbFunctionDetailsAttribute. At model creation, a registrar provided by the convention reflectively inspects the associated Type for its attribute decorated methods and injects the necessary mapping information for each into the DbModel.

FunctionsConvention greatly eases the mechanics of invoking the procedures and formalizes most of the complexities of dealing with multiple resultsets. However, the end result is still constrained by ObjectResult<T>. ObjectResult<T> has no public constructors, so there is no inherent override mechanism which a subclass of T could be returned by a repository developer. The code in this article provides such a mechanism using reflection, dynamic typing, and extension methods. A methodology to encapsulate the mechanics of unloading multiple resultsets from the underlying stream in a standard fashion is also provided.

The Models

The stored procedures illustrating the techniques in this article operate over a database containing hierarchical tables representing a segment of the Bentonian ‘Tree of Life’ for dinosaurs. The database, as is typical for Code First default implementations, has surrogate keys, and its associated conceptual model is illustrated by Figure 1.

The stored procedures transform the database into a composite key model shown in Figure 2; the specializations differ by whether or not the stored procedure returns a comment (if any) associated with the entity.

The POCO class hierarchies allow the tabular and stored procedure based entities to share as much commonality as possible while allowing each to be configured separately. Because there are no physical database entities with the composite keys, the population of the object graph shown in Figure 2 is partly done by the stored procedure and partly by repository code that populates the navigation properties after the procedure executes. To preclude Entity Framework from acting on the navigation properties in the composite model, each mapping must explicitly Ignore the navigation properties. Note that use of the NotMappedAttribute in some of the POCOs is due to a EF bug pending resolution which causes the DbModelBuilder to bypass Ignore configurations in ancestor classes. In addition, at runtime the DbContext must be configured to work only with the composite POCOs, not EF proxies, i.e., DbContext.Configuration.LazyLoadingEnabled = DbContext.Configuration.ProxyCreationEnabled = false.

Working With CodeFirstConventions

As mentioned in the Introduction, each collection of stored procedures must have a subclass of FunctionsConvention and a class containing stored procedure invocation code. In this sample, these classes are DboConventions and DboFunctions, respectively.

/// <summary>
///     Registrar for stored procedure function calls in DbContext
/// </summary>
public class DboConventions : FunctionsConvention
{
    public DboConventions() : base(DboFunctions.Schema, typeof (DboFunctions))
    {
    }
}

/// <summary>
///     Container for stored procedure invocation methods
/// </summary>
public abstract class DboFunctions : DbFunctionsBase
{
    public const string Schema = "dbo";

    public DboFunctions(DbContext ctxt) : base(ctxt)
    {
    }

    [DbFunction(DefaultDbContextName, "usp_Superorder_Dinosauria")]
    [DbFunctionDetails(DatabaseSchema = Schema,
        ResultTypes = new[] {typeof (Order_sp), typeof (SubOrder_sp), typeof (InfraOrder_sp)})]
    public ObjectResult<Order_sp> GetDinosauriaByOrderId(int orderId)
    {
        return ((IObjectContextAdapter) Context).ObjectContext.ExecuteFunction<Order_sp>(
            ExtractFunctionName(MethodBase.GetCurrentMethod()), new[]
            {
                new ObjectParameter("OrderId", orderId)
            });
    }

    [DbFunction(DefaultDbContextName, "usp_OrdersOfDinosauria")]
    [DbFunctionDetails(DatabaseSchema = Schema)]
    public ObjectResult<Order_sp> GetAllOrdersOfDinosauria()
    {
        return ((IObjectContextAdapter) Context).ObjectContext.ExecuteFunction<Order_sp>(
            ExtractFunctionName(MethodBase.GetCurrentMethod()), new ObjectParameter[0]);
    }
}

public class DboRepositoryFunctions : DboFunctions
{
:
}

The DbFunctionAttribute provides the mapping between the method name and the actual stored procedure name. The two methods illustrate procedure invocations that return single and multiple resultsets. In the multiple resultset case, the DbFunctionDetailsAttribute provides the in-order Types of each resultset returned. The importance of concrete DboRepositoryFunctions class is discussed later.

The DboFunctions class derives from DbFunctionsBase.

public abstract class DbFunctionsBase
{
    // Default name of a DbContext in EF
    protected const string DefaultDbContextName = "CodeFirstContainer";

    // DbContext containing the EntityFramework Conventions on
    // which the subclass depends
    protected readonly DbContext Context;

    /// <summary>
    ///     Constructor
    /// </summary>
    /// <param name="context">DbContext containing required Conventions</param>
    protected DbFunctionsBase(DbContext context)
    {
        Context = context;
    }

    /// <summary>
    ///     Creates an EntityFramework query string
    /// </summary>
    /// <param name="schema">database schema containing target function</param>
    /// <param name="method">
    ///     code method supporting function call.
    ///     Actual TVF names is either method name or FunctionName property of
    ///     associated DbFunctionAttribute
    /// </param>
    /// <param name="arguments">Parameters (if any) of function
    /// in order expected by function</param>
    /// <returns></returns>
    protected string ComposeTvfCall(MethodBase method, params ObjectParameter[] arguments)
    {
        var result = new StringBuilder("[");
        // The 'schema' position in an entity query is taken by the context name
        result.Append(((IObjectContextAdapter)
             Context).ObjectContext.DefaultContainerName).Append("].[");
        result.Append(ExtractFunctionName(method)).Append("](");

        IEnumerable<string> argnames = (from arg in arguments select "@" + arg.Name);
        result.Append(string.Join(", ", argnames));

        result.Append(")");
        return result.ToString();
    }

    /// <summary>
    ///     Returns name of method or of associated DbFunctionAttribute
    /// </summary>
    /// <param name="method">Method decorated with DbFunction</param>
    /// <returns>FunctionName, either that of DbFunction or of method</returns>
    protected string ExtractFunctionName(MethodBase method)
    {
        var attr = method.GetCustomAttribute(typeof (DbFunctionAttribute),
                                                     false) as DbFunctionAttribute;
        return attr == null ? method.Name : attr.FunctionName;
    }
}

This class provides a placeholder for the DbContext associated with the procedures and provides two helpers that assist in implementing subclasses. ExtractFunctionName probes the DbFunctionAttribute for the correct method name for the EF’s ExecuteFunction to call. As mentioned previously, the FunctionsConvention supports table-valued-functions as well as procedures, and ComposeTvfCall assures that the subclass composes the EF call correctly. ComposeTvfCall is included for completeness, and its use is not illustrated herein.

At this point, a DbContext can be created and dynamically configured.

public class DinoContext : DbContext
{
    public DinoContext() : base(Settings.Default.PolymorphicResultSetsConnectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly());
        modelBuilder.Conventions.AddFromAssembly(Assembly.GetExecutingAssembly());
    }
}

The context can be injected into a repository implementation:

public class DinoRepository : IDinosauriaRepository
{
    public DinoRepository(DbContext dbContext)
    {
        dbContext.Configuration.LazyLoadingEnabled = false;
        dbContext.Configuration.ProxyCreationEnabled = false;
        RepositoryFunctions = new DboRepositoryFunctions(dbContext);
    }

    public DboRepositoryFunctions RepositoryFunctions { get; protected set; }
    :
}

Using a helper class DinosauriaResults:

public class DinosauriaResults
{
    public List<Order_sp> Orders { get; set; }
    public List<SubOrder_sp> SubOrders { get; set; }
    public List<InfraOrder_sp> InfraOrders { get; set; }
}

the methods within the repository implementation can now directly invoke the stored procedures via the methods exposed in DboRepositoryFunctions.

public virtual Order_sp GetDinosauriaGraphTheHardWay(int orderId)
{
    var queryResult = new DinosauriaResults();
    var firstQuery = RepositoryFunctions.GetDinosauriaByOrderId(orderId);
    queryResult.Orders = (from o in firstQuery select o).ToList();
    var secondQuery = firstQuery.GetNextResult<SubOrder_sp>();
    queryResult.SubOrders = (from s in secondQuery select s).ToList();
    var thirdQuery = secondQuery.GetNextResult<InfraOrder_sp>();
    queryResult.InfraOrders = (from io in thirdQuery select io).ToList();
    var result = ReconnectGraph(queryResult);
    return result[0];
}

Though straight forward, this code exhibits the tight coupling to ObjectResult<T> mentioned in the introduction. This is not troublesome until a specialization of DboFunctions is introduced that calls a second stored procedure returning the subclasses of the entities of those in the first.

public class PolymorphicConventions : FunctionsConvention
{
    public PolymorphicConventions()
        : base(DboFunctions.Schema, typeof (PolymorphicResults))
    {
    }
}

public class PolymorphicResults : DboRepositoryFunctions
{
    public PolymorphicResults(DbContext dbContext) : base(dbContext)
    {
        InvokeSuperorderDinosauria.Override(new MultiResultSetInvocation(GetType(),
            "GetDinosauriaByOrderIdWithComments"));
        InvokeOrdersOfDinosauria.Override(new SingleResultSetInvocation(GetType(),
            "GetAllOrdersOfDinosauriaWithComments"));
    }

    [DbFunction(DefaultDbContextName, "usp_Superorder_Dinosauria_Commented")]
    [DbFunctionDetails(DatabaseSchema = Schema,
        ResultTypes = new[] {typeof (Order_spWithComment),
               typeof (SubOrder_sp), typeof (InfraOrder_spWithComment)})
    ]
    public ObjectResult<Order_spWithComment> GetDinosauriaByOrderIdWithComments(int orderId)
    {
        return ((IObjectContextAdapter) Context).ObjectContext.ExecuteFunction<Order_spWithComment>(
            ExtractFunctionName(MethodBase.GetCurrentMethod()), new[]
            {
                new ObjectParameter("OrderId", orderId)
            });
    }

    :
}

A subclass of the repository can then override the implementation using the new procedure function.

public class PolymorphicRepository : DinoRepository, IDinosauriaRepository
{
    public PolymorphicRepository(DbContext dbContext) : base(dbContext)
    {
        RepositoryFunctions = new PolymorphicResults(dbContext);
    }

    public override Order_sp GetDinosauriaGraphTheHardWay(int orderId)
    {
        var queryResult = new DinosauriaResults();
        var firstQuery = (RepositoryFunctions as PolymorphicResults).
                                GetDinosauriaByOrderIdWithComments(orderId);
        queryResult.Orders = new List<Order_sp>((from o in firstQuery select o));
        var secondQuery = firstQuery.GetNextResult<SubOrder_sp>();
        queryResult.SubOrders = (from s in secondQuery select s).ToList();
        var thirdQuery = secondQuery.GetNextResult<InfraOrder_spWithComment>();
        queryResult.InfraOrders = new List<InfraOrder_sp>((from io in thirdQuery select io));
        var result = ReconnectGraph(queryResult);
        return result[0];
    }
}

FunctionsConvention requires that the stored procedure invocation code return an ObjectResult<T>, as does GetNextResult<T> when processing the subsequent resultsets. Since ObjectResult<T> has no public constructor, the subclass must reimplement the base class functionality. This code duplication as well as the tedium associated with multiple resultsets is the target of the next step.

ResultSetInvocation Classes

Reflection offers a mechanism that can emulate the overloading of ObjectResult<T> functions. The solution begins by encapsulating the MethodInfo of a stored procedure function (ProcedureInfo) in a SingleResultSetInvocation class.

public class SingleResultSetInvocation
{
    public static BindingFlags BindingFlags = BindingFlags.Instance |
                              BindingFlags.NonPublic | BindingFlags.Public;

    public SingleResultSetInvocation(MethodInfo method)
    {
        if (method == null) throw new ArgumentNullException
                    ("method", @"Missing ProcedureMethod specification");
        if (method.ReturnType.IsGenericType &&
            ProcedureMethod = method;
        else
        {
            throw new ArgumentException("Specified method does not derived from ObjectResult<T>.");
        }
        ResultTypes = ProcedureMethod.ReturnType.GetGenericArguments();
        GenericPopulateResultSet = GetType().GetMethod("PopulateFromQueryResult");
    }

    public SingleResultSetInvocation(Type t, string methodName) :
                                 this(t.GetMethod(methodName, BindingFlags))
    {
    }

    public Type[] ResultTypes { get; protected set; }

    /// <summary>
    ///     Initialized at construction this property is used to construct
    /// </summary>
    public MethodInfo GenericPopulateResultSet { get; private set; }

    /// <summary>
    ///     Method to call at runtime
    /// </summary>
    public MethodInfo ProcedureMethod { get; private set; }
        :
}

The constructors assure that the return Type of the specified method is ObjectResult<T>. The return type’s generic argument is cached in the ResultTypes array.

Helper methods in this class provide much of the runtime functionality. GetFirstResult returns the first QueryResult<T> produced by EF’s invocation of the stored procedure. By using dynamic typing, subsequent processing is simplified.

public dynamic GetFirstResult(object target, params object[] arguments)
{
    return ProcedureMethod.Invoke(target, arguments);
}

PopulateFromQueryResult unloads the resultset from a particular query into a List supporting the resultset Type.

public List<T> PopulateFromQueryResult<T>(dynamic queryResult) where T : class
{
    var list = new List<T>();
    foreach (dynamic r in queryResult)
    {
        list.Add(r as T);
    }
    return list;
}

The convention used by SingleResultSetContainer is that the resultsets are unloaded into a container class similar to DinosauriaResults shown above, i.e., a class that has a List<T> property corresponding to a resultset Type or one of its ancestors. The GetResultSetPropertyFromContainer method finds such a property in the container object for a particular Type.

public PropertyInfo GetResultSetPropertyFromContainer(Type propertyType, object container)
{
    PropertyInfo result =
        (from prop in container.GetType().GetProperties
                (BindingFlags.Public | BindingFlags.Instance)
            where prop.PropertyType.IsGenericType
                  && prop.PropertyType.GetGenericTypeDefinition() == typeof (List<>)
                  && prop.PropertyType.GetGenericArguments().First().
                                       IsAssignableFrom(propertyType)
            select prop).FirstOrDefault();
    if (result == null)
    {
        throw new ArgumentException(
            string.Format("Specified result container does not contain List<{0}> property.",
                propertyType.FullName));
    }
    return result;
}

The Override method allows the ProcedureMethod to be changed programmatically from another instance of SingleResultSetContainer. However, the emulated override must have the same number and Type of parameters as the original ProcedureMethod, and the return Type must be in the same Type hierarchy.

public void Override(SingleResultSetInvocation from)
{
    if (ProcedureMethod != null && !IsAssignableFrom(from))
    {
        throw new MethodAssignmentException();
    }
    ProcedureMethod = from.ProcedureMethod;
    ResultTypes = from.ResultTypes;
}

public bool IsAssignableFrom(SingleResultSetInvocation to)
{
    if (to.ResultTypes.Count() != ResultTypes.Count()) return false;
    ParameterInfo[] toParms = to.ProcedureMethod.GetParameters();
    ParameterInfo[] myParms = ProcedureMethod.GetParameters();
    if (myParms.Count() != toParms.Count()) return false;
    if (myParms.Any())
    {
        if (!myParms.Select((p, i) =>
            ReferenceEquals(p.ParameterType, toParms[i].ParameterType))
            .Aggregate((bResult, t) => bResult &= t)) return false;
    }
    return ResultTypes.Select((t, i) => t.IsAssignableFrom(to.ResultTypes[i]))
        .Aggregate((bResult, next) => bResult &= next);
}

MultiResultSetInvocation adds a specialization for procedures that return more than one resultset. Its constructors use the ResultType[] from the DbFunctionDetailsAttribute to initialize the class.

public class MultiResultSetInvocation : SingleResultSetInvocation
{
    /// <summary>
    ///     Constructor acquires ResultTypes from DbFunctionDetailsAttribute
    /// </summary>
    /// <param name="method"></param>
    public MultiResultSetInvocation(MethodInfo method)
        : base(method)
    {
        var details =
            method.GetCustomAttribute(typeof (DbFunctionDetailsAttribute))
                         as DbFunctionDetailsAttribute;
        if (details == null)
            throw new ArgumentNullException("method",
                string.Format("Missing DbFunctionDetailsAttribute on specified method {0}.{1}.",
                    method.DeclaringType.FullName, method.Name));
        ResultTypes = details.ResultTypes;
    }

    public MultiResultSetInvocation(Type t, string method) : this(t.GetMethod(method))
    {
    }
    :
}

MultiResultSetInvocation also provides a GetNextResult to reflectively call QueryResult<T>.GetNextResult<T’>(). Again dynamic typing sidesteps the problems associated with direct use of QueryResult<T>.

public dynamic GetNextResult(dynamic currentResult, Type nextResultType)
{
    const string getNextResult = "GetNextResult";
    MethodInfo queryMeth = currentResult.GetType().GetMethod(getNextResult);
    MethodInfo genericRestrictions = queryMeth.MakeGenericMethod(nextResultType);
    return genericRestrictions.Invoke(currentResult, null);
}

Armed with the invocation classes, an extension method to DbFunctionsBase can now be created to do all of the work of calling the procedure and unloading the resultset streams. The use of an extension method assures that the correct DbFunctionsBase this is used to start the query process.

public static void Load(this DbFunctionsBase f,
        SingleResultSetInvocation invoker, object resultSetContainer,
    object[] parameters)
{
    dynamic query = null;

    Array.ForEach(invoker.ResultTypes, t =>
    {
        // find the property in the result container that will hold the returned data
        PropertyInfo propInfo =
             invoker.GetResultSetPropertyFromContainer(t, resultSetContainer);
        // find the appropriate query object
        query = query == null
            ? invoker.GetFirstResult(f, parameters)
            : ((MultiResultSetInvocation) invoker).GetNextResult(query, t);
        // create the generic method that unloads the resultset from the database stream
        MethodInfo method =
            invoker.GenericPopulateResultSet.MakeGenericMethod(new[]
            {propInfo.PropertyType.GetGenericArguments().First()});
        // unload the data and save it in the container
        propInfo.SetValue(resultSetContainer, method.Invoke(invoker, new object[] {query}));
    });
}

Using the Code

In the example, two protected invocation class properties are added to the DboRepositoryFunctions class. These properties are bound to a specific stored procedure in the DboFunctions class during construction.

public class DboRepositoryFunctions : DboFunctions
{
    public DboRepositoryFunctions(DbContext dbContext) : base(dbContext)
    {
        InvokeSuperorderDinosauria =
         new MultiResultSetInvocation(GetType(), "GetDinosauriaByOrderId");

        InvokeOrdersOfDinosauria =
          new SingleResultSetInvocation(GetType(), "GetAllOrdersOfDinosauria");
    }

    protected MultiResultSetInvocation InvokeSuperorderDinosauria { get; private set; }
    protected SingleResultSetInvocation InvokeOrdersOfDinosauria { get; private set; }

    :
}

Two methods are then added to DboRepositoryFunctions to reflectively invoke the procedures and return the results.

public DinosauriaResults GetDinosauriaOrder(int orderId)
{
    var result = new DinosauriaResults();
    this.Load(InvokeSuperorderDinosauria, result, new object[] {orderId});
    return result;
}

public DinosauriaResults GetAllDinosauria()
{
    var result = new DinosauriaResults();
    this.Load(InvokeOrdersOfDinosauria, result, new object[0]);
    return result;
}

The repository implementation is then amended to use these new functions to implement its interface.

public class DinoRepository : IDinosauriaRepository
{
    :

    public DinosauriaResults GetDinosauriaResultsByOrderId(int orderId)
    {
        return RepositoryFunctions.GetDinosauriaOrder(orderId);
    }

    public Order_sp GetDinosauriaGraph(int orderId)
    {
        var results = GetDinosauriaResultsByOrderId(orderId);
        return ReconnectGraph(results)[0];
    }
    :
}

Now the stored procedures can easily be changed in the specialization of DboRepositoryFunctions by Overriding the stored procedure properties during construction.

public class PolymorphicResults : DboRepositoryFunctions
{
    public PolymorphicResults(DbContext dbContext) : base(dbContext)
    {
        InvokeSuperorderDinosauria.Override(new MultiResultSetInvocation(GetType(),
            "GetDinosauriaByOrderIdWithComments"));
        InvokeOrdersOfDinosauria.Override(new SingleResultSetInvocation(GetType(),
            "GetAllOrdersOfDinosauriaWithComments"));
    }
    :
}

No implementation is required in the subclass of DboRepositoryFunctions or in the repository that uses it. Subclass entities are now materialized without major or repetitive development.

Running the Samples

The provided sample contains a series of Visual Studio 2013 Unit Tests that exercise the code in this article. The associated database is delivered as an mdf file in the solution directory. The mdf file is attached to the localDb and is referenced from a connection string in the project’s app.config.

Packaging Note

The hierarchy of classes derived from DbFunctionsBase is largely based on the desire to keep the CodeFirstStoreFunctions related methods separate from methods involved in the invocation process. This hierarchy also allows the abstract class to be reused in other contexts. Ideally, the CodeFirstStoreFunctions methods would be in a protected scope, but as of this writing the FunctionsConvention registrar does not consider BindingFlags.NonPublic methods when probing for functions. A source code change would be required to make it do so.

References

• ¹Pawel Kadluczka. Support for Store Functions (TVFs and Stored Procs) in Code First (Entity Framework 6.1). 9 Apr 2014.
• ²Pawel Kadluczka. The Beta Version of Store Functions for EntityFramework 6.1.1+ Code First Available. 11 Aug 2014.
• ³Pawel Kadluczka. The final version of the Store Functions for EntityFramework 6.1.1+ Code First convention released. 18 Oct 2014.