Entity Framework Core Or Normal Sql Command Object

asp.net asp.net-core asp.net-mvc entity-framework-core

Question

I have to create a solution in Asp.net core and I have to use a stored procedure based approach. So is it feasible to use Entity Framework core for the application? Because data return from the stored procedure, not an object declared in Entity Core

1
4
9/24/2018 5:46:08 AM

Accepted Answer

It is important to understand what is needed. Entity Framework & using normal SQl command, both has it's pros & cons.

Basic points are :

Entity Framework

  1. Can act as ORM for different databases, if you application uses two or more databases like SQL server & oracle. Almost same code can be used. Also it is easier to switch from one db to another.

  2. EF has both code first & db first, also maintains connections & query generation so less concern for SQL injection & faster changes

  3. Using EF however if your team has many developers who are committing EF changes frequently it is hard to merge internal layers code conflicts for EF.

  4. If you have strong middleware & caching, EF can be optimised for application caching.

SQL Command object

  1. More control over SQL since query generated in EF are at runtime so hard to analyse & optimize indices, & execution plan.

  2. Need to take care for sql injections, parameter sniffing issues.

  3. No issues for code merges till DB design is done with consultation in large Dev Teams.

  4. SQL Command objects are easier to debug & analyse in maintenance with less effort in change since you need not to update whole EF files & related code.

Teams with dedicated DBA & large server for DB choose SQL command, Teams with small agile team with full stack dev with strong middleware usually go for EF.

Both of them are good for some use cases & none of them is silver bullet for all. so pick what suits you best.

2
9/24/2018 7:35:22 AM

Popular Answer

EF Core provides the following methods to execute a stored procedure:

  1. DbSet<TEntity>.FromSql()

  2. DbContext.Database.ExecuteSqlCommand()

    var data = context.DbSet<TEntity>.FromSql("SPName").ToList();

    var rowsAffected = context.Database.ExecuteSqlCommand("sql command");

For more details : entityframeworktutorial

and for use fields that not exist in model you can declare these in model and use NotMapped attribute. NotMapped Attribute

And for mapping stored procedures, fluently look at this link



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow