Using .Count on a ICollection<T> does not produce EXISTS as it does when using List<T>

[alaatm]

In the whats new in .net 9, it says that queries using .Count are now optimized to use EXISTS instead of COUNT. However, this seems to be true for collections of type List<T>. We use ICollection<T> in our projects and we would love to replace .Any() with .Count > 0 but this optimization isn't applied as shown below

public class Blog
{
    public int Id { get; set; }
    public ICollection<Post> Posts { get; set; } = default!;
}

public class Post
{
    public int Id { get; set; }
}

context.Blogs.Where(b => b.Posts.Count > 0);

produces:

SELECT [b].[Id]
FROM [Blogs] AS [b]
WHERE (
    SELECT COUNT(*)
    FROM [Post] AS [p]
    WHERE [b].[Id] = [p].[BlogId]) > 0

Using context.Blogs.Where(b => b.Posts.Count != 0); will make it even worse:

SELECT [b].[Id]
FROM [Blogs] AS [b]
WHERE (
    SELECT COUNT(*)
    FROM [Post] AS [p]
    WHERE [b].[Id] = [p].[BlogId]) <> 0 OR (
    SELECT COUNT(*)
    FROM [Post] AS [p]
    WHERE [b].[Id] = [p].[BlogId]) IS NULL

EF Core version: 9.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer

[roji]

@cincuranet interested in giving this a look?

[ChrisJollyAU]

@roji See QueryableMethodNormalizingExpressionVisitor line 73/74

efcore/src/EFCore/Query/Internal/QueryableMethodNormalizingExpressionVisitor.cs

Line 73 in f163289

when (member.DeclaringType.GetGenericTypeDefinition().GetInterfaces().Any(

GetInterfaces only gets those inherited/implemented. Normally fine but if the type is also an interface that is NOT returned as part of the list. So in this case member.DeclaringType is an ICollection, however GetInterfaces only returns IEnumerable and its generic version.

Changing that section to the following should do the trick

when (member.DeclaringType.GetGenericTypeDefinition() == typeof(ICollection<>) || member.DeclaringType.GetGenericTypeDefinition().GetInterfaces().Any(
    x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(ICollection<>)))

[roji]

@ChrisJollyAU thanks for looking into it... Are you interested in submitting a PR?

[ChrisJollyAU]

Sure. I haven't got a test sorted yet so will do that sometime