The second would probably be something like:
session.CreateQuery("select r from Receipt r join fetch r.Payments
where r.id in (:ids)")
.SetParameterList("ids", ids).List(); // results discarded
If you expect to have multiple initialized persistent collections on a paged
list, the join overhead is a small price to pay.
Of course, if this is going to end in a DTO, it's probably more performant
to do the connections in memory.
Diego
On Mon, Aug 30, 2010 at 16:29, nadav s <[email protected]> wrote:
> you wrote for the second query:
>
> QueryForReceiptsFetchingPayments()
> .FilteringByIdIn(ids)
> .List()
>
>
> did you mean something like this:
>
> select r.Payments
> from Recipient r
> where r.Id in (x1,x2,x3)
>
> if so i can understand how nhibernate does this, but won't it cause a join
> in the query (if so it will be not the most efficient way to query for the
> payments) although it will select only the payments' fields.
>
>
>
> sorry for all the questions, but i've stupidly did the work of connecting
> everything via memory my self in many occasions
>
>
> On Mon, Aug 30, 2010 at 10:25 PM, nadav s <[email protected]> wrote:
>
>> of course i understand how PK queries can resolve proxies without hitting
>> the DB,
>> but collections queries aren't the same.
>>
>> mother.Children
>> when lazy loaded will result in the following query:
>>
>> from Children c
>> where c.MotherId = X // the id of the father.
>>
>> now you're query does this:
>>
>> from Children c
>> where c.MotherId in (x1,x2,x3,x4)
>>
>> i'm very impressed, yet still uncertain that nhibernate will know that it
>> doesn't need to get the children of mother X
>> after you're query if X is one of the x1...x4
>>
>> i always thought i have to connect the mothers (the first query) and the
>> children (the second query) my self via memory.
>>
>> are you sure nhibernate does this for me?
>>
>> On Mon, Aug 30, 2010 at 10:13 PM, Diego Mijelshon <[email protected]
>> > wrote:
>>
>>> Yes, it does.
>>>
>>> Let me explain this with a much simpler example:
>>>
>>> var animal = session.Get<Animal>(id);
>>> var mother = session.Get<Animal>(idOfTheMotherOfTheOtherAnimal)
>>>
>>> After running those queries, animal.Mother is a fully initialized proxy.
>>> Collections work pretty much the same.
>>>
>>> The way Identity Map works is that not only you don't need to hit the DB
>>> twice for the same POID; it also means the exact same object is returned.
>>> The same is true for collections: Payment#1234.Receipts exists once and
>>> only once in the session.
>>>
>>> Diego
>>>
>>>
>>> On Mon, Aug 30, 2010 at 16:05, nadav s <[email protected]> wrote:
>>>
>>>> sorry for the ignorance, but the session really knows that the second
>>>> query's results contains the results of the lazy loading query that would
>>>> of
>>>> been created when accessing the collection?
>>>>
>>>> i thought it works for lazy loaded proxy, because that query is by the
>>>> primary key, so if an object of the same type and the same PK was loaded by
>>>> the same session (or in the second level cache) the proxy lazy loading
>>>> doesn't need to hit the DB, but how does the session know that all of the
>>>> one-to-many associated entities are already loaded?
>>>>
>>>> On Mon, Aug 30, 2010 at 6:04 PM, Diego Mijelshon <
>>>> [email protected]> wrote:
>>>>
>>>>> Yes.
>>>>>
>>>>> Diego
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Aug 30, 2010 at 11:40, Niclas Pehrsson <[email protected]>wrote:
>>>>>
>>>>>> Even if it is better to use DTO's, I want my receipts to have their
>>>>>> list with payments and receipt entries.
>>>>>> So in this solution each receipt will have their receiptentries and
>>>>>> payments loaded, and reachable from receipt.Payments?
>>>>>> On Aug 30, 3:43 pm, Diego Mijelshon <[email protected]> wrote:
>>>>>> > Pseudocode:
>>>>>> >
>>>>>> > var receipts = LinqCriteriaSqlOrHqlQueryForReceiptsOnly()
>>>>>> > .FilteringByIsNotSynchronizedAndIsClosed()
>>>>>> > .InAnyOrderThatYouWant()
>>>>>> > .SetMaxResults(200) // or .Take(200) for Linq
>>>>>> > .List<Receipt>()
>>>>>> > var ids = (from receipt in receipts select receipt.Id).ToList();
>>>>>> > var irrelevant = QueryForReceiptsFetchingPayments()
>>>>>> > .FilteringByIdIn(ids)
>>>>>> > .List()
>>>>>> > var irrelevantToo = QueryForReceiptsFetchingEntries()
>>>>>> > .YouGetTheIdea()
>>>>>> > .YouCanCombineTheseTwoQueriesWithFuture()
>>>>>> >
>>>>>> > Now, if you are going to use WFC, it's better to create DTOs,
>>>>>> regardless of
>>>>>> > how you do the queries.
>>>>>> >
>>>>>> > Diego
>>>>>> >
>>>>>> > On Mon, Aug 30, 2010 at 05:48, Niclas Pehrsson <[email protected]>
>>>>>> wrote:
>>>>>> > > Well its a lot.
>>>>>> >
>>>>>> > > Receipt has two collections, receipt entries and payments.
>>>>>> > > I want to load top 200 where the receipts is closed and not
>>>>>> > > synchronized.
>>>>>> > > The entities will be sent over wcf so lazyloading isn't anything
>>>>>> to
>>>>>> > > use it is best to load them all at the same time.
>>>>>> > > We are using NHibernate 2.1 right now.
>>>>>> >
>>>>>> > > Receipt.IsSynchronized
>>>>>> > > Receipt.IsClosed
>>>>>> > > Receipt.Payments
>>>>>> > > Receipt.Entries
>>>>>> >
>>>>>> > > How would I do this on the most efficient way?
>>>>>> >
>>>>>> > > On Aug 28, 12:55 pm, Diego Mijelshon <[email protected]>
>>>>>> wrote:
>>>>>> > > > ONE QUERY PER COLLECTION, not ONE QUERY PER COLLECTION PER
>>>>>> RECORD.
>>>>>> > > > If you have one filtered query with two collections, that's 3
>>>>>> queries
>>>>>> > > TOTAL,
>>>>>> > > > regardless of the record count.
>>>>>> >
>>>>>> > > > Future allows you to reduce the roundtrips (the number of
>>>>>> queries is
>>>>>> > > still
>>>>>> > > > the same), but you can't combine the first query and the others
>>>>>> if you're
>>>>>> > > > paging.
>>>>>> > > > You can still use it for queries other than the first, which
>>>>>> brings the
>>>>>> > > > roundtrip count to 2 regardless of how many collections or
>>>>>> records you
>>>>>> > > are
>>>>>> > > > loading.
>>>>>> >
>>>>>> > > > Diego
>>>>>> >
>>>>>> > > > On Sat, Aug 28, 2010 at 04:55, Niclas Pehrsson <
>>>>>> [email protected]>
>>>>>> > > wrote:
>>>>>> > > > > Well that means a lot of queries if MaxSize is 200.
>>>>>> > > > > But maybe it is the only way to do it.
>>>>>> >
>>>>>> > > > > Are there any example of this scenario with Criterion?
>>>>>> > > > > I guess the use of Future is needed, you want to get the root
>>>>>> entity
>>>>>> > > > > loaded with its collections as result.
>>>>>> >
>>>>>> > > > > On Aug 27, 8:08 pm, Diego Mijelshon <[email protected]>
>>>>>> wrote:
>>>>>> > > > > > The answer is right there: "two totally separate queries,
>>>>>> first to
>>>>>> > > get
>>>>>> > > > > just
>>>>>> > > > > > the entities, then to load the related associations"
>>>>>> >
>>>>>> > > > > > First, one paged query without fetching the collections,
>>>>>> then one
>>>>>> > > query
>>>>>> > > > > per
>>>>>> > > > > > collection, filtering with the Ids of the first query.
>>>>>> >
>>>>>> > > > > > Diego
>>>>>> >
>>>>>> > > > > > On Fri, Aug 27, 2010 at 09:49, Niclas Pehrsson <
>>>>>> [email protected]>
>>>>>> > > > > wrote:
>>>>>> > > > > > > Well it wont work :(
>>>>>> >
>>>>>> > > > > > > Ayende repsonded to an comment with this
>>>>>> > > > > > > "SetMaxResults, but that might cause issues with the
>>>>>> loading.
>>>>>> > > > > > > At that point, I might want to execute two totally
>>>>>> separate
>>>>>> > > queries,
>>>>>> > > > > > > first to get just the entities, then to load the related
>>>>>> > > associations.
>>>>>> > > > > > > "
>>>>>> >
>>>>>> > > > > > > So how can I solve this?
>>>>>> > > > > > > Its a bit annoying, I want to load 200 hundred of
>>>>>> thousands, and I
>>>>>> > > > > > > want to avoid 200+200 select queries.
>>>>>> > > > > > > Any other approaches?
>>>>>> >
>>>>>> > > > > > > On Aug 26, 3:03 pm, Diego Mijelshon <
>>>>>> [email protected]>
>>>>>> > > wrote:
>>>>>> > > > > > > > SetMaxResults and SetFetchMode on _collections_ don't
>>>>>> get along.
>>>>>> >
>>>>>> > > > > > > > Suggested read:
>>>>>> >
>>>>>> > >
>>>>>> http://ayende.com/Blog/archive/2010/01/16/eagerly-loading-entity-asso
>>>>>> .
>>>>>> > > > > ..
>>>>>> >
>>>>>> > > > > > > > Diego
>>>>>> >
>>>>>> > > > > > > > On Thu, Aug 26, 2010 at 04:50, Niclas Pehrsson <
>>>>>> > > [email protected]>
>>>>>> > > > > > > wrote:
>>>>>> > > > > > > > > Hi I have an query that looks like this.
>>>>>> >
>>>>>> > > > > > > > > Return (From receipt In _session.Linq(Of
>>>>>> > > > > > > > > PointOfSale.Receipt)() _
>>>>>> > > > > > > > > Where receipt.IsClosed
>>>>>> AndAlso Not
>>>>>> > > > > > > > > receipt.IsSynchronized _
>>>>>> > > > > > > > > Select receipt).Take(25).ToList()
>>>>>> >
>>>>>> > > > > > > > > Or
>>>>>> > > > > > > > > Dim criterion = _session.CreateCriteria(Of
>>>>>> Receipt)
>>>>>> > > > > > > > > ().SetMazSize(25).Add(Expression.Eq("IsClosed", True))
>>>>>> _
>>>>>> > > > > > > > > .Add(Expression.Eq("IsSynchronized",
>>>>>> False)) _
>>>>>> > > > > > > > > .SetFetchMode("Payments",
>>>>>> FetchMode.Eager) _
>>>>>> > > > > > > > > .SetFetchMode("ReceiptEntry",
>>>>>> FetchMode.Eager)
>>>>>> >
>>>>>> > > > > > > > > 'Return criterion.List(Of
>>>>>> PointOfSale.Receipt)()
>>>>>> >
>>>>>> > > > > > > > > Both of them first asks the database för the first
>>>>>> query
>>>>>> > > options
>>>>>> > > > > > > > > IsClosed and Not IsSynchronized, and then It creates
>>>>>> an select
>>>>>> > > N+1
>>>>>> > > > > for
>>>>>> > > > > > > > > each element. How can I do this better so it will be
>>>>>> just one
>>>>>> > > or
>>>>>> > > > > three
>>>>>> > > > > > > > > queries that selects top 25 entities?
>>>>>> >
>>>>>> > > > > > > > > --
>>>>>> > > > > > > > > You received this message because you are subscribed
>>>>>> to the
>>>>>> > > Google
>>>>>> > > > > > > Groups
>>>>>> > > > > > > > > "nhusers" group.
>>>>>> > > > > > > > > To post to this group, send email to
>>>>>> [email protected].
>>>>>> > > > > > > > > To unsubscribe from this group, send email to
>>>>>> > > > > > > > > [email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> >
>>>>>> > > > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> >
>>>>>> > > > > > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> >
>>>>>> > > > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> >
>>>>>> > > > > > > > > .
>>>>>> > > > > > > > > For more options, visit this group at
>>>>>> > > > > > > > >http://groups.google.com/group/nhusers?hl=en.
>>>>>> >
>>>>>> > > > > > > --
>>>>>> > > > > > > You received this message because you are subscribed to
>>>>>> the Google
>>>>>> > > > > Groups
>>>>>> > > > > > > "nhusers" group.
>>>>>> > > > > > > To post to this group, send email to
>>>>>> [email protected].
>>>>>> > > > > > > To unsubscribe from this group, send email to
>>>>>> > > > > > > [email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> >
>>>>>> > > > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> >
>>>>>> > > > > > > .
>>>>>> > > > > > > For more options, visit this group at
>>>>>> > > > > > >http://groups.google.com/group/nhusers?hl=en.
>>>>>> >
>>>>>> > > > > --
>>>>>> > > > > You received this message because you are subscribed to the
>>>>>> Google
>>>>>> > > Groups
>>>>>> > > > > "nhusers" group.
>>>>>> > > > > To post to this group, send email to [email protected]
>>>>>> .
>>>>>> > > > > To unsubscribe from this group, send email to
>>>>>> > > > > [email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> >
>>>>>> > > > > .
>>>>>> > > > > For more options, visit this group at
>>>>>> > > > >http://groups.google.com/group/nhusers?hl=en.
>>>>>> >
>>>>>> > > --
>>>>>> > > You received this message because you are subscribed to the Google
>>>>>> Groups
>>>>>> > > "nhusers" group.
>>>>>> > > To post to this group, send email to [email protected].
>>>>>> > > To unsubscribe from this group, send email to
>>>>>> > > [email protected]<nhusers%[email protected]>
>>>>>> <nhusers%[email protected]<nhusers%[email protected]>
>>>>>> >
>>>>>> > > .
>>>>>> > > For more options, visit this group at
>>>>>> > >http://groups.google.com/group/nhusers?hl=en.
>>>>>>
>>>>>> --
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "nhusers" group.
>>>>>> To post to this group, send email to [email protected].
>>>>>> To unsubscribe from this group, send email to
>>>>>> [email protected]<nhusers%[email protected]>
>>>>>> .
>>>>>> For more options, visit this group at
>>>>>> http://groups.google.com/group/nhusers?hl=en.
>>>>>>
>>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "nhusers" group.
>>>>> To post to this group, send email to [email protected].
>>>>> To unsubscribe from this group, send email to
>>>>> [email protected]<nhusers%[email protected]>
>>>>> .
>>>>> For more options, visit this group at
>>>>> http://groups.google.com/group/nhusers?hl=en.
>>>>>
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "nhusers" group.
>>>> To post to this group, send email to [email protected].
>>>> To unsubscribe from this group, send email to
>>>> [email protected]<nhusers%[email protected]>
>>>> .
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/nhusers?hl=en.
>>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "nhusers" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected]<nhusers%[email protected]>
>>> .
>>> For more options, visit this group at
>>> http://groups.google.com/group/nhusers?hl=en.
>>>
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected]<nhusers%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
