Using a database function with entity framework

Question

I'm working on a new website that's using entity framework.

Using code from another site, that was linq to sql, I have a database function to calculate distance from one set of latitude and longitude to another set. And the function returns a decimal with the distance.

I've added to my identitymodel.cs in the ApplicationDbContext class:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
 public ObjectResult<int> getDistance(string lat1, string lon1, string lat2, string lon2)
    {
        var params1 = new[] {
            new ObjectParameter("lat1", lat1),
            new ObjectParameter("lon1", lon1),
            new ObjectParameter("lat2", lat2),
            new ObjectParameter("lon2", lon2)
        };                

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("LatLonRadiusDistance", params1);
    }
}

And this is my query:

var datap = (from p in db.table
                     where db.LatLonRadiusDistance(data.lat, data.lon, p.platitude, p.plongitude) <= 10
                     select new latlon
                     {
                         lat = p.property_lat,
                         lon = p.property_lon,
                         address = p.property_address,
                         id = p.property_id,
                         count = ecount
                     }).Take(500);

However now I'm getting an error:

The FunctionImport 'LatLonRadiusDistanceVarchar' could not be found in the container 'ApplicationDbContext'.

What am I doing wrong?

Can you show us your context and how you are adding this function? — DavidG
– DavidG, Commented Apr 30, 2015 at 13:21

BSG · Accepted Answer · 2015-04-30 13:30:44Z

1

If you are using EF 4.1 and above, change "ObjectParameter" to "SqlParameter" and "ExecuteFunction" to "ExecuteStoreQuery" in your Context.cs file.

The "ExecuteStoreQuery" method also expects you to add the parameter names in-front of the stored proc. Find example below.

    var param1Parameter = param1 != null ?
    new SqlParameter("param1", param1) :
    new SqlParameter("param1", typeof(string));

    var param2Parameter = param2 != null ?
    new SqlParameter("param2", param2) :
    new SqlParameter("param2", typeof(int));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<sp_TestSproc_Result>("sp_TestSproc @param1, @param2", param1Parameter, param2Parameter);

answered Apr 30, 2015 at 13:30

BSG

1,96815 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prescient Over a year ago

Your answer got me going in the right direction. however now I have some other errors. First error I solved by using MultipleActiveResultSets=true in my connection string. however now I have "The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types."

Collectives™ on Stack Overflow

Using a database function with entity framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related