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();
}
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'