EF Core User Account Request

Question

I am working on a small ASP.NET Core project to tag images using the Entity Framework Core in a Sqlite database, mainly for training. There are two tables (and POCOs), tags and images, where several tags are associated with each image. I am trying to get the number of all images that have tags associated with them.

In simple SQL, I would write SELECT COUNT(DISTINCT ImageId) FROM Tags to get a counter, and in LINQ I came up with _context.Tags.Select(t => t.Image).Distinct().Count() . But this LINQ query causes EF-Core to join the two tables, return all the rows, and then execute the Distinct and Count code.

I tried to do _context.Tags.FromSql("SELECT COUNT(DISTINCT ImageId) FROM Tags") , but since this query returns only the score caused by the failure, because EF cannot match the result with the tag. I also tried using _context.Database.FromSql<int> , but could not find any real documentation on it, and it does not look like IntelliSense to it.

Now I have done what is described in detail in the ADO.NET section of this blog post by 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 this the best way to use the account effectively?

---

Edit: Here is the request that EF places in 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

You cannot determine the ad-hoc result at this time. The good news is that it is currently lagging behind: https://github.com/aspnet/EntityFramework/issues/1862

In the meantime, the extension method will be used here:

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

Using:

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

Answer 2

Your original line of code should have done exactly what you wanted. It is also recommended for embedded SQL.

 _context.Tags.Select(t => t.Image).Distinct().Count() 

Are you sure this is called a database for two tables, and then requested them in memory? If you observed this behavior during debugging, then it was possible that your inspection caused the IQueryable to enumerate, which would invoke the database using a different query than otherwise.

The way to check the actual request, without breaking into the current code, is to use MyLoggerProvider from the documentation of the main Entity Framework core.

https://docs.efproject.net/en/latest/miscellaneous/logging.html?highlight=logging

As soon as the registrar is registered in the code, any SQL query launched with the server will be displayed in the console window and / or in the file c: \ temp \ log.txt.

When using Distinct () and Count () in the database tables of the sample website, the following log message was generated.

 SELECT COUNT(*) FROM ( SELECT DISTINCT [a.Blog].[BlogId], [a.Blog].[Url] FROM [Posts] AS [a] INNER JOIN [Blogs] AS [a.Blog] ON [a].[BlogId] = [a.Blog].[BlogId] ) AS [t]Closing connection to database '***' on server 'tcp:**************'. 

---

Finally, since you do not need any properties on t.Image, it seems that you should use the Where () method, not Select ().