SELECT TOP 100 with JOIN using LLBLGEN

Question

I am using LLBLgen as ORM and want to achieve the following:

Table1:
SessionId

Table2:
SessionId
Timestamp

SELECT TOP 100 * FROM Table1
INNER JOIN Table2 ON Table1.SessionId = Table2.SessionId
ORDER BY Table2.Timestamp DESC

This code is running fine when executing it directly on SQL Server 2008 R2 - returning exactly 100 rows from Table1 if available, but somehow I am unable to achieve the same result with LLBLGen. Currently I'm using still 2.6, but updating is an option if needed.

Is there a possibility to achieve this behavior in LLBLGen?

This is the result if I use normal mechanisms in LLBLGen

SELECT * FROM Table1
INNER JOIN Table2 ON Table1.SessionId = Table2.SessionId
ORDER BY Table2.Timestamp DESC

BTW: I read that LLBLGen takes the TOP 100 results from the reader then kills the connection. Nonetheless the query takes A LOT longer using LLBLGen in comparison to just executing the SQL directly (this counts, to my surprise, also for latter query!)

How's this look? http://stackoverflow.com/questions/6837187/llblgen-pro-select-top-n-equivalent-query — Sam DeHaan, May 10 '12 at 14:58
Found that also. Without the join it is also working fine - but as soon as I'm joining it is not. The example you posted is without a join. — StampedeXV, May 10 '12 at 15:01
What are you selecting? Your sql statements in your question are missing what you are selecting. — Adam Spicer, May 16 '12 at 11:18
You are right - I corrected that. Although it is not important for my question. — StampedeXV, Jun 01 '12 at 11:55

score 1 · Answer 1 · answered May 16 '12 at 13:16

It doesn't add TOP as that would maybe return duplicate rows as you have a join and there's a situation in your query (you didn't post the real query) where you have distinct violating typed fields in your projection.

In general, when fetching entities, llblgen pro will add TOP in your case and DISTINCT. If it can't add distinct, because your query returns fields of type image, ntext, text, or you sort on a field which isn't in the projection (so distinct can't be applied otherwise sqlserver will throw an error), it won't add TOP either as that could mean you get potential duplicate rows in the set limited by TOP, which are filtered out, as entities are always unique.

Example: fetching Customers based on a filter on Order (so using a join), will create a Customers INNER JOIN Orders on northwind, but as this is a 1:n relationship, it will create duplicates. If Customers contains a text, image or ntext field, distinct can't be applied, so if we then would specify TOP, you'll get duplicate rows. As llblgen pro never materializes duplicate rows into entities, you'll get less entities back than the value you asked for.

So instead it switches, in THIS particular case, to client side limitation: it kills the connection once it has read the # of entities (not rows!) which you asked for. So if you ask for 10 entities and you have 10000 duplicate rows in the first 10010 rows, you'll get 10000 rows being fetched at least.

So my guess is the sort on table2 which is the issue, as that prevents DISTINCT from being emitted. This is an illegal query on sqlserver:

SELECT DISTINCT C.CompanyName FROM Customers C INNER JOIN Orders O on c.CustomerId = o.CustomerId ORDER BY o.OrderDate DESC;

The reason is that ORDER BY appends a hidden column for all fields to sort on which aren't in the projection, which ruins the distinct. This is common in RDBMS-s.

So TL;DR: it's a feature :)

SELECT TOP 100 with JOIN using LLBLGEN

1 Answers1