tags:

views:

48

answers:

2

I can do this if I leave the Entity's Insert method as 'Use Runtime' and have it deal with the inserts directly, but I need to use an SP.

I have tried to simply return the @@Identity from sql server and then assign this to the ID but this causes a change notification and LinqToSql thinks it has changed, even though it's just the ID.

So basically I need a way of assigning the primary key on inserts via an sp, without it triggering the change notification.

Is this possible, if so how?

thanks.

+2  A: 

A solution that works for me is to call the SP inside a partial method on your DataContext called InsertEntity (where Entity is the name of your class. In this method you must call your SP manually, but then you can map the return values to your entity's properties, and Linq To SQL will honour this.

For example:

public partial class YourDataContext
{
    partial void InsertYourEntity(YourEntity entity)
    {
        using (DbCommand cmd = this.Connection.CreateCommand())
        {
            ... // set the parameters and SP name here

            cmd.ExecuteNonQuery();

            entity.Id = (int) someParameter.Value;
        }
    }
}

Note that this will not suppress any IdChanging or IdChanged event (as it did change, and databindings can depend on a proper notification). However, calling SubmitChanges twice will not cause an Insert followed by an Update, as the second SubmitChanges will not see the entity as being changed.

Ruben
That's what i already have (pretty much), although when I assign a value to the entity.Id property, for example, it causes a change notification as it thinks the ID is now modified (just so happens to be from zero/null to my new value)
HAdes
You mean a change notification through an IdChanged event? Well it did change, didn't it? But calling SubmitChanges twice will not re-sumbit your object for an update.
Ruben
A: 

With "Use runtime" set, entity will emit a sql statement like the one below, try to mimic it inside your stored procedure implementation.

DECLARE @output TABLE([SurrogateKey] UniqueIdentifier)
DECLARE @id UniqueIdentifier

INSERT INTO [identity].[AddressTypes]([Name])
OUTPUT INSERTED.[SurrogateKey] INTO @output
VALUES (@p0)

SELECT @id = SurrogateKey FROM @output

SELECT [t0].[SurrogateKey], [t0].[LastUpdatedOn], [t0].[LastUpdatedBy]
FROM [identity].[AddressTypes] AS [t0]
WHERE [t0].[SurrogateKey] = (@id)
-- @p0: Input VarChar (Size = 5; Prec = 0; Scale = 0) [Other]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
guercheLE