Longing to know

Entity Framework makes it possible to execute scalar-valued functions when querying models. However, it’s not very intuitive to get this working, and it’s not directly possible to map the returned scalar value to an instance of your model. Odd, right?

Entity Framework provides an interface to what it calls “Conventions” to allow you to provide mappings to SQL functions. The particular interface to add a convention is IStoreModelConvention. It requires implementing a single “Apply” method in which you can reference a .NET method, which is basically a dummy placeholder, and the SQL function, by name. Additionally, the “dummy method” must be decorated with the DbFunction attribute. ModelBuilder is then used to inject the convention. It’s a lot of connecting the dots.

In the case where I wanted to be able to utilize a function, there is a Customer table where the name of the customer can vary based on whether they are an individual/business or other types. As such, there is a type column that triggers how the name is derived. The resulting scalar-valued function simply would take the Id of the customer and return the name.

To get this all connected/working in EF, initially, it required creating the appropriate IStoreModelConvention implementation. My dummy method is called “CustomerName” and the scaled-valued funciton’s name is “fnCustomerName.” This is what my defined convention then looks like:

public class CustomerNameConvention : IStoreModelConvention<EdmModel>
{
    public void Apply(EdmModel item, DbModel model)
    {
        var parameter = FunctionParameter.Create(
            "customerId",
            model.GetStorePrimitiveType(PrimitiveTypeKind.Int32),
            ParameterMode.In);

        var returnValue = FunctionParameter.Create(
            "result",
            model.GetStorePrimitiveType(PrimitiveTypeKind.String),
            ParameterMode.ReturnValue);

        var function = item.CreateAndAddFunction(
            "fnCustomerName",
            "CustomerName",
            new[] { parameter },
            new[] { returnValue });
    }
}

Note that the GetStorePrimitiveType and CreateAndAddFunction are extension methods that wrap EF’s EdmFunctions to simplified things a bit. I find the code for these on github here, and the code is shown below. I did modify it a bit since it didn’t support having a store name different from the local “dummy” method name. The GetDefaultSchema method was broken too.

public static class DbMetadataExtensions
{
    /// <summary>
    /// Creates an EdmFunction for an EdmModel with the scaled-value function storeName and functionName definied
    /// </summary>
    /// <param name="item"></param>
    /// <param name="storeName"></param>
    /// <param name="functionName"></param>
    /// <param name="parameters"></param>
    /// <param name="returnValues"></param>
    /// <param name="body"></param>
    /// <returns></returns>
    public static EdmFunction CreateAndAddFunction(this EdmModel item, string storeName, string functionName,
        IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues, string body = null)
    {
        var payload = new EdmFunctionPayload
        {
            StoreFunctionName = storeName,
            Parameters = parameters,
            ReturnParameters = returnValues,
            Schema = item.GetDefaultSchema()
        };

        EdmFunction function = EdmFunction.Create(functionName, item.GetDefaultNamespace(), item.DataSpace, payload, null);
        item.AddItem(function);
        return function;
    }

    /// <summary>
    /// Converts a PrimitiveTypeKind to an EdmType
    /// </summary>
    /// <param name="model"></param>
    /// <param name="typeKind"></param>
    /// <returns></returns>
    public static EdmType GetStorePrimitiveType(this DbModel model, PrimitiveTypeKind typeKind)
    {
        return model
            .ProviderManifest
            .GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind)))
            .EdmType;
    }

    /// <summary>
    /// Retrieve the namespace for a given EdmModel
    /// </summary>
    /// <param name="layerModel"></param>
    /// <returns></returns>
    public static string GetDefaultNamespace(this EdmModel layerModel)
    {
        return layerModel
            .GlobalItems
            .OfType<EdmType>()
            .Select(t => t.NamespaceName)
            .Distinct()
            .Single();
    }

    /// <summary>
    /// Returns the default database schema for a given EdmModel
    /// </summary>
    /// <param name="layerModel"></param>
    /// <returns></returns>
    public static string GetDefaultSchema(this EdmModel layerModel)
    {
        return layerModel
            .Container
            .EntitySets
            .FirstOrDefault()
            .Schema;
    }
}

The “dummy” placeholder method is just a shell of a method:

public static class CustomerMethods
{
    [DbFunction("CodeFirstDatabaseSchema", "CustomerName")]
    public static string CustomerName(int customerId)
    {
        // This will not be invoked when working with LINQ to entities
        // For functions that aren't tied specifically to the entity PK, an
        // implementation would be useful
        return string.Empty;
    }
}

Finally, adding the convention to the DbContext is a matter of adding it to the Conventions collection:

modelBuilder.Conventions.Add(new CustomerNameConvention());

With all of the setup in place, querying for the Customer with the FullName must be accomplished by projecting into a non-EF entity model/class. I use an anonymous class:

 var repo = (IRepository<Customer>)kernel.GetService(typeof(IRepository<Customer>));
var list = repo
    .All()
    .Select(x => new
    {
        Id = x.Id,
        FirstName = x.FirstName,
        LastName = x.LastName,
        FullName = CustomerMethods.CustomerName(x.Id),
        CustomerName = x.CustomerName,
        CustomerType = x.CustomerType,
        CreateDate = x.CreateDate,
        ModifyDate = x.ModifyDate
    })
    .OrderBy(x => x.FullName)
    .ToList()

However, I find it’s much more useful if we maintain our original model type if we are using that as a DTO, as such. As I said, you can’t map an EF query directly to the entity when using Conventions, but you can simply instantiate the model from a chained select:

var list = repo
    .All()
    .Select(x => new
    {
        Id = x.Id,
        FirstName = x.FirstName,
        LastName = x.LastName,
        FullName = CustomerMethods.CustomerName(x.Id),
        CustomerName = x.CustomerName,
        CustomerType = x.CustomerType,
        CreateDate = x.CreateDate,
        ModifyDate = x.ModifyDate
    })
    .OrderBy(x => x.FullName)
    .ToList()
    .Select(x => new Customer
    {
        Id = x.Id,
        FirstName = x.FirstName,
        LastName = x.LastName,
        FullName = x.FullName,
        CustomerName = x.CustomerName,
        CustomerType = x.CustomerType,
        CreateDate = x.CreateDate,
        ModifyDate = x.ModifyDate
    });

Note that I did add [NotMapped] FullName property to my Customer model.

That’s it. I was pleased to see that this can be done, but it does like a certain panache and isn’t quite as flexible as it could be since projections are required.