Code first Store Functions in a Property

Question

I am having trouble trying to map a string column in a database to an integer in my application.

I have a database with a collection of vehicles, when the table was created, the year column was specified as a varchar. I need the value as an integer in order to perform calculations with it.

I know I can use int.Parse to convert it, but I need it to work in an entity framework query. int.Parse is not supported in linq to entities, so that method is out.

The next thing i tried was creating a view and casting the column to an int. This worked perfectly, until I needed to write to the database, then an error is thrown because the view contains a derived field.

What I tried next was using The "EntityFramework.CodeFirstStoreFunctions" library from Pawel Kadluczka. This does work, but only when I specify it directly like this:

Store.Vehicles.Where(x => CustomSqlFunctions.ParseInt(x.ModelYear) == 2000)

Where ParseInt is defined in c# as

[CodeFirstDbFunction("ParseInt")]
public static int ParseInt(string number) {
    throw new NotSupportedException("Direct calls are not supported.");
}

The ParseInt function in sql simply calls cast( @p1 as int) where @p1 is the passed in string.

My question is:
Is it possible to call CustomSqlFunctions.ParseInt(ModelYear) from within a property in the Vehicle class?.

I have tried this:

public int? Year {
    get { return CustomSqlFunctions.ParseInt(ModelYear); }
    set { ModelYear = value.ToString(); }
}

but I get the error that Year is not supported in LINQ to Entities

Answer 1

I eventually figured it out. It was a rather simple solution. I went back to the custom view with the calculated column. This time however, I kept the original field that was called 'ModelYear' and added the column 'Year' that would be a cast from string to int.

The year property eventually looked like this:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public int? Year {
    get { return _year; }
    set {
        _year = value;
        ModelYear = value.ToString();
    }
}

When a value is set to Year it is also set to ModelYear as a string. When the model is saved to the database the ModelYear field is updated, and because the Year field is marked as computed, the framework doesn't try to save the field as well.

As a side note Year was actually not being called to begin with. After stepping through the source code for Entity framework I found that before an expression is evaluated it first checks to see if the called property is actually a mapped member in the database. Since Year was not mapped, the expression that contained Year was not even being evaluated.

Answer 2

I assume that Year is not mapped to the database. In that case, you do the equivalent "cast" as such:

get
{
    int value;
    return int.TryParse(ModelYear, out value) ? value : null;
}