EF Core自定义计数查询 [英] EF Core custom count query

问题描述

我正在研究一个小型ASP.NET Core项目，该项目用于在Sqlite数据库上使用Entity Framework Core标记图像.有两个表(和POCO)，标签和图像，其中每个标签与多个标签相关.我正在尝试计算所有具有与之关联的标签的图像.

I'm working on a small ASP.NET Core project for tagging images using Entity Framework Core on a Sqlite database, mainly just for learning. There are two tables (and POCOs), Tags and Images, where multiple tags are related to each image. I'm trying to get a count of all Images that have tags associated with them.

在普通的SQL中，我写SELECT COUNT(DISTINCT ImageId) FROM Tags来获取计数，在LINQ中我想出了_context.Tags.Select(t => t.Image).Distinct().Count().但是该LINQ查询似乎导致EF-Core联接两个表，返回所有行，然后在代码中执行Distinct和Count.

In plain SQL I'd write SELECT COUNT(DISTINCT ImageId) FROM Tags to get the count, and in LINQ I came up with _context.Tags.Select(t => t.Image).Distinct().Count(). But that LINQ query appears to cause EF-Core to join the two tables, return all of the rows, and then do the Distinct and Count in code.

我尝试做_context.Tags.FromSql("SELECT COUNT(DISTINCT ImageId) FROM Tags")，但是由于该查询仅返回计数，因此调用失败，因为EF无法将结果映射到Tag.我也尝试使用_context.Database.FromSql<int>，但无法在其上找到任何真实的文档，并且似乎也没有IntelliSense.

I tried to do _context.Tags.FromSql("SELECT COUNT(DISTINCT ImageId) FROM Tags"), but because that query only returns the count the call fails because EF can't map the result to a Tag. I also tried to use _context.Database.FromSql<int>, but wasn't able to find any real documentation on it and there doesn't seem to be IntelliSense for it.

我现在所做的是此

但是，这是有效获得计数的最好方法吗?

But is that the best way to go about getting the count efficiently?

这是EF放入调试输出中的查询:

Here's the query that EF is putting in the Debug output:

SELECT "t"."TagId", "t"."Content", "t"."ImageId", "t.Image"."ImageId", "t.Image"."FileName", "t.Image"."Path", "t.Image"."Url"
FROM "Tags" AS "t"
LEFT JOIN "Images" AS "t.Image" ON "t"."ImageId" = "t.Image"."ImageId"
ORDER BY "t"."ImageId"

推荐答案

到目前为止，您无法定义临时结果. 好消息是它当前正在积压中: https://github.com/aspnet/EntityFramework/issues /1862

As of now, you can't define an ad-hoc result. Good news is that it's currently on the backlog: https://github.com/aspnet/EntityFramework/issues/1862

同时，这是一个可以使用的扩展方法:

In the meantime, here's an extension method that would work:

public static int IntFromSQL(this ApplicationDbContext context, string sql )
{
    int count;
    using (var connection = context.Database.GetDbConnection())
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = sql;
            string result = command.ExecuteScalar().ToString();

            int.TryParse(result, out count);
        }
    }
    return count;
}

用法:

int result = _context.IntFromSQL("SELECT COUNT(DISTINCT ImageId) FROM Tags");

这篇关于EF Core自定义计数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！