Convert this SQL Query to a Linq Lambda Expression

Question

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

I'm curious, seeing that you have a working query, why not just query against the database with that, as opposed to using LINQ? — user2366842
– user2366842, Commented Oct 1, 2015 at 16:29
I'm confused because your original query looks wrong. Shouldn't the JOIN be on Queue.MonitorID=Monitor.MonitorID? — Robert McKee
– Robert McKee, Commented Oct 1, 2015 at 16:39

Robert McKee · Accepted Answer · 2015-10-01 16:44:17Z

1

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);

edited Oct 1, 2015 at 16:44

answered Oct 1, 2015 at 16:36

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

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

3 Comments

juharr Over a year ago

Forgot to new up your anonymous class new { monitor=m, queue=...}

somedev Over a year ago

@RobertMcKee Interesting, this looks promising but I do have some questions of course... This returns an anonymous object with a monitor and a queue, in my case I really only need the monitor, so would you do another select after the where to just return the monitor? I'm assuming you wrote it that way because I did a select * in the example. Thank you for this, it was very helpful.

Robert McKee Over a year ago

Yes, just add another select at the end like .Select(m=>m.monitor); or you can rewrite the Where clause like:

.Where(m=>m.Queues.OrderByDescending(q=>q.Created).FirstOrDefault()==null || m.Queues.OrderByDescending(q=>q.Created).FirstOrDefault().Created<DateTimeValue);

and remove the Select entirely.

recursive · Accepted Answer · 2015-10-01 17:23:56Z

1

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.

answered Oct 1, 2015 at 17:23

recursive

86.5k36 gold badges155 silver badges246 bronze badges

Comments

Jason Meckley · Accepted Answer · 2015-10-01 19:03:14Z

1

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);

edited Oct 1, 2015 at 19:03

answered Oct 1, 2015 at 16:23

Jason Meckley

7,6011 gold badge26 silver badges46 bronze badges

3 Comments

juharr Over a year ago

Your sub-query returns the max queue id for each monitor id. That's not a scalar value, so you cannot compare that with equals.

somedev Over a year ago

You're absolutely correct sorry, could you review it one more time for me?

Jason Meckley Over a year ago

@juharr thanks for catching that. i corrected the SQL

Collectives™ on Stack Overflow

Convert this SQL Query to a Linq Lambda Expression

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related