Entity Framework 6 Code First function mapping

Question

I want integrate Entity Framework 6 to our system, but have problem.

1. I want to use Code First. I don’t want to use Database First *.edmx file for other reasons.
2. I use attribute mapping [Table], [Column] and this works fine
3. Database has many User-Defined Functions and I need to use them in Linq To Entities query.

Problem is:

I cannot map function via attribute like [Table], [Column]. Only 1 attribute is available [DbFunction], which requires *.edmx file.

I’m ok to have functions mapping in *.edmx file, but it means I cannot use attributes mapping for Entities: [Table], [Column]. Mapping must be full in *.edmx or in attributes.

I tried to create DbModel and add function via this code:

public static class Functions
{
    [DbFunction("CodeFirstNamespace", "TestEntity")]
    public static string TestEntity()
    {
        throw new NotSupportedException();
    }
}


public class MyContext : DbContext, IDataAccess
{
    protected MyContext (string connectionString)
        : base(connectionString, CreateModel())
    {
    }

    private static DbCompiledModel CreateModel()
    {
        var dbModelBuilder = new DbModelBuilder(DbModelBuilderVersion.Latest);
        dbModelBuilder.Entity<Warehouse>();
        var dbModel = dbModelBuilder.Build(new DbProviderInfo("System.Data.SqlClient", "2008"));

        var edmType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String);
        var payload =
            new EdmFunctionPayload
            {
                Schema = "dbo",
                ParameterTypeSemantics = ParameterTypeSemantics.AllowImplicitConversion,
                IsComposable = true,
                IsNiladic = false,
                IsBuiltIn = false,
                IsAggregate = false,
                IsFromProviderManifest = true,
                StoreFunctionName = "TestEntity",
                ReturnParameters =
                    new[]
                    {
                        FunctionParameter.Create("ReturnType", edmType, ParameterMode.ReturnValue)
                    }
            };

        var function = EdmFunction.Create("TestEntity", "CodeFirst", DataSpace.CSpace, payload, null);
        dbModel.DatabaseMapping.Model.AddItem(function);
        var compiledModel = dbModel.Compile();       // Error happens here
        return compiledModel;
    }
}

But have exception:

One or more validation errors were detected during model generation:

Edm.String: : The namespace 'String' is a system namespace and cannot be used by other schemas. Choose another namespace name.

Problem is in “edmType” variable. I cannot create correctly ReturnType for function. Can anybody suggest how I can add function into model? Interface of adding function is exposed, so it should be able to do, but there is no information in web for this situation. Probably, somebody knows when Entity Framework team is going to implement attribute mapping for functions like Line To Sql does.

EF version: 6.0.0-beta1-20521

Thanks!

---

Yes, this works for me. But for scalar functions only. I, also, need map function, which returns IQueryable:

 IQueryable<T> MyFunction()

Where T is EntityType or RowType or any Type. I cannot do this at all (EF version is 6.0.2-21211). I think this should work in this way:

private static void RegisterEdmFunctions(DbModel model)
{
    var storeModel = model.GetStoreModel();
    var functionReturnValueType = storeModel.EntityTypes.Single(arg => arg.Name == "MyEntity").GetCollectionType();
    var payload =
        new EdmFunctionPayload
        {
            IsComposable = true,
            Schema = "dbo",
            StoreFunctionName = "MyFunctionName",
            ReturnParameters =
                new[]
                { 
                    FunctionParameter.Create("ReturnValue", functionReturnValueType, ParameterMode.ReturnValue)
                },
            Parameters =
                new[]
                {
                    FunctionParameter.Create("MyFunctionInputParameter", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32), ParameterMode.In)
                }
        };
    storeModel.AddItem(EdmFunction.Create(
        payload.StoreFunctionName,
        "MyFunctionsNamespace",
        DataSpace.SSpace,
        payload,
        payload.Parameters.Select(arg => MetadataProperty.Create(arg.Name, arg.TypeUsage, null)).ToArray()));
}

But still no luck:

  model.Compile();  // ERROR 

Is it possible or not? Probably steps are not right? Probably support will be added at EF 6.1. Any information will be very useful.

Thanks!

Answer 1

Haven't tried this yet, but Entity Framework 6.1 includes public mapping API. Moozzyk has implemented Store Functions for EntityFramework CodeFirst using this new functionality.

Here's what the code looks like:

public class MyContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(new FunctionsConvention<MyContext>("dbo"));
    }

    [DbFunction("MyContext", "CustomersByZipCode")]
    public IQueryable<Customer> CustomersByZipCode(string zipCode)
    {
        var zipCodeParameter = zipCode != null ?
            new ObjectParameter("ZipCode", zipCode) :
            new ObjectParameter("ZipCode", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext
            .CreateQuery<Customer>(
                string.Format("[{0}].{1}", GetType().Name, 
                    "[CustomersByZipCode](@ZipCode)"), zipCodeParameter);
    }

    public ObjectResult<Customer> GetCustomersByName(string name)
    {
        var nameParameter = name != null ?
            new ObjectParameter("Name", name) :
            new ObjectParameter("Name", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.
            ExecuteFunction("GetCustomersByName", nameParameter);
    }
}

Answer 2

Here are all the steps needed [Tested] :

Install-Package EntityFramework.CodeFirstStoreFunctions

Declare a class for output result:

public class MyCustomObject
{
   [Key]
   public int Id { get; set; }
   public int Rank { get; set; }
}

Create a method in your DbContext class

[DbFunction("MyContextType", "SearchSomething")]
public virtual IQueryable<MyCustomObject> SearchSomething(string keywords)
{
   var keywordsParam = new ObjectParameter("keywords", typeof(string)) 
                           { 
                              Value = keywords 
                            };
    return (this as IObjectContextAdapter).ObjectContext
    .CreateQuery<MyCustomObject>(
     "MyContextType.SearchSomething(@keywords)", keywordsParam);
}

Add

public DbSet<MyCustomObject> SearchResults { get; set; }

to your DbContext class

Add in the overriden OnModelCreating method:

modelBuilder.Conventions
.Add(new CodeFirstStoreFunctions.FunctionsConvention<MyContextType>("dbo"));

And now you can call/join with a table values function like this:

CREATE FUNCTION SearchSomething
(   
    @keywords nvarchar(4000)
)
RETURNS TABLE 
AS
RETURN 
(SELECT KEY_TBL.RANK AS Rank, Id
FROM MyTable 
LEFT JOIN freetexttable(MyTable , ([MyColumn1],[MyColumn2]), @keywords) AS KEY_TBL      
ON MyTable.Id = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 0   
)
GO

Answer 3

now Entity Framework is not beta, so maybe you solved your problem, but this one solved my problem How to use scalar-valued function with linq to entity?

Answer 4

You can get the Store type from the primitive type with a helper method:

    public static EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
    {
        return model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(
            PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType;
    }

In your example, you'd have to change the return parameter's type:

var edmType = GetStorePrimitiveType(model, PrimitiveTypeKind.String);

I found the help I needed here: http://entityframework.codeplex.com/discussions/466706