1

I'm using a database-first EF6 model with a few scalar-valued functions in it, and I'm having a problem with it not working the way I expected. The SQL function is defined as:

dbo.GetCurrentPayOffAmountFunction(@ApplicationID int, @PayOffDate DateTime)
Returns decimal(18,2)

In the .edmx, I'm using:

    <Function Name="GetCurrentPayOffAmountFunction" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" ReturnType="decimal">
      <Parameter Name="ApplicationID" Type="int" Mode="In" />
      <Parameter Name="PayOffDate" Type="datetime" Mode="In" />
    </Function>

In the .cs for the EF project:

    [DbFunction("PHXModel.Store", "GetCurrentPayOffAmountFunction")]
    public decimal GetCurrentPayOffAmountFunction(int ApplicationID, DateTime PayOffDate)
    {
        var objectContext = ((IObjectContextAdapter)this).ObjectContext;

        var parameters = new List<ObjectParameter>();
        parameters.Add(new ObjectParameter("ApplicationID", ApplicationID));
        parameters.Add(new ObjectParameter("PayOffDate", PayOffDate));

        return Convert.ToDecimal(objectContext.CreateQuery<string>("PHXModel.Store.GetCurrentPayOffAmountFunction(@ApplicationID, @PayOffDate)", parameters.ToArray())
             .Execute(MergeOption.NoTracking)
             .FirstOrDefault());
    }

Now, this all works fine when I call it as part of a select, as so:

decimal app = db.Applications.Take(1).Select (a => db.GetCurrentPayOffAmountFunction(1000, DateTime.Now)).FirstOrDefault();

("Applications" above can be any table in my database, and the function returns the correct value regardless of what I'm actually using to the left of the select).

However, when I try to run the function without a query before it, such as:

decimal payoffAmount = db.GetCurrentPayOffAmountFunction(1000, DateTime.Now);

I always get an error:

InvalidOperationException: The specified cast from a materialized 'System.Decimal' type to the 'System.String' type is not valid.

Now, I can use the first method to get my value and it works fine, but it feels wrong to me to get a random item just so I can call my function. Has anyone seen this? What am I doing wrong here?

Dan Santee
  • 31
  • 4
  • What happens if you do - `decimal payoffAmount = db.GetPayOffAmountFunction(1000, DateTime.Now).FirstOrDefault();` ? – displayName Oct 16 '15 at 02:54
  • Also I see that you are calling the wrong function. You are showing the code for Get**Current**PayOffAmountFunction(), while in the LINQ you are calling GetPayOffAmountFunction(). – displayName Oct 16 '15 at 03:02
  • Your problem could be similar to this: http://stackoverflow.com/questions/27008369/calling-db-function-with-entity-framework-6 – Frank Fajardo Oct 16 '15 at 03:32
  • We have both GetPayOffAmountFunction and GetCurrentPayOffAmountFunction, and they take exactly the same parameters and return the same type. I just copied into SO incorrectly and fixed the post. Thanks! – Dan Santee Oct 20 '15 at 15:12
  • FirstOrDefault() results in "'decimal' does not contain a definition for 'FirstOrDefault' and no extension method 'FirstOrDefault' accepting a first argument of type 'decimal' could be found". – Dan Santee Oct 20 '15 at 15:17
  • I went all through the other post (and the post it links to) but couldn't find anything which helped. I tried changing my function to be composable/not composable, changing the mappings, etc. As far as I can tell, it will only compile the way I have it above. – Dan Santee Oct 20 '15 at 19:13

0 Answers0