LINQ reading last Identity

[viepia]

Hi,

I orignally had a SPROC rerturning @@Identiy, it usually works but sometimes it
doesn't. In my current project I verified with SQL Server Profiler that I was reading
@@Identity just after an Insert, but @@Identiy still returned null.

This SPROC appears to work every time:

ALTER PROCEDURE dbo.GetSysIdentity
@TableName VARCHAR(64),
@LastIdentity BIGINT OUTPUT
AS
BEGIN
SELECT @LastIdentity = IDENT_CURRENT(@TableName)
RETURN 0
END

Since I know the name of the SQL Table I am inserting to I can manually type the name:
long ? lastIdentity = 0;
Trace.Assert(db.GetSysIdentity("MyTable",ref lastIdentity)==0,"Bad SPROC");

Is there anywhere in LINQ where I can programmically find the Table Name? The Table
Name is shown in the <database>DataClasses.dbml diagram in the table's properties in the
Name and Source fields, how do I access these?

Thanks
Viepia

 

[Mufaka]

Have you tried scope_identity() instead of @@Identity? IIRC @@Identity
doesn't work well if there are triggers or other writes that happen.

I think your new SPROC will also suffer from that race condition.

I may be a little off point here because I don't know the answer to what
you are specifically asking.

 

[viepia]

From http://msdn2.microsoft.com/en-us/library/ms190315.aspx "A scope is a module: a stored
procedure, trigger, function, or batch. Therefore, two statements are in the same scope if
they are in the same stored procedure, function, or batch." How does scope map to
threads in a C# LINQ application, if it at all?

In running this SPROC:

ALTER PROCEDURE dbo.GetSysIdentity
@TableName VARCHAR(64),
@LastIdentity BIGINT OUTPUT
AS
BEGIN
SELECT @LastIdentity = IDENT_CURRENT(@TableName)
DECLARE @TestIdentiy BIGINT
SELECT @TestIdentiy = scope_identity()
IF @TestIdentiy <> @LastIdentity
RETURN 1
RETURN 0
END

so far in my application it as always returned 0.

Iis there an easier way to do this?

System.Reflection.MemberInfo inf = typeof(MyTable);
object[] attributes = inf.GetCustomAttributes(false);
System.Data.Linq.Mapping.TableAttribute ta =
(System.Data.Linq.Mapping.TableAttribute)(attributes[0]);
long? temp = 0;
Trace.Assert(db.GetSysIdentity(ta.Name,ref temp) == 0, "Bad SPROC");


\\ instead of manually typing the table name
Trace.Assert(db.GetSysIdentity("MyTable",ref temp) == 0, "Bad SPROC");



Thanks very much,
Viepia

 

[Marc Gravell]

so far in my application it as always returned  0.
Well, your proc doesn't insert any data (let alone any with an
IDENTITY column) so returning 0 is the correct behavior for both
SCOPE_IDENTITY() and @@IDENTITY.

I wouldn't use IDENT_CURRENT except in administrative queries - it is
susceptible to both race conditions and phantom reads (due to
rollbacks).

Can we take this back a level? What are you trying to do? I would hope
that LINQ woule update your object's identity (and timestamp if
relevant) once saved; does it not? (II've no db at the moment, so I
can't check...)

Alternatively, if appropriate you can ask LINQ to use your existing
procs for INSERT/UPDATE; again, I haven't played with this enough to
know if it re-applies identity from an OUT.


Marc

 

[Paul Shapiro]

You would include the scope_identity() call in the stored procedure that
performs the insert since that's where the scope (context) exists. Return
the new identity value as an output parameter. If you call a separate stored
proc, there wasn't any insert in that scope so.

Since scope is a sql server boundary, I would not expect it to match to .net
threads at all. Each distinct call to sql server would be a separate scope.
If you pass sql server multiple sql statements in one call, that set of sql
statements is a batch, and would be the scope. If your sql call is executing
a single stored proc, that stored proc would be the scope. Within a stored
proc, scope is that one stored proc.

From http://msdn2.microsoft.com/en-us/library/ms190315.aspx "A scope is a
module: a stored
procedure, trigger, function, or batch. Therefore, two statements are in
the same scope if
they are in the same stored procedure, function, or batch." How does
scope map to
threads in a C# LINQ application, if it at all?

In running this SPROC:

ALTER PROCEDURE dbo.GetSysIdentity
@TableName VARCHAR(64),
@LastIdentity BIGINT OUTPUT
AS
BEGIN
SELECT @LastIdentity = IDENT_CURRENT(@TableName)
DECLARE @TestIdentiy BIGINT
SELECT @TestIdentiy = scope_identity()
IF @TestIdentiy <> @LastIdentity
RETURN 1
RETURN 0
END

so far in my application it as always returned 0.

Iis there an easier way to do this?

System.Reflection.MemberInfo inf = typeof(MyTable);
object[] attributes = inf.GetCustomAttributes(false);
System.Data.Linq.Mapping.TableAttribute ta =
(System.Data.Linq.Mapping.TableAttribute)(attributes[0]);
long? temp = 0;
Trace.Assert(db.GetSysIdentity(ta.Name,ref temp) == 0, "Bad
SPROC");


\\ instead of manually typing the table name
Trace.Assert(db.GetSysIdentity("MyTable",ref temp) == 0, "Bad
SPROC");



Thanks very much,
Viepia

Have you tried scope_identity() instead of @@Identity? IIRC @@Identity
doesn't work well if there are triggers or other writes that happen.

I think your new SPROC will also suffer from that race condition.

I may be a little off point here because I don't know the answer to what
you are specifically asking.