Selecting only certain properties from a NHibernate Subquery

Question

So I have a query like this:

var sq = QueryOver.Of<Image>()
    .Where(i => i.NumID == ImageID) //The ImageID is for example 1026
    .Select(i => i.Album.Id);       // here we return the Album.ID (column AlbumID)

// just Albums with searched Image
var query = QueryOver.Of<Album>()
    .WithSubquery
    .WhereProperty(a => a.Id)
    .In(sq) 
    .List<Album>();

Now let's say that my Album contains a large property that I don't always want to load, I only want to load the album id and name so I tried the following:

// just Albums with searched Image
var query = QueryOver.Of<Album>()
    .WithSubquery
    .WhereProperty(a => a.Id)
    .In(sq)
    .Select(x => new{x.Id, x.Name})
    .List<Album>();

But this generates me a System.InvalidOperationException: variable 'x' of type 'Album' referenced from scope '', but it is not defined

Placing the .Select after the .List does work, however then the SQL will load all of the Album, and that's just what I'm trying to avoid.

score 2 · Accepted Answer · answered Apr 15 '15 at 11:47

The point would be to use NHibernate QueryOver method SelectList, instead of LINQ Select method

// .Select(x => new{x.Id, x.Name})
.SelectList(list => list
    .Select(x => x.Id)
    .Select(x => x.Name)
)

The SelectList belongs to QueryOver world, and will be translated into projection

And if we want to get list of Album, we even would need trasfromer

Album a = null;

...
.SelectList(list => list
    .Select(x => x.Id).WithAlias(() => a.Id)
    .Select(x => x.Name).WithAlias(() => a.Name)
)
.TransformUsing(NHibernate.Transform.Transformers.AliasToBean<Album>())

Selecting only certain properties from a NHibernate Subquery

1 Answers1