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

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;
    if (batchCommand.CommandText.Length > 0)
     batchCommand.CommandText += “;”;
    batchCommand.CommandText += commandText;
   return batchCommand;

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

   DbConnection cn = this.Connection;

   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

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

  1. mcbeev says:

    Very good solution for a simple example, and something I would like to use, but there are some limitations to Translate() that I think you should mention, like The column name in the result set must correspond to the property name. So thats a deal breaker for me.

    I also had problems getting it to work with objects that are complex and use relations to fill themselves with. It seemed to go only one level deep.

    Great idea though.

  2. tonywr says:

    Hi mcbeev,

    I had a suspicion that it might be like that, but I’ve put it up anyway as others may be able to build on it to produce a better solution.

    For the columns, it might just be a case of reading the mapped column properties from within the DBML and translating between the Source (for database) and Name (for code) properties in the query and resetting the column names in the result set before attempting to Translate the results into LINQ.

    As for the complex objects, I didn’t seem to have any issues when the requests translated to single SQL commands, no matter how many related objects were walked within a single LINQ query. The query itself was generated with all the joins in it, and any parameters for all the queries in the one request seemed to generate just fine. You may have found a class of queries that it doesn’t handle. Could you please provide an example?


  3. maxi326 says:

    To find out an exception in a bunch of changes submitted in LINQ is really its weakness.
    Thank you.

  4. tonywr says:

    I agree maxi326. This was done more for academic reasons to prove it could be done. I would advise people not to use this method in a production system, as stored procedures are a much better choice. It’s a pity that LINQ to SQL can’t automatically handle multiple results from a stored procedure out of the box, as using a single query that returns one to many results per request, is always a better choice from a performance point of view.

  5. tonywr says:

    Just an fyi, in .net 4.0, PLINQ will be available. This will enable multiple queries to be executed not sequentially but parallel to each other. This is probably why Microsoft didn’t support multiple results from LINQ to SQL queries, as this will be their prefered method of execution.

  6. […] Selecting – Thanks to the great article from Tony Wright, I simply completed my batch obsession by adding Batch Selects.  Read […]

  7. […] found an article here that has a suggested solution, but i hate the idea of having to write up a massive amount of code […]

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: