Entity Framework/Linq EXpression converting from string to int

by

As much as I hate this answer, the actual answer is you can’t do it easily. It will be a real pain.
I’ve seen lots of wrong answers and lots of answers with people saying you should just have your database fields be the correct type in the first place, which is not helpful.

Your question is similar to this question on MSDN.

There are several possibilities depending on what sort of EF you are using.

1. You are using EDMX files.

(Not code first or reverse engineered code first).

You can use something like this (from this answer):

[EdmFunction("PlusDomain", "ParseDouble")]
public static double ParseDouble(string stringvalue)
{
    // This method exists for use in LINQ queries,
    // as a stub that will be converted to a SQL CAST statement.
    return System.Double.Parse(stringvalue);
}

and map it in your EDMX like this:

<Function Name="ParseDouble" ReturnType="Edm.Double">
    <Parameter Name="stringvalue" Type="Edm.String" />
    <DefiningExpression>
        cast(stringvalue as Edm.Double)
    </DefiningExpression>
</Function>

2. If you are using code first in EF >= 4.1.

You are screwed. Microsoft didn’t see fit to add any such function to SqlFunctions. The best you can hope for is to add a scalar SQL function into your database and (maybe) try to map it into your context. Microsoft didn’t see any point in doing anything like this in code first. Fortunately they didn’t totally block such things either. The Fluent API is powerful.

You can call the functions or stored procedures like this (reference):

var outParam = new SqlParameter("overHours", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;

Or like this (reference):

var data = context.Database.ExecuteSqlCommand("dbo.sp_getNumberJobs @overHours OUT", outParam);
int numJobs = (int)outParam.Value;

But to make them actually integrate into LINQ to Entities, you need something like CodeFirstFunctions, by using the EntityFramework.CodeFirstStoreFunctions NuGet package. It maps the SQL functions into the context, but it uses an external library only made for .NET 4.5 (see here).

Instead, you can attempt to do the same thing manually like in this question.

The quicker solution I’ve settled on for my needs is to just create a view with the converted types. This avoids the whole problem.

More Related Contents:

  1. Linq: help me to make this work
  2. How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?
  3. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  4. Filter/Search using Multiple Fields – ASP.NET MVC
  5. LEFT JOIN in LINQ to entities?
  6. The result of a query cannot be enumerated more than once
  7. How to convert an expression tree to a partial SQL query?
  8. Internal .NET Framework Data Provider error 1025
  9. Join and Include in Entity Framework
  10. Creating dynamic queries with entity framework
  11. Entity Framework Code Only error: the model backing the context has changed since the database was created
  12. Entity Framework 4: How to find the primary key?
  13. Max return value if empty query
  14. Remove item from list based on condition
  15. How to do an “in” query in entity framework?
  16. Change db table name in EF4 (entity framework 4)
  17. Keep Getting ‘The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities’ Exception
  18. Entity Framework Multiple Object Contexts
  19. Linq Query keeps throwing “Unable to create a constant value of type System.Object….”, Why?
  20. How to include() nested child entity in linq
  21. Should the order of LINQ query clauses affect Entity Framework performance?
  22. Repository Pattern, POCO, and Business Entities
  23. LINQ Query to Convert string to datetime
  24. How to get the index of an element in an IEnumerable?
  25. Value cannot be null. Parameter name: source
  26. Entity Framework – Add Navigation Property Manually
  27. EF Data Context – Async/Await & Multithreading
  28. Cannot implicitly convert type ‘System.Collections.Generic.IEnumerable’ to ‘System.Collections.Generic.List
  29. Get sum of two columns in one LINQ query
  30. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations

Leave a Comment