Selecting dataTable's data between date range

Thank you team, it's working now

public ActionResult GetTopTenBestSellingProducts(DateTime startDate, DateTime endDate)
{
    ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();
    
    var dataList = db.tblSales
        .Join(db.tblSalesDetails,
            sales => sales.SalesId,
            detail => detail.SalesId,
            (sales, detail) => new { Sales = sales, Detail = detail })
        .Where(x => x.Sales.OrderDate >= startDate && x.Sales.OrderDate <= endDate);
    
    var modifiedData = dataList
        .GroupBy(x => new { x.Detail.ProductId })
        .Select(x => new BestSellingProducts
        {
            SalesDetailId = x.FirstOrDefault().Detail.SalesDetailId,
            Quantity = x.Sum(s => s.Detail.Quantity),
            ProductId = x.Key.ProductId,
            ProductName = x.FirstOrDefault().Detail.tblProduct.ProductName,
            SalesId = x.FirstOrDefault().Sales.SalesId,
            TotalAmount = x.Sum(s => s.Sales.TotalAmount)
        })
        .OrderByDescending(x => x.Quantity)
        .Take(10)
        .ToList();
    
    var footerData = new BestSellingProducts
    {
        Quantity = modifiedData.Sum(x => x.Quantity),
        TotalAmount = modifiedData.Sum(x => x.TotalAmount)
    };
    
    modifiedData.Add(footerData);
    
    return Json(modifiedData, JsonRequestBehavior.AllowGet);
}

Hello,

You can use where clause to filter records,

  public ActionResult GetTopTenBestSellingProducts(DateTime startDate, DateTime endDate)
{
    ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();
    var dataList = db.tblSales
        .Join(
            db.tblSalesDetails,
            sales => sales.SalesId,
            detail => detail.SalesId,
            (sales, detail) => new { Sales = sales, Detail = detail }
        )
        .Where(x => x.Sales.OrderDate >= startDate && x.Sales.OrderDate <= endDate)
        .GroupBy(x => new { x.Detail.ProductId })
        .Select(x => new BestSellingProducts
        {
            Quantity = x.Sum(s => s.Detail.Quantity),
            ProductId = x.Key.ProductId,
            ProductName = x.FirstOrDefault().Detail.tblProduct.ProductName,
            TotalAmount = x.Sum(s => s.Sales.TotalAmount)
        })
        .OrderByDescending(x => x.Quantity)
        .Take(10)
        .ToList();

    // Calculate the total quantity and total amount
    int totalQuantity = dataList.Sum(x => x.Quantity);
    decimal totalAmount = dataList.Sum(x => x.TotalAmount);

    // Create a new object to hold the result with the footer data
    var responseData = new
    {
        Data = dataList,
        TotalQuantity = totalQuantity,
        TotalAmount = totalAmount
    };

    return Json(responseData, JsonRequestBehavior.AllowGet);
}
)
        {
           
            ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();
            var dataList = db.tblSales.Join(
               db.tblSalesDetails,
               sales => sales.SalesId,
               detail => detail.SalesId,
               (sales, detail) => new { Sales = sales, detail = detail });
            var modifiedData = dataList.GroupBy(x => new { x.Sales.OrderDate, x.detail.ProductId 
                            })
             .Where(x => x.OrderDate >= YourStartDate && x.OrderDate <= YourEndDate)
             .Select(x => new BestSellingProducts

            {
                SalesDetailId = x.FirstOrDefault().detail.SalesDetailId,
                Quantity = x.Sum(s => s.detail.Quantity),
                ProductId = x.Key.ProductId,
                ProductName = x.FirstOrDefault().detail.tblProduct.ProductName,
                SalesId = x.FirstOrDefault().Sales.SalesId,
                strOrderDate = x.Key.OrderDate,
                TotalAmount = x.Sum(s => s.Sales.TotalAmount)
            }).Take(10).ToList(); 

          
            return Json(responseData, JsonRequestBehavior.AllowGet);
        }

You can call above methos passing start date and End date.

var startDate = DateTime.Now.AddDays(-7);

var endDate =  DateTime.Now;

var result = GetTopTenBestSellingProducts(startDate,endDate);

To select data between a date range and display product quantity and total amount at the footer, you can modify your existing code as follows:

public ActionResult GetTopTenBestSellingProducts(DateTime startDate, DateTime endDate)
{
    ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();
    var dataList = db.tblSales
        .Join(
            db.tblSalesDetails,
            sales => sales.SalesId,
            detail => detail.SalesId,
            (sales, detail) => new { Sales = sales, Detail = detail }
        )
        .Where(x => x.Sales.OrderDate >= startDate && x.Sales.OrderDate <= endDate)
        .GroupBy(x => new { x.Detail.ProductId })
        .Select(x => new BestSellingProducts
        {
            Quantity = x.Sum(s => s.Detail.Quantity),
            ProductId = x.Key.ProductId,
            ProductName = x.FirstOrDefault().Detail.tblProduct.ProductName,
            TotalAmount = x.Sum(s => s.Sales.TotalAmount)
        })
        .OrderByDescending(x => x.Quantity)
        .Take(10)
        .ToList();

    // Calculate the total quantity and total amount
    int totalQuantity = dataList.Sum(x => x.Quantity);
    decimal totalAmount = dataList.Sum(x => x.TotalAmount);

    // Create a new object to hold the result with the footer data
    var responseData = new
    {
        Data = dataList,
        TotalQuantity = totalQuantity,
        TotalAmount = totalAmount
    };

    return Json(responseData, JsonRequestBehavior.AllowGet);
}