Search code examples
c#linq.net-coreentity-framework-corelinq-to-entities

Nesting a LINQ query inside a where clause of another query


How would I combine the following two queries into a single query?

    using (var ctx = ContextFactory.CreateDbContext())
    {
        int parentId = await ctx.MenuEntity.Where(m => m.Title == thisVariable).Select(m => m.MenuId).SingleAsync();
        menuEntityList = await ctx.MenuEntity.Where(m => m.ParentId == parentId).ToListAsync();
    }

Solution

  • This query is equivalent to EXISTS in sql

     var menuEntityList = context.MenuEntity.Where(
     m => context.MenuEntity.Where(m =>
     m.Title == thisVariable).Any(d => d.MenuId == m.ParentId)
                    ).ToList();
    
    
    

    I get Query with Profiler

    exec sp_executesql N'SELECT [m].[Id], [m].[MenuId], [m].[ParentId], [m].[Title]
    FROM [MenuEntity] AS [m]
    WHERE EXISTS (
        SELECT 1
        FROM [MenuEntity] AS [m0]
        WHERE [m0].[Title] = @__thisVariable_0 
        AND [m0].[MenuId] = [m].[ParentId])'
    ,N'@__thisVariable_0 nvarchar(4000)',@__thisVariable_0=N'Org'