How to convert SQL query to Linq in Entity Framework 6?

Question

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?

Matt Hope · Accepted Answer · 2021-02-05 12:33:21Z

1

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.

answered Feb 5, 2021 at 12:33

Matt Hope

3312 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kogonidze Over a year ago

Thanks for the answer. Considering of the size of db your query takes much more time but seems to be quite clear for maintain the code in the future.

Svyatoslav Danyliv Over a year ago

Include is not needed in this query.

Collectives™ on Stack Overflow

How to convert SQL query to Linq in Entity Framework 6?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related