LINQ to Entities string to double conversion

Question

I'm working for the first time with LINQ to Entities.

My data store has a string field that depending on certain other fields is often an integer. I cannot change the structure of this database.

The table in question in the EF is masterdatas.

I am attempting to create the sum for these records in the varvalue field based on a grouping based on the varname field as such:

var varsum = from md in context.masterdatas
             where md.imparcid == SelectedDataSet.imparcid && md.varname == t.VarName
             group md by md.varname into g
             select new { varname = g.Key, SummedAmt = g.Sum(md => Convert.ToDouble(md.varvalue)) };

I get an error LINQ to Entities does not recognize method Double ToDouble(system string).

Based on this SO question, I added a DefiningExpression to my EDMX model, and defined the function in a partial class of my datamodel and used this code:

var varsum = from md in context.masterdatas
             where md.imparcid == SelectedDataSet.imparcid && md.varname == t.VarName
             group md by md.varname into g
             select new { varname = g.Key, SummedAmt = g.Sum(md => context.ParseDouble(md.varvalue)) };

Now I'm getting an error that the ParseDouble function cannot be translated into a LINQ to entities store expression.

All I'm really trying to do is to get this SQL statement to run:

SELECT [varname]
      ,[vartype]
      ,Sum(CASE WHEN Isnumeric([varvalue])=1 THEN cast([varvalue] as float) Else 0 END)
  FROM [masterdata]
  Group by varname, imparcid, vartype
  Having imparcid=25 AND varname = 'S2CMT8B'

I'm at a loss about the best way to get this done.

Slauma · Answer 1 · 2012-09-10T16:49:00.130

On the last line of your LINQ query you need to call the static ParseDouble method on your context class, not the context instance:

// ...
select new { varname = g.Key, SummedAmt = g.Sum(md =>
    MyContextClass.ParseDouble(md.varvalue)) };
//  ^^^^^^^^^^^^^^
//  NOT the context instance, just the class name because ParseDouble is static

I believe only in that case the EdmFunction attribute is handled by EF, otherwise it tries to call the method like an ordinary .NET method which throws the exception.

Edit

It's also important that you specify the correct model namespace in the EdmFunction attribute. If your EDMX looks like this...

<edmx:ConceptualModels>
  <Schema Namespace="MyDBModel" Alias="Self"
      xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
      xmlns="http://schemas.microsoft.com/ado/2008/09/edm">

    <!-- etc. -->

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

  </Schema>
</edmx:ConceptualModels>

... your must specify the namespace of the schema MyDBModel as first parameter of the EdmFunctionAttribute:

[EdmFunction("MyDBModel", "ParseDouble")]
public static double ParseDouble(string stringvalue)
{
    // ...
}

Otherwise ParseDouble will be treated as a normal .NET method and result in the exception that it can't be translated into a store expression.

Thanks Slauma, but after making this change, I'm still getting the error "The specified method 'Double ParseDouble(System.String) on the type 'IFR_Full.IFREntities' (i.e MyContextClass) cannot be translated into a LINQ to Entities store expression when I step through the code and the linq to entities statement executes. — DeeDub84, Sep 10 '12 at 16:26
@DeeDub84: Can you check my Edit section above? Having the wrong namespace specified leads to exactly your exception. — Slauma, Sep 10 '12 at 16:49

LINQ to Entities string to double conversion

1 Answers1