MSSQL 2100 parameter limit

Hi,

we ran into the 2100 parameters limit of mssql a couple of times (the workarounds are quite easy but also force to write additional code and execute multiple queries). The nasty thing about this is, that it can be easily missed and only occurres in production after a year or two.

We did a test now and found that there is room for improvement. We start with this:

  LightSpeedContext<ModelUnitOfWork> context = new LightSpeedContext<ModelUnitOfWork>("Production");
  using (var uow = context.CreateUnitOfWork()) {
    long[] ids = uow.Locations.Select(l => l.Id).Take(3000).ToArray();

First try using LightSpeed:

jobstaffs = jobstaffs.OrderBy(l => l).ToList(); // This leads to an exception

Second try "by hand":

    StringBuilder sb = new StringBuilder("where l.location_id in (");
    sb.Append(string.Join(",", ids));
    sb.Append(")");

    var cmd = uow.Context.DataProviderObjectFactory.CreateCommand();
    cmd.CommandText = string.Format(@"SELECT [location_id] FROM [dbo].[location] l {0};", sb.ToString());
    cmd.CommandType = CommandType.Text;
    uow.PrepareCommand(cmd);
    var reader = cmd.ExecuteReader();

    IList<long> jobstaffs = new List<long>();

    while (reader.Read()) {
      long locationId = reader.GetInt64(0);
      jobstaffs.Add(locationId);
    }

It works. Are we missing something when we use LightSpeed?

No, we don't try and detect for this type of problem since its specific to MySQL. You can also work around this by taking a batched approach of say 1000 at a time when building your query which will keeps it paramaterized rather than building up the raw SQL directly as in your "by hand" example.

e.g.

var jobStaffs = new List<JobStaff>();

while (ids.Count > 0)
{
  var batch = ids.Take(1000).ToList();
  ids = ids.RemoveRange(0, batch.Count);

  var params = batch.Cast<object>().ToArray();  // this is needed to force the values to be emitted correctly regardless of the source type
  var queryExpression = Entity.Attribute("LocationId").In(params));
  var results = new Query(typeof(JobStaff), queryExpression);

  jobStaffs.AddRange(results);
}

Jeremy

Thank you for your answer. Your example is basically what we have been doing. I think that the use of MSSQL is quite common for a .net-based-framework and this type of code creates quite a lot of overhead all over the code.