specifying fetch strategy (select, join, etc) in nhibernate queryover query

Question

I am trying to create a query using QueryOver, which will fetch a collection using the Select or SubSelect mode. The entity in question is Track. I want to load a collection called TrackPrices, and I am doing this in the query:

q = q.Fetch(item => item.TrackPrices).Eager;

However, this creates a left join, which results in a problem for pagination. I would like it to perform a seperate select or subselect. Any idea if it can be done? As far as I know, using the criteria API one would do:

q.DetachedCriteria.SetFetchMode("TrackPrices", FetchMode.Select);

But I want to avoid magic strings in the code, thus I would prefer doing it using the QueryOver API.

Harm

Answer 1

From the time I posted this question, I've managed to find a workaround / solution which others might find useful as well.

Basically, in such case you must first create another query which distinctly selects the primary keys of the main query, together with pagination. Since the DISTINCT(ID) will return only the results you want, you can use the pagination of SQL without any problems. Then, you re-run the main query, without pagination but using a condition where the ID is in one of the list returned. I created a general method which takes a criteria, looks up the IDs returned and adds them as a condition to the main criteria. Code below:

public static void LimitCriteriaByPrimaryKeys(this NHibernate.ICriteria criteria, string primaryKeyName, int pageNum, int pageSize)
    {
        var session = NHManager.Instance.GetCurrentSessionFromContext();
        if (pageSize <= 0) pageSize = Int32.MaxValue - 1;
        var nhSession = NHManager.Instance.GetCurrentSessionFromContext();
        var pagingCriteria = (ICriteria)criteria.Clone();
        IList ids = null;
        var pKeyIDName = Projections.Property(primaryKeyName);  
        var pKeyProjection = Projections.Distinct(pKeyIDName); 
        {
            {
                //paging
                pagingCriteria.SetProjection(pKeyProjection); //sets the primary key distinct projection
                if (pageSize > 0)
                {

                    if (pageNum < 1)
                        pageNum = 1;
                    int skipAmt = (pageNum - 1) * pageSize;
                    pagingCriteria.SetFirstResult(skipAmt);
                    pagingCriteria.SetMaxResults(pageSize); 

                    ids = pagingCriteria.List(); //this returns the distinct list of IDs which should be returned for the given page & size

                }
            }
        }
        {
            if (ids != null && ids.Count > 0)
            {
                criteria.Add(Expression.In(pKeyIDName, ids));   //adds the primary key restriction
                var crit = criteria;
                crit.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer());
            }
            else
            {
                criteria.Add(Expression.Eq(pKeyIDName, 0)); //this is added specifically so that the main criteria returns NO results
                criteria.Add(Expression.Eq(pKeyIDName, 1));
            }
        }
    }

The methods NHManager.Instance.GetCurrentSessionFromContext(); can be replaced with your own method to retrieve the current session from the session factory.

Hope it helps!

Harm

what about, I would go just as create repository as iqueryable with eager loading. After pagination done via linq query on iqueryable collection of lists. where you use .Take(x) – Muenster

But then, I have to load all the collection itself from the database. If you have a lot of records, it is extremely wasteful and expensive, plus slow. – Harm

Answer 2

I know this isn't what you asked for, but worst case you can encapsulate your magic strings in a type safe way using different methodologies here: C# String enums

Unmerciful

Answer 3

Combining paging and eager fetching is tricky.

If you specify fetch to select via Criteria API then each item in TrackPrices will be loaded in separate query - N+1 issue. You don't need bother with eager loading at all.

You can set batch size for TrackPrices in mapping to aleviate N+1 issue. There are some details about mixing eager fetching and paging in this article.

Skipjack