Linq Calculate current year and 3 months Cost

Cenk 956 Reputation points
2022-08-14T12:24:43.83+00:00

Hello,

I am trying to get the current year and last 3 months' cost but couldn't figure it out. Can you help me? By the way, I am using EF Core 6.

Thanks in advance. 

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" }   
  
 };  
  
 // LINQ Query Syntax to find out teenager students  
  
  var data = orderList.Select(o => new { o.OrderDateTime.Year, o.OrderDateTime.Month, o.OrderDetails.All(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; }  
  
    }
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
697 questions
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,236 questions
0 comments
Accepted answer
  1. Jack J Jun 24,286 Reputation points Microsoft Vendor
    2022-08-17T06:52:28.987+00:00

    @Cenk , Welcome to Microsoft Q&A, I make a code example to test your code and reproduced your problem.

    Also, I find some problems in your code.

    First, I find that your linq query does not have the relationship between entities. For example, we need to set the condition that Order.Id== OrderDetail.OrderId.

    Second, the code you provided does not check if OrderDateTime is last 3 months.

    Third, you defined all the orderId is 1 in the orderDetailList but you defined different orderId in your database insert.

    I make a code example to solve it and you could refer to it.

    code.txt

    Result:

    231829-image.png

    If the answer is the right solution, please click "Accept Answer" and 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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cenk 956 Reputation points
    2022-08-15T12:16:11.557+00:00

    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)  
              
        })

    231113-linqpad.png

    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) 

        })

    enter image description here

    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

    0 comments