FirstOrDefault in a Join with QueryOver

Question

First of all, I'm new to NHibernate. I have searched about the QueryOver thing in many pages, but the most profitable page to me has been http://nhibernate.info/blog/2009/12/17/queryover-in-nh-3-0.html. Thanks to it, I have arrived to a "semi-solution" I think, but I need this solution to be better.

I have these classes:

public class Variable
{
    public virtual int Id {get; set; }
    public virtual string Nombre { get; set; }
    public virtual string Descripcion { get; set; }
    public virtual IList<ValorVariable> Valores { get; set; }
    public virtual bool Temporal { get; set; }
    public virtual bool Eliminado{ get; set; }
}

public class ValorVariable
{
    public virtual int Id {get; set; }
    public virtual int IdVariable { get; set; }
    public virtual Variable Variable { get; set; }
    public virtual DateTime FechaValor { get; set; }
    public virtual decimal Valor { get; set; }
}

public class VariableLigera
{
    public virtual int Id {get; set; }
    public string Nombre { get; set; }
    public string Descripcion { get; set; }
    public bool Temporal { get; set; }
    public Decimal Valor { get; set; }
}

Variable is the "important" class, that, among other things, have a IList of "ValorVariable"s, that have a value("Valor") and a value date("FechaValor"). "VariableLigera" is like a "light" class of variable, that have some properties of Variable and one value of the ValorVariable List. I hope that is clear.

To populate VariableLigera, I'm trying to do a QueryOver. I would like to do something like this:

Variable variableAlias = null;
VariableLigera variableLigeraAlias = null;

var result = _session
            .QueryOver(() => variableAlias ).Where(x => x.Eliminado == false)
            .Select(
                Projections.Property(() => variableAlias .Id).WithAlias(() => variableLigeraAlias .Id),
                Projections.Property(() => variableAlias .Nombre).WithAlias(() => variableLigeraAlias .Nombre),
                Projections.Property(() => variableAlias .Descripcion).WithAlias(() => variableLigeraAlias .Descripcion),
                Projections.Property(() => variableAlias .Temporal).WithAlias(() => variableLigeraAlias .Temporal),
                Projections.Property(() => variableAlias .Valores.FirstOrDefault().Valor).WithAlias(() => variableLigeraAlias .Valor)
            )
            .TransformUsing(Transformers.AliasToBean<VariableLigera>())
            .List<VariableLigera>();

And here is the problem. I would like to put into the property "Valor" of the variableLigeraAlias the FirstOrDefault value of the Valores list of variableLigera. But it throws an exception, "function FirstOrDefault not recognized".

So I tried another thing, create an alias to ValorVariable and join it to the query. Like this:

Variable variableAlias = null;
VariableLigera variableLigeraAlias = null;
ValorVariable valorVariableAlias = null;
var result = _session
            .QueryOver(() => variableAlias).Where(x => x.Eliminado == false)
            .JoinAlias(() => variableAlias.Valores, () => valorVariableAlias)
            .Select(
                Projections.Property(() => variableAlias.Id).WithAlias(() => variableLigeraAlias.Id),
                Projections.Property(() => variableAlias.Nombre).WithAlias(() => variableLigeraAlias.Nombre),
                Projections.Property(() => variableAlias.Descripcion).WithAlias(() => variableLigeraAlias.Descripcion),
                Projections.Property(() => variableAlias.Temporal).WithAlias(() => variableLigeraAlias.Temporal),
                Projections.Property(() => valorVariableAlias.Valor).WithAlias(() => variableLigeraAlias.Valor)
            )
            .TransformUsing(Transformers.AliasToBean<VariableLigera>())
            .List<VariableLigera>();

With this query, I get results, but I get each variable many times(one duplicate per value in the ValoresVariables list). For example, if one variable have 3 values, the query will return 3 "VariablesLigera"s with the 3 values, but I only want one with the first value of it's list.

In short, and to make it clear, I would like to have one result per variable with the firstordefault value in the values("Valor") list. Is it possible using QueryOver? Thanks a lot.

PS: Since english it's not my native language, maybe some things are not much understandable. Feel free to ask if any doubt. Thanks again.

Answer 1

The thing to remember with QueryOver queries in general is that they're ultimately turned into SQL. With this in mind, whenever you access a nested property, you need to think in terms of joins or subqueries to get the job done.

In this case, you need to use a subquery to get the first ValorVariable.Valor:

Variable variableAlias = null;
VariableLigera variableLigeraAlias = null;

var result = _session
    .QueryOver(() => variableAlias).Where(x => x.Eliminado == false)
    .Select(
        Projections.Property(() => variableAlias.Id).WithAlias(() => variableLigeraAlias.Id),
        Projections.Property(() => variableAlias.Nombre).WithAlias(() => variableLigeraAlias.Nombre),
        Projections.Property(() => variableAlias.Descripcion).WithAlias(() => variableLigeraAlias.Descripcion),
        Projections.Property(() => variableAlias.Temporal).WithAlias(() => variableLigeraAlias.Temporal),
        Projections.Subquery(
            QueryOver.Of<ValorVariable>()
                .Where(vv => vv.Variable.Id == variableAlias.Id)
                .Select(vv => vv.Valor)
                .Take(1)
        ).WithAlias(() => variableLigeraAlias.Valor)
    )
    .TransformUsing(Transformers.AliasToBean<VariableLigera>())
    .List<VariableLigera>();

This would generate SQL that looks like this:

SELECT 
    /*Variable properties */
    (       
        SELECT 
            TOP(1) this_0_.Valor AS y0_ 
        FROM 
            ValorVariable this_0_ 
        WHERE 
            this_0_.VariableID = this_.ID
    ) AS y1_ 
FROM 
    Variable this_ 
WHERE 
    this_.Eliminado = 0

Now, as @Carl points out, you probably should order your subquery results by something so that taking the "first record" actually means something.

To do that, you could add an OrderBy to the subquery:

QueryOver.Of<ValorVariable>()
    .Where(vv => vv.Variable.Id == variableAlias.Id)
    .Select(vv => vv.Valor)
    .OrderBy(vv => vv.FechaValor).Desc
    .Take(1)

Which would generate similar SQL, except with an ORDER BY clause.

Also, since you were using FirstOrDefault, that implies that there's a chance that the Valor property could be null. If this is the case, you should update your VariableLigera.Valor property to be a decimal? (nullable decimal) instead.

Answer 2

If I understand you, VariableLigera is functioning as DTO for the entity Variable. You want most of the properties of Variable, and one value of the Valores collection. Unless you want any value of the collection, you'll need to specify the criteria for choosing the first value. Since VariableValor has a date property, let's use that to select the most recent value.

Here's a stab at doing this with a subquery.

Untested code (it compiles, but I haven't executed it:

Variable variableAlias = null;
VariableLigera variableLigeraAlias = null;
ValorVariable valorVariableAlias = null;
var result = _session
        .QueryOver(() => variableAlias).Where(x => x.Eliminado == false)
        .JoinAlias(() => variableAlias.Valores, () => valorVariableAlias)
        .SelectList(projections => projections
            .Select(() => variableAlias.Id).WithAlias(() => variableLigeraAlias.Id),
            .Select(() => variableAlias.Nombre).WithAlias(() => variableLigeraAlias.Nombre),
            .Select(() => variableAlias.Descripcion).WithAlias(() => variableLigeraAlias.Descripcion),
            .Select(() => variableAlias.Temporal).WithAlias(() => variableLigeraAlias.Temporal),

            .SelectSubQuery(QueryOver.Of<ValorVariable>( () => valorVariableAlias)
                                .Where(vv => vv.IdVariable == variableAlias.Id)
            .Select(Projections.Max<ValorVariable>(vv => vv.FechaValor)))
                                .Select(Projections.Property(() => valorVariableAlias.Valor))
           .WithAlias(() => variableLigeraAlias.Valor)
        )
        .TransformUsing(Transformers.AliasToBean<VariableLigera>())
        .List<VariableLigera>();