Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Top not being emitted
 

Pages: 1
LLBLGen Pro Runtime Framework
Top not being emitted
Page:1/1 

  Print all messages in this thread  
Poster Message
Ian
User



Location:
Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
Posted:
511 posts
# Posted on: 11-Sep-2016 04:12:42.  
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...

Quote:
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...

Code:
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?

Code:
            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>>();
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37869 posts
# Posted on: 11-Sep-2016 10:09:05.  
what version of llblgen pro are you using?

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Ian
User



Location:
Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
Posted:
511 posts
# Posted on: 11-Sep-2016 15:07:52.  
3.5 Final.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37869 posts
# Posted on: 12-Sep-2016 09:35:06.  
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).



Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Ian
User



Location:
Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
Posted:
511 posts
# Posted on: 12-Sep-2016 15:36:08.  
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.
  Top
Ian
User



Location:
Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
Posted:
511 posts
# Posted on: 12-Sep-2016 15:47:50.  
If the database didn't honour the ROWLOCK hint, then I don't think my scheme would have worked anyway.

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14585 posts
# Posted on: 13-Sep-2016 06:06:38.  
Do you mean that the query works as you expect it when you remove these hints?
  Top
Ian
User



Location:
Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
Posted:
511 posts
# Posted on: 13-Sep-2016 17:23:21.  
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...

Code:
            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.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14585 posts
# Posted on: 13-Sep-2016 17:37:21.  
Glad you have sorted it out.
Thanks for the feedback.
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.