Home > C#, LINQ > LINQ to SQL Batch Updates/Deletes: Fix for ‘Could not translate expression’

LINQ to SQL Batch Updates/Deletes: Fix for ‘Could not translate expression’

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):

var pay =
	from h in HistoryData
	where h.his.Groups.gName == "Client" && h.hisType == "pay"
	select h;

HistoryData.UpdateBatchPreview( 
	pay,
	h => new HistoryData {
		hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString()
	} ).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:

UPDATE [HistoryData]
SET [hisIndex] = [hisIndex] AS [s]
FROM [HistoryData] AS j0 INNER JOIN (
	
	SELECT [t0].[hisKey]
	FROM [HistoryData] AS [t0]
	LEFT OUTER JOIN [Profile] AS [t1] ON [t1].[pKey] = [t0].[hispKey]
	LEFT OUTER JOIN [Groups] AS [t2] ON [t2].[gKey] = [t1].[pgKey]
	WHERE ([t2].[gName] = @p0) AND ([t0].[hisType] = @p1)

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

-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Client]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [pay]
-- 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.

var pay =
	from h in HistoryData
	where h.his.Groups.gName == "Ochsner" && h.hisType == "pay"
	select new
	{
		hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString()
	};
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).

/// <summary>
/// Some LINQ Query syntax is invalid because SQL (or whomever the 
/// provider is) can not translate it to its native language. 
/// DataContext.GetCommand() does not detect this, only IProvider.Execute 
/// or IProvider.Compile call the necessary code to check this.  This function 
/// invokes the IProvider.Compile to make sure the provider can translate 
/// the expression.
/// </summary>
/// <remarks>
/// An example of a LINQ query that previously 'worked' in the UpdateBatch 
/// methods but needs to throw an exception is something
/// like the following:
///
/// var pay = 
/// 	from h in HistoryData
/// 	where h.his.Groups.gName == "Ochsner" && h.hisType == "pay"
///		select h;
///
/// HistoryData.UpdateBatchPreview( 
///		pay, 
///		h => new HistoryData { 
///			hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString() 
///		} ).Dump();
///
/// The int.Parse is not valid and needs to throw an exception like:
///
/// Could not translate expression '(Parse(p.hisIndex) - 1).ToString()' into 
/// SQL and could not treat it as a local expression.
///
/// Unfortunately, the IProvider.Compile is internal and I need to use 
/// Reflection to call it (ugh).  I've several e-mails sent into MS LINQ team 
/// members and am waiting for a response and will correct/improve code 
/// as soon as possible.
/// </remarks>
private static void ValidateExpression( ITable table, Expression expression )
{
	var context = table.Context;

	PropertyInfo providerProperty =
		context.GetType().GetProperty( "Provider",
			BindingFlags.Instance | BindingFlags.NonPublic );

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

	var compileMI = 
		provider.GetType().GetMethod( "System.Data.Linq.Provider.IProvider.Compile",
			BindingFlags.Instance | BindingFlags.NonPublic );

	// Simply compile the expression to see if it will work.
	compileMI.Invoke( provider, new object[] { expression } );
}

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

kick it on DotNetKicks.com

Categories: C#, LINQ
  1. April 23, 2008 at 2:16 am

    Surely adding something like UpdateBatch / DeleteBatch should be a walk in the park for ScottGu and his guys? (hint to the team – I want it, but actually want LINQ support for SQL2005′s XQuery support much sooner! – otherwise I might need to blog about the ‘evil’ workaround I devised, so be warned ;o] )…

  2. June 1, 2008 at 3:40 am

    There is a bug in the updated code.

    Line 140: "deleteCommand.CommandText = string.Format("DELETE [{0}]\r\n", table.GetDbName())" should change to
    "deleteCommand.CommandText = string.Format("DELETE {0}\r\n", table.GetDbName())"

    because the sql server can’t recognize the object [dbo.tablename]

  3. June 1, 2008 at 9:05 am

    Thanks for the catch. I’ve updated the code (modifying the GetDbName() extension method) to properly handle when the mapped database name has a . present (i.e. dbo.tablename) or starts with a [ (i.e. [dbo].[tablename]).

    In the former, I split the name and put [ ] around each part. In the later, I assume the table name is already aliased if need be. I should really do some research to see if I even need to bother with this, the MetaTable.TableName may already take care of all of this for me.

    In any case a new version has been published. Let me know if you find any problems.

  4. June 1, 2008 at 9:12 am

    Just a quick update. It looks like my efforts are [b]not[/b] in vain. We have a legacy table named [i]User[/i]. However, the MetaTable.TableName simply returns [i]User[/i] right back, so if I try to write an UPDATE statement like:

    UPDATE User
    SET …

    SQL throws an error: "Incorrect syntax near the keyword ‘User’."

    This is why I was trying to correctly ‘alias’ all tables [i]generically[/i]. Hopefully with the latest fix, things will be good to go again.

  5. June 1, 2008 at 7:45 pm

    Well, thanks for the latest code, but there still a space in line 363: tableName = string.Format("[{0}]", string.Join("].[ ", parts));

  6. June 2, 2008 at 1:37 am

    Ugh…I’ve fixed it again. Sloppy work by me. Sorry.

  7. June 11, 2008 at 1:33 am

    Hi Terry. I finally got around to taking a look at the code you posted here (you posted a couple comments on my post at http://skainsez.blogspot.com/2008/04/bulk-deletes-in-linq-to-sql.html).

    Indeed your solution is similar but better so I’ve made a note at the top of my post pointing people both to this post and the first you made about this.

    Thanks. I will be using your code from here on out.

  8. Patay Khan
    August 29, 2008 at 12:38 pm

    Really good work. and i have a suggestion too.

    Currently UpdateBatch and DeleteBatch takes IQueryable<TEntity> parameters, so we write the code like this (UpdateAll is the same method as DeleteBatch, i just renamed it to my likes):

    [quote]
    db.MyDataBaseEntity.UpdateAll(db.MyDataBaseEntity.Where(p => p.DateCreated == null), p => new MyDataBaseEntity() { DateCreated = DateTime.Now });
    [/quote]

    However, if we overwrite both these methods with Expression<Func<TEntity, bool>> parameter, this would avoid us to repeat db.MyDataBaseEntity pharse (as most of the time, we are doing simple filter), so the code becomes

    [quote]
    db.MyDataBaseEntity.UpdateAll(p => p.DateCreated == null, p => new MyDataBaseEntity() { DateCreated = DateTime.Now });
    [/quote]

    Much cleaner in my opinion.

    The two methods i have written are

    [quote]
    public static int UpdateAll<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
    return table.UpdateAll(table.Where(filter), evaluator);
    }

    public static int DeleteAll<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class
    {
    return table.DeleteAll(table.Where(filter));
    }
    [/quote]

    if you like it, please add these to your code.
    thanks

  9. October 19, 2008 at 1:09 pm

    Steve – Thanks for the plug. I’ll be adding more features/extensions about Batch Selecting soon as well based on this excellent article (http://tonesdotnetblog.wordpress.com/2008/07/23/linq-to-sql-batches-and-multiple-results-without-stored-procedures-by-tony-wright/) by Tony Wright.

    Patay – Thanks for the comments. I do like that idea, but only as an override. I don’t plan on getting rid of my current implementation. I could see in a ‘real-world application’ using simple WHERE filters like you mentioned, so it would be extremely handy there. However, I use these extension methods most frequently in LINQPad when I’m doing some ad-hoc updating/deleting from our databases.

    Therefore I am experimenting with an IQueryable<TEntity> object that has some fairly complex/dynamic filtering logic going on (i.e. joining other tables, comparing a db value to an array list of IDs, etc.) and once I get the filter right, it’s nice to just pass in the IQueryable<TEntity> variable, instead of trying to get the entire where clause writen as a parameter to a function (or having to declare a Expression<Func<TEntity, bool>> variable).

    So bottom line, I’ll be adding the overrides, but was just trying to justify my current function signature decisions ;)

  10. Moez Tounsi
    December 2, 2008 at 1:57 pm

    The corresponding field on the [dbo].[Employees] table could not be found.
    Parameter name: NameTest

    This error is generated.

    //I changed FirstName to NameTest (in the mapping not in the
    //database) db ref. Northwind

    When mapping, its a mormal behavior to change the mapping name, coz all developers aren’t using the default Northwind database, and may be handled by a Naming Chart in the database tier.

    Here the library must read the columns meta data then commit changes to the database.

    Thanks for any updates, please mail me on moeztounsi@hotmail.co.uk

  11. December 5, 2008 at 7:34 am

    I’ve put a Quick Links summary at the top of the post that will stay updated with any new posts semi related to these LINQ to SQL extension methods. Please see "I’ve Left Query Analyzer Hell for LINQPad Heaven" for notes on many of the features suggested in the comments.

  12. December 9, 2008 at 4:45 am

    HI, tnx for the information…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 164 other followers

%d bloggers like this: