Query needs to be filtered after .ThenInclude using .Where clause

user_5955138 0
Viewed 388

Im using following query to filter my BranchId as I want.

var query = await _dbContext.TargetItems                
.Include(i => i.BranchTargetItem)
.ThenInclude(i => i.BranchTarget)                
.Where(x=> x.BranchTargetItem.Any(x=> x.BranchTarget.Any(x=> x.BranchId.Equals("9417"))))
.ToListAsync(cancellationToken);

Above expression creates following SQL, I think .Any() cause these Left Join statements

SELECT *
  FROM PERF_TARGET_ITEMS "p"
  LEFT JOIN (SELECT *
               FROM PERF_BRANCH_TARGET_ITEMS "p0"
               LEFT JOIN PERF_BRANCH_TARGETS "p1"
                 ON "p0".BTI_ID = "p1".BT_BRANCH_TARGET_ITEM_ID) "t"
    ON "p".TI_ID = "t".BTI_ITEM_TARGET_ITEM_ID
 WHERE EXISTS (SELECT 1
          FROM PERF_BRANCH_TARGET_ITEMS "p2"
         WHERE ("p".TI_ID = "p2".BTI_ITEM_TARGET_ITEM_ID)
           AND EXISTS
         (SELECT 1
                  FROM PERF_BRANCH_TARGETS "p3"
                 WHERE ("p2".BTI_ID = "p3".BT_BRANCH_TARGET_ITEM_ID)
                   AND ("p3".BT_BRANCH_ID = N'9417')))
 ORDER BY "p".TI_ID, "t".BTI_ID, "t".BT_ID

But I want to convert Left Join to Inner Join as following,

  SELECT *
    FROM WESTCORE.PERF_BRANCH_TARGET_ITEMS "p"
   INNER JOIN WESTCORE.PERF_PERIODS "p0"
      ON "p".BTI_ITEM_PERIOD_ID = "p0".P_ID
   INNER JOIN WESTCORE.PERF_TARGET_ITEMS "p1"
      ON "p".BTI_ITEM_TARGET_ITEM_ID = "p1".TI_ID
    LEFT JOIN (SELECT *
                 FROM WESTCORE.PERF_BRANCH_TARGETS "p2"
                WHERE "p2".BT_BRANCH_ID = '9417') "t"
      ON "p".BTI_ID = "t".BT_BRANCH_TARGET_ITEM_ID
   WHERE ((SELECT COUNT(*)
             FROM WESTCORE.PERF_BRANCH_TARGETS "p3"
            WHERE ("p".BTI_ID = "p3".BT_BRANCH_TARGET_ITEM_ID)
              AND ("p3".BT_BRANCH_ID = '9417')) > 0)
   ORDER BY "p".BTI_ID, "p0".P_ID, "p1".TI_ID, "t".BT_ID

I want to return TargetItems with included its relational entities(TargetItems > BranchTargetItems > BranchTargets)

//TargetItem.cs
    ...
    public virtual ICollection<BranchTargetItem> BranchTargetItem { get; set; }
    ...

//BranchTargetItem.cs
    ...
    public virtual ICollection<BranchTarget> BranchTarget { get; set; }
    ...

//BranchTarget.cs
    ...
    public virtual BranchTargetItem BranchTargetItem { get; set; }
    ...

And here are my relationships,

//TargetItemConfiguration.cs

    builder.HasMany(x => x.BranchTargetItem).WithOne(x => x.TargetItem).HasForeignKey(x => x.ItemTargetItemId).IsRequired();

//BranchTargetItemConfiguration.cs
    builder.HasMany(x => x.BranchTarget).WithOne(x => x.BranchTargetItem).HasForeignKey(x => x.BranchTargetItemId).IsRequired();

EDIT:

All I want to add this filter, .ThenInclude( bti=> bti.BranchTarget).Where( bt=> bt.BranchId.Equals("9417")) to following query,

    var query = await _dbContext.TargetItems
        .Include(ti => ti.BranchTargetItem)
        .ThenInclude( bti=> bti.BranchTarget)
        .ToListAsync(cancellationToken);

EDIT:

I've fixed the issue using following query, but I'm still looking for ideal solution.

    var targetItems = await _dbContext.TargetItems
        .Include(ti => ti.BranchTargetItem)
        .ThenInclude(bti => bti.BranchTarget)
        .ToListAsync(cancellationToken);

    foreach (TargetItem ti in targetItems)
    {
        foreach (BranchTargetItem bti in ti.BranchTargetItem)
        {
            bti.BranchTarget = bti.BranchTarget.Where(bt => bt.BranchId.Equals(branchId)).ToList();
        }
    }

EDIT:

I also tried this approach, TargetItems entity has one-to-many relationship with itself here, [DataMember] public virtual ICollection<TargetItem> TargetItems { get; set; } When I apply below code, child nodes are being empty. It only returns first nodes.

    var targetItems = await _dbContext.BranchTargets
                    .Where(x => x.BranchId.Equals("9417"))
                    .Include(t => t.BranchTargetItem)
                    .ThenInclude(t => t.TargetItem)
                    .Select(x=> x.BranchTargetItem.TargetItem)
                    .ToListAsync(cancellationToken);
entity-framework-core .net-core entity-framework c#
5 Answers

It is not Any you should worry about. Any -> EXISTS. Your problem is Include, it translates into LEFT OUTER JOIN. So you need extra filtration, something like this:

var query = await _dbContext.TargetItems
.Include(i => i.BranchTargetItem)
.Where(e => e.BranchTargetItem != null) //added filtration
.ThenInclude(i => i.BranchTarget)
.Where(e => e.BranchTarget != null) //added filtration
.Where(x => x.BranchTargetItem.Any(x => x.BranchTarget.Any(x => x.BranchId.Equals("9417"))))
.ToListAsync(cancellationToken);
user_75923900

If you need INNER JOIN you'll need to use Join instead. The expected SQL query can be optimized and also simplified to this :

SELECT *
FROM 
    WESTCORE.PERF_BRANCH_TARGET_ITEMS p
INNER JOIN WESTCORE.PERF_PERIODS p0 ON p.BTI_ITEM_PERIOD_ID = p0.P_ID
INNER JOIN WESTCORE.PERF_TARGET_ITEMS p1 ON p.BTI_ITEM_TARGET_ITEM_ID = p1.TI_ID
INNER JOIN WESTCORE.PERF_BRANCH_TARGETS p2 ON p.BTI_ID = p2.BT_BRANCH_TARGET_ITEM_ID
WHERE 
    p2.BT_BRANCH_ID = '9417'
ORDER BY 
    p.BTI_ID
,   p0.P_ID
,   p1.TI_ID
,   p2.BT_BRANCH_TARGET_ITEM_ID

To apply this, you would do something like this :

var query = await _dbContext.BranchTargetItem
            .Join(_dbContext.TargetItems,
                bti => bti.BranchId, 
                ti  => ti.BranchId,
                (bti, ti) => new { TargetItems = ti, BranchTargetItem = bti })
            .Join(_dbContext.BranchTarget,
                ti => ti.TargetItems.BranchId,
                bt => bt.BranchId, 
                (bti, ti) => new { TargetItems = ti, BranchTarget = bt })           
            .Where(x=> x.TargetItems.BranchId.Equals("9417"))
            .ToListAsync(cancellationToken);

The query itself needs to be tested and adjusted to the correct entities.

user_23220990

If you only want the BranchTarget that match a where clause, you need to make that the primary detail record of your query, then include its parent records;

var query = await _dbContext.BranchTarget
    .Where(x => x.BranchId.Equals("9417"))
    .Include(t => t.BranchTargetItems)
    .ThenInclude(t => t.TargetItems)
    .ToListAsync(cancellationToken);
user_41398090

Answer to original question

Im using following query to filter my BranchId as I want.

var query = await _dbContext.TargetItems                
.Include(i => i.BranchTargetItem)
.ThenInclude(i => i.BranchTarget)                
.Where(x=> x.BranchTargetItem.Any(x=> x.BranchTarget.Any(x=> x.BranchId.Equals("9417"))))
.ToListAsync(cancellationToken);

But I want to convert Left Join to Inner Join [...]

To solve your original question, a simple solution is to just reverse the order of your includes:

var branchTargets = context.BranchTargets                
    .Include(bt => bt.BranchTargetItem)
        .ThenInclude(bti => bti.TargetItem)                
    .Where(bt => bt.BranchId == "9417")
    .ToList();

This will use INNER JOINs and return the BranchTarget entities, from where you can get to your TargetItem entities via branchTargets[0].BranchTargetItem.TargetItem:

SELECT [p].[BT_BRANCH_TARGET_ITEM_ID], [p].[BT_BRANCH_ID ], [p].[BT_ID], [p0].[BTI_ID], [p0].[BTI_ITEM_TARGET_ITEM_ID], [p1].[TI_ID]
FROM [PERF_BRANCH_TARGETS ] AS [p]
INNER JOIN [PERF_BRANCH_TARGET_ITEMS] AS [p0] ON [p].[BT_ID] = [p0].[BTI_ID]
INNER JOIN [PERF_TARGET_ITEMS] AS [p1] ON [p0].[BTI_ITEM_TARGET_ITEM_ID] = [p1].[TI_ID]
WHERE [p].[BT_BRANCH_ID ] = N'9417'

If you don't want the query to return BranchTarget as the root entities, you can just do the following:

var targetItems = context.BranchTargets                
    .Include(bt => bt.BranchTargetItem)
        .ThenInclude(bti => bti.TargetItem)                
    .Where(bt => bt.BranchId == "9417")
    .AsEnumerable()
    .Select(bt => bt.BranchTargetItem.TargetItem)
    .ToList();

Here is a fully working sample console project, that demonstrates this approach:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class TargetItem
    {
        public int TargetItemId { get; set; }

        public ICollection<BranchTargetItem> BranchTargetItem { get; set; }
    }

    public class BranchTargetItem
    {
        public int BranchTargetItemId { get; set; }
        public int ItemTargetItemId { get; set; }
        
        public TargetItem TargetItem { get; set; }
        public ICollection<BranchTarget> BranchTarget { get; set; }
    }

    public class BranchTarget
    {
        public int BranchTargetId { get; set; }
        public string BranchId { get; set; }
        public int BranchTargetItemId { get; set; }

        public BranchTargetItem BranchTargetItem { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<TargetItem> TargetItems { get; set; }
        public DbSet<BranchTargetItem> BranchTargetItems { get; set; }
        public DbSet<BranchTarget> BranchTargets { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(
                    @"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63636238")
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TargetItem>(
                entity =>
                {
                    entity.ToTable("PERF_TARGET_ITEMS");

                    entity.Property(e => e.TargetItemId)
                        .HasColumnName("TI_ID");

                    entity.HasMany(x => x.BranchTargetItem)
                        .WithOne(x => x.TargetItem)
                        .HasForeignKey(x => x.ItemTargetItemId);

                    entity.HasData(
                        new TargetItem {TargetItemId = 1},
                        new TargetItem {TargetItemId = 2},
                        new TargetItem {TargetItemId = 3});
                });

            modelBuilder.Entity<BranchTargetItem>(
                entity =>
                {
                    entity.ToTable("PERF_BRANCH_TARGET_ITEMS");

                    entity.Property(e => e.BranchTargetItemId)
                        .HasColumnName("BTI_ID");
                    entity.Property(e => e.ItemTargetItemId)
                        .HasColumnName("BTI_ITEM_TARGET_ITEM_ID");
                    
                    entity.HasMany(x => x.BranchTarget)
                        .WithOne(x => x.BranchTargetItem)
                        .HasForeignKey(x => x.BranchTargetItemId);
                    
                    entity.HasData(
                        new BranchTargetItem {BranchTargetItemId = 11, ItemTargetItemId = 1},
                        new BranchTargetItem {BranchTargetItemId = 22, ItemTargetItemId = 2},
                        new BranchTargetItem {BranchTargetItemId = 33, ItemTargetItemId = 3},
                        new BranchTargetItem {BranchTargetItemId = 41, ItemTargetItemId = 1});
                });
            
            modelBuilder.Entity<BranchTarget>(
                entity =>
                {
                    entity.ToTable("PERF_BRANCH_TARGETS ");

                    entity.Property(e => e.BranchTargetItemId)
                        .HasColumnName("BT_ID");
                    entity.Property(e => e.BranchId)
                        .HasColumnName("BT_BRANCH_ID ");
                    entity.Property(e => e.BranchTargetId)
                        .HasColumnName("BT_BRANCH_TARGET_ITEM_ID");
                    
                    entity.HasData(
                        new BranchTarget {BranchTargetId = 100, BranchId = "9417", BranchTargetItemId = 11},
                        new BranchTarget {BranchTargetId = 200, BranchId = "9417", BranchTargetItemId = 41},
                        new BranchTarget {BranchTargetId = 300, BranchId = "1234", BranchTargetItemId = 22});
                });
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            
            // Your original query:
            //
            // var result = context.TargetItems                
            //     .Include(ti => ti.BranchTargetItem)
            //         .ThenInclude(bti => bti.BranchTarget)                
            //     .Where(ti => ti.BranchTargetItem.Any(bti => bti.BranchTarget.Any(bt => bt.BranchId.Equals("9417"))))
            //     .ToList();
            //
            // Debug.Assert(result.Count == 2);
            // Debug.Assert(result[0].BranchTargetItem != null);
            // Debug.Assert(result[0].BranchTargetItem.Count == 2);
            // Debug.Assert(result[0].BranchTargetItem.First().BranchTargetItemId == 11);
            // Debug.Assert(result[0].BranchTargetItem.First().BranchTarget != null);
            // Debug.Assert(result[0].BranchTargetItem.First().BranchTarget.Count == 1);

            var result = context.BranchTargets                
                .Include(bt => bt.BranchTargetItem)
                    .ThenInclude(bti => bti.TargetItem)                
                .Where(bt => bt.BranchId == "9417")
                .AsEnumerable()
                .Select(bt => bt.BranchTargetItem.TargetItem)
                .OrderBy(bt => bt.TargetItemId)
                .ToList();

            Debug.Assert(result.Count == 2);
            Debug.Assert(result[0].BranchTargetItem != null);
            Debug.Assert(result[0].BranchTargetItem.Count == 2);
            Debug.Assert(result[0].BranchTargetItem.First().BranchTargetItemId == 11);
            Debug.Assert(result[0].BranchTargetItem.First().BranchTarget != null);
            Debug.Assert(result[0].BranchTargetItem.First().BranchTarget.Count == 1);
        }
    }
}

Answer to follow-up question regarding recursive navigation properties

With traditional approaches, you usually have the following choices:

  • If you know how many levels deep you want to query your recursive structure, just add additional .ThenInclude(ti => ti.TargetItems) clauses.
  • Iterate over the returned items and execute additional queries.
  • Just eager load all TargetItem entities.

The following fully working sample project demonstrates a couple of approaches:

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class TargetItem
    {
        public int TargetItemId { get; set; }
        public int? ParentTargetItemId { get; set; }

        public TargetItem ParentTargetItem { get; set; }
        public ICollection<BranchTargetItem> BranchTargetItem { get; set; }
        public ICollection<TargetItem> TargetItems { get; set; }
    }

    public class BranchTargetItem
    {
        public int BranchTargetItemId { get; set; }
        public int ItemTargetItemId { get; set; }
        
        public TargetItem TargetItem { get; set; }
        public ICollection<BranchTarget> BranchTarget { get; set; }
    }

    public class BranchTarget
    {
        public int BranchTargetId { get; set; }
        public string BranchId { get; set; }
        public int BranchTargetItemId { get; set; }

        public BranchTargetItem BranchTargetItem { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<TargetItem> TargetItems { get; set; }
        public DbSet<BranchTargetItem> BranchTargetItems { get; set; }
        public DbSet<BranchTarget> BranchTargets { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(
                    @"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63636238_01")
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TargetItem>(
                entity =>
                {
                    entity.ToTable("PERF_TARGET_ITEMS");

                    entity.Property(e => e.TargetItemId)
                        .HasColumnName("TI_ID");

                    entity.HasMany(x => x.BranchTargetItem)
                        .WithOne(x => x.TargetItem)
                        .HasForeignKey(x => x.ItemTargetItemId);

                    entity.HasMany(x => x.TargetItems)
                        .WithOne(x => x.ParentTargetItem)
                        .HasForeignKey(x => x.ParentTargetItemId);

                    entity.HasData(
                        new TargetItem {TargetItemId = 1, ParentTargetItemId = null},
                        new TargetItem {TargetItemId = 2, ParentTargetItemId = 1},
                        new TargetItem {TargetItemId = 3, ParentTargetItemId = 1},
                        new TargetItem {TargetItemId = 4, ParentTargetItemId = 2});
                });

            modelBuilder.Entity<BranchTargetItem>(
                entity =>
                {
                    entity.ToTable("PERF_BRANCH_TARGET_ITEMS");

                    entity.Property(e => e.BranchTargetItemId)
                        .HasColumnName("BTI_ID");
                    entity.Property(e => e.ItemTargetItemId)
                        .HasColumnName("BTI_ITEM_TARGET_ITEM_ID");
                    
                    entity.HasMany(x => x.BranchTarget)
                        .WithOne(x => x.BranchTargetItem)
                        .HasForeignKey(x => x.BranchTargetItemId);
                    
                    entity.HasData(
                        new BranchTargetItem {BranchTargetItemId = 11, ItemTargetItemId = 1},
                        new BranchTargetItem {BranchTargetItemId = 22, ItemTargetItemId = 2},
                        new BranchTargetItem {BranchTargetItemId = 33, ItemTargetItemId = 3},
                        new BranchTargetItem {BranchTargetItemId = 41, ItemTargetItemId = 1});
                });
            
            modelBuilder.Entity<BranchTarget>(
                entity =>
                {
                    entity.ToTable("PERF_BRANCH_TARGETS ");

                    entity.Property(e => e.BranchTargetItemId)
                        .HasColumnName("BT_ID");
                    entity.Property(e => e.BranchId)
                        .HasColumnName("BT_BRANCH_ID ");
                    entity.Property(e => e.BranchTargetId)
                        .HasColumnName("BT_BRANCH_TARGET_ITEM_ID");
                    
                    entity.HasData(
                        new BranchTarget {BranchTargetId = 100, BranchId = "9417", BranchTargetItemId = 11},
                        new BranchTarget {BranchTargetId = 200, BranchId = "9417", BranchTargetItemId = 41},
                        new BranchTarget {BranchTargetId = 300, BranchId = "1234", BranchTargetItemId = 22});
                });
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            SetupDatabase();

            Approach1();
            Approach2();
            Approach3();
        }

        private static void Approach1()
        {
            using var context = new Context();
            var targetItems = GetTargetItems(context);

            var handled = new HashSet<TargetItem>();
            var queue = new Queue<TargetItem>(targetItems);
            while (queue.TryDequeue(out var targetItem))
            {
                handled.Add(targetItem);
                context.Entry(targetItem)
                    .Collection(ti => ti.TargetItems)
                    .Load();

                foreach (var childTargetItem in targetItem.TargetItems)
                {
                    if (!handled.Contains(childTargetItem))
                    {
                        queue.Enqueue(childTargetItem);
                    }
                }
            }

            AssertTargetItems(targetItems);
        }

        private static void Approach2()
        {
            using var context = new Context();
            var targetItems = GetTargetItems(context);

            var handled = new HashSet<TargetItem>();
            var queue = new Queue<TargetItem>(targetItems);
            while (queue.TryDequeue(out var targetItem))
            {
                handled.Add(targetItem);

                var childTargetItems = context.TargetItems
                    .Where(ti => ti.ParentTargetItemId == targetItem.TargetItemId)
                    .ToList();

                foreach (var childTargetItem in childTargetItems)
                {
                    if (!handled.Contains(childTargetItem))
                    {
                        queue.Enqueue(childTargetItem);
                    }
                }
            }

            AssertTargetItems(targetItems);
        }

        private static void Approach3()
        {
            using var context = new Context();
            var targetItems = GetTargetItems(context);
            
            context.TargetItems.Load();
            
            AssertTargetItems(targetItems);
        }

        private static void AssertTargetItems(List<TargetItem> targetItems)
        {
            Debug.Assert(targetItems.Count == 2);
            Debug.Assert(targetItems[0].BranchTargetItem != null);
            Debug.Assert(targetItems[0].BranchTargetItem.Count == 2);
            Debug.Assert(targetItems[0].BranchTargetItem.First().BranchTargetItemId == 11);
            Debug.Assert(targetItems[0].BranchTargetItem.First().BranchTarget != null);
            Debug.Assert(targetItems[0].BranchTargetItem.First().BranchTarget.Count == 1);
            Debug.Assert(targetItems[0].TargetItemId == 1);
            Debug.Assert(targetItems[0].TargetItems != null);
            Debug.Assert(targetItems[0].TargetItems.Count == 2);
            Debug.Assert(targetItems[0].TargetItems.First().TargetItems != null);
            Debug.Assert(targetItems[0].TargetItems.First().TargetItems.Count == 1);
        }

        private static List<TargetItem> GetTargetItems(Context context)
            => context.BranchTargets                
                .Include(bt => bt.BranchTargetItem)
                .ThenInclude(bti => bti.TargetItem)
                .Where(bt => bt.BranchId == "9417")
                .AsEnumerable()
                .Select(bt => bt.BranchTargetItem.TargetItem)
                .OrderBy(bt => bt.TargetItemId)
                .ToList();

        private static void SetupDatabase()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }
    }
}
user_26183190

You can use slightly different syntax (linq-to-sql):

var query = (
    from ti in _dbContext.TargetItems
    join bti in _dbContext.BranchTargetItem on ti.Id = bti.ItemTargetItemId
    join bt in _dbContext.BranchTarget on bti.Id = bt.BranchTargetItemId
    where bt.BranchId.Equals("9417")
    select new {
           ti, 
           btis = ti.BranchTargetItem, 
           bts  = ti.BranchTargetItem.SelectMany(x=> x.BranchTarget)
       }
).ToListAsync();

This will load whatever you want in single call to db without include magic.

user_56396880
Related