What to do about @p LINQtoSQL parameters? What is @p anyhow?

Question

Okay still fighting with doing some SqlCacheDependecy in my Asp.net MVC application

I got this piece of code from Microsoft to cache LINQtoSQL, basically what it does is it gets the SqlCommand text from the LINQ query and executes that via the System.Data.SqlClient.SqlCommand which SqlDependecy needs...

However there is one slight problem with this and that is whenever you do a where clause in LINQ the SQL generated is like so

SELECT [t0].[MemberID], [t0].[Aspnetusername], [t0].[Aspnetpassword], [t0].[EmailAddr], [t0].[DateCreated], [t0].[Location], [t0].[DaimokuGoal], [t0].[PreviewImageID], [t0].[LastDaimoku] AS [LastDaimoku], [t0].[LastNotefied] AS [LastNotefied], [t0].[LastActivityDate] AS [LastActivityDate], [t0].[IsActivated]
FROM [dbo].[Members] AS [t0]
INNER JOIN [dbo].[MemberStats] AS [t1] ON [t0].[MemberID] = [t1].[MemberID]
WHERE [t1].[TotalDeterminations] > @p0

Notice the where [t1].[TotalDeterminations] > @p0, the SqlCommand yells at me because it wants me to declare a scalar variable of @p0... which obviously I can't

So how the heck does Microsoft which provides this code to cache Linq queries expect people to use where clauses? Anyone have any ideas around this?

Edit Plus how the heck does SQL know what @p is anyhow when just executing the LINQ like normal the above query is whats getting passed in no matter what to the database?

Answer 1

Why can't you just add SqlParameter objects to the Parameters collection of SqlCommand object before adding them to the SqlDependecy?

Answer 2

@something is a SQL Parameter, which .NET handles by adding to the SqlParameter collection of the SqlCommand object.

You can create parameters with any name, Linq to SQL just generates them named p#, where # represents its position on the collection.

Answer 3

This answer may help you. It still keeps the linq query.

http://stackoverflow.com/questions/238978/should-i-still-see-the-query-hit-in-sql-profiler#239074

Answer 4

Hi there... If not already solved... This is how I did it I while ago... code.msdn.com

Regards Uffe