CODEWITHSUNDEEP
c#sql-serverlinqlambda
somedev
somedev

Reputation: 1053

Convert this SQL Query to a Linq Lambda Expression

I have the following SQL:

select Monitor.* from Monitor 
left join Queue on Queue.MonitorID = Monitor.MonitorID
and Queue.QueueID = (select top 1 Queue.QueueID from Queue where Queue.MonitorID = Monitor.MonitorID order by Queue.Created)
where Queue.Created is null or Queue.Created < 'DateTimeValue'

This query selects all "Monitors" whose queue is overdue or missing, if you can think of better ways at getting that info that'd be fine too.

The results from this query are overdue items that need to be run. I am using EF6.

I am trying to convert this from SQL to Linq Lambdas, I tried Linqer but it doesn't seem to output Lambda examples or I can't find the setting to make it happen.

So, can someone help guide me in converting this query and offer improvements if there are any? I know subquery is a performance killer...

Once I see it done once I feel like I will be able to learn the syntax.

I'm specifically looking for examples of this join syntax in linq/lambdas

Upvotes: 0

Views: 1592

Answers (3)

Jason Meckley
Jason Meckley

Reputation: 7591

I don't think your join is quite right because you have queues left joined to monitors, queue must exist, but monitor may not. it will also only return a single queue because you only take the first queue in the subquery. I think you want to adjust the SQL statement to 

 select * from Monitor 
 left join Queue on Monitor.MonitorID = Queue.QueueID
 and Queue.QueueID in (select max(Queue.QueueID) from Queue group by Queue.MonitorID)
 /*this assumes queues are stored in sequential order*/
 where Queue.Created is null or Queue.Created < 'DateTimeValue'

then you can write the following linq statement

 var monitors = _context.Set<Monitor>();
 var queues = _context.Set<Queue>();

 var queueIds = queues
      .GroupBy(q => q.MonitorID)
      .Select(q => q.Max(x => x.QueueID));

 return monitors
      .GroupJoin(queues.Where(q => queueIds.Contains(q.QueueID)), 
                 m => m.MonitorID, 
                 q => q.QueueID, 
                 (m, q) => new { monitor = m, queue = q.FirstOrDefault() })
      .Where(x => x.queue == null || x.queue.Created < date);

Upvotes: 1

recursive
recursive

Reputation: 86064

You can just do a query for all the monitors that don't have any queues created after the threshold.

var result = db.Monitors
  .Where(m => !m.Queues.Any(q => q.Created >= DateTimeValue));

I don't think there is a compelling reason to use an explicit join here. In fact, there rarely is with idiomatic EF queries.

Upvotes: 1

Robert McKee
Robert McKee

Reputation: 21487

If you are using Entity Framework with navigation properties, it would be:

var result=db.Monitors
  .Select(m=> new {
    monitor=m,
    queue=m.Queues.OrderByDescending(q=>q.Created).FirstOrDefault()
  })
  .Where(m=>m.queue.Created==null || m.queue.Created < DateTimeValue);

Upvotes: 1

Related Questions