EF Core custom count query

Question

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.

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.

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.

What I have done for now is what's detailed in the "ADO.NET" section of this blog post from Eric Anderson:

int count;
using (var connection = _context.Database.GetDbConnection())
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(DISTINCT ImageId) FROM Tags";
        string result = command.ExecuteScalar().ToString();

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

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

---

Edit: 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"

Answer 1

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;
}

Usage:

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