Creating and accessing stored procedures using the core of the Entity structure

Question

I am implementing the basic Asp.Net web API, the core of the entity structure, the first database approach using Visual Studio 2017. I was able to create context and class files based on the existing database. I need to access stored procedures using my context. In an earlier version of the entity structure, it was easy to select the stored procedure objects in the wizard and create an edmx that contains these objects. Then I could access stored procedures through objects of a complex type that are exposed by the entity infrastructure. How to do this in the core of an entity. Will an example help?

Answer 1

The first database approach is missing in EF Core with edmx files. Instead, you need to use Scaffold-DbContext

Install Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.SqlServer.Design Nuget Packages

Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

but this will not get your stored procedures. It's still at work tracking issue # 245

But to execute stored procedures, use the FromSql method , which executes RAW SQL queries

eg,

var products= context.Products
    .FromSql("EXECUTE dbo.GetProducts")
    .ToList();

For use with options

var productCategory= "Electronics";

var product = context.Products
    .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
    .ToList();

or

var productCategory= new SqlParameter("productCategory", "Electronics");

var product = context.Product
    .FromSql("EXECUTE dbo.GetProductByName  @productCategory", productCategory)
    .ToList();

There are certain restrictions on the execution of RAW SQL queries or stored procedures. You cannot use it for INSERT / UPDATE / DELETE. if you want to execute INSERT, UPDATE, DELETE queries, use the ExecuteSqlCommand command

var categoryName = "Electronics";
dataContext.Database
           .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);

Answer 2

, , , . , , ? EF Core 2, , , .

, . , .

namespace Example.EF.Model
{
    public class Sample
    {
        public int SampleID { get; set; }
        public string SampleName { get; set; }
    }
}

DBSet :

public virtual DbSet<Sample> Sample { get; set; }

, , :

var products = _samplecontext.Sample
      .FromSql($"EXEC ReturnAllSamples {id}, {startdate}, {enddate}").ToList();

, .

Answer 3

, EF Core , - FromSql, :

List<Employee> employees = dbcontext.Employee
                    .FromSql("GetAllEmployees").ToList();

Create, Update Delete ExecuteSqlCommand, :

var employee = "Harold Javier";
dbcontext.Employee
           .ExecuteSqlCommand("InsertEmployee @emp", employee);

Answer 4

Rohith/Harold Javier/Sami . , EF6 # , EFCore. , , , : EDMX

, VS2017: https://marketplace.visualstudio.com/items?itemName=MadsKristensen.TypeScriptDefinitionGenerator

, , .

: VS2017 dbconext https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools. , VS Scaffold-DbContext.

Answer 5

- fooobar.com/questions/211200/...

EF Core, 3 .

1. , . SP. , SP Id Name, -

public class MySPModel
{
    public int Id {get; set;}
    public string Name {get; set;}
}

2.

DbQuery DBContext SP.

public partial class Sonar_Health_AppointmentsContext : DbContext
{
        public virtual DbSet<Booking> Booking { get; set; } // your existing DbSets
        ...

        public virtual DbQuery<MySPModel> MySP { get; set; } // your new DbQuery
        ...
}

3.

SP DBContext.

var result = await _context.Query<MySPModel>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();

UnitOfWork & Repository. SP -

/// <summary>
/// Execute function. Be extra care when using this function as there is a risk for SQL injection
/// </summary>
public async Task<IEnumerable<T>> ExecuteFuntion<T>(string functionName, string parameter) where T : class
{
    return await _context.Query<T>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();
}

, - !!!