在NHibernate QueryOver API中使用连接与投影时的排序和分页实现问题

Hey there! Let's work through that sorting and pagination snag you're hitting with NHibernate's QueryOver when using projections and joins. I’ve dealt with this exact issue before, so here’s a step-by-step fix:

1. Getting Sorting Right with Projections #

When you use SelectList to project results, you can’t just order directly against the original entity properties like you might in a non-projected query. You’ve got two solid options:

• Sort using your projection DTO aliases: Since you’re using WithAlias(() => item.TenantId) to map projections to your DTO, you can reference those aliases directly with OrderByAlias.
• Sort using the original entity aliases: If you prefer to stick to the source entities, just make sure you use the join aliases you defined (like customerAlias) for any related table properties.

2. Pagination Done Correctly #

NHibernate’s Skip() and Take() methods handle pagination, but there’s a critical rule: always apply sorting before pagination. If you paginate first, you’ll end up with random chunks of unsorted data, which is never what you want.

Full Working Example #

Let’s expand your code with these fixes, assuming you’ve got a DTO (let’s call it TenantProjectionDto) to hold the results:

// Define your aliases (make sure these are in scope!)
Tenant tenantAlias = null;
Customer customerAlias = null;
TenantProjectionDto item = null;

var restrictions = Restrictions.Conjunction();
// Add your existing restrictions here, e.g.:
// restrictions.Add(Restrictions.Like(() => tenantAlias.DomainName.Value, "%example%"));

var query = Session.QueryOver(() => tenantAlias)
    .JoinAlias(x => x.Customer, () => customerAlias)
    .Where(restrictions)
    .SelectList(list => list
        .Select(() => tenantAlias.Id).WithAlias(() => item.TenantId)
        .Select(() => tenantAlias.DomainName.Value).WithAlias(() => item.DomainName)
        .Select(() => customerAlias.CompanyName).WithAlias(() => item.CustomerCompanyName)
    )
    // Option 1: Sort using the projection DTO alias
    .OrderByAlias(() => item.CustomerCompanyName).Asc
    // Option 2: Sort using the original entity alias (for reference)
    // .OrderBy(() => tenantAlias.DomainName.Value).Desc
    // Apply pagination - MUST come after sorting!
    .Skip(10) // Skip first 10 records
    .Take(20); // Grab next 20 records

// Map the projected results to your DTO
var tenantResults = query
    .TransformUsing(Transformers.AliasToBean<TenantProjectionDto>())
    .List<TenantProjectionDto>();

Key Things to Remember #

• Use Transformers.AliasToBean: This is what links your projection aliases (like item.TenantId) to your DTO’s properties, making the alias-based sorting work.
• Reference join aliases for related properties: If you’re sorting on a Customer property, always use customerAlias instead of tenantAlias.Customer—QueryOver needs the explicit join alias to resolve the property correctly.
• Sort before paginating: This ensures you’re slicing a pre-sorted dataset, so your pages are consistent and logical.

内容的提问来源于stack exchange，提问作者Fernando Gómez