Entity Framework 6 Code First function mapping

Question

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

I want to use Code First. I don’t want to use Database First *.edmx file for other reasons.
I use attribute mapping [Table], [Column] and this works fine
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!

You have a DB and want to access via Code first? No need for model then. See http://msdn.microsoft.com/en-us/data/jj200620 — phil soady, Nov 17 '13 at 13:16

score 16 · Answer 1 · edited Aug 21 '18 at 13:08

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);
    }
}

Can I return a different type than a table type with this? IE: I want to return ObjectResult ? — Ezra Bailey, Mar 22 '15 at 13:11
@SarahBourt I have no idea. You should either ask a question on SO properly or ask the author of the library directly. — Athari, Mar 22 '15 at 14:22
Good idea! I found the solution, and posted it as a question/answer here: http://stackoverflow.com/questions/29198416/using-ef6-store-functions-for-entityframework-codefirst-can-i-return-a-custom-t Hope this helps someone else — Ezra Bailey, Mar 22 '15 at 18:51

score 4 · Answer 2 · answered Nov 17 '16 at 19:35

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

score 3 · Answer 3 · answered Nov 22 '13 at 22:22

3

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

answered Nov 22 '13 at 22:22

drew

306
2
11

How to map composable function which result is queryable of any type? – Alexey Dec 25 '13 at 14:47
I'm not sure what you're asking. I used the above code to map a scalar DB function to a C# method for comparing a concurrency field (byte[]) and use that method to compose queries. Maybe my original answer will help: http://stackoverflow.com/a/20225824/2808810 – drew Jan 22 '14 at 14:56

score 1 · Answer 4 · edited May 23 '17 at 12:09

1

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?

edited May 23 '17 at 12:09

Community

1
1

answered Feb 18 '14 at 11:49

DanielV

2,076
2
40
61

The question is about using function with code-first approach, you've linked to a solution with .edmx file, which is not. – Athari Feb 18 '14 at 13:29
the thing is that there is no way to use a scalar function with entity framework using code-first approach, i was trying to do so, and finally had to use what is suggested in the link – DanielV Feb 19 '14 at 15:57
I just tried the tool for entity framework 6 and allowed me to use code-first approach – DanielV Mar 05 '14 at 16:35

Entity Framework 6 Code First function mapping

4 Answers4

Linked