Blazor
A free and open-source web framework that enables developers to create web apps using C# and HTML being developed by Microsoft.
1,386 questions
Try Where instead of Any.
Linq ThenInclude Where Problem
Cenk
956
Reputation points
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'kizildagcenk@gmail.com', 5)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (18, N'2022-09-04 12:07:17', N'Completed', N'user@test.com', 6)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (19, N'2022-09-09 14:12:51', N'Completed', N'kizildagcenk@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'testemail@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@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@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'testvendor@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'testemail@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@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'testcustomermail@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; }
}
Blazor
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,238 questions
4 answers
Sort by: Most helpful
-
Viorel 112.1K Reputation points2022-09-15T07:52:17.383+00:00 -
Cenk 956 Reputation points
2022-09-15T07:55:16.267+00:00 Orders.Include(d => d.OrderDetails).ThenInclude(v => v.Vendor).Where(o=> o.OrderDetails.Where(od => od.IsActive == 1)).ToListAsync();
CS0029 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<IMS.CoreBusiness.OrderDetail>' to 'bool'
-
Viorel 112.1K Reputation points
2022-09-15T08:08:11.763+00:00 Check if this part can be compiled in your environment:
_db.Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).
Sign in to comment -
-
Cenk 956 Reputation points
2022-09-15T08:04:45.103+00:00 Do you mean like this?
Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToListAsync(); -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 90%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EZL%3C/text%3E%3C/svg%3E)
Zhi Lv - MSFT 32,011 Reputation points Microsoft Vendor2022-09-16T07:32:41.93+00:00 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 -
Cenk 956 Reputation points
2022-09-16T07:45:17.573+00:00 @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();
-
Zhi Lv - MSFT 32,011 Reputation points Microsoft Vendor
2022-09-16T08:43:22.73+00:00 Hi @Cenk ,
Yes, you are right, the second whether clause is no need. Using the following query statement, we can get the same result.
_db.Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToList();In my previous test, I checked multiple query statements in the same time it might be related cache or something wrong, the above code doesn't return the correct data. So, in my reply, I add the second where clause. But now, if I only use this query statement, it also returns the same result. So there is no need to add the second where clause, thank for reminding me about it. Have a nice day!
Regards,
Dillion -
Viorel 112.1K Reputation points
2022-09-16T08:47:49.417+00:00 Maybe it is required if you do not want to get the orders that do not have any active detail. (You want non-empty OrderDetails only).
-
Cenk 956 Reputation points
2022-09-16T09:17:10.733+00:00 It is not allowed to have orders without details, thank you.
Sign in to comment -