没有 DbSet 的原始 SQL 查询 - Entity Framework Core [英] Raw SQL Query without DbSet - Entity Framework Core

问题描述

随着 Entity Framework Core 删除 dbData.Database.SqlQuery 我找不到为我的全文搜索查询构建原始 SQL 查询的解决方案，该查询将返回表数据和还有等级.

With Entity Framework Core removing dbData.Database.SqlQuery<SomeModel> I can't find a solution to build a raw SQL Query for my full-text search query that will return the tables data and also the rank.

我见过的在 Entity Framework Core 中构建原始 SQL 查询的唯一方法是通过 dbData.Product.FromSql("SQL SCRIPT"); 这没有用，因为我没有将映射我在查询中返回的排名的 DbSet.

The only method I've seen to build a raw SQL query in Entity Framework Core is via dbData.Product.FromSql("SQL SCRIPT"); which isn't useful as I have no DbSet that will map the rank I return in the query.

任何想法???

推荐答案

这取决于您使用的是 EF Core 2.1 还是 EF Core 3 及更高版本.

It depends if you're using EF Core 2.1 or EF Core 3 and higher versions.

如果您使用自 2018 年 5 月 7 日起可用的 EF Core 2.1 Release Candidate 1，您可以利用提议的新功能，即查询类型.

If you're using EF Core 2.1 Release Candidate 1 available since 7 may 2018, you can take advantage of the proposed new feature which is Query type.

什么是查询类型?

除了实体类型，EF Core 模型还可以包含查询类型，可用于对数据执行数据库查询未映射到实体类型.

In addition to entity types, an EF Core model can contain query types, which can be used to carry out database queries against data that isn't mapped to entity types.

何时使用查询类型?

用作临时 FromSql() 查询的返回类型.

Serving as the return type for ad hoc FromSql() queries.

映射到数据库视图.

映射到没有定义主键的表.

Mapping to tables that do not have a primary key defined.

映射到模型中定义的查询.

Mapping to queries defined in the model.

因此，您不再需要执行作为问题答案提出的所有技巧或解决方法.只需按照以下步骤操作:

So you no longer need to do all the hacks or workarounds proposed as answers to your question. Just follow these steps:

首先，您定义了一个 DbQuery 类型的新属性，其中 T 是将携带 SQL 查询的列值的类的类型.因此，在您的 DbContext 中，您将拥有:

First you defined a new property of type DbQuery<T> where T is the type of the class that will carry the column values of your SQL query. So in your DbContext you'll have this:

public DbQuery<SomeModel> SomeModels { get; set; }

第二次使用 FromSql 方法，就像使用 DbSet 一样:

Secondly use FromSql method like you do with DbSet<T>:

var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

另请注意，DdContext 是 部分类，因此您可以创建一个或多个单独的文件来组织最适合您的原始 SQL DbQuery"定义.

Also note that DdContexts are partial classes, so you can create one or more separate files to organize your 'raw SQL DbQuery' definitions as best suits you.

查询类型现在称为 无键实体类型.如上所述，查询类型是在 EF Core 2.1 中引入的.如果您使用的是 EF Core 3.0 或更高版本，您现在应该考虑使用无键实体类型，因为查询类型现在被标记为过时.

Query type is now known as Keyless entity type. As said above query types were introduced in EF Core 2.1. If you're using EF Core 3.0 or higher version you should now consider using keyless entity types because query types are now marked as obsolete.

此功能是在 EF Core 2.1 中以查询类型的名称添加的.在 EF Core 3.0 中，该概念被重命名为无键实体类型.这[Keyless] 数据注释在 EFCore 5.0 中可用.

This feature was added in EF Core 2.1 under the name of query types. In EF Core 3.0 the concept was renamed to keyless entity types. The [Keyless] Data Annotation became available in EFCore 5.0.

对于何时使用无键实体类型，我们仍然有与查询类型相同的场景.

We still have the same scenarios as for query types for when to use keyless entity type.

因此，要使用它，您需要首先使用 [Keyless] 数据注释或使用 .HasNoKey() 进行流畅的配置来标记您的类 SomeModel> 方法调用如下:

So to use it you need to first mark your class SomeModel with [Keyless] data annotation or through fluent configuration with .HasNoKey() method call like below:

public DbSet<SomeModel> SomeModels { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeModel>().HasNoKey();
}

在该配置之后，您可以使用解释的方法之一在这里 执行您的 SQL 查询.例如你可以使用这个:

After that configuration, you can use one of the methods explained here to execute your SQL query. For example you can use this one:

var result = context.SomeModels.FromSqlRaw("SQL SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

这篇关于没有 DbSet 的原始 SQL 查询 - Entity Framework Core的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！