How to use LINQ to SQL to batch queries and return Multiple Results without Stored Procedures, by Tony Wright

July 23, 2008

I must admit I’ve been a bit disappointed with LINQ to SQL. I went looking for the concept of LINQ batches, that is, the ability to execute more than one query in the same database request. I would have thought that this feature would be highly desirable. Instead of making an SQL request, waiting for all the results, making a second request, waiting for it’s results, then a third request, waiting for it’s results…well, you get my point.

If only you could make a single request and get all the results back in one hit. This is one of the benefits of sql server stored procedures. You can put a number of select statements in the stored procedure, call it from your client, and get all the results back via a DataReader or DataSet. With a DataReader, you get the first result back and then when you’ve finished reading all the rows of that result, you call the NextResult() method and it moves on to the next result and so on. With a DataSet, you can set up a DataAdapter, set it’s select statement to call a stored proc, and then call the Fill method to populate one-to-many DataTables within the DataSet. It’s very efficient, and can return the entire batch very quickly.

I often find implementations of the domain model, where someone has implemented a whole lot of objects in .net that hide the details of the underlying sql calls. Basically, as the developer traverses the object model, extra calls are created to the database. This causes all sorts of unnecessary traffic. In some instances, I’ve seen up to a hundred calls in a Page_Load!

 With web sites, the ultimate goal is to try to only have a maximum of a single request per action. So (caching aside) a Page_Load would only call the database once to retrieve everything it needed to populate the page. After the page is populated, you modify some data and then click an Update button, and that action should perform another single request to the database to update the data and get any further results needed to ensure the state of the page is correct. I often achieve pretty close to this, so all that extra traffic is unnecessary and unwanted.

With LINQ to SQL, you shouldn’t need to call stored procedures just to batch queries – especially for simple ones. I mean, doesn’t that defeat the purpose of getting people to develop in LINQ, if as soon as it gets too hard, they say, nup, go off and use a stored procedure? Well, I’ve come up with a simple solution that does allow you to batch LINQ queries.

Here is an example of the types of queries you can execute using the batching function. Note that this performs a single query to the database, consisting of multiple select statements, and returns three enumerable collections.

  var StocktraderLevelQuery =
   from StocktraderLevel in db.StocktraderLevels
   where StocktraderLevel.ItemID==10
   select Stocktrader;

  var StocktraderFunctionQuery =
   from StocktraderFunction in db.StocktraderFunctions
   select StocktraderFunction ;

  var StocktraderLevelAccessQuery =
   from StocktraderLevelAccess in db.StocktraderLevelAccesses
   select StocktraderLevelAccess;

  var mr = db.GetMultipleResults(StocktraderLevelQuery, StocktraderFunctionQuery, StocktraderLevelAccessQuery);

  StocktraderLevels = mr.GetResult<StocktraderLevel>();
  StocktraderFunctions = mr.GetResult<StocktraderFunction>();
  StocktraderLevelAccesses = mr.GetResult<StocktraderLevelAccess>();

 
To be able to do this, open up your project and add a dbml file. You do this by right-clicking on the project, selecting Add New Item, and then selecting LINQ to SQL classes. Type in your database name (in my case I called it Stocktrader.dbml). Then you use the Server Explorer to add a database connection to that database and drag the tables onto the dbml designer.

Behind the scenes, there is designer generated code, created by the LINQ to SQL custom tool. If you can’t see this file, you need to select “Show all files” from the toolbar. If you open that file, you can see that it’s a partial class. Good, that means we can extend it. So add a new class to the project. I named mine StocktraderExt.cs. Then I added the following code:

using System.Data;
using System.Linq;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data.Common;

namespace Stocktrader.DBML
{
 public partial class StocktraderDataContext
 {
  private DbParameter CloneParameter(DbParameter src)
  {
   SqlParameter source = (SqlParameter)src;
   SqlParameter destination = new SqlParameter();

   destination.Value = source.Value;
   destination.Direction = source.Direction;
   destination.Size = source.Size;
   destination.Offset = source.Offset;
   destination.SourceColumn = source.SourceColumn;
   destination.SourceVersion = source.SourceVersion;
   destination.SourceColumnNullMapping = source.SourceColumnNullMapping;
   destination.IsNullable = source.IsNullable;

   destination.CompareInfo = source.CompareInfo;
   destination.XmlSchemaCollectionDatabase = source.XmlSchemaCollectionDatabase;
   destination.XmlSchemaCollectionOwningSchema = source.XmlSchemaCollectionOwningSchema;
   destination.XmlSchemaCollectionName = source.XmlSchemaCollectionName;
   destination.UdtTypeName = source.UdtTypeName;
   destination.TypeName = source.TypeName;
   destination.ParameterName = source.ParameterName;
   destination.Precision = source.Precision;
   destination.Scale = source.Scale;

   return destination;
  }

  private SqlCommand CombineCommands(List<DbCommand> commandList)
  {
   SqlCommand batchCommand = new SqlCommand();
   SqlParameterCollection newParamList = batchCommand.Parameters;
   int commandCount = 0;
   foreach (DbCommand cmd in commandList)
   {
    string commandText = cmd.CommandText;
    DbParameterCollection paramList = cmd.Parameters;
    int paramCount = paramList.Count;
    for (int currentParam = paramCount – 1; currentParam >= 0; currentParam–)
    {
     DbParameter param = paramList[currentParam];
     DbParameter newParam = CloneParameter(param);
     string newParamName = param.ParameterName.Replace(“@”, string.Format(“@{0}_”, commandCount));
     commandText = commandText.Replace(param.ParameterName, newParamName);
     newParam.ParameterName = newParamName;
     newParamList.Add(newParam);
    }
    if (batchCommand.CommandText.Length > 0)
    {
     batchCommand.CommandText += “;”;
    }
    batchCommand.CommandText += commandText;
    commandCount++;
   }
   return batchCommand;
  }

  public IMultipleResults GetMultipleResults(params IQueryable[] queryableList)
  {
   List<DbCommand> commandList = new List<DbCommand>();
   foreach (IQueryable query in queryableList)
   {
    DbCommand cmd = this.GetCommand(query);
    commandList.Add(cmd);
   }
   SqlCommand batchCommand = CombineCommands(commandList);
   batchCommand.Connection = (SqlConnection)this.Connection;

   DbConnection cn = this.Connection;
   cn.Open();

   DbDataReader dr = batchCommand.ExecuteReader(CommandBehavior.CloseConnection);
   var mr = this.Translate(dr);
   return mr;
  }

 }
}

What I am doing here is passing a list of IQueryables into the GetMultipleResults method. The LINQ DbContext object exposes a GetCommand method. This enabled me to obtain the DbCommand object that would otherwise be used to execute the LINQ query. So I build up a list of DbCommand objects for all the queries in my parameter list.

Next, I decided to combine all the command objects into a single command (called the batch command). There are three parts to this. Firstly, the CommandText for each command can have parameters specified. In LINQ, they are always the same, starting at @p0 and working their way up, @p1, @p2, @p3 etc.

This isn’t a bad thing, because I can put in a counter that increments for every command that I am adding to the batch command, then substitute the name for a new name that includes my command counter. So @p0 becomes @0_p0 (the @p0 param for the first command). I am also using string.Replace to replace the parameters inside the CommandText, and I am building up a collection of new parameters for my new batch command. Note that this idea is based on the SqlCommandSet class that is hidden inside the SqlDataAdapter class. You could probably use this code as a basis for writing a standard batch command process for executing queries in bulk, if you wanted to.

After combining the DbCommand objects into one enormous command (potentially), I then attach to the DbContext connection, and execute the DbReader with an instruction to close the connection as soon as all data is read. No problem doing that because it’s a batch query – you shouldn’t need the connection afterwards as you’ve done all your queries in the one hit.

Finally, we need an object that has an interface of IMultipleResults. That’s done by another internal method, called Translate. Translate takes a DbDataReader as input, and returns multiple results from the reader.

Note these lines of code in the code section at the top:

  StocktraderLevels = mr.GetResult<StocktraderLevel>();
  StocktraderFunctions = mr.GetResult<StocktraderFunction>();
  StocktraderLevelAccesses = mr.GetResult<StocktraderLevelAccess>();

Basically, these retrieve the results in the order of the LINQ queries you passed to the GetMultipleResults method. And that’s it, you can now perform batch queries with LINQ to SQL.

I have provided the code here. Unfortunately wordpress only supports a few different file types, so here it is as a doc file: linqtosqlbatchqueries