How can I cast a string to a decimal using a custo

Question

I have a table with decimals (and other types) saved as strings. I want to write a Linq query on the database context that will translate to a database cast and not a local cast (for performance reasons).

This non-working example is conceptually what I want to achieve.

using ( var context = new MyContext() )
{
    return context.SomeTable
        .Select(o => new { o.Id, (decimal)o.SomeString });
}

This is a bad way to achieve it since it will run the conversion on the application side.

using ( var context = new MyContext() )
{
    return context.SomeTable
        .Select(o => new { o.Id, o.SomeString })
        .ToList()
        .Select(o => new { o.Id, Convert.ToDecimal(o.SomeString) });
}

I believe the way to go is with DbFunctions but I cannot find a way to use it with Code First.

This is the partial answer, but I have not been able to find the documentation I required to finalize the part where I define what this function does on the SQL server.

[DbFunction("MyContext", "ConvertToDecimal")]
public static decimal ConvertToDecimal(string s)
{
    throw new Exception("Direct calls are not supported.");
}

.

using ( var context = new MyContext() )
{
    return context.SomeTable
        .Select(o => new { o.Id, ConvertToDecimal(o.SomeString) });
}

If I was using a Edmx driven alternative, this would be the missing part:

<Function Name="ConvertToDecimal" ReturnType="Edm.Decimal">
    <Parameter Name="s" Type="Edm.String" />
    <DefiningExpression>
        CAST(s AS decimal(22,6))
    </DefiningExpression>
</Function>

I am using Entity Framework 6 Code First.

Answer 1

I think you can not as per current version user defined functions are one of the elements that are not yet supported in code first.

Answer 2

I figured out the solution with some information coming from this topic.

Like I was assuming in the original question, I had the first 2 parts down. The last part is to register in the DbModel the functions you want accessible and how to use them. There is more then one way to do this but I used a Convention

public class MyFunctionsConvetion : IStoreModelConvention<EntityContainer>
{
    public void Apply(EntityContainer item, DbModel model)
    {
        //Get the Edm Model from the DbModel
        EdmModel storeModel = model.GetStoreModel();

        //Delare your parameters name, edm type and mode (You can ignore this if you use a parameter-less function)
        List<FunctionParameter> Parameters = new List<FunctionParameter>();
        Parameters.Add(FunctionParameter.Create("StringValue", GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In));

        //Same thing goes for the return type(s) (Why is it a list? Perhaps you can return tables? I haven't tested however since it is no use to me)
        List<FunctionParameter> ReturnParameters = new List<FunctionParameter>();
        ReturnParameters.Add(FunctionParameter.Create("ReturnValue", GetStorePrimitiveType(model, PrimitiveTypeKind.Decimal), ParameterMode.ReturnValue));

        //Create the payload and fill the required information alone with the parameter lists we declared
        EdmFunctionPayload payload = new EdmFunctionPayload();
        payload.IsComposable = true;
        payload.Schema = "dbo";
        payload.StoreFunctionName = "ConvertToDecimal";
        payload.ReturnParameters = ReturnParameters;
        payload.Parameters = Parameters;

        //Create the function with it's payload
        EdmFunction function = EdmFunction.Create("ConvertToDecimal", "MyContext", DataSpace.SSpace, payload, new MetadataProperty[] { });

        //Add it to the model
        storeModel.AddItem(function);
    }

    //Little helper method to get the primitive type based on the database provider
    private EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
    {
        return model
            .ProviderManifest
            .GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind)))
            .EdmType;
    }
}

Then we add the Convention to the model in the OnModelCreating method

modelBuilder.Conventions.Add<MyProject.MyConventions.MyFunctionsConvention>();

Note: The code could be cleaner and written in a DRY fashion, but for the sake of simplicity, I wanted to post it like this and let you organize it however you see fit.