Hi all, I'm use LLBLGen 4.2 and SQLServer 2012. Self-Servicing

I have a problem with create query like this :

SELECT * FROM [TimeZones]
WHERE Id = (
          SELECT top 1 tz.TimeZoneId
          FROM [TimeZonesCities] tz
          CROSS JOIN (SELECT * from [Leads] WHERE [Leads].[id] = 111) l
          ORDER BY
            CASE WHEN (tz.ZipCode = SUBSTRING(SUBSTRING(l.Zip, PATINDEX('%[^-]%', l.Zip+'.'), 5), PATINDEX('%[^0]%', l.Zip+'.'), 5)) THEN 0
                WHEN (l.State=tz.State AND l.City=tz.City) THEN 1
                WHEN (LEN(CAST(l.PrimaryPhone AS nvarchar(50))) = 10 AND CHARINDEX(tz.AreaCode, l.PrimaryPhone) = 1) THEN 2
            ELSE 3
          END ASC
        )

Can you suggest with me API can support nested select with result. And Join with select result ? I not see FieldCompareSetPredicate and Relation support this.

QuerySpec can be very handy here.

For example the below:

var qf = new QueryFactory();

var empQ = qf.Employee
    .Where(EmployeeFields.Country == "USA");

var q = qf.Create();
q.Select(CustomerFields.CustomerId)
    .From(qf.Customer.CrossJoin(empQ));

var qOrders = qf.Order
    .Where(OrderFields.CustomerId.In(q));

using (var adapter = new DataAccessAdapter())
{
    var results = adapter.FetchQuery(qOrders);
}

Generates the following:

SELECT [NORTHWND].[dbo].[Orders].[CustomerID] AS [CustomerId], .... 
FROM [NORTHWND].[dbo].[Orders]   
WHERE ( ( [NORTHWND].[dbo].[Orders].[CustomerID] IN 

(
SELECT [NORTHWND].[dbo].[Customers].[CustomerID] AS [CustomerId] 
FROM ( 
(SELECT [NORTHWND].[dbo].[Employees].[Address], ... 
FROM [NORTHWND].[dbo].[Employees]   
WHERE ( ( [NORTHWND].[dbo].[Employees].[Country] = 'USA'))) [LPA_L1]  
CROSS JOIN [NORTHWND].[dbo].[Customers] )))
)

Thank you so much Walaa. It's work

Are you have any suggest about this sort ?

Dim orderFunc = New DbFunctionCall(
                "CASE WHEN ({0} = SUBSTRING(SUBSTRING({1}, PATINDEX('%[^-]%', {1}+'.'), 5), PATINDEX('%[^0]%', {1}+'.'), 5)) THEN 0" +
                    "WHEN ({2}={3} AND {4}={5}) THEN 1" +
                    "WHEN (LEN(CAST({6} AS nvarchar(50))) = 10 AND CHARINDEX({7}, {6}) = 1) THEN 2" +
                "ELSE 3",
                New Object() {TZCityFields.ZipCode, LeadFields.Zip _
                            , TZCityFields.City, LeadFields.City _
                            , TZCityFields.State, LeadFields.State _
                            , LeadFields.PrimaryPhone, TZCityFields.AreaCode
                            })
            Dim orderField = TZCityFields.ZipCode.SetExpression(orderFunc)

            ' Order by Zip -> City,State -> Convert PrimaryPhone to Areacode
            Dim orderClause = New SortClause(orderField, Nothing, SortOperator.Ascending)
            orderClause.EmitAliasForExpressionAggregateField = True

What SQL does it generate and what's the required?