SQLClient String Function NVARCHAR instead of STR

Question

I'm am trying to use SQLClient for Entity Framework functions, but I'm having some issues. I need to convert ID (which in the database is a an int) to a nvarchar so that I can compare it using wildcards.

This works to an extent (i.e. it builds and executes without erroring), however it's not what I need to do as db side, it returns the wrong results. In fact, it returns 0 rows, when it should be returning a number.

return "SqlServer.STR(ID) LIKE '824%'";

The above line basically translates to the below line in SQL

SELECT COUNT(*) FROM Table1 WHERE STR(ID) LIKE '824%'

What I need in SQL is the following line (or something similar) because this returns the correct number of rows.

SELECT COUNT(*) FROM Table1 WHERE CONVERT(NVARCHAR(50), ID) LIKE '824%'

I've tried using:

return "CAST(ID AS NVARCHAR(50)) LIKE '824%'";

But this gives the following error at runtime:

Type 'NVARCHAR' could not be found. Make sure that the required schemas are loaded and that the namespaces are imported correctly.

Can anyone tell me how to do this using SqlClient string functions, or some other variant?

Thanks all.

Yes, this does appear to work, many thanks. Can I ask why exactly it works like that? Thanks again. If you add this to the answers, I will mark it as answered. — Mattplus
– Mattplus, Commented Oct 9, 2013 at 18:31
Maybe you should store the data in a way that makes complex calculations on it unnecessary. If you insist, I have provided an answer. — usr
– usr, Commented Oct 9, 2013 at 19:22

Alexandre Rondeau · Accepted Answer · 2013-10-09 19:51:47Z

2

You can using the SqlFunction.StringConvert() function. There is no overload for int, so you have to type cast

var test = dataContext.Table1
                      .Where(f => SqlFunctions.StringConvert((double) f.Id)
                                               .Trim()
                                               .StartsWith("824"))
                      .Select(f => SqlFunctions.StringConvert((double) f.Id)
                                               .Trim())
                      .ToList();

This will be converted as

SELECT 
LTRIM(RTRIM(STR( CAST( [Extent1].[Id] AS float)))) AS [C1]
FROM [dbo].[Table1] AS [Extent1]
WHERE (LTRIM(RTRIM(STR( CAST( [Extent1].[Id] AS float)))) LIKE N'4%')

edited Oct 9, 2013 at 19:51

answered Oct 9, 2013 at 19:19

Alexandre Rondeau

2,68725 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

usr Over a year ago

I consider this to be a hack. The problem is the right-aligning, not that the search condition is wrong. I don't like to plaster fixed over bugs, I fix the root cause. Get rid of the right-aligning (which is crazy behavior).

Alexandre Rondeau Over a year ago

But using SqlFunctions.StringConvert((double)x.Id)) will not solve it because EntityFramwork is using STR to convert it.

usr Over a year ago

I fail to find any way clean to convert an int into a string with EF. This is very disappointing.

Alexandre Rondeau Over a year ago

actually .Trim() works. Thanks for your comment about it being a hack It forced me to dung further. I'll update my anwsers.

usr Over a year ago

I'm forced to upvote this disgusting hack. I'd probably investigate custom EF functions. There seems to be a way to reference T-SQL functionality and casts.

|

Collectives™ on Stack Overflow

SQLClient String Function NVARCHAR instead of STR

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related