FluentNhibernate: Query to retrieve distinct values

Question

Using FluentNhibernate 1.3.0.0, NHibernate 3.3.1.4000 on a DB2 LUW 9.7 database.

I want to get some distinct data from only one table / entity. In SQL, it´s easy:

select distinct Corporation, CalculationDate, ValuationRule
from MySchema.MyTable
where State == 0

Now, i´m trying to get those data using Linq, but it won´t work...

First try using select:

var result = Session.Query<MyEntity>()
        .Where( x => x.State == State.Pending)
        .Select(
           x =>
              new
              {
                 Corporation = x.Corporation,
                 CalculationDate = x.CalculationDate,
                 ValuationRule = x.ValuationRule,
              }).Distinct().ToList();

Resulting exception: Expression type 'NhDistinctExpression' is not supported by this SelectClauseVisitor.

Second try, using Groupby and only getting the keys:

var result = Session.Query<MyEntity>()
        .Where( x => x.State == State.Pending)
        .GroupBy(
           x =>
              new
              {
                 Corporation = x.Corporation,
                 CalculationDate = x.CalculationDate,
                 ValuationRule = x.ValuationRule,
              }).Select( x => x.Key).ToList();

Resulting Exception: "Could not execute Query". Complaining about another field "Model" missing in the group by clause that is stated in the select term. This is totally confusing me, as the specified field exists in the table but i dont want to use that field in that use case...

What is it i am missing?

score 5 · Answer 1 · edited Apr 24 '15 at 16:51

5

Try to use QueryOver...

var result = Session.QueryOver<MyEntity>()
    .Where(x => x.State == State.Pending)
    .SelectList(list => list
        .SelectGroup(x => x.Corporation)
        .SelectGroup(x => x.CalculationDate)
        .SelectGroup(x => x.ValuationRule)               
    )
    .ToList();

If you want to use distinct:

var result = Session.QueryOver<MyEntity>()
    .Where(x => x.State == State.Pending)
    .SelectList(list => list
                .Select(Projections.Distinct(Projections.Property<MyEntity>(x => x.Corporation)))
                .Select(x => x.CalculationDate)
                .Select(x => x.ValuationRule)
                 )
                .ToList();

edited Apr 24 '15 at 16:51

Najera

2,869
3
28
52

answered Apr 24 '15 at 16:06

BrennQuin

656
10
19

1

Thanks, I tried both variants. Both are throwing an exception "Unable to perform find[SQL: SQL not available]". The inner exception (translated): "System.Object[] is no value of type MyEntity and may not be used in this generic listing." – Udontknow Apr 24 '15 at 19:00
1

Maybe you need to cast to object array `.ToList – BrennQuin Apr 24 '15 at 22:14

Najera · Accepted Answer · 2015-04-24T22:12:24.170

In both examples from Brenda are missing the transformation.

Disclaimer: Check first if the types are correct in the DTO or in the Linq projection.

public class MyDto
{
    public string Corporation { get; set; }
    public DateTime? CalculationDate { get; set; }
    public string ValuationRule { get; set; }
}

MyDto myDto = null;

var result = Session.QueryOver<MyEntity>()
    .Where(x => x.State == State.Pending)
    .SelectList(list => list
        .Select(Projections.Distinct(Projections.Property<MyEntity>(x => x.Corporation))).WithAlias(() => myDto.Corporation)
        .Select(x => x.CalculationDate).WithAlias(() => myDto.CalculationDate)
        .Select(x => x.ValuationRule).WithAlias(() => myDto.ValuationRule)
        )
    .TransformUsing(Transformers.AliasToBean<MyDto>())
    //.TransformUsing(Transformers.AliasToBean<MyEntity>()) // You can use your entity but i recommend to use a DTO (Use MyEntity in the alias too)
    .ToList();

If you dont want to use a transformer, you need to cast to obj array:

var result = Session.QueryOver<MyEntity>()
    .Where(x => x.State == State.Pending)
    .SelectList(list => list
        .Select(Projections.Distinct(Projections.Property<MyEntity>(x => x.Corporation)))
        .Select(x => x.CalculationDate)
        .Select(x => x.ValuationRule)
        )
    .ToList<object[]>()
    //.Select(x => new    // This is optional to use anonymus type instead a object[]
    //      {
    //         Corporation = (string) x[0],
    //         CalculationDate = (DateTime?) x[1],
    //         ValuationRule = (string) x[2]
    //      })
    //.List()
    ;

FluentNhibernate: Query to retrieve distinct values

2 Answers2