LINQ/EF declaring a temp variable for performance

Question

I have a database query. I can make go nice and fast with raw SQL but I'd really like to do something similar in LINQ

declare @ruleInstanceCount int
select @ruleInstanceCount
    = (select count(distinct(gri.RuleInstance_Id))
       from [Group] g  
       left join GroupRuleInstance gri on g.id = gri.Group_Id
       where g.name= @groupName)

SELECT cache.EntityKey
from RuleInstanceCurrentMembershipCacheItem cache
left join RuleInstance ri on ri.id = cache.RuleInstanceId 
left join GroupRuleInstance gri on ri.id = gri.RuleInstance_Id
left join [Group] g on g.id = gri.Group_Id 
where cache.IsMember = 1 and g.Name = @groupName 
group by cache.EntityKey
HAVING  count(cache.RuleInstanceId) = @ruleInstanceCount
order by cache.EntityKey

Yay SQL is really fast because I can declare a variable and use it later

this has been my best attempt so far with LINQ but its about 50x slower

from g in ctx.Groups
where g.Name == groupName
let ruleCount = g.Rules.Select(r => r.Id).Distinct().Count()
from ri in g.Rules
from mem in ri
    .CurrentMembership
    .Where(m => m.IsMember)
    .Select(m => new { m.EntityKey, m.RuleInstanceId, ruleCount })
group mem by mem.EntityKeyinto ekGroup
let ruleCount = ekGroup.Any() ? ekGroup.FirstOrDefault().ruleCount : 0
where ekGroup.Count() == ruleCount
orderby ekGroup.Key
select ekGroup.Key

The generated SQL is a bit of a mess (I can post it if required) but there are 2 important differences between this and my hand coded SQL.

1. There's no declare statement - which means it does the actual @ruleInstanceCount over and over.
2. There's no having clause when it does the comparison of how many times it saw the rule instance.

I think its slow because of 1 but I don't know any way to do a declare or similar so that it only executes the count once.

Edit:

As requested here is the generated SQL

SELECT 
    [Project13].[EntityKey] AS [EntityKey]
    FROM ( SELECT 
        [Project12].[EntityKey] AS [EntityKey]
        FROM ( SELECT 
            [Project11].[EntityKey] AS [EntityKey], 
            [Project11].[C1] AS [C1], 
            [Project11].[C2] AS [C2], 
            [Project11].[C3] AS [C3], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM   [dbo].[Group] AS [Extent17]
                INNER JOIN [dbo].[GroupRuleInstance] AS [Extent18] ON [Extent17].[Id] = [Extent18].[Group_Id]
                INNER JOIN [dbo].[RuleInstanceCurrentMembershipCacheItem] AS [Extent19] ON ([Extent18].[RuleInstance_Id] = [Extent19].[RuleInstanceId]) AND ([Extent19].[IsMember] = 1)
                WHERE ((([Extent17].[Name] = @p__linq__0) AND ( NOT ([Extent17].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent17].[Name] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Project11].[EntityKey] = [Extent19].[EntityKey])) AS [C4]
            FROM ( SELECT 
                [Project10].[EntityKey] AS [EntityKey], 
                [Project10].[C1] AS [C1], 
                [Project10].[C2] AS [C2], 
                (SELECT 
                    COUNT(1) AS [A1]
                    FROM   [dbo].[Group] AS [Extent14]
                    INNER JOIN [dbo].[GroupRuleInstance] AS [Extent15] ON [Extent14].[Id] = [Extent15].[Group_Id]
                    INNER JOIN [dbo].[RuleInstanceCurrentMembershipCacheItem] AS [Extent16] ON ([Extent15].[RuleInstance_Id] = [Extent16].[RuleInstanceId]) AND ([Extent16].[IsMember] = 1)
                    WHERE ((([Extent14].[Name] = @p__linq__0) AND ( NOT ([Extent14].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent14].[Name] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Project10].[EntityKey] = [Extent16].[EntityKey])) AS [C3]
                FROM ( SELECT 
                    [Project9].[EntityKey] AS [EntityKey], 
                    [Project9].[C1] AS [C1], 
                    (SELECT 
                        COUNT(1) AS [A1]
                        FROM   [dbo].[Group] AS [Extent11]
                        INNER JOIN [dbo].[GroupRuleInstance] AS [Extent12] ON [Extent11].[Id] = [Extent12].[Group_Id]
                        INNER JOIN [dbo].[RuleInstanceCurrentMembershipCacheItem] AS [Extent13] ON ([Extent12].[RuleInstance_Id] = [Extent13].[RuleInstanceId]) AND ([Extent13].[IsMember] = 1)
                        WHERE ((([Extent11].[Name] = @p__linq__0) AND ( NOT ([Extent11].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent11].[Name] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Project9].[EntityKey] = [Extent13].[EntityKey])) AS [C2]
                    FROM ( SELECT 
                        [Project7].[EntityKey] AS [EntityKey], 
                        CASE WHEN ( EXISTS (SELECT 
                            1 AS [C1]
                            FROM   [dbo].[Group] AS [Extent8]
                            INNER JOIN [dbo].[GroupRuleInstance] AS [Extent9] ON [Extent8].[Id] = [Extent9].[Group_Id]
                            INNER JOIN [dbo].[RuleInstanceCurrentMembershipCacheItem] AS [Extent10] ON ([Extent9].[RuleInstance_Id] = [Extent10].[RuleInstanceId]) AND ([Extent10].[IsMember] = 1)
                            WHERE ((([Extent8].[Name] = @p__linq__0) AND ( NOT ([Extent8].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent8].[Name] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Project7].[EntityKey] = [Extent10].[EntityKey])
                        )) THEN [Project7].[C1] ELSE 0 END AS [C1]
                        FROM ( SELECT 
                            [Project6].[EntityKey] AS [EntityKey], 
                            [Project6].[C1] AS [C1]
                            FROM ( SELECT 
                                [Project2].[EntityKey] AS [EntityKey], 
                                (SELECT TOP (1) 
                                    [Project4].[C1] AS [C1]
                                    FROM    (SELECT 
                                        [Extent4].[Id] AS [Id], 
                                        (SELECT 
                                            COUNT(1) AS [A1]
                                            FROM ( SELECT DISTINCT 
                                                [Extent5].[RuleInstance_Id] AS [RuleInstance_Id]
                                                FROM [dbo].[GroupRuleInstance] AS [Extent5]
                                                WHERE [Extent4].[Id] = [Extent5].[Group_Id]
                                            )  AS [Distinct2]) AS [C1]
                                        FROM [dbo].[Group] AS [Extent4]
                                        WHERE (([Extent4].[Name] = @p__linq__0) AND ( NOT ([Extent4].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent4].[Name] IS NULL) AND (@p__linq__0 IS NULL)) ) AS [Project4]
                                    INNER JOIN [dbo].[GroupRuleInstance] AS [Extent6] ON [Project4].[Id] = [Extent6].[Group_Id]
                                    INNER JOIN [dbo].[RuleInstanceCurrentMembershipCacheItem] AS [Extent7] ON ([Extent6].[RuleInstance_Id] = [Extent7].[RuleInstanceId]) AND ([Extent7].[IsMember] = 1)
                                    WHERE [Project2].[EntityKey] = [Extent7].[EntityKey]) AS [C1]
                                FROM ( SELECT 
                                    [Distinct1].[EntityKey] AS [EntityKey]
                                    FROM ( SELECT DISTINCT 
                                        [Extent3].[EntityKey] AS [EntityKey]
                                        FROM   [dbo].[Group] AS [Extent1]
                                        INNER JOIN [dbo].[GroupRuleInstance] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Group_Id]
                                        INNER JOIN [dbo].[RuleInstanceCurrentMembershipCacheItem] AS [Extent3] ON ([Extent2].[RuleInstance_Id] = [Extent3].[RuleInstanceId]) AND ([Extent3].[IsMember] = 1)
                                        WHERE (([Extent1].[Name] = @p__linq__0) AND ( NOT ([Extent1].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))
                                    )  AS [Distinct1]
                                )  AS [Project2]
                            )  AS [Project6]
                        )  AS [Project7]
                    )  AS [Project9]
                )  AS [Project10]
            )  AS [Project11]
        )  AS [Project12]
        WHERE (([Project12].[C2] = [Project12].[C1]) AND ( NOT ([Project12].[C3] IS NULL OR [Project12].[C1] IS NULL))) OR (([Project12].[C4] IS NULL) AND ([Project12].[C1] IS NULL))
    )  AS [Project13]
    ORDER BY [Project13].[EntityKey] ASC

And model:

public class Group
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<RuleInstance> Rules { get; set; }
}

public class RuleInstance
{
    public Guid Id { get; set; }
    public ICollection<Group> Groups { get; set; }
    public ICollection<RuleInstanceCurrentMembershipCacheItem> CurrentMembership { get; set; }
}

public class RuleInstanceCurrentMembershipCacheItem
{
    public Guid RuleInstanceId { get; set; }
    public int EntityKey { get; set; }
    public RuleInstance RuleInstance { get; set; }
    public bool IsMember { get; set; }
}

Edit:

Based on @MarcinJuraszek's answer I settled on the following:

var ruleInstances = from g in ctx.Groups
            from ri in g.Rules
            where g.Name == groupName
            select ri.Id;

var cache = (   from g in ctx.Groups
        where g.Name == groupName
        from ri in g.Rules
        from mem in ri.CurrentMembership
        where mem.IsMember
        group mem by mem.AnimalKey into akGroup
        where akGroup.Count() == ruleInstances.Distinct().Count()
        orderby akGroup.Key
        select akGroup.Key).ToArray();

it produces the following query

SELECT 
[Project4].[EntityKey] AS [EntityKey]
FROM ( SELECT 
    [GroupBy1].[K1] AS [EntityKey]
    FROM     (SELECT 
        [Extent3].[EntityKey] AS [K1], 
        COUNT(1) AS [A1], 
        COUNT(1) AS [A2], 
        COUNT(1) AS [A3]
        FROM   [dbo].[Group] AS [Extent1]
        INNER JOIN [dbo].[GroupRuleInstance] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Group_Id]
        INNER JOIN [dbo].[RuleInstanceCurrentMembershipCacheItem] AS [Extent3] ON ([Extent2].[RuleInstance_Id] = [Extent3].[RuleInstanceId]) AND ([Extent3].[IsMember] = 1)
        WHERE (([Extent1].[Name] = @p__linq__0) AND ( NOT ([Extent1].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))
        GROUP BY [Extent3].[EntityKey] ) AS [GroupBy1]
    CROSS JOIN  (SELECT 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            [Extent5].[RuleInstance_Id] AS [RuleInstance_Id]
            FROM  [dbo].[Group] AS [Extent4]
            INNER JOIN [dbo].[GroupRuleInstance] AS [Extent5] ON [Extent4].[Id] = [Extent5].[Group_Id]
            WHERE (([Extent4].[Name] = @p__linq__1) AND ( NOT ([Extent4].[Name] IS NULL OR @p__linq__1 IS NULL))) OR (([Extent4].[Name] IS NULL) AND (@p__linq__1 IS NULL))
        )  AS [Distinct1] ) AS [GroupBy2]
    CROSS JOIN  (SELECT 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            [Extent7].[RuleInstance_Id] AS [RuleInstance_Id]
            FROM  [dbo].[Group] AS [Extent6]
            INNER JOIN [dbo].[GroupRuleInstance] AS [Extent7] ON [Extent6].[Id] = [Extent7].[Group_Id]
            WHERE (([Extent6].[Name] = @p__linq__1) AND ( NOT ([Extent6].[Name] IS NULL OR @p__linq__1 IS NULL))) OR (([Extent6].[Name] IS NULL) AND (@p__linq__1 IS NULL))
        )  AS [Distinct2] ) AS [GroupBy3]
    CROSS JOIN  (SELECT 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            [Extent9].[RuleInstance_Id] AS [RuleInstance_Id]
            FROM  [dbo].[Group] AS [Extent8]
            INNER JOIN [dbo].[GroupRuleInstance] AS [Extent9] ON [Extent8].[Id] = [Extent9].[Group_Id]
            WHERE (([Extent8].[Name] = @p__linq__1) AND ( NOT ([Extent8].[Name] IS NULL OR @p__linq__1 IS NULL))) OR (([Extent8].[Name] IS NULL) AND (@p__linq__1 IS NULL))
        )  AS [Distinct3] ) AS [GroupBy4]
    WHERE (([GroupBy1].[A1] = [GroupBy2].[A1]) AND ( NOT ([GroupBy1].[A2] IS NULL OR [GroupBy3].[A1] IS NULL))) OR (([GroupBy1].[A3] IS NULL) AND ([GroupBy4].[A1] IS NULL))
)  AS [Project4]
ORDER BY [Project4].[EntityKey] ASC

Answer 1

I would try with following:

var ctx = new Context();

var groupName = "something";

var ruleInstances = from g in ctx.Groups
                    from gri in g.Rules.DefaultIfEmpty()
                    where g.Name == groupName
                    select gri.Id;

var keys = from c in ctx.CurrentMembership
           let ri = c.RuleInstance
           from g in ri.Groups.DefaultIfEmpty()
           where c.IsMember && g.Name == groupName
           group c by c.EntityKey into g
           where g.Select(x => x.RuleInstanceId).Count() == ruleInstances.Distinct().Count()
           orderby g.Key
           select g.Key;

It should give you SQL quite close to:

SELECT cache.EntityKey
from RuleInstanceCurrentMembershipCacheItem cache
left join RuleInstance ri on ri.id = cache.RuleInstanceId 
left join GroupRuleInstance gri on ri.id = gri.RuleInstance_Id
left join [Group] g on g.id = gri.Group_Id 
where cache.IsMember = 1 and g.Name = @groupName 
group by cache.EntityKey
HAVING  count(cache.RuleInstanceId) =
       (select count(distinct(gri.RuleInstance_Id))
       from [Group] g  
       left join GroupRuleInstance gri on g.id = gri.Group_Id
       where g.name= @groupName)
order by cache.EntityKey

I assumed your context looks close to that one:

public class Context
{
    public IQueryable<Group> Groups { get; set; }
    public IQueryable<RuleInstance> Rules { get; set; }

    public IQueryable<RuleInstanceCurrentMembershipCacheItem> CurrentMembership { get; set; }
}

Answer 2

Your LINQ more different than your SQL. First off all, in your SQL you have two queries, but in LINQ you are trying to combine them in one. Why? I have tried to convert your SQL to LINQ, and i think, the generated SQL will be simular the raw SQL:

var ruleInstanceCount = (
    from g in Group
    join gri in GroupRuleInstance on g.id equals gri.Group_Id into gris
    from gri in gris.DefaultIfEmpty()
    where g.name == groupName
    group gri by gri.RuleInstance_Id into grp
    select new 
        {
            Id = grp.Key
        }
    ).Count();

var items = (
    from cache in RuleInstanceCurrentMembershipCacheItem
    join ri from RuleInstance on cache.RuleInstanceId equals ri.id into ris
    from ri in ris.DefaultIfEmpty()
    join gri from GroupRuleInstance on ri.id equals gri.RuleInstance_Id into gris
    from gri in gris.DefaultIfEmpty()
    join g from Group on gri.Group_Id equals g.id into gs
    from g in gs.DefaultIfEmpty()
    where cache.IsMember == 1 && g.Name = groupName
    group cache by cache.EntityKey into grp
    select new 
        {
            Count = grp.Count(x => x.RuleInstanceId),
            EntityKey = grp.Key
        })
        .Where(x => x.Count == ruleInstanceCount)
        .OrderBy(x => x.EntityKey);