LINQ to SQL - String.IndexOf() does not work with parameters of length 1
-
Hi, Can anyone explain why LINQ to SQL translates the String.IndexOf(string value) method into this SQL:
CASE
WHEN (DATALENGTH(@p0) / 2) = 0 THEN 0
ELSE CHARINDEX(@p0, [t0].[Name]) - 1
ENDThe WHEN statment restricts parameters to be a minimum length of 2. This prevents me from passing in a string with a length of 1 to the String.IndexOf() method. Is this due to some sort of SQL limitation? Is there away around this? I am running .Net 3.5 with SP1, Visual Studio 2008 with SP1, and SQL Server 2005. Thanks for any help, kp
-
Hi, Can anyone explain why LINQ to SQL translates the String.IndexOf(string value) method into this SQL:
CASE
WHEN (DATALENGTH(@p0) / 2) = 0 THEN 0
ELSE CHARINDEX(@p0, [t0].[Name]) - 1
ENDThe WHEN statment restricts parameters to be a minimum length of 2. This prevents me from passing in a string with a length of 1 to the String.IndexOf() method. Is this due to some sort of SQL limitation? Is there away around this? I am running .Net 3.5 with SP1, Visual Studio 2008 with SP1, and SQL Server 2005. Thanks for any help, kp
Actually, its not a minimum length of 2, its a minimum length of 0. DATALENGTH() returns the byte length, not the character length. L2S usually passes strings in as unicode, so characters are 2 bytes in length. The WHEN check is actually checking for a string length of 0 because any byte length less than 2, in integer divide, will result in 0. Since nvarchar results in lengths that are always multiples of 2, if the string length is 0, then the string your looking for can not be found. CHARINDEX returns 0 when the expression seeked is not found, and 1 or greater if it is found, so this case statement should work for any string length...0 up to nvarchar(max).
-
Actually, its not a minimum length of 2, its a minimum length of 0. DATALENGTH() returns the byte length, not the character length. L2S usually passes strings in as unicode, so characters are 2 bytes in length. The WHEN check is actually checking for a string length of 0 because any byte length less than 2, in integer divide, will result in 0. Since nvarchar results in lengths that are always multiples of 2, if the string length is 0, then the string your looking for can not be found. CHARINDEX returns 0 when the expression seeked is not found, and 1 or greater if it is found, so this case statement should work for any string length...0 up to nvarchar(max).
Okay, that makes sense... and what stuffed me up more is that I was testing it in SQL with 'i' instead of N'i' (using ascii instead of unicode). Well, thanks very much for clearing that up :)