CODEWITHSUNDEEP
c#hibernatenhibernatefluent-nhibernate
Anchit Pancholi
Anchit Pancholi

Reputation: 1214

NHibernate ICriteria Return Empty Result

I am using NHibernate and creating query as below :- 

ICriteria criteria = session.CreateCriteria<PayinoutBookentry>();
ProjectionList projList = Projections.ProjectionList();
             projList.Add(Projections.GroupProperty("PaymentOption").As("PaymentOption"));
             criteria.SetProjection(projList);
             criteria.SetResultTransformer(Transformers.AliasToBean<PayinoutBookentry>());

IList<PayinoutBookentry> payinoutBookentryList = criteria.List<PayinoutBookentry>();

POCO:

public class PayinoutBookentry 
{
    public virtual int PayinoutBookentryId { get; set; }

    public virtual Methodofpayment PaymentOption { get; set; }
}

Mapper:

public PayinoutBookentryMap() 
{
        Table("payinout_bookentry");
        Schema("test");
        Lazy(true);
        Id(x => x.PayinoutBookentryId, map => { 
           map.Column("PAYINOUT_BOOKENTRY_ID");
           map.Generator(Generators.Native); });

        ManyToOne(x => x.PaymentOption, map =>
        {
            map.Column("PAYMENT_OPTION");
            //  map.NotNullable(true);
            map.Cascade(Cascade.None);
        });

But when I try to get list I get only one row in list and that row has empty object.

Can someone please let me what is wrong with GroupProperty ? as without GroupProperty it is working fine.

Upvotes: 1

Views: 744

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123891

The result we are receiving, is really correct - in relation to the query. Because the ICriteria query above would end up in this SQL Statement:

SELECT this_.PAYMENT_OPTION as y0_ 
FROM [test].[payinout_bookentry] this_ 
GROUP BY     this_.PAYMENT_OPTION

As we can see, only GROUP BY column is in SELECT clause... nothing else is selected. 

...
ProjectionList projList = Projections.ProjectionList();
// just one projected SELECT statement
projList.Add(Projections.GroupProperty("PaymentOption").As("PaymentOption"));
// still only one SELECT result
criteria.SetProjection(projList);

So, even if we use Transformer in the next line

// iterates retrieved data and convert them into properties
criteria.SetResultTransformer(Transformers.AliasToBean<PayinoutBookentry>());

We still have almost NULL everywhere (and default ValueTypes) - because that's how it works.

So, what we can do to change that?

Firstly we could add more columns into projections

...
projList.Add(Projections.Count("PayinoutBookentryId").As("PayinoutBookentryId"));
...

But in fact, that won't make sense, because in ID column we know would have (transformed) count of all IDs... it is not what we want.

That all is happening, because (I guess) the Projecting and Grouping is here used not the right way. It serves to some kind of reporting (we create DTO and compute how many Money, Count we have per some type)

But in case we want to get BookEntries related to some payment.. I would simply use WHERE, e.g.:

criteria.Add(Restrictions
              .Where<PayinoutBookentry>(o => o.PaymentOption.ID == somePaymentOptionType));

and that way we can get all the entries, related to filtered payment type...

Read more about it:

Upvotes: 4

Related Questions