EF Core 2.1 Raw SQL Queries （转自MSDN）

Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. This can be useful if the query you want to perform can't be expressed using LINQ, or if using a LINQ query is resulting in inefficient SQL being sent to the database. Raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model.

Tip
You can view this article's sample on GitHub.

 

 

Limitations

There are a few limitations to be aware of when using raw SQL queries:

• The SQL query must return data for all properties of the entity or query type.
• The column names in the result set must match the column names that properties are mapped to. Note this is different from EF6 where property/column mapping was ignored for raw SQL queries and result set column names had to match the property names.
• The SQL query cannot contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).
• SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (for example, to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery. This means that the SQL passed should not contain any characters or options that are not valid on a subquery, such as:
  • a trailing semicolon
  • On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
  • On SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
• SQL statements other than SELECT are recognized automatically as non-composable. As a consequence, the full results of stored procedures are always returned to the client and any LINQ operators applied after FromSql are evaluated in-memory.

 

 

Basic raw SQL queries

You can use the FromSql extension method to begin a LINQ query based on a raw SQL query

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();

Raw SQL queries can be used to execute a stored procedure.

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Use SqlParameter instance to specify the value of IN or OUT parameters to execute a stored procedure as below:

int totalRowCount = default(int);

var paramLanguageCode = new SqlParameter("@languageCode", languageCode);
var paramCurrentPage = new SqlParameter("@currentPage", currentPage);
var paramPageSize = new SqlParameter("@pageSize", pageSize);
var paramOutTotalRowCount = new SqlParameter("@totalRowCount", totalRowCount)
{
    Direction = ParameterDirection.Output
};

//var paramOutTotalRowCount = new SqlParameter()
//{
//    ParameterName = "@totalRowCount",
//    Direction = ParameterDirection.Output,
//    SqlDbType = SqlDbType.Int
//};

//var parameterQuestionaryCode = new SqlParameter()
//{
//    ParameterName = "@questionaryCode",
//    SqlDbType = SqlDbType.NVarChar,
//    Direction = ParameterDirection.Output,
//    Size = 50 //注意如果是SqlDbType.NVarChar的Output参数，记得还要定义Size的大小，否者执行的时候会报错
//};

var categoriesView = context.SP_GetCategoriesViewInPage.FromSql("EXEC [MD].[SP_GetCategoriesViewInPage] @languageCode, @currentPage, @pageSize, @totalRowCount OUT", paramLanguageCode, paramCurrentPage, paramPageSize, paramOutTotalRowCount).ToList();
totalRowCount = Convert.ToInt32(paramOutTotalRowCount.Value);

同样IN和OUT参数也可以用于执行DbContext.Database.ExecuteSqlCommand方法，来返回存储过程的OUT参数值，如下所示：

int totalRowCount = default(int);

var paramLanguageCode = new SqlParameter("@languageCode", languageCode);
var paramCurrentPage = new SqlParameter("@currentPage", currentPage);
var paramPageSize = new SqlParameter("@pageSize", pageSize);
var paramOutTotalRowCount = new SqlParameter("@totalRowCount", totalRowCount)
{
    Direction = ParameterDirection.Output
};

//var paramOutTotalRowCount = new SqlParameter()
//{
//    ParameterName = "@totalRowCount",
//    Direction = ParameterDirection.Output,
//    SqlDbType = SqlDbType.Int
//};

//var parameterQuestionaryCode = new SqlParameter()
//{
//    ParameterName = "@questionaryCode",
//    SqlDbType = SqlDbType.NVarChar,
//    Direction = ParameterDirection.Output,
//    Size = 50 //注意如果是SqlDbType.NVarChar的Output参数，记得还要定义Size的大小，否者执行的时候会报错
//};

context.Database.ExecuteSqlCommand("EXEC [MD].[SP_GetCategoriesViewInPage] @languageCode, @currentPage, @pageSize, @totalRowCount OUT", paramLanguageCode, paramCurrentPage, paramPageSize, paramOutTotalRowCount);
totalRowCount = Convert.ToInt32(paramOutTotalRowCount.Value);

下面代码展示了如何创建一个SQL Server数据库DECIMAL(8,4)类型的SqlParameter参数@Price

private static void AddSqlParameter(SqlCommand command)
{
    SqlParameter parameter = new SqlParameter("@Price", SqlDbType.Decimal);
    parameter.Value = 3.1416;
    parameter.Precision = 8;
    parameter.Scale = 4;

    command.Parameters.Add(parameter);
}

 

更多关于SqlParameter的信息，请参阅"SqlParameter Class"

 

 

 

Passing parameters

As with any API that accepts SQL, it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
The following example passes a single parameter to a stored procedure. While this may look like String.Format syntax, the supplied value is wrapped in a parameter and the generated parameter name inserted where the {0} placeholder was specified.

var user = "johndoe";

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

This is the same query but using string interpolation syntax, which is supported in EF Core 2.0 and above:

var user = "johndoe";

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

You can also construct a DbParameter and supply it as a parameter value. This allows you to use named parameters in the SQL query string

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

 

 

Composing with LINQ

If the SQL query can be composed on in the database, then you can compose on top of the initial raw SQL query using LINQ operators. SQL queries that can be composed on being with the SELECT keyword.
The following example uses a raw SQL query that selects from a Table-Valued Function (TVF) and then composes on it using LINQ to perform filtering and sorting.

var searchTerm = ".NET";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

 

 

Including related data

Composing with LINQ operators can be used to include related data in the query.

var searchTerm = ".NET";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

 

Warning
Always use parameterization for raw SQL queries: APIs that accept a raw SQL string such as FromSql and ExecuteSqlCommand allow values to be easily passed as parameters. In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. If you are using string concatenation to dynamically build any part of the query string then you are responsible for validating any input to protect against SQL injection attacks.

 

EF Core 3.0更新
注意在EF Core 3.0中，FromSql方法和ExecuteSqlCommand方法都已经过时，请使用FromSqlRaw方法和ExecuteSqlRaw方法进行替代

 

EF Core 7.0更新

在EF Core 7.0中，FromSql方法在增强安全性后又被微软加回来了（又不过时了），而ExecuteSqlCommand方法则被ExecuteSql方法彻底取代，而FromSqlRaw方法和ExecuteSqlRaw方法仍然存在，可以被继续使用

 

参考文献：

Working with Stored Procedure in Entity Framework Core

 

原文链接