1

I have following query which working ok when variable regionId if not null (it is Nullabel<int>) but failed with "Object reference not set to an instance of an object" error when it is passed as null.

var result = Session.QueryOver<DtaMonthFreeze>()
    .Where(Restrictions.Eq(Projections.SqlFunction("MONTH", NHibernateUtil.DateTime, Projections.Property<DtaMonthFreeze>(x => x.Date)), monthDate.Month))
    .And(Restrictions.Eq(Projections.SqlFunction("YEAR", NHibernateUtil.DateTime, Projections.Property<DtaMonthFreeze>(x => x.Date)), monthDate.Year))
    .And(x => x.BranchGroupId == regionId || (regionId == null && x.BranchGroupId == null))
    .And(x => x.IsFrozen)
    .List();

Here is entity, it is quite simple:

public class DtaMonthFreeze : BaseEntity {
    public virtual Date Date { get; set; }
    public virtual bool IsFrozen { get; set; }
    public virtual int? BranchGroupId { get; set; }
}

And corresponding mapping:

public class DtaMonthFreezeMap : ClassMap<DtaMonthFreeze> {
    public DtaMonthFreezeMap() {
        Table("tbl_dta_month_freeze");
        Id(x => x.Id, "month_freeze_id").GeneratedBy.Identity();
        Map(x => x.Date, "date").Not.Nullable();
        Map(x => x.IsFrozen, "is_frozen").Not.Nullable();
        Map(x => x.BranchGroupId, "branch_group_id").Nullable();
     }
}

After small research I've found that if I replace this:

.And(x => x.BranchGroupId == regionId || (regionId == null && x.BranchGroupId == null))

with that:

.And(x => x.BranchGroupId == regionId || (regionId == null))

error is gone but query working incorrectly. SQL produced by NHibernate looks good (it is code of previous call where regionId is not null because if I am passing null nothing appears in SQL profiler, so it seems that error occures somewhere inside NHibernate):

SELECT
    this_.month_freeze_id as month1_11_0_,
    this_.date as date11_0_,
    this_.is_frozen as is3_11_0_,
    this_.branch_group_id as branch4_11_0_
FROM
    dbo.tbl_dta_month_freeze this_
WHERE
    datepart(month, this_.date) = @p0
    and datepart(year, this_.date) = @p1
    and (
        this_.branch_group_id = @p2
        or (
            @p3 is null
            and this_.branch_group_id is null
        )
    )
    and this_.is_frozen = @p4;

Actually is is looks like old error "aggregation of empty collection" but I am not sure that this is it. And, moreover, how it could be fixed.

Alex G.P.
  • 9,609
  • 6
  • 46
  • 81
  • 1
    have you tried Disjunction or Restrictions.Or in place of your `||` ? See answers to http://stackoverflow.com/q/5474306/1236044 – jbl Dec 19 '13 at 17:50

2 Answers2

1

As @jbl recommended, I replaced my query with following and now it is working. But I still do not know why my code failed.

    var criteria = Session.CreateCriteria<DtaMonthFreeze>()
        .Add(regionId.HasValue ? Restrictions.Eq("BranchGroupId", regionId) : Restrictions.IsNull("BranchGroupId"))
        .Add(Restrictions.Eq(Projections.SqlFunction("MONTH", NHibernateUtil.DateTime, Projections.Property<DtaMonthFreeze>(x => x.Date)), monthDate.Month))
        .Add(Restrictions.Eq(Projections.SqlFunction("YEAR", NHibernateUtil.DateTime, Projections.Property<DtaMonthFreeze>(x => x.Date)), monthDate.Year))
        .Add(Restrictions.Eq("IsFrozen", true));

    var result = criteria.List<DtaMonthFreeze>();

    return result.Any();

Another cons, is that I cannot look onto this code and not crying with a blood.

Alex G.P.
  • 9,609
  • 6
  • 46
  • 81
  • Criteria is quite verbose, but it has its pros as you have seen (like being able to _easily_ build dynamic queries) ;-) – jbl Dec 20 '13 at 10:33
0

You may try the code below, which expressions can be translated correctly by NH (left out the Date part for simplicity)

int? regionId=1;
bool regionIdIsNull = !regionId.HasValue;

var result = Session.QueryOver<DtaMonthFreeze>()
    .Where(Restrictions.Disjunction()
        .Add<DtaMonthFreeze>(x => regionIdIsNull==true && x.BranchGroupId == null)
        .Add<DtaMonthFreeze>(x => x.BranchGroupId == (regionId.HasValue?regionId:null))
    )
    .And(x => x.IsFrozen)
    .List();

Edit just tested the simple :

.And(x => x.BranchGroupId == (regionId.HasValue ? regionId : null) 
    || (regionIdIsNull == true && x.BranchGroupId == null))

which works too.

jbl
  • 15,179
  • 3
  • 34
  • 101