Daniel Dreier Daniel Dreier - 2 months ago 45
C# Question

EF Core custom count query

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?

Answer

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");
Comments