Come posso chiamare una stored procedure SQL utilizzando EntityFramework 7 e Asp.Net 5

Per gli ultimi due giorni sono alla ricerca di alcuni tutorial su come chiamare una Stored Procedure da un metodo di controller Web API utilizzando EntityFramework 7 .

Tutte le esercitazioni che ho seguito lo mostrano al contrario, ovvero l’approccio Code First . Ma ho già un database in atto e ho bisogno di usarlo per build Web API . Varie logiche di business sono già state scritte come Stored Procedure e Views e devo consumarle dalla mia Web API.

Domanda 1: È ansible continuare con l’approccio Database First con EF7 e consumare oggetti di database come sopra?

Ho installato EntityFramework 6.1.3 nel mio pacchetto con il seguente comando NuGet :

install-package EntityFramework che aggiunge la versione 6.1.3 al mio progetto ma inizia immediatamente a mostrarmi un messaggio di errore (vedere lo screenshot qui sotto). Non ho idea di come risolvere questo problema.

inserisci la descrizione dell'immagine qui

Ho un altro progetto di test in cui in project.json posso vedere due voci come segue:

"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final", "EntityFramework.MicrosoftSqlServer.Design": "7.0.0-rc1-final",

Tuttavia, quando cerco nel gestore pacchetti Nu-Get , non vedo questa versione! Solo il 6.1.3 sta arrivando.

Il mio objective principale è quello di consumare stored procedure e viste già scritte da un database esistente.

1) Non voglio usare ADO.Net , piuttosto mi piacerebbe usare ORM usando EntityFramework

2) Se EntityFramework 6.1.3 ha la capacità di chiamare Stored Procs e Views Stored Procs da un database già esistente, come posso risolvere l’errore (screenshot)?

Qual è la migliore pratica per raggiungere questo objective?

Spero di capire correttamente il tuo problema. È presente una PROCEDURA MEMORIZZATA, ad esempio dbo.spGetSomeData , nel database, che restituisce l’elenco di alcuni elementi con alcuni campi e è necessario fornire i dati dal metodo API Web.

L’implementazione potrebbe riguardare quanto segue. Puoi definire un DbContext vuoto come:

 public class MyDbContext : DbContext { } 

e per definire appsettings.json con la stringa di connessione al database

 { "Data": { "DefaultConnection": { "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true" } } } 

È necessario utilizzare Microsoft.Extensions.DependencyInjection per aggiungere MyDbContext al

 public class Startup { // property for holding configuration public IConfigurationRoot Configuration { get; set; } public Startup(IHostingEnvironment env) { // Set up configuration sources. var builder = new ConfigurationBuilder() .AddJsonFile("appsettings.json") .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true); .AddEnvironmentVariables(); // save the configuration in Configuration property Configuration = builder.Build(); } // This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { // Add framework services. services.AddMvc() .AddJsonOptions(options => { options.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver(); }); services.AddEntityFramework() .AddSqlServer() .AddDbContext(options => { options.UseSqlServer(Configuration["ConnectionString"]); }); } public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory) { ... } } 

Ora puoi implementare l’azione WebApi come segue:

 [Route("api/[controller]")] public class MyController : Controller { public MyDbContext _context { get; set; } public MyController([FromServices] MyDbContext context) { _context = context; } [HttpGet] public async IEnumerable Get() { var returnObject = new List(); using (var cmd = _context.Database.GetDbConnection().CreateCommand()) { cmd.CommandText = "exec dbo.spGetSomeData"; cmd.CommandType = CommandType.StoredProcedure; // set some parameters of the stored procedure cmd.Parameters.Add(new SqlParameter("@someParam", SqlDbType.TinyInt) { Value = 1 }); if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open(); var retObject = new List(); using (var dataReader = await cmd.ExecuteReaderAsync()) { while (await dataReader.ReadAsync()) { var dataRow = new ExpandoObject() as IDictionary; for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++) { // one can modify the next line to // if (dataReader.IsDBNull(iFiled)) // dataRow.Add(dataReader.GetName(iFiled), dataReader[iFiled]); // if one want don't fill the property for NULL // returned from the database dataRow.Add( dataReader.GetName(iFiled), dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] // use null instead of {} ); } retObject.Add((ExpandoObject)dataRow); } } return retObject; } } } 

Il codice sopra appena eseguito usando exec dbo.spGetSomeData e usa dataRader per leggere tutti i risultati e salvarli in object dynamic . Se effettui $.ajax call da api/My , otterrai i dati restituiti da dbo.spGetSomeData , che puoi utilizzare direttamente nel codice JavaScript. Il codice sopra è molto trasparente. I nomi dei campi dal set di dati restituito da dbo.spGetSomeData saranno i nomi delle proprietà nel codice JavaScript. Non è necessario gestire alcuna class di entity framework nel codice C # in alcun modo. Il tuo codice C # non ha nomi di campi restituiti dalla stored procedure. Pertanto, se estendere / modificare il codice di dbo.spGetSomeData (rinominare alcuni campi, aggiungere nuovi campi) sarà necessario modificare solo il codice JavaScript, ma non il codice C #.

DbContext ha una proprietà Database , che contiene una connessione al database che puoi fare quello che vuoi con:

 context.Database.SqlQuery("exec [dbo].[GetFoo] @Bar = {0}", bar); 

Tuttavia, piuttosto che farlo nelle azioni API Web, suggerirei di aggiungere un metodo al contesto o qualsiasi servizio / repository che interagisce con il contesto. Quindi chiama questo metodo nella tua azione. Idealmente, vuoi mantenere tutte le tue cose SQL in un unico posto.

Proprio come la risposta di cui sopra, puoi semplicemente usare FromSQL () invece di SqlQuery <> ().

 context.Set().FromSql("[dbo].[GetFoo] @Bar = {0}", 45); 

Per il primo approccio al database, è necessario utilizzare il comando Scaffold-DbContext

Installa i pacchetti Nuget Microsoft.EntityFrameworkCore.Tools e Microsoft.EntityFrameworkCore.SqlServer.Design

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

ma quello non otterrà le vostre procedure memorizzate. È ancora in lavorazione, tenendo traccia del problema n. 245

Ma, per eseguire le stored procedure, utilizzare il metodo FromSql che esegue query SQL RAW

per esempio

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

Da usare con i parametri

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

o

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

Esistono alcune limitazioni per eseguire query SQL RAW o stored procedure. Non è ansible utilizzarlo per INSERT / UPDATE / DELETE. se si desidera eseguire query INSERT, UPDATE, DELETE, utilizzare ExecuteSqlCommand

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

Utilizzo del connettore MySQL e di Entity Framework core 2.0

Il mio problema era che stavo ottenendo un’eccezione come FX. Ex.Message = “La colonna richiesta ‘body’ non era presente nei risultati di un’operazione ‘FromSql’.”. Pertanto, per recuperare le righe tramite una procedura memorizzata in questo modo, è necessario restituire tutte le colonne per quel tipo di quadro a cui è associato il DBSet, anche se non sono necessari tutti i dati per questa chiamata specifica.

 var result = _context.DBSetName.FromSql($"call storedProcedureName()").ToList(); 

O con parametri

 var result = _context.DBSetName.FromSql($"call storedProcedureName({optionalParam1})").ToList();