convert sql query to c# lambda query

Question

Hi

I want to convert sql query to c# lambda query

selelct * from ABC where  joiningdate between (Select top 1  DateAdd(d, -90,joiningdate) from ABC order by joiningdate desc) and (Select top 1 joiningdate from ABC order by joiningdate desc)

I need to convert WHERE clause in c# lambda query

Thanks.

Amit Mohanty · Answer

var dbContext = new YourDbContext(); // Replace with your actual DbContext instance DateTime startDate = dbContext.ABC .OrderByDescending(item => item.JoiningDate) .Select(item => item.JoiningDate) .FirstOrDefault() .AddDays(-90); var result = dbContext.ABC .Where(item => item.JoiningDate >= startDate) .Where(item => item.JoiningDate item.JoiningDate)) .ToList();

Tahir Ansari · Answer

DateTime ninetyDaysAgo = DateTime.Now.AddDays(-90); var query = dbContext.ABC.OrderByDescending(x => x.JoiningDate); var result = dbContext.ABC.Where(item => item.JoiningDate >= query.Select(x => EntityFunctions.AddDays(x.JoiningDate, -90)).FirstOrDefault() && item.JoiningDate x.JoiningDate).FirstOrDefault() ).ToList(); dbContext.ABC.OrderByDescending(x => x.JoiningDate), and then reuse that query when selecting the minimum joining date and applying the AddDays() function. This approach prevents executing the same subquery multiple times and can result in better performance. Make sure to replace dbContext.ABC with your actual DbSet name and have the necessary using statements for Entity Framework.

Mohammad Hussain · Answer

Here is how you can convert that SQL WHERE clause to C# lambda query: var results = abc.Where(x => x.JoiningDate >= abc.OrderByDescending(y => y.JoiningDate).FirstOrDefault().JoiningDate.AddDays(-90) && x.JoiningDate y.JoiningDate).FirstOrDefault().JoiningDate); The WHERE clause is converted to a .Where() lambda expression The BETWEEN is handled by checking >= min date and The subqueries to get min and max dates are done using .OrderByDescending().FirstOrDefault() The DateAdd() is converted to .AddDays() The x refers to each entity in the main query The y refers to each entity in the subqueries This filters for entities in abc whose JoiningDate is between the max date minus 90 days and the max date overall.

Cr Bhargavi · Answer

using System; using System.Linq; class Program { static void Main(string[] args) { // Your collection of ABC objects (assuming you have a class named ABC) IQueryable abcCollection = ...; // Subquery to get the date for lower range var lowerDate = abcCollection .OrderByDescending(item => item.JoiningDate) .Select(item => item.JoiningDate) .FirstOrDefault(); // Subquery to get the date for upper range var upperDate = abcCollection .OrderBy(item => item.JoiningDate) .Select(item => item.JoiningDate) .FirstOrDefault(); // Construct the lambda query var result = abcCollection .Where(item => item.JoiningDate >= lowerDate.AddDays(-90) && item.JoiningDate