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>>();