18
Feb
Tags: , , | 2 Comments »

SQL command when inserting M:N association with identity columns in the underlying table

Today I uncovered a magic command from Entity Framework v4 when you create M:N association and the underlying table is defined with both columns as identity and you insert there. I don’t what’s it good for, as this table in fact only stores the two IDs to connect other tables. But somebody may build some logic on identity there, sure.

When I first saw the command, I was completely stunned. I had no idea what’s going on there and whether I see there one or more commands. You can have fun too:

declare @generated_keys table([ID_A] int, [ID_B] int)
insert [dbo].[A_B]
output inserted.[ID_A], inserted.[ID_B] into @generated_keys
default values
select t.[ID_A], t.[ID_B]
from @generated_keys as g join [dbo].[A_B] as t on g.[ID_A] = t.[ID_A] and g.[ID_B] = t.[ID_B]
where @@ROWCOUNT > 0

As an old school guy I was first looking for semicolons and then later tried to decode it by “parsing” the content.

Isn’t it nice… :)

There's 2 Comments So Far

  • Craig Stuntz
    February 18th, 2010 at 15:51

    There are three statements here.

    The first, which is line 1 only, declares a table-valued variable. Table valued variables are sort of like temp tables, but they are scoped only for the current batch.

    The second, which is lines 2 through 4, does the INSERT.

    The third, lines 5-7, returns the identity values.

    The basic problem is that you cannot use SCOPE_IDENTITY() to retrieve the identity values, since there is more than one. But the EF needs to know the values for both columns, so that it can set the entity keys correctly.

  • cincura.net
    February 19th, 2010 at 09:10

    I know what’s there. But for a first sight without semicolons it was “interesting”. :)

Share your thoughts, leave a comment!