Convert String to Int in EF 4.0

Question

Is there any way of doing this at all? I have a string field in the DB and I want to parse it into a int property in my LINQ query (yes, it must be at the IQueryable level, not in memory).

I know 2 years ago EF 1.0 couldn't do this (even though LINQ to SQL supported this basic functionality out of the box)...but I'm just wondering if anyone has come up with a way of doing this at this point?

Custom function mappings? Special syntax? Anything at all....

UPDATE:

I tried a model defined function as follows:

    <Function Name="ConvertToInt32" ReturnType="Edm.Int32">
      <Parameter Name="v" Type="Edm.String" />
      <DefiningExpression>
        CAST(v AS INT)
      </DefiningExpression>
    </Function>

    [EdmFunction("Model.Repository", "ConvertToInt32")]
    public static int ConvertToInt32(string value)
    {
        throw new InvalidOperationException("Only valid when used as part of a LINQ query.");
    }

but it doesn't seem to work. I get the runtime exception:

        ErrorDescription=Type 'INT' could not be found. Make sure that the required schemas are loaded and that the namespaces are imported correctly.
        StackTrace:
             at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertTypeName(Node typeName, SemanticResolver sr)
             at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertTypeExprArgs(BuiltInExpr astBuiltInExpr, SemanticResolver sr)
             at System.Data.Common.EntitySql.SemanticAnalyzer.<CreateBuiltInExprConverter>b__73(BuiltInExpr bltInExpr, SemanticResolver sr)
             at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertBuiltIn(Node astExpr, SemanticResolver sr)
             at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr)
             at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpression(Node astExpr, SemanticResolver sr)
             at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryStatementToDbExpression(Statement astStatement, SemanticResolver sr)
             at System.Data.Common.EntitySql.SemanticAnalyzer.AnalyzeQueryCommand(Node astExpr)
             at System.Data.Common.EntitySql.CqlQuery.<AnalyzeQueryExpressionSemantics>b__8(SemanticAnalyzer analyzer, Node astExpr)
             at System.Data.Common.EntitySql.CqlQuery.AnalyzeSemanticsCommon[TResult](Node astExpr, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables, Func`3 analysisFunction)
             at System.Data.Common.EntitySql.CqlQuery.AnalyzeQueryExpressionSemantics(Node astQueryCommand, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables)
             at System.Data.Common.EntitySql.CqlQuery.<>c__DisplayClass4.<CompileQueryCommandLambda>b__3(Node astCommand, ParserOptions validatedParserOptions)
             at System.Data.Common.EntitySql.CqlQuery.CompileCommon[TResult](String commandText, Perspective perspective, ParserOptions parserOptions, Func`3 compilationFunction)
             at System.Data.Common.EntitySql.CqlQuery.CompileQueryCommandLambda(String queryCommandText, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables)
             at System.Data.Mapping.ViewGeneration.Utils.ExternalCalls.CompileFunctionDefinition(String functionFullName, String functionDefinition, IList`1 functionParameters, EdmItemCollection edmItemCollection)
             at System.Data.Metadata.Edm.EdmItemCollection.GenerateFunctionDefinition(EdmFunction function)
             at System.Data.Common.Utils.Memoizer`2.<>c__DisplayClass2.<Evaluate>b__0()
             at System.Data.Common.Utils.Memoizer`2.Result.GetValue()
             at System.Data.Common.Utils.Memoizer`2.Evaluate(TArg arg)
             at System.Data.Metadata.Edm.EdmItemCollection.GetGeneratedFunctionDefinition(EdmFunction function)
             at System.Data.Metadata.Edm.MetadataWorkspace.GetGeneratedFunctionDefinition(EdmFunction function)
             at System.Data.Query.PlanCompiler.ITreeGenerator.Visit(DbFunctionExpression e)
        InnerException:

UPDATE: I got it to work as follows

 <Function Name="ConvertToInt32" ReturnType="Edm.Int32">
      <Parameter Name="v" Type="Edm.String" />
      <DefiningExpression>
        CAST(v AS Edm.Int32)
      </DefiningExpression>
    </Function>

said function must me added in the ConceptualModels session – pistacchio Jun 07 '11 at 06:45 — pistacchio, Jun 07 '11 at 06:45
What happens if the string is a non-numeric value? – FMFF Jan 19 '12 at 18:22 — FMFF, Jan 19 '12 at 18:22
could you pls help me how to create model defined function? – ZarNi Myo Sett Win Apr 26 '19 at 11:04 — ZarNi Myo Sett Win, Apr 26 '19 at 11:04

score 14 · Accepted Answer · edited May 23 '17 at 11:45

14

If you are using EFv4 + EDMX you can create custom model defined function which will do the CAST for you. You can then use that function in Linq-to-entities queries.

edited May 23 '17 at 11:45

Community

1
1

answered Apr 22 '11 at 10:41

Ladislav Mrnka

360,892
59
660
670

1

Any way to add this to the MetadataWorkspace at runtime? Ideally I'd like the Convert.ToInt32 to perform this function. Thanks! – Jeff Apr 22 '11 at 16:00
I tried this and it doesn't seem to work: see updated question. – Jeff Apr 22 '11 at 16:26
The question about adding this to metadata workspace at runtime is very good but I don't know the answer for that. Anyway you will not be able to map it to `Convert.ToInt32` because you can't add `EdmFunction` attribute to that method. – Ladislav Mrnka Apr 22 '11 at 16:46

Convert String to Int in EF 4.0

1 Answers1

Linked

Related