Linq ThenInclude Where Problem

Question

Hello,

I am trying to get only IsActive=1 data with this Linq;

_db.Orders.Include(d => d.OrderDetails).ThenInclude(v => v.Vendor).Where(o=> o.OrderDetails.Any(od => od.IsActive == 1)).ToListAsync();  

But the query brings all the data with IsActive = 0 How can I fix it?

Here is my sample data;

SET IDENTITY_INSERT [dbo].[Orders] ON  
    INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (16, N'2022-08-29 16:58:17', N'Cancelled', N'******@gmail.com', 5)  
    INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (18, N'2022-09-04 12:07:17', N'Completed', N'******@test.com', 6)  
    INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (19, N'2022-09-09 14:12:51', N'Completed', N'******@gmail.com', 7)  
    SET IDENTITY_INSERT [dbo].[Orders] OFF  
      
    SET IDENTITY_INSERT [dbo].[OrdersDetail] ON  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (24, N'000001', N'Shirtt', 100, 14, 12.654, 1110, 1300, N'shipment1', N'Shipped', N'tracking1', N'description1', 16, 1, N'TL', 11.1, 13, N'fff', N'dff', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (25, N'000002', N'Gol', 50, 12, 22.512, 1005.0000000000001, 1250, N'shipment2', N'Being supplied', N'tracking2', N'description2', 16, 1, N'TL', 20.1, 25, N'h', N'g', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (26, N'000003', N'Excipal', 35, 6, 62.54, 2065, 2310, N'shipment3', N'Getting ready', N'tracking3', N'description3', 16, 2, N'TL', 59, 66, N'', N'', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (27, N'00004', N'Ball', 45, 12, 207.2, 8325, 9495, N'shipment4', N'Getting ready', N'tracking4', N'description4', 16, 3, N'TL', 185, 211, N'order0001', N'stok0001', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (28, N'00011', N'Atkı', 40, 11.5, 28.544, 1024, 12440, N'shipment 01', N'Completed', N'tracking 01', N'description 01', 18, 3, N'Euro', 25.6, 311, N'order 011', N'stock 001', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (29, N'0022', N'Şapka', 15, 12, 33.6, 450, 600, N'shipment 02', N'Completed', N'tracking 02', N'desc 02', 18, 1, N'Dolar', 30, 40, N'order 222', N'stock 022', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (30, N'05', N'Gözlük', 10, 20, 1020, 8500, 11000, N'ship05', N'Getting ready', N'track05', N'desc05', 16, 1, N'TL', 850, 1100, N'order05', N'stock05', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (31, N'06', N'Çanta', 20, 11.8, 385.4864, 6896, 8020, N'ship06', N'Shipped', N'track06', N'desc06', 16, 1, N'TL', 344.8, 401, N'order06', N'stock06', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (32, N'07', N'Atkı', 1, 10, 24.2, 22, 25, N'ship07', N'In warehouse', N'track07', N'desc07', 16, 1, N'TL', 22, 25, N'order07', N'stock07', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (33, N'08', N'Kalem', 200, 6.6, 21.32, 4000, 4580, N'ship08', N'At customs', N'track08', N'desc08', 16, 1, N'TL', 20, 22.9, N'order08', N'stock08', 1, 0)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (34, N'333', N'Lamp', 50, 11, 4.4289000000000005, 199.5, 256, N'abc shipment', N'Completed', N'1234 tracking', N'description', 19, 4, N'Dolar', 3.99, 5.12, N'12345 order', N'customer stokck', 0, 221.44500000000002)  
    INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (35, N'4444', N'Handset', 15, 17.5, 131.6, 1680, 2220, N'def shipment', N'Completed', N'569 tracking', N'description', 19, 3, N'Dolar', 112, 148, N'order customer 23242', N'stok 34opı', 1, 0)  
    SET IDENTITY_INSERT [dbo].[OrdersDetail] OFF  
      
    SET IDENTITY_INSERT [dbo].[Vendors] ON  
    INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (1, N'Test Vendor1', N'Test Address1', N'******@mail.com', N'123456789', N'responsible1', N'responsible2')  
    INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (2, N'Test vendor2', N'Address2', N'******@vendor.com', N'000000000011122', N'Main Resp1', N'Assitant Resp1')  
    INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (3, N'vendor 3', N'duadadajdajda', N'******@mail.comd', N'8329392842823', N'kdakdla daşd', N'dşakdşadad')  
    INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (4, N'Test Vendor 4', N'adress test adress', N'******@test.com', N'12345678', N'main responsible name', N'Assistant responsible name')  
    SET IDENTITY_INSERT [dbo].[Vendors] OFF  
      
    SET IDENTITY_INSERT [dbo].[Customers] ON  
    INSERT INTO [dbo].[Customers] ([Id], [TaxNumber], [TaxAdministration], [Name], [Address], [DeliveryAddress], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (5, 1234567888, N'Adıyaman Vergi Dairesi USA ', N'Customer 1', N'addresss', N'deliivery hero', N'******@test.com', N'(332) 2324242', N'Mikail', N'Şaban')  
    INSERT INTO [dbo].[Customers] ([Id], [TaxNumber], [TaxAdministration], [Name], [Address], [DeliveryAddress], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (6, 99009988, N'Tax Dairesi', N'Customer 2', N'tedaıjdajda saldkşlsakdşlsakdsa', N'dadasa safasfsadsadsdadsa sadsad sa', N'******@test.com', N'(666) 6666666', N'Main', N'asssddsfddfd')  
    INSERT INTO [dbo].[Customers] ([Id], [TaxNumber], [TaxAdministration], [Name], [Address], [DeliveryAddress], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (7, 13124214221421, N'Vali Konağı test', N'Test Customer 3', N'Customer Address Test', N'Delivery Address Test 04', N'******@mail.com', N'(213) 2323424', N'Responsible Name 04', N'Assistant Responsible 4')  
    SET IDENTITY_INSERT [dbo].[Customers] OFF  

Entities:

public class Order  
    {  
        public int Id { get; set; }  
          
        [Required]  
        public DateTime OrderDateTime { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public int CustomerId { get; set; }  
        public string Status { get; set; }  
        [MaxLength(50)]  
        public string DoneBy { get; set; }  
        public List<OrderDetail> OrderDetails { get; set; }  
        public Customer Customer { get; set; }  
  
  
    }  
	  
public class OrderDetail  
    {  
        public int Id { get; set; }  
          
        [Required]  
        [MaxLength(100)]  
        public string ProductCode { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public string ProductName { get; set; }  
        [Required]  
        public int Quantity { get; set; }  
        [Required]  
        public double BuyUnitPrice { get; set; }  
        public double CostRatio { get; set; }  
        public double UnitCost { get; set; }  
        public double TotalBuyPrice { get; set; }  
        public double SellUnitPrice { get; set; }  
        public double TotalSellPrice { get; set; }  
        [MaxLength(150)]  
        public string ShippingNumber { get; set; }  
        public string Status { get; set; }  
        [MaxLength(150)]  
        public string TrackingNumber { get; set; }  
        [MaxLength(400)]  
        public string Description { get; set; }  
        public string Currency { get; set; }  
        public string CustomerStockCode { get; set; }  
        public string CustomerOrderNumber { get; set; }  
        public int IsActive { get; set; }  
        public double TotalUnitCost { get; set; }  
        public int OrderId { get; set; }  
        public int VendorId { get; set; }  
        public Order Order { get; set; }  
        public Vendor Vendor { get; set; }  
         
    }  
public class Vendor  
    {  
        public int Id { get; set; }  
        [Required]  
        public string Name { get; set; }  
        [Required]  
        public string Address { get; set; }  
        [Required]  
        [RegularExpression(@"^((?!\.)[\w-_.]*[^.])(@\w+)(\.\w+(\.\w+)?[^.\W])$", ErrorMessage = "Invalid email address.")]  
        public string Email { get; set; }  
        [Required]  
        public string PhoneNumber { get; set; }  
        [Required]  
        public string MainResponsibleName { get; set; }  
        public string AssistantResponsibleName { get; set; }  
       public List<OrderDetail> OrderDetails { get; set; }  
  
    }  

Answer 1

Try Where instead of Any.

Answer 2

Do you mean like this?

Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToListAsync();  

Answer 3

Hi @Cenk ,

Do you mean like this?
Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToListAsync();

[Update]

If you want to get all Orders and its OrderDetails, but remove the IsActive is 0 items from the OrderDetails list, you can use the above code.

I create database using your sample data and use the following query statement, then the result as below: the OrderDetails only contains the items which IsActive is 1

_db.Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToList();

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 4

@Zhi Lv - MSFT Thank you for your reply. Why is the second Where needed?

Where(o => o.OrderDetails.Any( od => od.IsActive == 1 ))  

Isn't this enough?

Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToListAsync();