/**
获取物料次类的跟类别
**/

IF  EXISTS (SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N‘[dbo].[FN_ROOTITEMTYPE]‘) AND type in (N‘FN‘, N‘IF‘, N‘TF‘, N‘FS‘, N‘FT‘))
DROP FUNCTION [dbo].[FN_ROOTITEMTYPE]
GO

CREATE FUNCTION [dbo].[FN_ROOTITEMTYPE](@CODE VARCHAR(10))
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @PARENTCODE varchar(10),@ROOTCODE VARCHAR(10)

    SELECT @PARENTCODE=PARENTID,@ROOTCODE=CODE FROM ITEMTYPE WHERE CODE=@CODE

    WHILE ISNULL(@PARENTCODE,‘‘)<>‘‘
    BEGIN
        SELECT @PARENTCODE=PARENTID,@ROOTCODE=CODE FROM ITEMTYPE WHERE CODE=@PARENTCODE
    END
    RETURN @ROOTCODE
END
GO

public class RegisterFunctionConvention : IStoreModelConvention<EdmModel>
{
    /// <summary>
    /// 将此约定应用于模型中的项
    /// </summary>
    ///<param name="item">概念模型或存储模型</param>
    ///<param name="DbModel">实体数据模型</param>
    public void Apply(EdmModel item, DbModel model)
    {
        // 创建传入参数
        var codeParameter = FunctionParameter.Create("CODE", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
        // 创建返回参数
        var returnValue = FunctionParameter.Create("result", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.ReturnValue);

        var function = CreateAndAddFunction(item, "FN_ROOTITEMTYPE", new[] { codeParameter }, new[] { returnValue });

    }
    /// <summary>
    /// 创建并添加函数
    /// </summary>
    /// <param name="item">概念模型或存储模型</param>
    /// <param name="name">函数名称</param>
    /// <param name="parameters">函数参数</param>
    /// <param name="returnValues">返回值</param>
    /// <param name="IsBuiltIn">是否是内置函数</param>
    /// <returns></returns>
    protected EdmFunction CreateAndAddFunction(EdmModel item, String name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues, bool IsBuiltIn = false)
    {
        var payload = new EdmFunctionPayload { StoreFunctionName = name, Parameters = parameters, ReturnParameters = returnValues, Schema = this.GetDefaultSchema(item), IsBuiltIn = IsBuiltIn };
        var function = EdmFunction.Create(name, this.GetDefaultNamespace(item), item.DataSpace, payload, null);

        item.AddItem(function);

        return (function);
    }

    /// <summary>
    ///  获取存储原始类型
    /// </summary>
    /// <param name="model">实体数据模型</param>
    /// <param name="typeKind">实体数据模型定义的基元类型</param>
    protected EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
    {
        return (model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType);
    }

    /// <summary>
    /// 获取默认包名称
    /// </summary>
    protected String GetDefaultNamespace(EdmModel layerModel)
    {
        return "CodeFirstDatabaseSchema";
    }

    /// <summary>
    /// 获取默认架构名称
    /// </summary>
    protected String GetDefaultSchema(EdmModel layerModel)
    {
        return (layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault());
    }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // 注册数据库中函数
    modelBuilder.Conventions.Add(new RegisterFunctionConvention());
}