QuerySpec Sub Query Example

Posts   
 
    
dazedorconfused avatar
Posts: 89
Joined: 06-Apr-2006
# Posted on: 09-Feb-2018 00:00:47   

Hello, I've looked at several examples for the "In" operator but they all use a static List instead of a subquery. Can someone please tell me what the Query Spec equivalent of the SQL below would be?


select  top 300 v.* 
from    view_BaseProfile v
where   v.UserName 
Not In  (Select UserName From ProfileUpdateAudit)
Or      v.LAST_UPDATED > 
        (select a.LastUpdateDate 
         from   ProfileUpdateAudit a 
         where a.UserName = v.UserName)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Feb-2018 08:18:54   

Hi Steven,

Here is an example using Northwind: All employees whom have never participated in an order or their hire date is greater than the max order date in which they participated:

var q = qf.Employee
        .Where(EmployeeFields.EmployeeId.NotIn(qf.Order.Select(OrderFields.EmployeeId))
            .Or(EmployeeFields.OtherDate > qf.Order
                .CorrelatedOver(OrderFields.EmployeeId == EmployeeFields.EmployeeId)
                .Max(OrderFields.OrderDate)
            )
        );
David Elizondo | LLBLGen Support Team
dazedorconfused avatar
Posts: 89
Joined: 06-Apr-2006
# Posted on: 09-Feb-2018 21:06:32   

Thank you David, this is exactly what I was looking for.