Query failed when parameter is null

Question

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.

have you tried Disjunction or Restrictions.Or in place of your || ? See answers to stackoverflow.com/q/5474306/1236044 — jbl
– jbl, Commented Dec 19, 2013 at 17:50

Alex G.P. · Accepted Answer · 2013-12-20 07:08:36Z

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.

answered Dec 20, 2013 at 7:08

Alex G.P.

10.1k6 gold badges50 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jbl Over a year ago

Criteria is quite verbose, but it has its pros as you have seen (like being able to easily build dynamic queries) ;-)

jbl · Accepted Answer · 2013-12-20 10:37:30Z

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.

edited Dec 20, 2013 at 10:37

answered Dec 20, 2013 at 10:24

jbl

15.5k3 gold badges37 silver badges104 bronze badges

Collectives™ on Stack Overflow

Query failed when parameter is null

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related