CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-6.1entity-framework-extended
Ken Smith
Ken Smith

Reputation: 20445

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

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?

Upvotes: 4

Views: 1318

Answers (1)

Gubbins
Gubbins

Reputation: 134

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.

Upvotes: 2

Related Questions