using Z.EntityFramework.Plus;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
public static void Main()
using (var context = new EntityContext())
context.Database.EnsureCreated();
using (var context = new EntityContext())
QueryIncludeOptimizedManager.AllowIncludeSubPath = true;
QueryIncludeOptimizedManager.AllowQueryBatch=true;
var list = context.Orders
.IncludeOptimized(x => x.Items.Select(o=>o.OrderItemDetails))
.IncludeOptimized(x => x.Items.SelectMany(o=>o.OrderItemDetails).Select(y=>y.Gifts)).ToList();
var list2 = context.Orders
.IncludeOptimized(x => x.Items.Select(o=>o.OrderItemDetails))
.IncludeOptimized(x => x.Items.Select(o=>o.OrderItemDetails.Select(y=>y.Gifts))).ToList();
foreach(var order in list)
Console.WriteLine("Order items in {0}: {1}", order.Name, order.Items.Count());
foreach (var orderItem in order.Items)
Console.WriteLine("Order item Id:{0} details count {1}",orderItem.OrderItemId,orderItem.OrderItemDetails.Count());
foreach (var orderItemDetail in orderItem.OrderItemDetails)
Console.WriteLine("Order Item Detail Id: {0},Gifts count {1}",orderItemDetail.OrderItemDetailId,orderItemDetail.Gifts.Count());
public static void GenerateData()
using (var context = new EntityContext())
context.Orders.Add(new Order() { Name = "Order_A", Date = DateTime.Now.AddDays(-4), IsSoftDeleted = true });
context.Orders.Add(new Order() { Name = "Order_B", Date = DateTime.Now.AddDays(-5), IsSoftDeleted = true });
context.Items.Add(new OrderItem() { Date = DateTime.Now.AddDays(-4), IsSoftDeleted = false, Price = 35, OrderId = 1 });
context.Items.Add(new OrderItem() { Date = DateTime.Now.AddDays(-4), IsSoftDeleted = false, Price = 22, OrderId = 1 });
context.Items.Add(new OrderItem() { Date = DateTime.Now.AddDays(-5), IsSoftDeleted = false, Price = 37, OrderId = 2 });
context.Items.Add(new OrderItem() { Date = DateTime.Now.AddDays(-5), IsSoftDeleted = true, Price = 7, OrderId = 2 });
context.OrderItemDetails.Add(new OrderItemDetail() { OrderItemId = 1 });
context.OrderItemDetails.Add(new OrderItemDetail() { OrderItemId = 1 });
context.OrderItemDetails.Add(new OrderItemDetail() { OrderItemId = 2 });
context.OrderItemDetails.Add(new OrderItemDetail() {OrderItemId = 2 });
context.Gifts.Add(new Gift() { OrderItemDetailId = 1 });
context.Gifts.Add(new Gift() { OrderItemDetailId = 1 });
context.Gifts.Add(new Gift() { OrderItemDetailId = 2 });
context.Gifts.Add(new Gift() { OrderItemDetailId = 2 });
public class EntityContext : DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));
base.OnConfiguring(optionsBuilder);
public DbSet<Order> Orders { get; set; }
public DbSet<OrderItem> Items { get; set; }
public DbSet<OrderItemDetail> OrderItemDetails { get; set; }
public DbSet<Gift> Gifts { get; set; }
public int OrderId { get; set; }
public string Name { get; set; }
public DateTime Date { get; set; }
public bool IsSoftDeleted { get; set; }
public List<OrderItem> Items { get; set; }
public int OrderItemId { get; set; }
public DateTime Date { get; set; }
public bool IsSoftDeleted { get; set; }
public int Price { get; set; }
public int OrderId { get; set; }
public List<OrderItemDetail> OrderItemDetails{get;set;}
public class OrderItemDetail
public int OrderItemDetailId {get;set;}
public int OrderItemId { get; set; }
public List<Gift> Gifts{get;set;}
public int GiftId {get;set;}
public int OrderItemDetailId { get; set; }