linq isn't putting single quotes around string value

Question

I am getting a query from linq that isn't surrounding the string value in this call with single quotes.  Anyone know what would cause this?  I've verified that boht the parameter and the property AffiliateSiteId are both strings so I'm not sure what else to check.  Thanks.

 

public ModuleViewConfiguration GetModuleConfigByModuleId(int ModuleId, string AffiliateSiteId)

{

var mod = (from module in tdc.vwModuleLookupByGUIDs where module.ModuleId == ModuleId && module.AffiliateSiteId == AffiliateSiteId.ToString() select module);

....

}

Answer 1

What is the db type of the fields? Can you include the DDL to create the table?

 

What flavor of LINQ are you referring to, Linq-to-SQL, Linq-to-entities, linq-to-nhibernate, linq-to..?

 

...and can you include the generated SQL query..?

Answer 2

It's Linq to SQL.  The AffiliateSiteId type is system.string. The generated sql query is below.

 

SELECT [t0].[HeaderBtmGradientBtm], [t0].[HeaderBtmGradientTop], [t0].[HeaderTopGradientBtm], [t0].[HeaderTopGradientTop], [t0].[SeekBar], [t0].[GUID], [t0].[AbsoluteURL], [t0].[Title], [t0].[Width], [t0].[Height], [t0].[VideoFormat], [t0].[AffiliateSiteId], [t0].[FileType], [t0].[SystemPath], [t0].[SupportDocTitle], [t0].[TrainerName], [t0].[ModuleTypeId], [t0].[ModuleId], [t0].[ChapterTime], [t0].[ChapterTitle], [t0].[Picture], [t0].[Credentials], [t0].[Bio], [t0].[Experience], [t0].[PublishedWorks]
FROM [dbo].[vwModuleLookupByGUID] AS [t0]
WHERE ([t0].[ModuleId] = 167) AND ([t0].[AffiliateSiteId] = ACM01)

 

It should be

 

SELECT [t0].[HeaderBtmGradientBtm], [t0].[HeaderBtmGradientTop], [t0].[HeaderTopGradientBtm], [t0].[HeaderTopGradientTop], [t0].[SeekBar], [t0].[GUID], [t0].[AbsoluteURL], [t0].[Title], [t0].[Width], [t0].[Height], [t0].[VideoFormat], [t0].[AffiliateSiteId], [t0].[FileType], [t0].[SystemPath], [t0].[SupportDocTitle], [t0].[TrainerName], [t0].[ModuleTypeId], [t0].[ModuleId], [t0].[ChapterTime], [t0].[ChapterTitle], [t0].[Picture], [t0].[Credentials], [t0].[Bio], [t0].[Experience], [t0].[PublishedWorks]
FROM [dbo].[vwModuleLookupByGUID] AS [t0]
WHERE ([t0].[ModuleId] = 167) AND ([t0].[AffiliateSiteId] = 'ACM01')

 

From the Affiliate table here's the definition for the affiliatesiteid field.

 

[AffiliateSiteId] [char](10) NOT NULL CONSTRAINT [DF_Affiliate_AffiliateSiteId] DEFAULT (''),

Answer 3

Very strange. L2S would normally parameterize the query. How did you capture the query (SQL profiler?). Are you running (or have you previously had) a beta or ctp version of L2S on your machine?

 

Answer 4

I don't recall what I installed a while back but it was something that allowed me to view the linq to sql generated query by hovering over it just like VS shows a properties value when you mouse over it.  A coworker does not have that installed and when I hover over that property on his box it is a parameterized query.  So, I'm not sure what to make of it.

Answer 5

I think the tool you are using is showing an incorrect representation of the parameterized SQL command.

 

Attach SQL Server Profiler to your db and I think you will see that it runs a parameterized command.

 

Answer 6

Okay this is weird.  My query is pulling it's results from a view that I brought into my dbml file.  I compared the affiliatesiteid field in the design view between my affiliate table and the view.  The only difference was that Update Check was set to always on the view and never on the table.  I changed it to never on the view and now it's working.  I changed it back to always on the view to see if it would break it and it still works.  I have no idea why that did it but at least it's working now.

Answer 7

 

Well...We have the same problem....

Changing the update check  to  'never' didn't solve the problem.

Does someone got a solution? It seems to be a bug.

 

Kind regards,

 

Brendo

Answer 8

I think the tool you are using is showing an incorrect representation of the parameterized SQL command.

 

Attach SQL Server Profiler to your db and I think you will see that it runs a parameterized command.

 

I think that might be the issue. See http://dailydevscoveries.blogspot.com/2009/04/linq-to-sql-debugger-visualizer-error.html