"Invalid column name" error performing update with EntityFramework.Extended

Question

I'm using the EntityFramework.Extended library to do a set of batch updates. The important parts of the table I'm updating look like this:

CREATE TABLE [dbo].[BoosterTargetLog]
(
    [Id] BIGINT NOT NULL identity PRIMARY KEY,
    [CustomerUserId] INT NULL,
    CookieId uniqueidentifier not null,
    CONSTRAINT [FK_BoosterTargetLog_ToOrganizationCookie] FOREIGN KEY (CookieId) REFERENCES [OrganizationCookie]([CookieId])
)

The C# update code looks like this:

var query = db.BoosterTargetLogs
    .Where(x =>
        x.OrganizationCookie.OrganizationId == organizationId &&
        x.CookieId == cookieId &&
        x.CustomerUserId == null);

var updated = await query.UpdateAsync(x => new BoosterTargetLog
{
    CustomerUserId = customerUserId
});

That generates SQL update code that looks like this:

exec sp_executesql N'UPDATE [dbo].[BoosterTargetLog] SET 
[CustomerUserId] = @p__update__0 
FROM [dbo].[BoosterTargetLog] AS j0 INNER JOIN (
SELECT 
    [Extent2].[OrganizationId] AS [OrganizationId], 
     CAST( [Extent1].[Id] AS int) AS [C1]
    FROM  [dbo].[BoosterTargetLog] AS [Extent1]
    INNER JOIN [dbo].[OrganizationCookie] AS [Extent2] ON [Extent1].[CookieId] = [Extent2].[CookieId]
    WHERE ([Extent2].[OrganizationId] = @p__linq__0) AND ([Extent1].[CookieId] = @p__linq__1) AND ([Extent1].[CustomerUserId] IS NULL)
) AS j1 ON (j0.[Id] = j1.[Id])',N'@p__linq__0 int,@p__linq__1 uniqueidentifier,@p__update__0 int',@p__linq__0=1075,@p__linq__1='44A191F0-9086-4867-9777-ACEB9BB3B944',@p__update__0=95941

When I run the update, either through the C# code, or if I manually execute the generated SQL, I get this error:

Invalid column name 'Id'.

The problem is that there's an error in the generated SQL. Note the piece that reads like this:

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

It should actually read:

 AS j1 ON (j0.[Id] = j1.[C1])

My assumption, then, is that there's a bug of some sort in the EntityFramework.Extended library.

Suggestions for workarounds?

Did you find a fix for this? I'm running in the same issue: wrong column name picked. Any thoughts? — Micaël Félix, May 26 '15 at 13:37
No, no solution other than to switch to a different method for doing the batch updates - I think I moved my logic over to a stored procedure. I'm sure it wouldn't be incredibly difficult to track down the bug in the code, but it was easier to switch to a sproc than to do that, so... :-(. — Ken Smith, May 26 '15 at 18:15

Gubbins · Answer 1 · 2014-09-16T15:19:30.770

2

I received this same error but only when my Where clause doesn't match any items.

Please check to ensure you have data that matches:

var query = db.BoosterTargetLogs
    .Where(x =>
        x.OrganizationCookie.OrganizationId == organizationId &&
        x.CookieId == cookieId &&
        x.CustomerUserId == null);

I put in a check to ensure I have some records to update, maybe try this:

if (query.Any())
{
    var updated = await query.UpdateAsync(x => new BoosterTargetLog
    {
        CustomerUserId = customerUserId
    });
}

I'm not using UpdateAsync though, just plain Update. Hopefully the same applies to your case.

edited Sep 16 '14 at 15:19

answered Sep 16 '14 at 14:56

Gubbins

134
1
9

The whole point of using batch update is to do it in a single query... workaround with using Any() kinda misses the point. EntityFramework.Extended seems to be too hackish way of using Linq queries to perform batch updates and therefore it doesn't work in 50% of the cases in EF6/6.1. – Lukasz Spas Mar 10 '16 at 09:44
Can't say I strongly disagree @lukasz-spas , we're moving some of our batch deletes and updates into sprocs for better performance than EF.Extended can provide. I disagree with you slightly though, the check for `.Any()` is one call, `UpdateAsync` could be a second call, depending on the quantity data being updated it's still better than making 1000 separate calls. – Gubbins Mar 13 '16 at 21:58
That helped me too! – Elisabeth Nov 10 '16 at 12:51

"Invalid column name" error performing update with EntityFramework.Extended

1 Answers1