LINQ Exchange

There is a bug with LINQ to SQL when inserting a record with an ID column that is of type uniqueidentifier, and uses a trigger to on Insert/Update, and you request the new ID to be returned by LINQ. So, the following three conditions must be met to recreate the bug:

1. ID Column is type uniqueidentifier
2. Table has a trigger on Insert/Update which copies the new/modified record to a history table
3. In order to get our LINQ Data Context to use the default value we had to set the column's Auto Generated Value to True and Auto-Sync to OnInsert in the LINQ To SQL designer. We also set the Read Only property to True as we should never be updating the primary key.

Running your query will result in the following error:

The target table 'dbo.XYZ' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Upon further investigation it seems LINQ to SQL is attempting to execute an INSERT statement similar to the following:

DECLARE @output TABLE([ItemID] UniqueIdentifier)
INSERT INTO [dbo].[Items]
    ([SiteID],
    [ItemContentTypeID],
    [Title],
    [DisplayTitle],
    [ItemContent],
    [Version],
    [LanguageCode],
    [CreatedByUserName],
    [CreatedDate],
    [HitCount],
    [LastUpdatedDate],
    [LastUpdatedByUserName])
OUTPUT
    INSERTED.[ItemID]
VALUES
    (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)
SELECT [ItemID] FROM @output

Of course what it should be executing is

DECLARE @output TABLE([ItemID] UniqueIdentifier)
INSERT INTO [dbo].[Items]
    ([SiteID],
    [ItemContentTypeID],
    [Title],
    [DisplayTitle],
    [ItemContent],
    [Version],
    [LanguageCode],
    [CreatedByUserName],
    [CreatedDate],
    [HitCount],
    [LastUpdatedDate],
    [LastUpdatedByUserName])
OUTPUT INTO
    INSERTED.[ItemID]
VALUES
    (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)
SELECT [ItemID] FROM @output

The subtle difference is the "OUTPUT INTO" rather than OUTPUT. Apperantly, this didn't get noticed by anyone, and it seems like it didn't get fixed in .NET 3.5 SP1.

Resolving the Issue

We were left with only a few ways to resolve this:

   1. Remove the Trigger and it works fine (but we lose our nice history feature)
   2. Stop using LINQ (but we love it so tenderly)
   3. Create the Guid ourselves by overriding the OnCreated event of the Table in LINQ up front and insert it (lose the nice default value of NewSequentialID())
   4. Create an Insert and Update stored procedure which uses the default properly.

We chose option 4 in the end, because it keeps the Trigger and keeps our default value so we can still work with the database directly without messing anything up. 

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.