I've found and posted a new fix in the code from my original post: Batch Updates and Deletes with LINQ to SQL.  I'm not sure of the etiquette for this sort of thing: new post (like I'm doing) or just a comment in the original post.  But since I did get a fair amount of hits to the article but minimal comments, people who may have downloaded the code wouldn't get an update notification and I want to be sure to make them aware of an issue/fix (assuming they are monitoring via a RSS feed).

Quick Links to All Articles

Batch Updates and Deletes with LINQ to SQL
LINQ to SQL Batch Updates/Deletes: Fix for 'Could not translate expression'
I've Left Query Analyzer Hell For LINQPad Heaven

Summary

First, I'll give the quickest summary I can.  My method of creating a single UPDATE SQL statement was to create the required Expression objects from the Lambda assignment expression.  Then compile each of them into an IQueryable object, finally calling DataContext.GetCommand() to let the underlying provider generate the native SQL for us.  Some expressions successfully went through this process, but in reality, if you had written the same expression in a normal IQueryable and tried to execute it, you would get an error like: Could not translate expression 'xxx' into SQL and could not treat it as a local expression.  So I had to emulate that behavior - throwing an exception.  If that was enough of an explanation, download the code here.  If you want to learn a little bit about how I discovered and fixed it, read on.

The statement that lead me to this error was something like the following (assuming hisIndex is a string representation of a year):

   1: var pay = 

   2:         from h in HistoryData

   3:         where h.his.Groups.gName == "Client" && h.hisType == "pay"

   4:         select h;

   5:  

   6: HistoryData.UpdateBatchPreview( 

   7:         pay, 

   8:         h => new HistoryData { 

   9:             hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString() 

  10:         } ).Dump();

The only reason I caught it was that I previewed this SQL before executing it because I was 'curious' to see how 'smart' LINQ to SQL was about performing int.Parse().  The SQL it returned was:

   1: UPDATE [HistoryData]

   2: SET [hisIndex] = [hisIndex] AS [s]

   3:  

   4: FROM [HistoryData] AS j0 INNER JOIN (

   5:  

   6: SELECT [t0].[hisKey]

   7: FROM [HistoryData] AS [t0]

   8: LEFT OUTER JOIN [Profile] AS [t1] ON [t1].[pKey] = [t0].[hispKey]

   9: LEFT OUTER JOIN [Groups] AS [t2] ON [t2].[gKey] = [t1].[pgKey]

  10: WHERE ([t2].[gName] = @p0) AND ([t0].[hisType] = @p1)

  11:  

  12: ) AS j1 ON (j0.[hisKey] = j1.[hisKey])

  13:  

  14: -- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Client]

  15: -- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [pay]

  16: -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Uh oh, as you can see from this result, I was thinking LINQ to SQL wasn't so smart (i.e. the SET [hisIndex] = [hisIndex] AS [s] statement wasn't exactly decrementing the index).  So I emailed a few LINQ to SQL team members and Michael Pizzo was nice enough to reply with the following:

I suspect the problem has to do with the fact that you're calling a method w/in the constructor for the anonymous type that can't be executed on the server.

This lead me to investigate what would happen if I just turned my pay IQueryable into a normal select with the int.Parse() projection to see what would happen.

   1: var pay = 

   2:     from h in HistoryData

   3:     where h.his.Groups.gName == "Ochsner" && h.hisType == "pay"

   4:     select new 

   5:         { 

   6:             hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString() 

   7:         };

   8: pay.Dump();

Upon executing this in LINQPad, I received the following error: Could not translate expression '(Parse(h.hisIndex) - 1).ToString()' into SQL and could not treat it as a local expression.  Aha, Michael was on to something!  So, only slightly disappointed that LINQ to SQL couldn't translate int.Parse(), at least my confidence was restored in LINQ to SQL in general ;)  Looking at the call stack, the exception was thrown in System.Data.Linq.SqlClient.ObjectReaderCompiler.Generator.Generate(SqlNode node, LocalBuilder locInstance) which was called from System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query).  Here comes the discouraging part: I had to use Reflection to accomplish my goal of throwing the same exception.

Since DataContext.GetCommand() did not throw exceptions, but only IProvider.Execute and IProvider.Compile seem to call the bits of code that did the actual validation, I needed to invoke one of them (I may have missed something in my Reflector analysis).  Obviously, IProvider.Execute was not an option as I didn't want to run a query for every expression I was processing otherwise it would defeat the whole purpose of my batch updates, so I chose IProvider.Compile.

With that decision, here is the validation method I call for every expression I evaluate during the UPDATE SQL statement creation (comments included).

   1: /// <summary>

   2: /// Some LINQ Query syntax is invalid because SQL (or whomever the provider is) can not translate it to its native language.  

   3: /// DataContext.GetCommand() does not detect this, only IProvider.Execute or IProvider.Compile call the necessary code to 

   4: /// check this.  This function invokes the IProvider.Compile to make sure the provider can translate the expression.

   5: /// </summary>

   6: /// <remarks>

   7: /// An example of a LINQ query that previously 'worked' in the UpdateBatch methods but needs to throw an exception is something

   8: /// like the following:

   9: /// 

  10: /// var pay = 

  11: ///from h in HistoryData

  12: ///where h.his.Groups.gName == "Ochsner" && h.hisType == "pay"

  13: ///select h;

  14: ///

  15: /// HistoryData.UpdateBatchPreview( pay, h => new HistoryData { hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString() } ).Dump();

  16: /// 

  17: /// The int.Parse is not valid and needs to throw an exception like: 

  18: /// 

  19: ///Could not translate expression '(Parse(p.hisIndex) - 1).ToString()' into SQL and could not treat it as a local expression.

  20: ///

  21: ///    Unfortunately, the IProvider.Compile is internal and I need to use Reflection to call it (ugh).  I've several e-mails sent into

  22: ///    MS LINQ team members and am waiting for a response and will correct/improve code as soon as possible.

  23: /// </remarks>

  24: private static void ValidateExpression( ITable table, Expression expression )

  25: {

  26:     var context = table.Context;

  27:  

  28:     PropertyInfo providerProperty = 

  29:         context.GetType().GetProperty( "Provider", 

  30:                         BindingFlags.Instance | BindingFlags.NonPublic );

  31:  

  32:     var provider = providerProperty.GetValue( context, null );

  33:  

  34:     var compileMI = 

  35:         provider.GetType().GetMethod( "System.Data.Linq.Provider.IProvider.Compile", 

  36:                         BindingFlags.Instance | BindingFlags.NonPublic );

  37:     

  38:     // Simply compile the expression to see if it will work.

  39:     compileMI.Invoke( provider, new object[] { expression } );

  40: }

As the comment above mentions, I've sent a few emails (along with links to this article) to a couple LINQ to SQL team members to see if they have any other suggested workarounds.  If notified, I will only update this post (as opposed to making a new post) so anyone who downloads this code, you've been warned :) 

I apologize for the missed bug and I hope your batch updating and deleting is going as well as mine!

Download the updated code