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

问题描述

在Entity Framework 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具有更强的说服力.该方法的文档解释说它存在一些限制如下:

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

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.

SQL查询必须返回有关 实体或查询类型的所有属性 的数据.

The SQL query must return data for all properties of the entity or query type.

[...]

因此，您要使用的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

什么是查询类型:

现在，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<MySuperClass>的属性，如下所示:

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<T>

如果您不想使用DbQuery<T>并且不想定义仅包含一个属性的类，则可以像@ vivek nuna 那样使用ExecuteSqlCommandAsync (他的回答部分正确).但是您必须知道，该方法返回的值是受查询影响的行数.另外，您必须将标题作为输出参数，以便使查询成为存储过程.使用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屋！