EF core executes SQL statements and stored procedures

Time:2021-3-6
No matter how powerful ORM is, there will always be some special circumstances, it can not meet our requirements. In this article, we introduce several ways to execute SQL.

Table structure

Before we start, let’s briefly describe the table structure to be used.

public class Category
{
    public int CategoryID
    {
        get;
        set;
    }
    public string CategoryName
    {
        get;
        set;
    }
}

stayCategoryTwo fields are definedCategoryIDCategoryName

public class SampleDbContext: DbContext
{
    public virtual DbSet < Category > Categories
    {
        get;
        set;
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var sqlConnectionStringBuilder = new SqlConnectionStringBuilder
        {
            DataSource = "10.0.1.5", InitialCatalog = "TestDataBase", UserID = "sa", Password = "******"
        };
        optionsBuilder.UseSqlServer(sqlConnectionStringBuilder.ConnectionString);
        base.OnConfiguring(optionsBuilder);
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        EntityTypeBuilder < Category > entityTypeBuilder = modelBuilder.Entity < Category > ();
        entityTypeBuilder.ToTable("Category");
        entityTypeBuilder.HasKey(e => e.CategoryID);
        entityTypeBuilder.Property(e => e.CategoryID).UseSqlServerIdentityColumn();
    }
}

We useSampleDbContextTo access the database.

Executing SQL statements from SQL

Entity Framework core isDbSetAn extension method is providedFromSql, which is used to execute SQL statements or stored procedures. The following example usesFromSqlLoad all the data.

using(var dataContext = new SampleDbContext())
{
    var query = dataContext.Categories.FromSql("select * from Category");
    var result = query.ToList();
}

For SQL statements with parameters, we use C # 6 syntax to write SQL as follows:

using(var dataContext = new SampleDbContext())
{
    var categoryID = 1;
    var query = dataContext.Categories.FromSql($ "select * from Category where CategoryID={categoryID}");
    var result = query.ToList();
}

Note: instead of directly using splicing to process SQL, it is converted into parameterized SQL statements, which helps to prevent SQL injection attacks.We can use SQL Server profiler to help us verify:

exec sp_executesql N 'select * from Category where [email protected] ', N '@p0 int', @p0 = 1

EF Core 执行SQL语句和存储过程

If you don’t use the syntax feature of C # 6, we must use @ P0, @ P1… @ PN as the parameters of the SQL statement

using(var dataContext = new SampleDbContext())
{
    var categoryID = 1;
    var categoryName = "Product";
    var query = dataContext.Categories.FromSql("select * from Category where [email protected] and [email protected]"
        categoryID, categoryName);
    var result = query.ToList();
    Assert.NotNull(result);
}

In the above SQL statement, the@p0Map tocategoryID@ p1Map tocategoryName

FromSqlThe extension method returnsIQueryableObjects. You can also use some LINQ methods. Examples are as follows:

using(var dataContext = new SampleDbContext())
{
    var categoryID = 1;
    var query = dataContext.Categories.FromSql("select * from Category").Where(item => item.CategoryID == categoryID).OrderBy(item => item.CategoryName);
    var result = query.ToList();
}

However, the subquery is used here. The SQL statements captured by SQL Server profiler are as follows:

exec sp_executesql N 'SELECT [item].[CategoryID], [item].[CategoryName] FROM ( select * from Category ) AS [item] WHERE [item].[CategoryID] = @__categoryID_1 ORDER BY [item].[CategoryName]', N '@__categoryID_1 int', @__categoryID_1 = 1

 

Tips: useFromSqlAll columns need to be returned in the executed SQL statement, and the column name must match the entity property name, otherwise the execution will make an error.

From SQL executes stored procedures

The writing method of stored procedure is basically consistent with that of SQL statement. Examples of using stored procedure are as follows:

using(var dataContext = new SampleDbContext())
{
    var categoryID = 1;
    var query = dataContext.Categories.FromSql($ "GetCategoryById {categoryID}");
    var result = query.ToList();
    Assert.NotNull(result);
}

These parameters must be in the same order as the stored procedure parameters.

Tips: useFromSqlWhen executing stored procedures, if LINQ syntax such as’ where ‘and’ orderby ‘is used, these operations will not generate SQL statements, but filter and sort the collection returned by stored procedures in. Net.

ExecuteSqlCommand

stayDbContextExposed oneDatabaseProperty, which includes aExecuteSqlCommandmethod. This method returns an integer representing the number of rows affected by the executed SQL statement. Effective operation isINSERTUPDATEandDELETE, cannot be used to return an entity.

using(var dataContext = new SampleDbContext())
{
    var categoryID = 1;
    var categoryName = "Product";
    var result = dataContext.Database.ExecuteSqlCommand($ "UPDATE dbo.Category SET CategoryName={categoryName} WHERE CategoryID={categoryID}");
}

summary

This section introduces several methods of executing SQL statements and stored procedures in Entity Framework core. I hope it can help you. Thank you!

Original address:https://www.cnblogs.com/tdfblog/p/execute-sql-stored-procedure-in-entity-framework-core.html