concept executesqlcommand in category entity framework

appears as: n ExecuteSqlCommand, ExecuteSqlCommd, ExecuteSqlCommd
Entity Framework Core in Action

This is an excerpt from Manning's book Entity Framework Core in Action.

Next, you’ll work through a simple example, more applicable to unit testing than production usage, showing what a UDF looks like and how to add it to a database. This listing uses the EF Core ExecuteSqlCommand command to add the SQL code that defines the AverageVotes UDF.

Listing 8.5 Adding your UDF to the database via the ExecuteSqlCommand method
public const string UdfAverageVotes =
    nameof(MyUdfMethods.AverageVotes);    #1   
context.Database.ExecuteSqlCommand(    #2  
    $"CREATE FUNCTION {UdfAverageVotes} (@bookId int)" +    #3  
    @"  RETURNS float
      AS
      BEGIN
      DECLARE @result AS float
      SELECT @result = AVG(CAST([NumStars] AS float)) 
           FROM dbo.Review AS r
           WHERE @bookId = r.BookId
      RETURN @result
      END");

#1   Captures the name of the static method that represents your UDF and uses it as the name of the UDF you add to the database
#2   Uses EF Core’s ExecuteSqlCommand method to add the UDF into the database
#3   The SQL code that follows adds a UDF to an SQL server database.
  • ExecuteSqlCommand method, which executes a nonquery command
  • Reload command, used to refresh an EF Core–loaded entity that has been changed by an ExecuteSqlCommand method
  • In addition to putting raw SQL commands in a query, you can execute nonquery SQL commands via EF Core’s ExecuteSqlCommand method. Typical commands are SQL UPDATE or DELETE commands, but any nonquery SQL command can be called. Listing 9.11 shows an SQL UPDATE command, which takes two parameters.

    Listing 9.11 The ExecuteSqlCommand method executing an SQL UPDATE
    var rowsAffected = context.Database    #1  
        .ExecuteSqlCommand(    #2  
            "UPDATE Books " +    #3  
            "SET Description = {0} " +
            "WHERE BookId = {1}",
            uniqueString, bookId);    #4  
    
    #1   ExecuteSqlCommand can be found in the context.Database property.
    #2   ExecuteSqlCommand executes the SQL and returns an integer, in this case the number of rows updated.
    #3   The SQL command as a string, with places for the parameters to be inserted.
    #4   Provides two parameters that are referred to in the command.

    The ExecuteSqlCommand method returns an integer, which is useful for checking that the command was executed in the way you expected. In this example, you’d expect it to return 1 to show that it found a row in the Books table that had the primary key you provided and updated it.