convert sql query to c# lambda query

Ritu

3y1.1kC#

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

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 <= dbContext.ABC.Max(item => item.JoiningDate))
    .ToList();

Cr Bhargavi3y ago

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 <= upperDate)
            .ToList(); // Execute the query

        // Now 'result' contains the filtered items based on the given condition
    }
}

class ABC
{
    public DateTime JoiningDate { get; set; }
    // Other properties of ABC class
}

Tahir Ansari3y ago
```
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 <= query.Select(x => 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.
+3
Mohammad Hussain3y ago
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 <= abc.OrderByDescending(y => y.JoiningDate).FirstOrDefault().JoiningDate);
```
- The WHERE clause is converted to a .Where() lambda expression
- The BETWEEN is handled by checking >= min date and <= max date
- 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.
+2