LLBLGen doesn't emit a TOP clause if there's a join in the query.

This issue is mentioned here...

https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4021

It says...

Only 1:1 relations don't result in duplicates, the other joins always potentially result in duplicate rows. Also, with m:1 relations, and selecting the m part, you don't have to run into duplicates, but it's not determinable.

My question is, when is a m:1 relation not determinable? I'm thinking of hiding my join in a view so that LLBLGen will emit the TOP clause. I have the following simple query...

ALTER VIEW [dbo].[vw_weekender_chalet_UPDLOCK]
AS
SELECT
    ci.ChaletInstanceId,
    ci.Berth,
    ci.EventId,
    ci.ForSale,
    c.ChaletID,
    cc.ChaletClassID,
    cc.AssignmentOrder
FROM
    tbl_chalet_instance ci WITH (UPDLOCK, ROWLOCK, READPAST)
INNER JOIN
    tbl_chalet c
ON
    c.chaletid = ci.chaletid
INNER JOIN
    tbl_chalet_class cc
ON
    cc.ChaletClassID = c.ChaletClassID

What could go wrong and lead to duplicates when I'm joining on primary keys?

In fact, I've just tried my query, and its not emitting TOP even with a 1:1 join! Should it be?

            var m = new LinqMetaData(adapter);

            IQueryable<ChaletInstanceEntity> allocatedChaletInstances = (from ci in m.ChaletInstance
                                                         join pa in m.ChaletProductAllocation on ci.ChaletInstanceId equals
                                                             pa.ChaletInstanceId
                                                         join pag in m.ChaletProductAllocationGroup on
                                                             pa.ChaletProductAllocationGroupId equals
                                                             pag.ChaletProductAllocationGroupId
                                                         where ci.EventId == eventId
                                                         select ci);

            IQueryable<ChaletInstanceEntity> availableChalets = (from ci in m.ChaletInstance
                                                         join cu in m.VwWeekenderChaletUpdlock on ci.ChaletInstanceId equals
                                                             cu.ChaletInstanceId
                                                         where ci.Berth == berth && ci.ForSale && ci.EventId == eventId
                                                         orderby cu.AssignmentOrder
                                                         select ci).Except(allocatedChaletInstances).Take(quantity);

            var collection = ((ILLBLGenProQuery)availableChalets).Execute<EntityCollection<ChaletInstanceEntity>>();

what version of llblgen pro are you using?

3.5 Final.

Ian wrote:

3.5 Final.

The build nr. is what's important (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722). Either way, older versions before v4 had some problems deciding when 'distinct' is required or not. For some reason it decides distinct isn't possible yet the runtime thinks it is required. This is likely due to the fact it sees a join and always concludes a distinct is required. I think a text/image/blob/clob field is in the projection (assignmentorder ? ) so distinct can't be emitted and it therefore switches to client side limiting. This means it will fetch as much entities as ordered from the reader and then close it. In general this is a tiny bit slower than doing distinct on the server, but not necessarily that much slower (as not much more data is read).

assignmentorder is an integer.

I've removed ROWLOCK, READPAST. A problem I think I may have had is that, rather than a concurrent query reading past a handful of locked rows to some available rows, it was reading past all of the rows and thinking there were no chalets at all.

If the database didn't honour the ROWLOCK hint, then I don't think my scheme would have worked anyway.

Do you mean that the query works as you expect it when you remove these hints?

Actually, no!

It occurred to me that without READPAST, deadlocks could arise. If there's no TOP in the query, then every query will try and lock all the rows. If two queries start from different points, then they will each have what the other wants. So, I need the TOP such that, if one query locks, say, two rows, then another query will skip them and lock some other rows.

I've pushed more of the query into the view and re-written the query as so...

            var m = new LinqMetaData(adapter);

            var availableChalets = m.VwWeekenderChaletUpdlock
                    .Where(c => c.Berth == berth && c.ForSale &&
                        c.EventId == eventId && c.ChaletProductAllocationId == null)
                    .OrderBy(c => c.AssignmentOrder)
                    .Select(c => c.ChaletInstanceId);

            var chaletInstances = m.ChaletInstance.Where(ci => availableChalets.Contains(ci.ChaletInstanceId))
                                    .Take(quantity);

            var collection = ((ILLBLGenProQuery)chaletInstances).Execute<EntityCollection<ChaletInstanceEntity>>();

Now its outputting the TOP in the SQL, which is what I was after all along.

Glad you have sorted it out. Thanks for the feedback.