How can I convert this SQL query to a linq expression?
Select distinct Clients.Id From Clients Join Orders On Clients.Id = Orders.ClientsId and Orders.Date < DATEADD(DD, -Clients.Term, CAST(GETDATE() AS DATE))
Yes, I know about the Database.SqlQuery<>()
function. Probably it’s the best solution in terms of performance. But code uniformity is preferred, so I need to use linq.
Here is my solution:
var outdatedClients = context.Clients.GroupJoin(context.Orders, client => client.Id, order => order.ClientId, (client, order) => new { Id = client.Id, Term = client.Term, Orders = order.Any(order => DbFunctions.DiffDays(order.Date, _datenow) > client.Term) }) .ToList() .Where(client => client.Orders == true);
But maybe it`s possible to do it more elegantly?
Advertisement
Answer
This is all freehand so I apologize if there are some syntax issues…
Assuming you are using EF/EFCore and your classes are structured similar to the following:
public class Client { public Guid Id { get; set; } public int Term { get; set; } public virtual ICollection<Order> Orders { get; set; } } public class Order { public Guid Id { get; set; } public Guid ClientId { get; set; } public DateTime Date {get; set; } }
Then something like the following should match your SQL query?:
var clients = await context.Clients.Include(c => C.Orders) .Where(c => c.Orders.Any(o => o.Date < DateTime.Now.AddDays(-c.Term))) .ToArrayAsync();
You could also distinct the result, but assuming that ‘Id’ is your primary key for Client
then it shouldn’t be necessary as they will be unique.