Entity Framework execute stored procedure with params

Question

Ok, hope to not get too many flags, but it's to annoying.

I have a method in my controller which calls a method from another class:

offerForCreate.Rating = CalculateRating.CreateRating(addOffer);

and entire called class :

public class CalculateRating
{
    private readonly DataContext mainContext;

    public CalculateRating(DataContext mainContext)
    {
        this.mainContext = mainContext;
    }

    // calcul rating oferte noi
    public decimal CreateRating(OfferForCreate offer)
    {
        decimal rating = mainContext.Database.FromSql<decimal>("RatingCalculator", offer.locationId, offer.typeId);

        return rating;
    }
}

I get an error when try to execute this procedure:

Error CS1061: 'DatabaseFacade' does not contain a definition for 'FromSql' and no extension method 'FromSql' accepting a first argument of type 'DatabaseFacade' could be found

and another if I don't create an instance of CalculateRating class in my controller :

Controllers\AnnouncesController.cs(127,37): error CS0120: An object reference is required for the non-static field, method, or property 'CalculateRating.CreateRating(OfferForCreate)

Everywhere I see must specify the entity, but what entity I can specify if my stored procedure use multiple tables?

Asp.Net Core Web API

Mohsin Mehmood · Accepted Answer · 2018-06-14 23:09:27Z

You can execute stored proc like this:

 using (var command = mainContext.Database.GetDbConnection().CreateCommand())
   {
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.CommandText = "dbo.RatingCalculator";

       var locationIdParam = new System.Data.SqlClient.SqlParameter("@locationId", System.Data.SqlDbType.Int);
       locationIdParam .Value = offer.locationId;

        //DO same for typeId parameter

          //Params to Parameters collection
          command.Parameters.Add(locationIdParam);

       command.Connection.Open();
       return (double)command.ExecuteScalar();
   }

Controllers\AnnouncesController.cs(127,37): error CS0120: An object reference is required for the non-static field, method, or property 'CalculateRating.CreateRating(OfferForCreate)

This error is occuring because if you declare CalculateRating as static you can not reference in non-static field mainContext.

You should create an instance of your CalculateRating class using Dependency Injection. Here are steps:

Create an interface ICalculateRating

public interface ICalculateRating { decimal CreateRating(OfferForCreate offer); }
Update CalculateRating class to implement ICalculateRating
Register the DBContext and ICalculateRating mappings in ConfigureServices method of Startup.cs file like this:

services.AddDbContext<DbContext>(opts=> { opts.UseSqlServer("sqlserver conntection string") }, ServiceLifetime.Scoped);

services.AddTransient<ICalculateRating, CalculateRating>();
In your controller constructor, input an argument of type ICalculateRating which will be injected by Microsoft Dependency Injection framework at runtime:

private readonly ICalculateRating _calculateRating; public MyController(ICalculateRating calculateRating) { _calculateRating = calculateRating; }

You can then call the method like this: offerForCreate.Rating = _calculateRating.CreateRating(addOffer);

awesome. thanks a lot. I never tought that is necesary to create interface and inject it in my controller. and about execution of the procedure, it's clean code and easy to understand. thanks

Collectives™ on Stack Overflow

Entity Framework execute stored procedure with params

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related