Geek Answers Handbook

Weird Select / Delete query generation in Linq2SQL

For one reason or another, Linq2SQL generates the following in one of my tables for deletion:

DELETE FROM [dbo].[Tag] WHERE ([TagId] = @p0) AND ([Type] = @p1) -- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [fb538481-562d-45f2-bb33-3296cd7d0b28] -- @p1: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [1] -- @p2: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [0] -- @p3: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [7] -- @p4: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [5] -- @p5: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [8] -- @p6: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [4] -- @p7: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [3] -- @p8: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [9] -- @p9: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [6] -- @p10: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [1] -- @p11: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [2] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

As you can see, the first 2 parameters (@ p0 and @ p1) are correct, but then it generates a randomized set of a unique number from 0 to 9.

Now this does not affect the request / behavior, I just wonder what is happening here.

UPDATE:

A tag is the base class for inheriting Linq2SQL. Apparently, the integer values ​​of the discriminator (Type) of all inherited types are additional parameters. If I delete the inherited types, the extra options are reduced.

UPDATE 2:

I noticed that this happens for SELECT as well.

 SELECT (CASE WHEN EXISTS( SELECT NULL AS [EMPTY] FROM [Tag] AS [t0] WHERE ([t0].[TagId] = @p0) AND ([t0].[TagType] = @p1) ) THEN 1 ELSE 0 END) AS [value] -- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [60000000-0000-0000-0000-fe0000000025] -- @p1: Input Byte (Size = 0; Prec = 0; Scale = 0) [25] -- @p2: Input Byte (Size = 0; Prec = 0; Scale = 0) [0] -- @p3: Input Byte (Size = 0; Prec = 0; Scale = 0) [10] -- @p4: Input Byte (Size = 0; Prec = 0; Scale = 0) [28] -- @p5: Input Byte (Size = 0; Prec = 0; Scale = 0) [13] -- @p6: Input Byte (Size = 0; Prec = 0; Scale = 0) [27] -- @p7: Input Byte (Size = 0; Prec = 0; Scale = 0) [1] -- @p8: Input Byte (Size = 0; Prec = 0; Scale = 0) [2] -- @p9: Input Byte (Size = 0; Prec = 0; Scale = 0) [3] -- @p10: Input Byte (Size = 0; Prec = 0; Scale = 0) [4] -- @p11: Input Byte (Size = 0; Prec = 0; Scale = 0) [5] -- @p12: Input Byte (Size = 0; Prec = 0; Scale = 0) [6] -- @p13: Input Byte (Size = 0; Prec = 0; Scale = 0) [7] -- @p14: Input Byte (Size = 0; Prec = 0; Scale = 0) [8] -- @p15: Input Byte (Size = 0; Prec = 0; Scale = 0) [9] -- @p16: Input Byte (Size = 0; Prec = 0; Scale = 0) [11] -- @p17: Input Byte (Size = 0; Prec = 0; Scale = 0) [12] -- @p18: Input Byte (Size = 0; Prec = 0; Scale = 0) [14] -- @p19: Input Byte (Size = 0; Prec = 0; Scale = 0) [15] -- @p20: Input Byte (Size = 0; Prec = 0; Scale = 0) [16] -- @p21: Input Byte (Size = 0; Prec = 0; Scale = 0) [17] -- @p22: Input Byte (Size = 0; Prec = 0; Scale = 0) [18] -- @p23: Input Byte (Size = 0; Prec = 0; Scale = 0) [19] -- @p24: Input Byte (Size = 0; Prec = 0; Scale = 0) [20] -- @p25: Input Byte (Size = 0; Prec = 0; Scale = 0) [21] -- @p26: Input Byte (Size = 0; Prec = 0; Scale = 0) [22] -- @p27: Input Byte (Size = 0; Prec = 0; Scale = 0) [23] -- @p28: Input Byte (Size = 0; Prec = 0; Scale = 0) [24] -- @p29: Input Byte (Size = 0; Prec = 0; Scale = 0) [26] -- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.30729.1

Just a note. I use the discriminator enumeration according to the MS examples.

UPDATE 3

Now I run the SQL profiler, and I see the following, and this is quite worrying :(

 exec sp_executesql N'SELECT (CASE WHEN EXISTS( SELECT NULL AS [EMPTY] FROM [Tag] AS [t0] WHERE ([t0].[TagId] = @p0) AND ([t0].[TagType] = @p1) ) THEN 1 ELSE 0 END) AS [value]', N'@p0 uniqueidentifier, @p1 tinyint, @p2 tinyint, @p3 tinyint, @p4 tinyint, @p5 tinyint, @p6 tinyint, @p7 tinyint, @p8 tinyint, @p9 tinyint, @p10 tinyint, @p11 tinyint, @p12 tinyint, @p13 tinyint, @p14 tinyint, @p15 tinyint, @p16 tinyint, @p17 tinyint, @p18 tinyint, @p19 tinyint, @p20 tinyint, @p21 tinyint, @p22 tinyint, @p23 tinyint, @p24 tinyint, @p25 tinyint, @p26 tinyint, @p27 tinyint, @p28 tinyint, @p29 tinyint, @p30 tinyint, @p31 tinyint, @p32 tinyint', @p0='60000000-0000-0000-0000-FE0000000025', @p1=25,@p2=0,@p3=23,@p4=29,@p5=31,@p6=16,@p7=14,@p8=1,@p9=2, @p10=3,@p11=4,@p12=5,@p13=6,@p14=7,@p15=8,@p16=9,@p17=10,@p18=11, @p19=12,@p20=13,@p21=22,@p22=15,@p23=17,@p24=18,@p25=19,@p26=20, @p27=21,@p28=27,@p29=24,@p30=26,@p31=30,@p32=28

thanks

leppie

+4
source share
6 answers

It sounds like an error somewhere in Linq2Sql. I think that additional parameters should not be generated.

I think you should indicate a Microsoft Connect error.

+2
source

Very interesting - it seems he could not decide whether he can use the primary key or not ... what does the object model look like? No hand based inheritance or anything else?

0
source

Did you pass an enumerable collection instead of a scalar value for a parameter that converts to @ p1?

0
source

Is TagId tagged as your primary key in DBML constructor properties? To check, open the DBML file, click the TagId property of the Tag class and view the properties.

0
source

Please delete the dbml file from the project, restart the visual studio and restore the dbml file. Sometimes a visual studio caches old versions of your sql table or stored procedure schema. This can help linq handle sql correctly.

0
source

Have you ever thought about looking into the source code of the framework and debug the script yourself?

Since the source for the .NET Framework is finally available, it's pretty easy to achieve :)

Link: Setting up Visual Studio to debug .NET Framework source code

0
source

More articles:


All Articles