I have Order, OrderDetail, and Vendor entities. How can I obtain annual, last 3 months' total sales data, and a total number of orders data on a vendor basis from the entities consisting of these sample data? I tried but I'm not very familiar with Linq. By the way, I am using EF Core 6.
I got an invalid anonymous type member when I try to implement linq on the dotnetfiddle.
using System;
using System.Linq;
using System.Collections.Generic;
public class Program
{
public static void Main()
{
// Order collection
IList<Order> orderList = new List<Order>() {
new Order() { Id = 1, OrderDateTime = new DateTime(2019, 11, 12, 22, 45, 12, 004), CustomerName = "ABC", Status = "Completed",DoneBy="Cenk"} ,
new Order() { Id = 2, OrderDateTime = new DateTime(2019, 11, 12, 22, 45, 12, 004), CustomerName = "A", Status = "Ccancelled",DoneBy="Cenk" } ,
new Order() { Id = 3, OrderDateTime = new DateTime(2022, 07, 12, 22, 45, 12, 004), CustomerName = "dsds", Status = "Contiues",DoneBy="Cenk" } ,
new Order() { Id = 4, OrderDateTime = new DateTime(2022, 08, 12, 22, 45, 12, 004) , CustomerName = "dsds", Status = "Completed",DoneBy="Cenk"} ,
new Order() { Id = 5, OrderDateTime = new DateTime(2022, 09, 12, 22, 45, 12, 004) , CustomerName = "jdsj",Status = "Completed",DoneBy="Cenk" }
};
// OrderDetail collection
IList<OrderDetail> orderDetailList = new List<OrderDetail>() {
new OrderDetail() { Id = 1, ProductCode = "12345", ProductName = "modem1",BuyQuantity=1,SellQuantity=10,CostRatio=2 , UnitCost=5,TotalBuyPrice=10,TotalSellPrice=15,Status = "Completed",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=1} ,
new OrderDetail() { Id = 2, ProductCode = "Erf7899", ProductName = "modem2",BuyQuantity=2,SellQuantity=10,CostRatio=2 , UnitCost=5,TotalBuyPrice=10,TotalSellPrice=15,Status = "Contiunes",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=1 } ,
new OrderDetail() { Id = 3, ProductCode = "0090owd", ProductName = "modem3",BuyQuantity=10,SellQuantity=15,CostRatio=2 , UnitCost=1,TotalBuyPrice=10,TotalSellPrice=15,Status = "Completed",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=1 } ,
new OrderDetail() { Id = 4, ProductCode = "fse929", ProductName = "modem4",BuyQuantity=11,SellQuantity=15,CostRatio=2 , UnitCost=5,TotalBuyPrice=10,TotalSellPrice=15,Status = "Cancelled",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=2} ,
new OrderDetail() { Id = 5, ProductCode = "1009ksks", ProductName = "modem5",BuyQuantity=19,SellQuantity=1,CostRatio=2 , UnitCost=1,TotalBuyPrice=10,TotalSellPrice=15,Status = "Completed",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=1 },
new OrderDetail() { Id = 6, ProductCode = "556dhdk", ProductName = "modem6",BuyQuantity=9,SellQuantity=13,CostRatio=2 , UnitCost=5,TotalBuyPrice=10,TotalSellPrice=15,Status = "Completed",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=2 } ,
new OrderDetail() { Id = 7, ProductCode = "99999", ProductName = "modem7",BuyQuantity=10,SellQuantity=14,CostRatio=2 , UnitCost=15,TotalBuyPrice=10,TotalSellPrice=15,Status = "Completed",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=2 },
new OrderDetail() { Id = 8, ProductCode = "00000", ProductName = "modem8",BuyQuantity=1,SellQuantity=15,CostRatio=2 , UnitCost=1,TotalBuyPrice=10,TotalSellPrice=15,Status = "Completed",ShippingNumber="Cenk", TrackingNumber="72827", Description="Test", OrderId=1, VendorId=1 }
};
// vendor collection
IList<Vendor> vendorList = new List<Vendor>() {
new Vendor() { Id = 1, Name = "Vendor1", Address = "ABC Address", Email = "Email", PhoneNumber="1234353", MainResponsibleName="test", AssistantResponsibleName="test1"} ,
new Vendor() { Id = 2, Name = "Vendor1", Address = "ABC Address", Email = "Email", PhoneNumber="1234353", MainResponsibleName="test", AssistantResponsibleName="test1" }
};
var data = orderList
.Select(o => new { o.OrderDateTime.Year, o.OrderDateTime.Month, o.OrderDetails.Sum(d => d.TotalSellPrice)})
.GroupBy(x => new { x.Year, x.Month }, (key, group) => new
{
yr = key.Year,
mnth = key.Month,
Price = group.Sum(k => k.TotalSellprice)
}).ToList();
}
}
public class Order
{
public int Id { get; set; }
public DateTime OrderDateTime { get; set; }
public string CustomerName { get; set; }
public string Status { get; set; }
public string DoneBy { get; set; }
public List<OrderDetail> OrderDetails { get; set; }
}
public class OrderDetail
{
public int Id { get; set; }
public string ProductCode { get; set; }
public string ProductName { get; set; }
public int BuyQuantity { get; set; }
public int SellQuantity { get; set; }
public double CostRatio { get; set; }
public double UnitCost { get; set; }
public double TotalBuyPrice { get; set; }
public double TotalSellPrice { get; set; }
public string ShippingNumber { get; set; }
public string Status { get; set; }
public string TrackingNumber { get; set; }
public string Description { get; set; }
public string Currency { 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; }
public string Name { get; set; }
public string Address { get; set; }
public string Email { get; set; }
public string PhoneNumber { get; set; }
public string MainResponsibleName { get; set; }
public string AssistantResponsibleName { get; set; }
public List<OrderDetail> OrderDetails { get; set; }
}
Edit 1
I ran the query without any errors on LinqPad, but I couldn't fetch the data as requested.
OrdersDetails.GroupBy(x => new { Year = x.Order.OrderDateTime.Year, Month = x.Order.OrderDateTime.Month })
.Select(u => new
{
Year = u.Key.Year,
Month = u.Key.Month,
TotalPrice = u.Sum(x => x.TotalSellPrice)
})
Sample data
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (1, N'2022-06-07 16:46:21', N'customer1', N'Completed', N'test1111')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (2, N'2022-06-08 00:00:00', N'Vestel', N'Continues', N'Cenk')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (3, N'2022-07-09 00:00:00', N'Arçelik', N'Cancelled', N'Cavit')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (4, N'2022-08-08 23:31:59', N'Fener', N'Continues', N'Test')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (5, N'2022-08-08 23:37:54', N'Ümraniye', N'Continues', N'Ümraniye')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (6, N'2021-08-04 23:43:28', N'Bruma', N'Completed', N'Gustova')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (7, N'2021-09-09 13:16:36', N'Custom', N'Completed', N'Test')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (8, N'2022-08-09 13:21:14', N'Tsubasa', N'Continues', N'Employee')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (9, N'2021-10-27 13:37:36', N'New Customer', N'Completed', N'Test')
SET IDENTITY_INSERT [dbo].[Orders] OFF
SET IDENTITY_INSERT [dbo].[OrdersDetail] ON
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (4, N'1', N'r', 12, 34, 2, 0, 56, 4, N'shipment', N'Completed', N'001', N'desc', 1, 2, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (5, N'12345-ABCN', N'Ipad', 5, 5, 10, 1000, 1000, 1500, N'S1', N'Getting ready', N'T11111', N'description1', 2, 1, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (6, N'0000-DDDD', N'Modem', 6, 6, 15, 200, 150, 225, N'S2', N'Getting ready', N'T11112', N'description2', 2, 2, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (7, N'98937-OPSKJ', N'ZTE', 7, 7, 10, 5000, 4500, 5500, N'S3', N'Getting ready', N'T11113', N'description3', 2, 1, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (8, N'11111-YUSH', N'Laptop', 8, 8, 6, 50, 50, 75, N'S4', N'Cancelled', N'T11114', N'description1', 3, 2, N'Dolar')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (9, N'0090', N'Knife', 20, 677, 40, 0, 700, 343, N'9202930', N'Completed', N'ueı9q9u', N'test', 1, 2, N'Euro')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (10, N'4', N'mouse', 12, 10, 10, 0, 10, 10, N's', N'Cancelled', N't', N'd', 3, 1, N'Euro')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (11, N'rrrrrrrr', N'tank palet', 100, 8878, 40, 0, 288, 39, N'trk*0292', N'Getting ready', N'79ıwowu998', N'desc', 4, 1, N'Dolar')
SET IDENTITY_INSERT [dbo].[OrdersDetail] OFF
CodePudding user response：
Since you are using EF Core, and have Navigation properties connecting your entities, you should just be able to use the Navigation properties to find the related entities and count or sum as necessary:
var ans = vendorList.Select(v => new {
Vendor = v,
Last3MonthsSales = v.OrderDetails
//.Where(od => od.Order.OrderDateTime >= DateTime.Now.AddMonths(-3))
.GroupBy(od => new { od.Order.OrderDateTime.Year, od.Order.OrderDateTime.Month })
.Select(odg => new {
odg.Key.Month,
Count = odg.Count(),
TotalSales = odg.Sum(od => od.TotalSellPrice)
})
.ToList()
});