C# Entity Framework 6 Code First 函数映射

Entity Framework 6 Code First function mapping

提问by Alexey

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

我想将 Entity Framework 6 集成到我们的系统中，但有问题。

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.

1. 我想使用代码优先。由于其他原因，我不想使用 Database First *.edmx 文件。
2. 我使用属性映射 [Table], [Column] 这很好用
3. 数据库有许多用户定义的函数，我需要在 Linq To Entities 查询中使用它们。

Problem is:

问题是：

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

我无法通过 [Table]、[Column] 等属性映射函数。只有 1 个属性可用 [DbFunction]，需要 *.edmx 文件。

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.

我可以在 *.edmx 文件中进行函数映射，但这意味着我不能对实体使用属性映射：[表]、[列]。*.edmx 或属性中的映射必须完整。

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

我尝试通过以下代码创建 DbModel 并添加函数：

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.

问题出在“edmType”变量中。我无法为函数正确创建 ReturnType。有人可以建议我如何将功能添加到模型中吗？添加函数的接口是暴露的，应该可以，但是web中没有关于这种情况的信息。可能有人知道 Entity Framework 团队何时会为 Line To Sql 之类的函数实现属性映射。

EF version: 6.0.0-beta1-20521

EF 版本：6.0.0-beta1-20521

Thanks!

谢谢！

---

---

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

是的，这对我有用。但仅适用于标量函数。我还需要 map 函数，它返回 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:

其中 T 是 EntityType 或 RowType 或任何类型。我根本不能这样做（EF 版本是 6.0.2-21211）。我认为这应该以这种方式工作：

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.

有可能吗？大概步骤不对吧？可能会在 EF 6.1 中添加支持。任何信息都会非常有用。

Thanks!

谢谢！

回答by drew

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

您可以使用辅助方法从原始类型获取 Store 类型：

    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

我在这里找到了我需要的帮助：http: //entityframework.codeplex.com/discussions/466706

回答by DanielV

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?

现在实体框架不是测试版，所以也许你解决了你的问题，但这个解决了我的问题如何在 linq to entity 中使用标量值函数？

回答by Athari

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

尚未尝试过，但Entity Framework 6.1包含public mapping API。Moozzyk 已使用此新功能为 EntityFramework CodeFirst实现了存储功能。

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

回答by Nina

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

在 DbContext 类中创建一个方法

[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

到您的 DbContext 类

Add in the overriden OnModelCreatingmethod:

添加覆盖OnModelCreating方法：

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