none
Replication & LINQ to SQL compatability problem (?) RRS feed

  • Question

  • I have a straightforward web app that uses LINQ to SQL to modify the a replicated database at the publisher.  Because replication requires a rowguidcol in every replicated table, we use the rowguidcol GUID as the identifier for every row (rather than using a generated int); moreover, we follow the best-practice of allowing SQL to generate the GUID using newsequentialid for the sake of indexing.  Using LINQ, this means for every primary key we set the column to AutoSync = OnInsert and AutoGeneratedValue = true.

     

    Until yesterday I hadn't turned on replication on my development machine, so I hadn't been running into this problem.  Now that replication is turned on, I can't insert any objects into the database without hitting this exception:

     

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

     

    I used the context.Log property to get an example command that fails:

     

    DECLARE @output TABLE([MenuItemRevisionID] UniqueIdentifier)
    INSERT INTO [dbo].[MenuItemRevisions]([MenuItemID], [EffectiveDate], [MenuItemCode], [NameEnglish], [NameFrench], [KVSNameEnglish], [KVSNameFrench], [ReceiptNameEnglish], [ReceiptNameFrench], [ShortNameEnglish], [ShortNameFrench], [DisplayGroup], [SubFamilyGroupID], [DefaultPrice], [ABSNozzleID], [ABSSizeID])
    OUTPUT INSERTED.[MenuItemRevisionID]
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15)
    SELECT [MenuItemRevisionID] FROM @output

     

    If I'm interpreting this correctly, it appears that LINQ and Replication don't play as nicely together as I'd hoped.  I didn't find any hits on the LINQ forums, and I'm surprised no one else has run into this issue - am I doing something unusual or incorrectly?

     

    I greatly appreciate any help, as we're far into our development and any changes to our code to solve this problem may likely take a while.  Thanks!

    Tuesday, October 21, 2008 3:13 PM

Answers

  • This is a bug in LINQ to SQL in .NET 3.5 SP1 and it has been fixed for .NET 4.0.  A hotfix is also available from support services.

    [)amien
    Wednesday, April 15, 2009 6:16 AM
    Moderator