EF 4.X Code First 如何调用存储过程

类似的技术也是从网上学习的，以前也看到过更详细的技术文章，今日见有人发问，自己再杜撰一份，以充实下自己的技术博客。

这是代码片断，实现 Code First 调用存储过程，期待这个功能在将来新版本中有简化的方式。

第一步，有这样一个存储过程:

ALTER  PROCEDURE [dbo].[sp_get_goods]
@all char(1),
@productID int
AS
select goods_id,gs_name,gs_id,goods_no,name,spec,gwt,nwt,voldesc,qtyunit ,price
--into t_product_temp

from t_goods g

 

第二步，定义好Class和Map，我通常用EF Power Tool生成

Product.CS

using System;
using System.Collections.Generic;
using System.Data.Services.Common;

namespace ERP.Domain
{
   [DataServiceKey("ProductID")]
    public class Product
    {
       public int ProductID { get; set; }

       public string ProductNo { get; set; } 

        public string ProductName { get; set; } 

        public string ProductSpec { get; set; } 

        public decimal? GrossWeight { get; set; } 

        public decimal? NetWeight { get; set; } 

        public string VolumeDescribe { get; set; } 

        public string QuantityUnit { get; set; } 

        public decimal? Price  { get; set; } 

        public string Currency { get; set; } 

        public string CategoryName { get; set; } 

        public int? CategoryID { get; set; } 

    }
}

ProductMap.CS

using System;
using System.Data.Entity.ModelConfiguration;
using System.Data.Common;
using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using ERP.Domain;

namespace ERP.Domain.Mapping
{
    public class ProductMap : EntityTypeConfiguration<Product>
    {
        public ProductMap()
        {
            this.ToTable("t_product_temp");
            this.HasKey(t => t.ProductID);
            this.Property(t => t.ProductID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            this.Property(t => t.ProductID).HasColumnName("goods_id");
            this.Property(t => t.ProductNo).HasColumnName("goods_no");
            this.Property(t => t.ProductName).HasColumnName("name");
            this.Property(t => t.ProductSpec).HasColumnName("spec");
            this.Property(t => t.GrossWeight).HasColumnName("gwt");
            this.Property(t => t.NetWeight).HasColumnName("nwt");
            this.Property(t => t.VolumeDescribe).HasColumnName("voldesc");
            this.Property(t => t.QuantityUnit).HasColumnName("qtyunit");
            this.Property(t => t.Price).HasColumnName("price");
            this.Property(t => t.Currency).HasColumnName("currency_no");
            this.Property(t => t.CategoryName).HasColumnName("gs_name");
            this.Property(t => t.CategoryID).HasColumnName("gs_id");            
        }
    }
}

第三步：想办法执行context.Database.SqlQuery(mapCacheItem.MapType, sql, parameters)调用存储过程返回数据

SqlQueryService.CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Reflection.Emit;
using System.Reflection;
using System.Collections;
using System.Data.Entity.ModelConfiguration;

namespace COM.Data
{
    public class SqlQueryService
    {
        private DbContext context;
        public SqlQueryService(DbContext context)
        {
            this.context = context;
        }

        class MapCacheItem
        {
            //public EntityTypeConfiguration<T> ConfigurationMap{get;set;}
            public Type MapType {get;set;}
            public Dictionary<string,string> PropertyNameMap{get;set;}
        }

        static private Dictionary<string,MapCacheItem> MapCache = new Dictionary<string,MapCacheItem>();

        private MapCacheItem CreateMapCacheItem<T>(string typeName,EntityTypeConfiguration<T> map) where T : class,new()
        {
            dynamic mapDynamic = new AccessPrivateWrapper(map);
            dynamic m = new AccessPrivateWrapper(mapDynamic.Configuration);
            dynamic configList = m.PrimitivePropertyConfigurations;
            Dictionary<string, string> entityMap = new Dictionary<string, string>();
            Dictionary<string, Type> dynamicTypePropList = new Dictionary<string, Type>();
            foreach (var c in configList)
            {
                //System.Data.Entity.ModelConfiguration.Configuration.Properties.Primitive.PrimitivePropertyConfiguration
                //System.Data.Entity.ModelConfiguration.Configuration.PrimitivePropertyConfiguration p;

                dynamic config = new AccessPrivateWrapper(c);
                Type type = typeof(int);
                string pName = string.Empty;
                foreach (var configItem in config.Key)
                {
                    PropertyInfo p = configItem as PropertyInfo;
                    type = configItem.PropertyType;
                    pName = p.Name;
                }
                dynamic value = new AccessPrivateWrapper(config.Value);
                var item = new { PropertyName = pName, ColumnName = value.ColumnName };
                //var type = typeof(T).GetProperty(item.PropertyName).DeclaringType;
                dynamicTypePropList.Add(item.ColumnName, type);
                entityMap.Add(item.ColumnName, item.PropertyName);
            }
            Type resultType = CreateDynamicType.Instance.CreateTempType(typeName, dynamicTypePropList);
            return new MapCacheItem() { MapType = resultType, PropertyNameMap = entityMap };
        }

        #region DynamicExecuteSql
        public IEnumerable<T> DynamicExecuteSql<T>(EntityTypeConfiguration<T> map, string sql, params object[] parameters) where T : class,new()
        {
            string mapTypeName = map.GetType().FullName;
            if (!MapCache.ContainsKey(mapTypeName))
                MapCache.Add(mapTypeName, this.CreateMapCacheItem(mapTypeName,map));
            MapCacheItem mapCacheItem = MapCache[mapTypeName];
            var queryList = context.Database.SqlQuery(mapCacheItem.MapType, sql, parameters);
            foreach (var item in queryList)
            {
                var entity = new T();
                EntityReflectService.Instance.CopyEntityProperty(item, entity, mapCacheItem.PropertyNameMap);
                yield return entity;
            }
        }

 
        #endregion 
        
    }



        
}

第四步：这里的mapCacheItem.MapType是通过TypeBuilder依据ProductMap运行期创建的Type：

CreateDynamicType.CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;

namespace COM.Data
{
    public class CreateDynamicType
    {
        private static CreateDynamicType instance;
        public static CreateDynamicType Instance
        {
            get
            {
                return instance ?? (instance = new CreateDynamicType());
            }
        }

        private Dictionary<string, Type> cacheList = new Dictionary<string,Type>();
        public Type CreateTempType(string typeName, Dictionary<string, Type> propertyList)
        {
            if (!cacheList.Keys.Contains(typeName))
            {
                var builder = this.CreateTypeBuilder("DynamicAssembly", "DynamicModule", "DynamicType");
                foreach (var item in propertyList)
                {
                    this.CreateAutoImplementedProperty(builder, item.Key, item.Value);
                }

                cacheList.Add(typeName, builder.CreateType());
            }
            return cacheList[typeName];
        }


        private TypeBuilder CreateTypeBuilder(string assemblyName, string moduleName, string typeName)
        {
            TypeBuilder typeBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(new AssemblyName(assemblyName), AssemblyBuilderAccess.Run)
                                                            .DefineDynamicModule(moduleName)
                                                            .DefineType(typeName, TypeAttributes.Public);
            typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
            return typeBuilder;
        }

        private void CreateAutoImplementedProperty(TypeBuilder builder, string propertyName, Type propertyType)
        {
            const string PrivateFieldPrefix = "m_";
            const string GetterPrefix = "get_";
            const string SetterPrefix = "set_";

            // Generate the field.
            FieldBuilder fieldBuilder = builder.DefineField(string.Concat(PrivateFieldPrefix, propertyName),propertyType, FieldAttributes.Private);

            // Generate the property
            PropertyBuilder propertyBuilder = builder.DefineProperty( propertyName, PropertyAttributes.HasDefault, propertyType, null);

            // Property getter and setter attributes.
            MethodAttributes propertyMethodAttributes =MethodAttributes.Public | MethodAttributes.SpecialName |MethodAttributes.HideBySig;

            // Define the getter method.
            MethodBuilder getterMethod = builder.DefineMethod(string.Concat(GetterPrefix, propertyName), propertyMethodAttributes, propertyType, Type.EmptyTypes);

            // Emit the IL code.
            // ldarg.0
            // ldfld,_field
            // ret
            ILGenerator getterILCode = getterMethod.GetILGenerator();
            getterILCode.Emit(OpCodes.Ldarg_0);
            getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
            getterILCode.Emit(OpCodes.Ret);

            // Define the setter method.
            MethodBuilder setterMethod = builder.DefineMethod(string.Concat(SetterPrefix, propertyName),propertyMethodAttributes, null, new Type[] { propertyType });

            // Emit the IL code.
            // ldarg.0
            // ldarg.1
            // stfld,_field
            // ret
            ILGenerator setterILCode = setterMethod.GetILGenerator();
            setterILCode.Emit(OpCodes.Ldarg_0);
            setterILCode.Emit(OpCodes.Ldarg_1);
            setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
            setterILCode.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getterMethod);
            propertyBuilder.SetSetMethod(setterMethod);
        }
    }
}

第五步：从ProductMap的映射关系，可以构造出适合存储过程返回数据的类型，我还要借助AccessPrivateWrapper类访问ProductMap:

AccessPrivateWrapper.CS

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Reflection.Emit;
using System.Dynamic;

namespace COM.Data
{
public class AccessPrivateWrapper : DynamicObject
    {

        /// <summary>
        /// The object we are going to wrap
        /// </summary>
        object _wrapped;

        /// <summary>
        /// Specify the flags for accessing members
        /// </summary>
        static BindingFlags flags = BindingFlags.NonPublic | BindingFlags.Instance| BindingFlags.Static | BindingFlags.Public;

        /// <summary>
        /// Create a simple private wrapper
        /// </summary>
        public AccessPrivateWrapper(object o)
        {
            _wrapped = o;
        }

        /// <summary>
        /// Create an instance via the constructor matching the args 
        /// </summary>
        public static dynamic FromType(Assembly asm, string type, params object[] args)
        {

            var allt = asm.GetTypes();
            var t = allt.First(item => item.Name == type);


            var types = from a in args
                        select a.GetType();

            //Gets the constructor matching the specified set of args
            var ctor = t.GetConstructor(flags, null, types.ToArray(), null);

            if (ctor != null)
            {
                var instance = ctor.Invoke(args);
                return new AccessPrivateWrapper(instance);
            }

            return null;
        }

        /// <summary>
        /// Try invoking a method
        /// </summary>
        public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
        {
            var types = from a in args
                        select a.GetType();

            var method = _wrapped.GetType().GetMethod(binder.Name, flags, null, types.ToArray(), null);

            if (method == null)
                return base.TryInvokeMember(binder, args, out result);
            else
            {
                result = method.Invoke(_wrapped, args);
                return true;
            }
        }

        /// <summary>
        /// Tries to get a property or field with the given name
        /// </summary>
        public override bool TryGetMember(System.Dynamic.GetMemberBinder binder, out object result)
        {
            //Try getting a property of that name
            var prop = _wrapped.GetType().GetProperty(binder.Name, flags);

            if (prop == null)
            {
                //Try getting a field of that name
                var fld = _wrapped.GetType().GetField(binder.Name, flags);
                if (fld != null)
                {
                    result = fld.GetValue(_wrapped);
                    return true;
                }
                else
                    return base.TryGetMember(binder, out result);
            }
            else
            {
                result = prop.GetValue(_wrapped, null);
                return true;
            }
        }

        /// <summary>
        /// Tries to set a property or field with the given name
        /// </summary>
        public override bool TrySetMember(SetMemberBinder binder, object value)
        {
            var prop = _wrapped.GetType().GetProperty(binder.Name, flags);
            if (prop == null)
            {
                var fld = _wrapped.GetType().GetField(binder.Name, flags);
                if (fld != null)
                {
                    fld.SetValue(_wrapped, value);
                    return true;
                }
                else
                    return base.TrySetMember(binder, value);
            }
            else
            {
                prop.SetValue(_wrapped, value, null);
                return true;
            }
        }




    }
}

第六步：生成ProductList时，通过反射赋值，自己搞了个属性复制的功能类：

EntityReflectService.CS

    public class EntityReflectService
    {
        static private EntityReflectService instance;
        static public EntityReflectService Instance
        {
            get
            {
                return instance ?? (instance = new EntityReflectService());
            }
        }

        /// <summary>
        /// 复制属性,如果原始对象与目标对象的属性名一致,则复制
        /// </summary>
        /// <param name="from">原始对象</param>
        /// <param name="to">目标对象</param>
        public void CopyEntityProperty(object from, object to)
        {
            PropertyInfo[] propertyInfoFrom = this.GetTypePropertyInfo(from.GetType());
            PropertyInfo[] propertyInfoTo = this.GetTypePropertyInfo(to.GetType());
            var dictFrom = propertyInfoFrom.ToDictionary(p => p.Name, p => p);
            foreach (var property in propertyInfoTo)
            {
                if (property.PropertyType.IsSealed && property.PropertyType.IsSerializable && dictFrom.ContainsKey(property.Name))
                {
                    Object propertyValue = dictFrom[property.Name].GetValue(from, null);
                    if (property.GetSetMethod() != null)
                        property.SetValue(to, propertyValue, null);
                }
            }
        }

        private Dictionary<Type, PropertyInfo[]> propertyInfoCache = new Dictionary<Type, PropertyInfo[]>();
        private PropertyInfo[] GetTypePropertyInfo(Type type)
        {
            if (!propertyInfoCache.ContainsKey(type))
                propertyInfoCache.Add(type, type.GetProperties());
            return propertyInfoCache[type];
        }

        /// <summary>
        /// 从对象to复制所有属性到对象from,属性名可能不同
        /// </summary>
        /// <param name="from">原始对象</param>
        /// <param name="to">目标对象</param>
        /// <param name="propertyCopy">列名到属性名的映射表</param>
        public void CopyEntityProperty(object from, object to, Dictionary<string, string> propertyCopy)
        {
            PropertyInfo[] propertyInfoTo = this.GetTypePropertyInfo(to.GetType());
            PropertyInfo[] propertyInfoFrom = this.GetTypePropertyInfo(from.GetType());
            var dictFrom = propertyInfoFrom.ToDictionary(p => p.Name, p => p);
            var dictTo = propertyInfoTo.ToDictionary(p => p.Name, p => p);
            foreach (var copy in propertyCopy)
            {
                Object propertyValue = dictFrom[copy.Key].GetValue(from, null);
                var property = dictTo[copy.Value];
                if (property.GetSetMethod() != null)
                    property.SetValue(to, propertyValue, null);
            }
        }
 }
}

第七步：绕了一大圈，终于可以测试调用了，期待更轻量的调用存储过程的方式，性能还有待提升

 [Test]
        public IEnumerable<Product> GetProductListBySP(bool isAll,int productID)
        {
            var s = new SqlQueryService(this.CurrentDataSource);
            var p = isAll ? "Y" : "N";
            var result = s.DynamicExecuteSql(new ERP.Domain.Mapping.ProductMap(), string.Format("exec sp_get_goods '{0}',{1}", p,productID));
            return result;
        }