EF: SQL Function as property

Question

Is it possible to add scalar value SQL Functions as properties in EF?

I have some functions which sum different values in an OrderLine table that take an OrderId (ie, SUM of OrderLineCost).

I would like to be able to access these via the Order data model.

Is there a way of adding these functions as properties on the Order data model so I can just access Order.OrderLineCostTotal instead of doing separate calls to the database?

Thanks

Answer 1

Yes. You should be able to accomplish that. With EF 6.1 it is easier to build that kind of stuff in both code-first and database-first.

There is a codeplex project called Code first store functions to simplify working with data-store functions from your data-context. Here is the nuget package description:

Support for store functions (table valued functions, scalar user defined functions and stored procedures) for Entity Framework 6.1.1+ Code First.

https://codefirstfunctions.codeplex.com/

Here is an example from the link above of a context method to get customers by zip code by invoking a SQL function. You can easily convert it to a readonly property:

[DbFunction("MyContext", "CustomersByZipCode")]
public IQueryable<Customer> CustomersByZipCode(string zipCode)
{
    var zipCodeParameter = zipCode != null ?
        new ObjectParameter("ZipCode", zipCode) :
        new ObjectParameter("ZipCode", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext
        .CreateQuery<Customer>(
            string.Format("[{0}].{1}", GetType().Name, 
                "[CustomersByZipCode](@ZipCode)"), zipCodeParameter);
}

If you are using EF 6 database-first, you can use the function import to get the functions into your model. However, the wizard is pretty buggy (even in the newest Visual Studio 2013), so there is a common approach to fix the imported stuff by hand in the EDMX xml. You can find instructions here:

http://logiczone.me/entity-framework-and-sql-db-scalar-functions