Please write linq query of the below sql query

Hello Sandeep,

You can write a single query and pass parameters to make it generic. please check the below code.

Public Class DBAccessClass
{
  public int getBatchCountByDate(DateTime FromDate, DateTime? ToDate,int DisposalStatus)
  {
   using (var context = new BV_OCMContext())
   {
     var count = context.TblBatch.Where(b => b.DisposalStatus == DisposalStatus && b.ScheduleDisposalOn >= FromDate && (ToDate != null ? b.ScheduleDisposalOn < ToDate : b.ScheduleDisposalOn = b.ScheduleDisposalOn).Count();
    
     return count;
   }
  }
}



// Call method
public class Program
{ 
  public static void Main(string[] args) 
  {
    DBAccessClass objDBAccessClass = new DBAccessClass();
    
    // Request 1: Current Date to Add 1 Day
    var result1 = objDBAccessClass.getBatchCountByDate(DateTime.Now.Date,DateTime.Now.Date.AddDays(1),17);
    Console.Write(result1);

    // Request 2: 8 day ago to current Date
    var result2 = objDBAccessClass.getBatchCountByDate(DateTime.Now.Date.AddDays(-8),DateTime.Now.Date,17);
    Console.Write(result2);

    // Request 3: greater than current date
    var result3 = objDBAccessClass.getBatchCountByDate(DateTime.Now.Date,null,17);
    Console.Write(result3);

    Console.ReadKey();
  }
}

Thanks

Vishal Joshi

Query 1:

using (var context = new BV_OCMContext())
{
    var currentDate = DateTime.Now.Date;
    var tomorrow = currentDate.AddDays(1);

    var count = context.TblBatch.Count(b => b.DisposalStatus == 17 && b.ScheduleDisposalOn >= currentDate && b.ScheduleDisposalOn < tomorrow);
    Console.WriteLine(count);
}

Query 2:

using (var context = new BV_OCMContext())
{
    var currentDate = DateTime.Now.Date;
    var eightDaysAgo = currentDate.AddDays(-8);

    var count = context.TblBatch.Count(b => b.DisposalStatus == 17 && b.ScheduleDisposalOn >= eightDaysAgo && b.ScheduleDisposalOn < currentDate);
    Console.WriteLine(count);
}

Query 3:

using (var context = new BV_OCMContext())
{
    var currentDate = DateTime.Now.Date.AddDays(1);

    var count = context.TblBatch.Count(b => b.DisposalStatus == 17 && b.ScheduleDisposalOn > currentDate);
    Console.WriteLine(count);
}

Make sure to replace "BV_OCMContext" and "TblBatch" with the appropriate names used in your application. Also, adjust the logic for getting the current date based on your system's timezone and requirements.