Incorrect SQL when using Linq 'Any'

[agileorange]

_context.Sites
.Where(b => b.WorkTasks.Any(c => c.WorkProgramId == workProgramId))
.Where(c => c.IsDeleted == false)
.Count()

Generates the following SQL

SELECT COUNT(*)
FROM [Site] AS [b]
INNER JOIN [Location] AS [b.Location] ON [b].[LocationId] = [b.Location].[Id]
INNER JOIN [SiteClient] AS [b.SiteClient] ON [b].[SiteClientId] = [b.SiteClient].[Id]
INNER JOIN [SiteCategory] AS [b.SiteCategory] ON [b].[SiteCategoryId] = [b.SiteCategory].[Id]
WHERE (
    SELECT CASE
        WHEN EXISTS (
            SELECT 1
            FROM [WorkTask] AS [c]
            WHERE ([c].[WorkProgramId] = @__workProgramId_0) AND ([b].[Id] = [c].[SiteId]))
        THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END
) AND ([b].[IsDeleted] = 0)

When it should have the '=1' after the WHERE (..)? i.e.

SELECT COUNT(*)
FROM [Site] AS [b]
INNER JOIN [Location] AS [b.Location] ON [b].[LocationId] = [b.Location].[Id]
INNER JOIN [SiteClient] AS [b.SiteClient] ON [b].[SiteClientId] = [b.SiteClient].[Id]
INNER JOIN [SiteCategory] AS [b.SiteCategory] ON [b].[SiteCategoryId] = [b.SiteCategory].[Id]
WHERE (
    SELECT CASE
        WHEN EXISTS (
            SELECT 1
            FROM [WorkTask] AS [c]
            WHERE ([c].[WorkProgramId] = @__workProgramId_0) AND ([b].[Id] = [c].[SiteId]))
        THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END
) **= 1** AND ([b].[IsDeleted] = 0)

[smitpatel]

Can you share your model & dbcontext classes?

[agileorange]

It is for a customer who would not be happy to share the model with public. Can I do this privately?

[smitpatel]

No problem. Which version of EF are you using?

[agileorange]

{
"version": "1.0.0-*",
"description": "Apollo1.Web.Data Class Library",
"authors": [ "mark.chidlow" ],
"tags": [ "" ],
"projectUrl": "",
"licenseUrl": "",

"dependencies": {
"Apollo1.Web.Domain": "1.0.0-*",
"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
"Microsoft.AspNet.Identity.EntityFramework": "3.0.0-rc1-final",
"Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final"
},

"commands": {
"ef": "EntityFramework.Commands"
},

"frameworks": {
"dnx451": { },
"dnxcore50": {
"dependencies": {
"Microsoft.CSharp": "4.0.1-beta-23409",
"System.Collections": "4.0.11-beta-23409",
"System.Linq": "4.0.1-beta-23409",
"System.Runtime": "4.0.21-beta-23409",
"System.Threading": "4.0.11-beta-23409"
}
}
}
}

[agileorange]

I have a 'using AutoMapper.QueryableExtensions'. I'll check that this isn't causing the issue.

[smitpatel]

This looks similar to #4877 which is fixed in latest codebase (but causes issue in rc1 release). I will test if above query works in latest code or not.

[agileorange]

OK I have tested and it has nothing to do with AutoMapper.QueryableExtensions.

[smitpatel]

Repro code:

public class Program
{
    public static void Main(string[] args)
    {
        using (var _context = new BloggingContext())
        {
            _context.Database.EnsureDeleted();
            _context.Database.EnsureCreated();
            var workProgramId = 1;
            var query = _context.Sites
                .Where(b => b.WorkTasks.Any(c => c.WorkProgramId == workProgramId))
                .Where(c => c.IsDeleted == false)
                .Count();
        }

    }
}

public class Site
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }

    public ICollection<WorkTask> WorkTasks { get; set; }


}

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

    public int SiteId { get; set; }

    public int WorkProgramId { get; set; }
}

public class BloggingContext : DbContext
{
    public DbSet<Site> Sites { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Data Source=(localdb)\MSSQLLocalDB;Database=test;Integrated Security=True;Connect Timeout=30");
    }
}

Generated query in latest code

exec sp_executesql N'SELECT COUNT(*)
FROM [Sites] AS [b]
WHERE EXISTS (
    SELECT 1
    FROM [WorkTask] AS [c]
    WHERE ([c].[WorkProgramId] = @__workProgramId_0) AND ([b].[Id] = [c].[SiteId])) AND ([b].[IsDeleted] = 0)',N'@__workProgramId_0 int',@__workProgramId_0=1

The issue is fixed in latest code. Feel free to re-open, if you still see issue happening after upgrading to RC2 (after it releases).
As a work-around in RC1 you can convert Any(...) to Count(...) > 0

[agileorange]

Excellent thank you.