Dynamic LINQ API - SQL Convert Function
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 lik开发者_StackOverflow中文版e 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')
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
精彩评论