LINQ Entity Data Reader

原文：http://archive.msdn.microsoft.com/LinqEntityDataReader

ts a really good class to help speed up db inserts when using entity framework or object collections.

Resource Page Description
An IDataReader implementation that reads a collecion of entities to enable using SqlbulkCopy with LINQ. 


This simple class wraps a collection of CLR objects (IEnumerable<T>) and exposes the collection as an IDataReader. This is usefull for using SqlBulkCopy with collections of entities, especially entities generated by LINQ to SQL and Entity Framework.

For instance, here's a snippet of code that creates a collection of entity objects and uses SqlBulkCopy to load them into a SQL Server table.

Notice the AsDataReader extension method that transforms the entity collection into an IDataReader.

    static int SendOrders(int totalToSend)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        con.Open();
        using (SqlTransaction tran = con.BeginTransaction())
        {
          var newOrders =
                  from i in Enumerable.Range(0, totalToSend)
                  select new Order
                  {
                    customer_name = "Customer " + i % 100,
                    quantity = i % 9,
                    order_id = i,
                    order_entry_date = DateTime.Now
                  };
 
          SqlBulkCopy bc = new SqlBulkCopy(con,
            SqlBulkCopyOptions.CheckConstraints |
            SqlBulkCopyOptions.FireTriggers |
            SqlBulkCopyOptions.KeepNulls, tran);
 
          bc.BatchSize = 1000;
          bc.DestinationTableName = "order_queue";
          bc.WriteToServer(newOrders.AsDataReader()); 
 
          tran.Commit();
        }
        con.Close();
 
      }
 
      return totalToSend;
 
    }
 

Also can be used to load a DataTable from an IEnumerable<T>, like this

    static void Main(string[] args)
    {
      using (var db = new testDataContext())
      {
        var q = from o in db.sales_facts
                select o;
 
        DataTable t = q.ToDataTable();
        
 
        t.WriteXml(Console.Out);
      }
      Console.ReadKey();
    }
  }

The EntityDataReader wraps a collection of CLR objects in a DbDataReader. Only "scalar" properties are projected, with the exception that Entity Framework EntityObjects that have references to other EntityObjects will have key values for the related entity projected. This is useful for doing high-speed data loads with SqlBulkCopy, and copying collections of entities ot a DataTable for use with SQL Server Table-Valued parameters, or for interop with older ADO.NET applciations.

For explicit control over the fields projected by the DataReader, just wrap your collection of entities in a anonymous type projection before wrapping it in an EntityDataReader.

For explicit mapping, instead of 

      IEnumerable<Order> orders;
      ...
      IDataReader dr = orders.AsDataReader();
      
 

do

      IEnumerable<Order> orders;
      ...
      var q = from o in orders
              select new 
              {
                 ID=o.ID,
                 ShipDate=o.ShipDate,
                 ProductName=o.Product.Name,
                 ...
              }
      IDataReader dr = q.AsDataReader();
 

The EntityDataReader now uses dynamic methods to access the properties on your collection of objects, providing dramatically improved performance over using Reflection for property accessors. Common CLR scalar types and nullable value types are all enabled for dynamic method access. 

Here's a simple example of using the DataReader to pass a collection to a SQL Server Table-Valued Parameter (TVP). This just passes a list of integers, but you could pass a full collection of entities the same way.

        using (SqlConnection con = new SqlConnection("Data Source=(local);Database=AdventureWorks;Integrated Security=true"))
        {
          con.Open();
 
          SqlCommand cmd = new SqlCommand(
              @"select max(LineTotal) 
              from Sales.SalesOrderDetail 
              where SalesOrderId in (select Value from @ids)", con);
          SqlParameter pIds = cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.Structured));
 
          //created with this DDL: CREATE TYPE Int_TableType AS TABLE(Value int NOT NULL)
          pIds.TypeName = "Int_TableType";
 
          //create a list of ID's
          var ids = Enumerable.Range(43659, 1000);
          pIds.Value = ids.AsDataReader();
 
          object val = cmd.ExecuteScalar();
 
          Console.WriteLine(val);
        }

Here's another TVP sample, this time using a multi-column TVP to pass a collection of entities to SQL 2008 and MERGE them into a table.

      using (var db = new AdventureWorksContextDataContext())
      using (var con = db.Connection)
      {
        //db.ObjectTrackingEnabled = false;
        var lo = new System.Data.Linq.DataLoadOptions();
        lo.LoadWith<SalesOrderHeader>(o => o.SalesOrderDetail );
        db.LoadOptions = lo;
        db.Connection.Open();
 
        var customerId = (from o in db.SalesOrderHeaders
                          where o.SalesOrderDetails.Count() > 2
                          select o).Take(1).First().CustomerID;
              
        
        var q = from o in db.SalesOrderHeaders
                where o.CustomerID == customerId
                select o;
 
        var orders = q.ToList();
 
        foreach (var o in orders)
        {
          foreach (var od in o.SalesOrderDetails)
          {
            od.UnitPrice = od.UnitPrice * 1.04M;
          }
        }
 
        string sql = @"
declare @output Sales_SalesOrderDetail_type
 
MERGE Sales.SalesOrderDetail AS target
USING 
( 
  SELECT
    SalesOrderID,
    SalesOrderDetailID,
    UnitPrice
  FROM @OrderDetails 
) AS source (SalesOrderID, SalesOrderDetailID, UnitPrice)
ON 
(
      target.SalesOrderID = source.SalesOrderID
  and target.SalesOrderDetailID = source.SalesOrderDetailID
)
WHEN MATCHED 
    THEN UPDATE SET target.UnitPrice = source.UnitPrice, 
                    target.ModifiedDate = GETDATE()
OUTPUT inserted.*
into @output;
 
select * from @output;
";
 
        var cmd = new SqlCommand(sql, (SqlConnection)db.Connection);
        var pOrderDetails = cmd.Parameters.Add(new SqlParameter("@OrderDetails", SqlDbType.Structured));
        pOrderDetails.TypeName = "Sales_SalesOrderDetail_type";
        
        /*
        CREATE TYPE Sales_SalesOrderDetail_type as TABLE
        (
	        [SalesOrderID] [int] NOT NULL,
	        [SalesOrderDetailID] [int]  NOT NULL,
	        [CarrierTrackingNumber] [nvarchar](25) NULL,
	        [OrderQty] [smallint] NOT NULL,
	        [ProductID] [int] NOT NULL,
	        [SpecialOfferID] [int] NOT NULL,
	        [UnitPrice] [money] NOT NULL,
	        [UnitPriceDiscount] [money] NOT NULL,
	        [LineTotal]  MONEY,
	        [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	        [ModifiedDate] [datetime] NOT NULL
        ) 
       */
        //project the results into an Anonymous type matching the TVP declaration
        var dtq = from od in orders.SelectMany(o => o.SalesOrderDetails).Take(1000)
                  select new 
                  {
                    SalesOrderID = od.SalesOrderID,
                    SalesOrderDetailId = od.SalesOrderDetailID,
                    CarrierTrackingNumber = od.CarrierTrackingNumber,
                    OrderQty = od.OrderQty,
                    ProductID = od.ProductID,
                    SpecialOfferID = od.SpecialOfferID,
                    UnitPrice = od.UnitPrice,
                    UnitPriceDiscount = od.UnitPriceDiscount,
                    LineTotal = od.LineTotal,
                    rowguid = od.rowguid,
                    ModifiedDate = od.ModifiedDate
                  };
 
        var i = 0;
 
        var f = new { a = i++, b = i++, c = i++ };
 
        //wrap the collection in a DataReader for sending to the server
        pOrderDetails.Value = dtq.AsDataReader();
 
        IList<L2S.SalesOrderDetail> results;
        using (var dr = cmd.ExecuteReader())
        {
          results = db.Translate<L2S.SalesOrderDetail>(dr).ToList();
        }
 
        
        foreach (var r in results)
        {
          Console.WriteLine("{0} {1}",r.SalesOrderDetailID,r.UnitPrice);
 
        }
 
        if (dtq.Count() != results.Count())
        {
          throw new InvalidOperationException("Wrong number of rows affected by MERGE");
        }
      }