I need to convert String to Int32 value in my query. It must be done on sql server-side, not in memory.
I've seen answers how to do this in Model First approach, LINQ to Entities.
here and here
But I need it to be done with Code First.
I write query to DbSet<>.
Is there any way to do this? Please, help :)
New Answer: The only way I can find information for is to use a custom query. For instance:
from user in Users.SqlQuery("SELECT Id, CAST(Age AS INT) as Age, FirstName, LastName FROM Users")
select new
{
id = user.Id,
Age = user.Age
}
In my tests it seems every value for property on the entity you map to have to be included in the select even if you select to a custom object that do not include every property. In my example above I include FirstName and LastName even though they aren't used in the select.
Old answer: About converting to string on sql-side: You can use SqlFunctions.StringConvert if you cast your int to a double or decimal first Problem with converting int to string in Linq to entities
from user in Users
select new
{
IdAsText = SqlClient.SqlFunctions.StringConvert((decimal)user.Id)
}
The cast to float or decimal is necessary because the STR-function on the sql-server requires a float: Why is there no int overload for SqlFunctions.StringConvert
Update:
In LINQPad the generated SQL-query from the above comes out to:
SELECT
[Extent1].[Id] AS [Id],
STR( CAST( [Extent1].[Id] AS decimal(19,0))) AS [C1]
FROM [dbo].[Users] AS [Extent1]
