SQL Convert Function in the Dynamic LINQ API

c# dynamic-linq linq linq-to-sql

Question

I'm trying to use a Dynamic LINQ Query to query a SQL database, and in the Where clause I need to evaluate an '=' condition with a field that is of type TEXT.

Right now, I've got this:

var result = DBCon.PcInValue
   .Where(String.Format("InputName = @0 and InputValue) {0} @1", f.Condition), f.Field, f.Value)
   .Select("new(OrderNum, OrderLine)");

This doesn't work since you can't use the equal operator on a TEXT data type. The field that is type TEXT is "InputValue". I tried to convert it like so:

var result = DBCon.PcInValue
   .Where(String.Format("InputName = @0 and Convert(nvarchar(100), InputValue) {0} @1", f.Condition), f.Field, f.Value)
   .Select("new(OrderNum, OrderLine)");

But it looks like this is not supported.

Anyone have any clues as to how I can do this?

EDIT: The following SQL Syntax works with no issues, but again I'm not sure if this is possible using the Dynamic LINQ API:

SELECT [t0].[OrderNum], [t0].[OrderLine]
FROM [PcInValue] AS [t0]
WHERE ([t0].[InputName] = 'OpenWidthFt')  AND (Convert(nvarchar(100), [t0].[InputValue]) = '10')
1
0
10/14/2009 7:21:51 PM

Popular Answer

I've tested this and it seems to work fine (though it's a bit odd):

var result = DBCon.PcInValue
   .Where(String.Format("InputName = @0 and InputValue.ToString() {0} @1", f.Condition), f.Field, f.Value)
   .Select("new(OrderNum, OrderLine)");

LinqPad tells me it's translated into something similar to the following (using my own table):

SELECT [t0].[Id], [t0].[Name], [t0].[InputValue]
FROM [People] AS [t0]
WHERE (CONVERT(NVarChar(MAX),[t0].[InputValue])) = @p0
1
10/14/2009 10:09:41 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow