LINQ Entity Data Reader

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. Int16, Int32, Int64, DateTime, Decimal, String and GUID are all enabled for dynamic method access. Other types still use reflection.

Here's a simple example of using the DataReader to pass a collection to a SQL Server Table-Valued Parameter. 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);
        }

David
dbrowne@microsoft.com