Entity Framework:存储过程 in entity client and EF Extension(二)

    接着早晨的写下去，为什么entityClientCommand和ExtensionCommand返回的结果的entity state不一样，原因是这行代码：var result = cmd.Materialize<OrderDetails>().Bind(context);的Bind()方法,在EF Extension 中的Bind()可以将entity加入到state manager.

  EF Extension中有的确许多令人兴奋的地方，为了解决最初的话题解决sp 返回Multiple result set的问题，我对NorthwindEF模型中的Order和OrderDtails实体进行扩展：

   首先添加对EF Extension .dll的引用，添加命名空间using Microsoft.Data.Extensions;由于EDM.Designer.cs是一个partial类，对一个分部(partial)类进行扩展是件容易的事，

namespace NorthWind
{
    public partial class NorthwindEFEntities : ObjectContext
    {  }
}

  只需要在上面的类中添加扩展方法即可,下面是实现Entity Set<T>,Materializer<T>的代码，

 1 EntitySet#region EntitySet<T>
 2        private EntitySet<Orders> orderSet;
 3        /**//// <summary>
 4        ///  Gets  entry point for the Orders entity set.
 5        /// </summary>
 6        public EntitySet<Orders> OrderSet
 7        {
 8            get
 9            {
                if (null == this.orderSet)
                {
                    orderSet = new EntitySet<Orders>(this, "Orders");
                }
                return this.orderSet;
            }
        }
        private EntitySet<OrderDetails> orderDetailSet;
        /**//// <summary>
        /// Gets  entry point for the OrderDetails entity set.
        /// </summary>
        public EntitySet<OrderDetails> OrderDetailSet
        {
            get
            {
                if (null == this.orderDetailSet)
                {
                    orderDetailSet = new EntitySet<OrderDetails>(this, "OrderDetails");

                }
                return this.orderDetailSet;
            }
        }
        #endregion

        Materializer#region Materializer<T>
        // Materializer performing column renames.
        private static readonly Materializer<Orders> s_orderMaterializer = new Materializer<Orders>(r =>
            new Orders
            {
                OrderID = r.Field<int>("OrderID"),
                EmployeeID = r.Field<int>("EmployeeID"),
                OrderDate = r.Field<DateTime>("OrderDate"),
                RequiredDate = r.Field<DateTime>("RequiredDate"),
                ShippedDate = r.Field<DateTime>("ShippedDate"),
                Freight = r.Field<decimal>("Freight"),
                ShipName = r.Field<string>("ShipName"),
                ShipAddress = r.Field<string>("ShipAddress"),
                ShipCity = r.Field<string>("ShipCity"),
                ShipRegion = r.Field<string>("ShipRegion"),

            });

        // Materializer performing column renames.
        private static readonly Materializer<OrderDetails> s_orderDetailMaterializer = new Materializer<OrderDetails>(r =>
            new OrderDetails
            {
                OrderID = r.Field<int>("OrderID"),
                Quantity = r.Field<short>("Quantity"),
                UnitPrice = r.Field<decimal>("UnitPrice"),
                Discount = r.Field<float>("Discount"),
                ProductID = r.Field<int>("ProductID")
            });

        private static readonly Materializer<KeyValuePair<Orders, OrderDetails>> s_OrderAndOrderDetailsMaterializer = new Materializer<KeyValuePair<Orders, OrderDetails>>(r =>
          new KeyValuePair<Orders, OrderDetails>(
           new Orders
          {
              OrderID = r.Field<int>("OrderID"),
              OrderDate = r.Field<DateTime>("OrderDate"),
              ShippedDate = r.Field<DateTime>("ShippedDate"),
              ShipName = r.Field<string>("ShipName")
          },
               new OrderDetails
          {
              OrderID = r.Field<int>("OrderID"),
              Quantity = r.Field<short>("Quantity"),
              UnitPrice = r.Field<decimal>("UnitPrice"),
              Discount = r.Field<float>("Discount")
          }));

        #endregion

  至于为什么重新实现Materializer，而不是像ExtensionCommand中那样直接调用，只要的原因是我想使用EntitySet<T>,另外重新实现Materializer可以对sp的返回结果进行处理。通过 Materializer<KeyValuePair<Orders, OrderDetails>>将Mutilple result set中的内容分割为相对应的entity，这里顺便说一下我定义的存储过程getOrderAndOrderDetails是这样的：

select a.OrderID,a.OrderDate,a.ShippedDate,a.ShipName,b.UnitPrice,b.Quantity,b.Discount
from Orders as a left join OrderDetails as b
on a.OrderID=b.OrderID
where a.OrderID=@OrderID

    下面就是对Order，OrderDetails的包装的3个方法：

 1   public Orders GetOrder(int OrderID)
 2        {
 3            DbCommand cmd = this.CreateStoreCommand("GetOrder", CommandType.StoredProcedure, new SqlParameter("@OrderID", OrderID));//specify DBCcommand
 4            Orders order = s_orderMaterializer.Materialize(cmd).SingleOrDefault();// return a most single match for this store proceduce
 5            return this.OrderSet.FindOrAttach(order);// //method made order be a tracked entity too
 6        }
 7        public OrderDetails GetOrderDetail(int OrderID)
 8        {
 9            DbCommand cmd = this.CreateStoreCommand("GetOrderDetails", CommandType.StoredProcedure, new SqlParameter("@OrderID", OrderID));
            OrderDetails orderDetails = s_orderDetailMaterializer.Materialize(cmd).FirstOrDefault<OrderDetails>();
            // return this.OrderDetailSet.FindOrAttach(details);
            return orderDetails;
        }
        /**//// <summary>
        ///return Multiple result sets
        /// </summary>
        /// <param name="pid">Product id</param>
        /// <returns>Product</returns>
        public Orders GetOrderAndOrderDetails(int OrderID)
        {
            DbCommand command = this.CreateStoreCommand("getOrderAndOrderDetails", CommandType.StoredProcedure, new SqlParameter("@OrderID", OrderID));
            //this store proceduce return Multiple result sets,contain Order and OrderDetails
            Orders order = null;
            var orderAndDetailSet = s_OrderAndOrderDetailsMaterializer.Materialize(command);
            foreach (var orderAndDetail in orderAndDetailSet)
            {
                order = this.OrderSet.FindOrAttach(orderAndDetail.Key);
                if (null != order)
                {
                    order.OrderDetails.Add(orderAndDetail.Value);
                    // order.OrderDetails = this.OrderDetailSet.FindOrAttach(orderAndDetail.Value);
                }

            }
            return order;
        }

   我用下面的代码来来测试上面的方法

 1Orders order = context.GetOrder(10248);//a single entity order
 2            Console.WriteLine("GetOrder:{0},OrderDetails Count:{2},Entity State:{1}", order.OrderID, order.OrderDetails.Count, order.EntityState);
 3            //writer.WriteLine(order);
 4            //DisplayResults<Orders>(order);
 5            OrderDetails orderDetails = context.GetOrderDetail(10248);//a silgle entity orderdetail
 6            //writer.WriteLine(orderDetails);
 7            Console.WriteLine("GetOrderDetail:{0},Entity State:{1}", orderDetails.OrderID, orderDetails.EntityState);
 8            Orders orderAndDetails = context.GetOrderAndOrderDetails(10248);//entity order with orderdetail
 9            Console.WriteLine("GetOrderAndOrderDetails:{0},OrderDetails Count:{1},Entity State:{2}", orderAndDetails.OrderID, orderAndDetails.OrderDetails.Count, orderAndDetails.EntityState);

运行的结果是：

 

 GetOrder()方法返回的order对象由于没有attach相应的OrderDetails对象,因此 order.OrderDetails.Count 为0,而GetOrderAndOrderDetails()就不同，我们将getOrderAndOrderDetails返回的Mutilple result set中的内容进行分割为Order,OrderDetails,并将OrderDetails 对象添加到Orders，这样的效果等同于

contex.Orders.Include("OrderDetails").Where(q=>q.OrderID=10248).FirstOrDefault();至于为什么GetOrderDetail返回的结果Entity State为Detach,那是因为 我将这行代码this.OrderDetailSet.FindOrAttach(details)注释了.

   EF Extension真的让我太高兴了，可以说让我感受到了sp在EF用应用不再那么局限性。可能后面我还会写一些关于还有一些关于通过设计器的映射sp的随笔.最后说一下还是说一下哪儿可以找到EF Extension.