Entity Framework Core 2.1: Difference between FromSql and ExecuteSqlCommand

In this article we will discuss with an example, what is the difference between FromSql and ExecuteSqlCommand functions in Entity Framework Core 2.1 framework.

Let's say we have a table named as Customers.

CREATE TABLE [dbo].[Customers](
	[CustomerId] [INT] NOT NULL,
	[Name] [VARCHAR](100) NOT NULL,
	[Country] [VARCHAR](50) NOT NULL,
	[Designation] [VARCHAR](50) NOT NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
INSERT INTO Customers
SELECT 1, 'Akash Sinha', 'India','S/W Enginner'
UNION ALL
SELECT 2, 'Tina Jain', 'India','HR'
UNION ALL
SELECT 3, 'John Mathews', 'USA','Accountant'
UNION ALL
SELECT 4, 'Sean Robert', 'UK','Architect'

Differences between FromSql and ExecuteSqlCommand

FromSql

• The ExecuteSqlCommand is used to execute SQL queries or Stored Procedures that does not fetch data. Example INSERT, UPDATE and DELETE queries.
• Returns the count of the number of Rows affected by the operation in INTEGER format.
• It is available in the Database property of the DbContext class.

FromSql Example

The following DbContext class has a method named GetCustomer which accepts the parameter CustomerId. Inside the function, the SQL Query to fetch the record from the Customers table is executed using the FromSql function. The FromSql function accepts the following two parameters:

• SQL Query – SQL Query to be executed.
• Parameters – One or more objects of SqlParameter class.

public class Context : DbContext
    {
        public Context(DbContextOptions<Context> options) : base(options)
        {
        }
 
        public DbSet<Customer> Customers { get; set; }
 
        public IQueryable<Customer> GetCustomer(int customerId)
        {
            string sql = @"SELECT * FROM Customers WHERE CustomerId = @CustomerId";
            SqlParameter pCustomerId = new SqlParameter("@CustomerId", customerId);
            return this.Customers.FromSql(sql, pCustomerId);
        }
    }


ExecuteSqlCommand

The ExecuteSqlCommand is used to execute SQL queries or Stored Procedures that does not fetch data. Example INSERT, UPDATE and DELETE queries.
Returns the count of the number of Rows affected by the operation in INTEGER format.
It is available in the Database property of the DbContext class.
ExecuteSqlCommand Example 

DbContext class has a method named DeleteCustomer which accepts the parameter CustomerId.
Inside the function, the SQL Query to delete the record from the Customers table is executed using the ExecuteSqlCommand function. 

The ExecuteSqlCommand function accepts the following two parameters: 
SQL Query – SQL Query to be executed.
Parameters – One or more objects of SqlParameter class.
public class Context: DbContext
    {
        public Context(DbContextOptions<Context> options) : base(options)
        {
        }
 
        public DbSet<Customer> Customers { get; set; }
 
        public void DeleteCustomer(int customerId)
        {
            string sql = @"DELETE FROM Customers WHERE CustomerId = @CustomerId";
            SqlParameter pCustomerId = new SqlParameter("@CustomerId", customerId);
            int affected = this.Database.ExecuteSqlCommand(sql, pCustomerId);
        }
    }

Summary

I hope you should understand Difference between FromSql and ExecuteSqlCommand. If you have anything then please revert us.