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 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.
I know what’s there. But for a first sight without semicolons it was “interesting”.