Archive for August, 2008

Working around LINQ to SQL’s bug with Database Triggers

Strange that I can only find one forum post on this issue as I would think it would be quite common. Admittedly the issue comes about only when all the stars are aligned, here’s how we came across it:

Reproducing the Issue

  1. We created a database table that has a uniqueidentifier (or System.Guid in .NET) as the primary key.
  2. We set the default value for this primary key column to be NewSequentialID() so that our table would be neatly paged and indexed.
    image
  3. Then we added an INSERT and UPDATE Trigger to the table so that any changes would get inserted into a History table. A pretty common practice as far as implementing history tables would go I’d say.
  4. 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.
    image
  5. It was at this point that everything looked like it would work beautifully as this had been done with other tables with great success.  Go to add an entry in our web app and… CRASH!

The target table ‘dbo.Items’ 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. What astounds me is this didn’t get noticed by anyone, and also seems like it didn’t get fixed in .NET 3.5 SP1, unfortunately I found it too late to log a bug with Connect (plus the fact that I can never connect to Connect …  ironic huh?)

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. OR Create an Insert and Update stored procedure which uses the default properly.

We opted for option 4 in the end as it keeps the Trigger and keeps our default value so we can still work with the database directly without stuffing anything up.

Here’s how we did it:

  1. Create an ItemsInsertItem stored proc using the correct insert statement above (the one with the OUTPUT INTO) so that we can return our NewSequentialID() as an OUTPUT parameter
  2. Create an ItemsUpdateItem stored proc. In this case the output stuff is not necessary as the ID already exists, unfortunately LINQ doesn’t figure this out so you have to do the Update yourself.
  3. Dragged the two stored proc’s from the Server Explorer to LINQ’s data designer surface.
  4. Set the Read Only property on the ItemID primary key column to False. This is so that we can return the resulting value from the insert stored procedure.
  5. Go to the code behind of the .dbml file and implement two partial methods in the DataContext partial class like so:
    public partial class MyLinqToSQLDataContext
    {
        partial void InsertItem(Item instance)
        {
            // Use a custom insert statement to fix Trigger issue
            Guid? newGuid = null;
            ItemsInsertItem(ref newGuid, 
                instance.SiteID, 
                instance.ItemContentTypeID, 
                instance.Title, 
                instance.DisplayTitle, 
                instance.ItemContent, 
                instance.LanguageCode, 
                instance.CreatedByUserName, 
                instance.CreatedDate);
            instance.ItemID = newGuid.Value;
        }

        partial void UpdateItem(Item instance)
        {
            ItemsUpdateItem(instance.ItemID,
                instance.SiteID,
                instance.ItemContentTypeID,
                instance.Title,
                instance.DisplayTitle,
                instance.ItemContent,
                instance.Version,
                instance.LanguageCode,
                instance.CreatedByUserName,
                instance.CreatedDate,
                instance.HitCount,
                instance.LastUpdatedDate,
                instance.LastUpdatedByUserName);
        }
    }

This means the data access code doesn’t need to change, we just do a little bit of stored proc injection and everyone’s happy!

1 Comment