CODEWITHSUNDEEP
entity-frameworktype-conversioncode-first
Igor
Igor

Reputation: 131

EF Code First - convert string to int in query

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 :)

Upvotes: 2

Views: 2486

Answers (1)

Fredrik Ljung
Fredrik Ljung

Reputation: 1475

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]

Upvotes: 1

Related Questions