Dynamic LINQ API - SQL Convert Function

Asked
Viewd1817

0

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 个答案

1

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
  • Also, the default response seems to be “convert to use nvarchar instead of text”, but I’m assuming that isn’t an option for you.

    Ryan VersawOctober 15, 2009 16:14
  • I’ll have to give that a whirl tonight when I get home. Thanks so much for your help thus far Ryan.

    OverhedOctober 15, 2009 17:42
  • I get a similar error message saying that NVarChar to Text is not comparable with the equals operator.

    OverhedOctober 15, 2009 14:54
  • Yeah converting the datatype is not an option, unfortunately. :\

    OverhedOctober 15, 2009 16:19
  • It doesn’t seem to work. The resulting query is the same as before:

    SELECT [t0].[OrderNum], [t0].[OrderLine] FROM [PcInValue] AS [t0] WHERE ([t0].[InputName] = ‘OpenWidthFt’) AND ([t0].[InputValue] = ‘10’)

    OverhedOctober 15, 2009 12:29
  • A few more quick thoughts - Does this work without using dynamic linq (keeping the .ToString())? Both ways seemed to work fine for me (generating that same CONVERT sql above). Are you using .NET 3.5 SP1? I am, but I wouldn’t think that would matter, unless this was an SP1 fix. I’m running out of ideas :(

    Ryan VersawOctober 15, 2009 16:13
  • I just checked: The InputValue field is of type Text and nullable. This is on SQL Server 2005.

    OverhedOctober 15, 2009 15:51
  • Odd. Just to make sure, when you leave out the .ToString(), what error message do you receive? I get “SQL Server does not handle comparison of NText, Text, Xml, or Image data types.” This is when hitting a SQL Server 2005 (Express) instance.

    Ryan VersawOctober 15, 2009 14:27
  • Maybe we’re using different Dynamic Linq libraries? Can you link me to whichever one you’re using? I got mine from the link over at Scott Gu’s blog: http://msdn.microsoft.com/en-us/vcsharp/bb894665.aspx (C-Sharp Samples).

    OverhedOctober 15, 2009 16:18
  • I’m guessing this is the exact error you’re getting: “The data types text and nvarchar are incompatible in the equal to operator.” I can reproduce that when InputValue’s Server Data Type (property in dbml) is set to NVarChar(255) NOT NULL instead of Text, as it should be. Could you check and confirm that it’s still set to Text?

    Ryan VersawOctober 15, 2009 15:25
  • That should be the same one I have. Did you try a query without using dynamic linq? That suggestion was to help remove the dynamic linq factor from the problem.

    Ryan VersawOctober 15, 2009 16:25