Here is a mysql query to get most recent orders for all employees

SELECT
e.EmployeeID,
v.OrderID, v.OrderDate
FROM Employees e  
LEFT JOIN
        Orders v
ON    v.OrderID = 
        (
        SELECT  OrderID
        FROM    Orders vi
        WHERE   vi.EmployeeID = e.EmployeeID
        ORDER BY
                vi.OrderDate DESC, vi.EmployeeID DESC
        LIMIT 1
        );

How to do this in llblgen Linq?

Here is where i'm now:

            var e = from ee in md.Employee
                    join o in md.Order on
                    (
                        from ox in md.Order
                        where ee.EmployeeId == ox.EmployeeId
                        orderby ox.OrderDate descending, ox.EmployeeId descending
                        select ox.OrderId
                    ).First() equals o.OrderId into outer
                    from oy in outer.DefaultIfEmpty()
                    select new { ee.EmployeeId, oy.OrderId, oy.OrderDate };

But the resulting query is completely wrong, apparently the inner predicates are being ignored or something.

Thanks. Hugo

Would this simplified query give the same results?

SELECT
e.EmployeeID,
MAX(v.OrderID), MAX(v.OrderDate)
FROM Employees e 
LEFT JOIN Orders v ON v.EmployeeID = e.EmployeeID
Group By e.EmployeeID

Assuming OrderID is inremental.

Hi Walaa,

Thanks for fast reply.

In this scenario, yes, the result is the same, but if I want to select any other fields from Orders table, like ShipCountry? This is my problem.

So the above SQL returns a table of EmployeeId and their latest OrderId. Using the OrderId you join to the Order table to get rest of fields.

Maybe my initial question was not clear about my intentions, my main objective is to understand how to perform correlated subqueries (where the subquery is dependent from the top query) in LLBLGen Linq Provider.

This kind of queries are really handy to solve day to day tasks.

Could you post the incorrectly generated SQL from your original query, and an example of how you would expect the SQL to look. This will give us a better idea of what you are trying to acheive.

Thanks

Matt

Correlated subqueries aren't defined directly in linq. You define the correlation implicitly in Linq by simply referencing the related element in a where clause for example. The correlated subquery is then created for you (if necessary).

If you want explicit control over when a correlated subquery is created, please take a look at our new query api system QuerySpec, which is in beta now for v3.0/v3.1 (customer area), or use our (more low level) query system and define the predicates (FieldCompareSetPredicate) with the correlated subquery manually.

Hello Frans,

I read in your blog about QuerySpec and it looks very promising, so far we are on v2.0 but we may consider an upgrade to v3.

Can you provide me the correct Linq code to perform the query from my initial post?

Thanks in advance, Hugo

so far we are on v2.0 but we may consider an upgrade to v3.

v.2.0, That's a very valuable piece of information. I assumed you were using v.3.1

Could you please specify which runtime library version are you using?

Sorry about not being so clear:

.NET 3.5 and LLBLGen Pro 2.0:

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll v2.6.9.407 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll v2.6.9.305

Hugo

That's 2.6 not 2.0 Anyway it seems you have an old runtime library.

Would you please downlowad the latest release of v.2.6 and try again.

Better yet, I'm using (evaluating) v3.1 now

Unfortunately I have the same problem, can't create that query. Any example?

Sorry to point you to another site rather than answer your question directly, but stackoverflow these days seems to come up first in almost any google search about a code query...

Does either of these threads point you in the right direction?

http://stackoverflow.com/questions/2225052/is-it-possible-to-create-a-correlated-subquery-in-linq

http://stackoverflow.com/questions/4248193/convert-sql-query-with-correlated-subquery-to-linq-in-c

Matt

ALso, what's the SQL query being generated from the linq query you've formulated in the starting post?

Hi MTrinder,

I think your suggestions pointing me in the write path! I got what I want now.

Here is the my final and WORKING linq query:

            var e = from ee in md.Employee
                    join o in md.Order on ee.EmployeeId equals o.EmployeeId into outer
                    from o in outer.DefaultIfEmpty()
                    where o.OrderId == (from oo in md.Order
                                        where oo.EmployeeId == ee.EmployeeId
                                        orderby oo.OrderDate descending
                                        select oo.OrderId).First() || o.OrderId == null
                    select new { ee.EmployeeId, o.OrderId, o.OrderDate };

The resulting SQL query seems quite good (removed extra parenthesis to simplify):

SELECT
    `LPA_L1`.`EmployeeID` AS `EmployeeId`,
    `LPA_L2`.`OrderID` AS `OrderId`,
    `LPA_L2`.`OrderDate`
FROM
    `employees` `LPA_L1`
LEFT JOIN `orders` `LPA_L2`
    ON `LPA_L1`.`EmployeeID` = `LPA_L2`.`EmployeeID`
WHERE
    `LPA_L2`.`OrderID` = (
        SELECT `LPLA_6`.`OrderID` AS `OrderId`
        FROM `orders` `LPLA_6`
        WHERE `LPLA_6`.`EmployeeID` = `LPA_L1`.`EmployeeID`
        ORDER BY `LPLA_6`.`OrderDate` DESC LIMIT 1
    ) OR (
        `LPA_L2`.`OrderID` IS NULL)

Thanks a lot for your great support. Hugo

@Otis

SELECT
 `LPA_L1`.`EmployeeID` AS `EmployeeId`,
 `LPA_L2`.`OrderID` AS `OrderId`,
 `LPA_L2`.`OrderDate`
FROM `employees` `LPA_L1`
LEFT JOIN `orders` `LPA_L2` ON  `LPA_L2`.`OrderID` IS NULL)