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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

AmirNorouzpour

Related Questions

Log Insert and Update Command sql query in Entity Framework

You gonna need to accumulate lines and detect SQL statement end. E.g. when the line begins with ...--... it is obviously past the end of the SQL statement. If you like to capture the comments (and parameters) you can trap the ...-- Completed... line. So instead calling ...WriteDetailed... directly, you call a method to aggregate and analyze the EF ...
c# entity-framework entity-framework-6
asked by Bozhidar Stoyneff

Execute SQL command in Entity Framework Core 2.0 to delete all data in a table

Ensure that you reference ...Microsoft.EntityFrameworkCore... to include all the necessary extension methods that would allow you to execute raw SQL commands....From the source repository I found ...ExecuteSqlCommand... and related extension methods...int count = await context.Database.ExecuteSqlCommandAsync("DELETE FROM [Blogs]"); ...Found an arti...
asp.net-core-2.0 c# entity-framework-core sql
asked by Nkosi

Entity Framework Core - Execute raw sql - Same code for SQL/SQLite

I'm currently using two different Connections types, (SQLConnection for normal use and SqliteConnection for integration testing), when inserting new exceptions in my LogService....Because I don't have access to the database context class I'll have to use raw sql to insert new entities, (I only have the database connectionstring)....When running int...
.net c# entity-framework-core sql sqlite
asked by Reft

Entity Framework 6 vs Entity Framework Core Raw Sql

Entity Framework 6 example writing SQL queries for non-entity types:...context.Database.SqlQuery<string>(" ; with tempSet as " + "(select " + ...In Entity Framework 6, I can also write the following query with ...SqlQuery.... How can I run the following query with Entity Framework Core?...; with tempSet as ( ...
entity-framework-6 entity-framework-core
asked by serenity

Entity Framework Core Mapping Multiple objects to an object in one to many

I am trying to work out the best way to handle a Note object that is linked to multiple objects i.e. Contacts, Quotes. These objects can have many Notes, A Quote has many Notes....public class Note { public int Id { get; set; } public string Title { get; set; } public string NoteDetails { get; set; } public DateTime CreatedD...
ef-code-first entity-framework-core object-object-mapping
asked by Gweilo

Multiple tenants with Entity Framework Core and SQL Server 2017 in ASP.NET Core application

I have an ASP.NET Core 2.1 application which uses Entity Framework Core 2.1 and SQL Server 2017 (Web edition). I read a lot of how to solve problem with multiple tenants, but more or less the answer is: ...it depends on your application, setup....Setup:...every client gets a new IIS site with own configuration for database...some tables (client de...
asp.net-core entity-framework entity-framework-core sql-server sql-server-2017
asked by Makla

Use SQL Query for Dropdown - ASP.NET Core 2.0; RazorPages; VS 2017; Entity Framework Core

I currently have an ASP.NET Core 2.0 Web Application (built in VS 2017 from the default web application template). I have a RazorPage with a dropdown built in that I would like to populate via a SQL Query. I've used Entity Framework Core to build a model of my database as follows:... public partial class INVOPEContext : DbContext { ...
asp.net-core-2.0 entity-framework-core razor
asked by junkbond916

.NET Core Entity Framework throws Invalid object name when accessed as entity, but works with SQL

I'm working with Entity Framework Core, v6.2. I'm getting an error...SqlException: Invalid object name 'Cdef.CellDefinition' ...when I try to access the ...DbSet... directly, but using the same ...DbContext... object, I can query the object directly using the ...FromSql... command. ...I've seen other answers saying to modify the conventions to rem...
asp.net-mvc entity-framework entity-framework-core
asked by John P

Entity Framework Core Entity > Value Object > Entity Relationship

I have the following classes...public class Slot : Entity { public SnackPile SnackPile { get; set; } public SnackMachine SnackMachine { get; set; } public int Position { get; } protected Slot() { } public Slot(SnackMachine snackMachine, int position) { SnackMachine = snackMachine; Position = positio...
domain-driven-design entity entity-framework-core relationship value-objects
asked by visualbam

SQL query to LINQ expression - Entity Core Framework 3 + SQL Server

DECLARE @thirtyDaysAgo DATETIME = getdate()-30 SELECT sum(case when CreatedWhen >= @thirtyDaysAgo then 1 else 0 end) lessThan30, sum(case when CreatedWhen < @thirtyDaysAgo then 1 else 0 end) Greaterthan30 FROM ThisAwesomeTable ...Cannot figure out how to convert this to a fluent query....The intent is to count the number of rows that were created...
entity-framework-core fluent linq sql-server
asked by Ashwin

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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