如何使用 Entity Framework Core 2.1 执行 SqlQuery? [英] How to execute SqlQuery with Entity Framework Core 2.1?

问题描述

在实体框架 6 中，我可以使用以下命令对数据库执行原始 SQL 查询:

In Entity Framework 6, I can execute a raw SQL query on the database using the following command:

IEnumerable<string> Contact.Database.SqlQuery<string>("SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10");

在一个新项目中，我尝试使用 Entity Framework Core 2.1.我需要执行原始 SQL 查询.在谷歌搜索时，我可以看到扩展名 SqlQuery 已更改为 FromSql.但是，FromSql 只存在于 DbSet<> 上，而不存在于 DbContext.Database 上.

On a new project, I am trying to use Entity Framework Core 2.1. I have a need to execute raw SQL query. While googling, I can see that the extension SqlQuery was changed to FromSql. However, FromSql only exists on the DbSet<> not on the DbContext.Database.

如何在 DbSet<> 之外运行 FromSql?方法 FromSql 在数据库对象 DbContext.Database.FromSql<> 上不存在.

How can I run FromSql outside the DbSet<>? The method FromSql does not exists on the database object DbContext.Database.FromSql<>.

推荐答案

更新

这适用于 EF Core 2.1，但如果您使用的是 EF Core 3.0 或更高版本，请参阅此 完整答案.

This is wokring for EF Core 2.1 but if you're using EF Core 3.0 or higher versions please refer to this complete answer.

可以看到扩展名SqlQuery改成了FromSql

I can see that the extension SqlQuery was changed to FromSql

但是新的FromSql 方法比SqlQuery 更具限制性.该方法的 documentation 解释说它存在一些限制如:

But the new FromSql method is more restrcitive than SqlQuery. The documentation of that method explains that it exists some limitations like:

SQL 查询只能用于返回属于您的模型.我们的 backlog 有一个改进，以允许从原始 SQL 查询返回临时类型.
SQL 查询必须返回实体或查询类型的所有属性的数据.

SQL queries can only be used to return entity types that are part of your model. There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries.
The SQL query must return data for all properties of the entity or query type.

[...]
更新:最近的 GitHub dotnet/efcore 讨论 支持原始 SQL 查询，无需为结果定义实体类型 #10753

因此，在您的情况下，您使用的 SQL 查询如下:

So in your case the SQL query you're using is the following:

SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10

正如文档所说，您只能将 FromSql 与 实体或查询类型一起使用.您的 SQL 查询不会返回模型中定义的实体的所有数据，但只会返回实体的一列.顺便说一下，EF Core 2.1 中引入了一项新功能，该功能自 2018 年 5 月 7 日起进入候选版本.微软表示:

As the documentation said you can only use FromSql with entity or query type. Your SQL query doesn't return all data of your entity defined in your model but it only returns one column of your entity. By the way a new feature is introduced in EF Core 2.1 which is in Release Candidate since 7 may 2018. Microsoft says:

EF Core 2.1 RC1 是一个上线"版本，这意味着一旦您测试您的应用程序与 RC1 一起正常工作，您可以在生产并获得 Microsoft 的支持，但您仍然应该一旦可用，更新到最终稳定版本.

EF Core 2.1 RC1 is a "go live" release, which means once you test that your application works correctly with RC1, you can use it in production and obtain support from Microsoft, but you should still update to the final stable release once it’s available.

##在查询类型上使用 FromSql##

##Using FromSql on query type##

什么是查询类型:

EF Core 模型现在可以包含查询类型.不像实体类型，查询类型没有定义键，不能被插入、删除或更新(即它们是只读的)，但它们可以通过查询直接返回.一些使用场景查询类型有:映射到没有主键的视图，映射到没有主键的表，映射到模型中定义的查询，作为FromSql()查询的返回类型

An EF Core model can now include query types. Unlike entity types, query types do not have keys defined on them and cannot be inserted, deleted or updated (i.e. they are read-only), but they can be returned directly by queries. Some of the usage scenarios for query types are: mapping to views without primary keys, mapping to tables without primary keys, mapping to queries defined in the model, serving as the return type for FromSql() queries

如果你想在你的 SQL 文本中使用查询类型特性，你首先定义一个类，我们将它命名为 MySuperClass:

If you want to use query type feature with your SQL text you first define a class, let's name it MySuperClass:

public class MySuperClass
{
    public string Title { get; set; }
}

然后在您的 DbContext 类中定义了一个 DbQuery 类型的属性，如下所示:

Then in your DbContext class defined a property of type DbQuery<MySuperClass> like below:

public DbQuery<MySuperClass> MySuperQuery { get; set; }

最后你可以像下面这样使用 FromSql :

Finally you can use FromSql on it like below:

var result = context.MySuperQuery.FromSql("SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10").ToList().First();
var title = result.Title;

##不想用DbQuery##如果你不想使用 DbQuery 并且不想定义一个只包含一个属性的类，那么你可以使用 ExecuteSqlCommandAsync 像@vivek nuna 在他的回答中做了(他的回答部分正确).但是您必须知道该方法的返回值是受您查询影响的行数.此外，您必须将标题作为输出参数，以便使您的查询成为存储过程.使用 ExecuteSqlCommandAsync 或 ExecuteSqlCommand，然后读取调用方法时传递的输出参数.

##Don't want to use DbQuery<T>## If you don't want to use DbQuery<T> and don't want to define a class that contains only one property then you can use ExecuteSqlCommandAsync like @vivek nuna did in his answer(his answer is partially correct). But you must know that returned value by that method is the number of rows affected by your query. Also you must put your title as an output parameter so make your query a stored procedure. Use ExecuteSqlCommandAsync or ExecuteSqlCommand and after that read the output parameter you passed when calling the method.

不创建存储过程因此不使用 ExecuteSqlCommandAsync 或 ExecuteSqlCommand 的一种更简单的方法是以下代码:

A simpler way without creating a stored procedure therefore not using ExecuteSqlCommandAsync or ExecuteSqlCommand is to the following code:

using (var context = new MyDbContext())
{
    var conn = context.Database.GetDbConnection();
    await conn.OpenAsync();
    var command = conn.CreateCommand();
    const string query = "SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10";
    command.CommandText = query;
    var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        var title = reader.GetString(0);
        // Do whatever you want with title 
    }
}  

您可以将此逻辑设为辅助方法，该方法将接收您的 SQL 查询并返回所需的数据.但我建议你使用 Dapper.Net 其中包含很多帮助方法，可以帮助您轻松处理RAW SQL 就像我们上面所做的那样，并且还与 DbContext 共享 smae 连接.

You can make this logic a helper method that will recevie your SQL Query and returns the desired data. But I recommend you use Dapper.Net whcih contains a lot of helpers methods that will help to deal easily with RAW SQL like we do above and also sharing the smae connection with DbContext.

这篇关于如何使用 Entity Framework Core 2.1 执行 SqlQuery?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！