Ra3IDeN Ra3IDeN - 5 months ago 90
SQL Question

How to use EntityFramework CodeFirstStoreFunctions nuget package?

I am trying to access an already existing function that is in my db.
I want to execute this function from code first.

Searching the net i found:

https://codefirstfunctions.codeplex.com/

My implementation of this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<TrainMessage>()
.Property(e => e.Latitude)
.HasPrecision(18, 16);

modelBuilder.Entity<TrainMessage>()
.Property(e => e.Longitude)
.HasPrecision(18, 16);

modelBuilder.Entity<Stop>()
.Property(e => e.stop_lat)
.HasPrecision(18, 16);

modelBuilder.Entity<Stop>()
.Property(e => e.stop_lon)
.HasPrecision(18, 16);

modelBuilder.Entity<Order>()
.Property(e => e.Latitude)
.HasPrecision(18, 16);

modelBuilder.Entity<Order>()
.Property(e => e.Longitude)
.HasPrecision(18, 16);

modelBuilder.Entity<Subscription>()
.Property(e => e.StartTime)
.HasPrecision(0);

modelBuilder.Entity<Subscription>()
.Property(e => e.EndTime)
.HasPrecision(0);

modelBuilder.Entity<OrderLocation>()
.Property(e => e.Latitude)
.HasPrecision(18, 16);

modelBuilder.Entity<OrderLocation>()
.Property(e => e.Longitude)
.HasPrecision(18, 16);

modelBuilder.Entity<Order>()
.HasMany(e => e.OrderLocations)
.WithOptional(e => e.Order)
.HasForeignKey(e => e.Order_Id);

modelBuilder.Conventions.Add(new FunctionsConvention<TransitContext>("dbo"));
}

[DbFunction("TransitContext", "tvf_GetAllStopsInBetween")]
public IQueryable<int> GetAllStopsBetweenStations(int StopA_id, int StopB_id)
{
var A = new ObjectParameter("StopA_id", StopA_id);
var B = new ObjectParameter("StopB_id", StopB_id);

return ((IObjectContextAdapter)this).ObjectContext
.CreateQuery<int>("[dbo.tvf_GetAllStopsInBetween](@StopA_id, StopB_id)", A, B);


}


It is not exactly what they show in the guide but I have a function in the db and they create it in the code so I figure I do not need the rest of the code.

I have two problems with this

1) No matter what data I am trying to get from the db I get:


The argument 'name' cannot be null, empty or contain only white space.


2)
When I remove this part of the code:

modelBuilder.Conventions.Add(new FunctionsConvention<TransitContext>("dbo"));


I get this:


ErrorDescription = "'dbo.tvf_GetAllStopsInBetween' cannot be resolved
into a valid type or function."

Message = "'dbo.tvf_GetAllStopsInBetween' cannot be resolved into a
valid type or function. Near escaped identifier, line 1, column 1."


I am fairly new to codefirst and I inherited this code.

How do i solve this?

Answer

created a SP instead of a TVF and used the following code and problem was solved:

public ObjectResult<int> GetAllStopsBetweenStations(int StopA_id, int StopB_id)
{
    var A = new SqlParameter("StopA_id", System.Data.SqlDbType.Int);
    var B = new SqlParameter("StopB_id", System.Data.SqlDbType.Int);

    A.Value = StopA_id;
    B.Value = StopB_id;

    return ((IObjectContextAdapter)this).ObjectContext.
            ExecuteStoreQuery<int>("GetAllStopsInBetweenTest @StopA_id, @StopB_id ", A,B);
}