NHibernate CreateCriteria with Restrictions does not work as expected

Question

The bellow given code sample is to retrieve all the active records.

session.CreateCriteria<VesselMasterData>()
                        .CreateAlias("BasicInfo", "bsInfo")
                        .CreateAlias("DimentionInfo", "diInfo")
                        .Add(Restrictions.Eq("IsActive", 1))
                        .Add(Restrictions.Eq("diInfo.IsActive", 1))
                        .Add(Restrictions.Eq("MasterDataID", masterDataID))
                        .Add(Restrictions.Eq("bsInfo.IsActive", 1))
                        .List<VesselMasterData>()

Parent table VesselMasterData and child tables are BasicInfo and DimentionInfo. Based on the query expected values should be all active records of the BasicInfo and DimentionInfo.

But in the out put i get all records from the BasicInfo and DimentionInfo. What could be the issue.

This is the mapping of the VesselMasterData table.

public class VesselMasterDataMap : ClassMap<VesselMasterData>
    {
        public VesselMasterDataMap()
        {
Table("VPD_VESSEL_MASTER_DATA");
            Schema("APPLN1");
            Id(x => x.MasterDataID).Column("MASTER_DATA_ID").GeneratedBy.Sequence("VPD_VESSEL_MASTER_DATA_SEQ");
            Map(x => x.VesselCode).Column("VESSEL_CODE");
            Map(x => x.IsActive).Column("IS_ACTIVE");
HasMany<VesselBasicInfo>(prop => prop.BasicInfo)
                    .KeyColumns.Add("MASTER_DATA_ID").Cascade.SaveUpdate().Inverse().Not.LazyLoad();
            HasMany<VesselDimension>(prop => prop.DimentionInfo)
                    .KeyColumns.Add("MASTER_DATA_ID").Cascade.SaveUpdate().Inverse().Not.LazyLoad();
        }
   }

Yogendra Singh · Answer 1 · 2012-11-15T03:50:02.763

0

Use Restrictions.And to add the join conditions as:

       session.CreateCriteria<VesselMasterData>()
              .CreateAlias("BasicInfo", "bsInfo")
              .CreateAlias("DimentionInfo", "diInfo")
              .Add(Restrictions.And(
                     Restrictions.Eq("IsActive", 1),
                     Restrictions.And(
                         Restrictions.Eq("diInfo.IsActive", 1),
                         Restrictions.And(
                              Restrictions.Eq("MasterDataID", masterDataID),
                              Restrictions.Eq("bsInfo.IsActive", 1)
                         )
                     )
                  )
               ).List<VesselMasterData>()

edited Nov 15 '12 at 03:50

answered Nov 15 '12 at 03:31

Yogendra Singh

33,927
6
63
73

Thanks for the prompt reply. Unfortunately the BasicInfo and DimentionInfo do not have a property called MasterDataID since they just have MasterData. This is a reference to the parent table. One more thing since i am using WCF reference to the parent entity from the child entity is not implemented. If i implement that then cyclic redundancy issue will arise. – Desmond Nov 15 '12 at 03:40
@Desmond: Is there any relational attribute? Also I think you need to use `Restrictions.And` to add various conditions. I updated the answer. Please check, if that helps. – Yogendra Singh Nov 15 '12 at 03:50
I tried but it returned the same. I created a Query and posted in the answer below and it works fine. But i do need to find the equivalent one using CreateCriteria. If you could convert this to that it would be grateful. – Desmond Nov 15 '12 at 04:05
@Desmond: That was thing I was asking. If you notice, there are four joining condition in this `VesselMasterData vm inner join fetch vm.AcquisitionInfo acq inner join fetch vm.BasicInfo bi inner join fetch vm.DimentionInfo di inner join fetch vm.SaleInfo si`. I don't see these conditions in the criteria query. In the beginning I suggested to add one but you mentioned there was no such columns. Since now we know it, add all joining conditions in the criteria query as well, I am sure it will work too! – Yogendra Singh Nov 15 '12 at 04:13
Hi, Thanks once again for the effort that you put to resolve the issue. **I do understand your point here**. But let me explain you. In the **BasicInfo** entity there is a property called **MasterData** which is a type of **VesselMasterData**. So the code that you mentioned was 'bsInfo.MasterDataID'. This property does not exist in the **BasicInfo** entity. – Desmond Nov 15 '12 at 04:20
@Desmond: I agree, MasterDataID may not be a property. I used that column as I didn't have much visibility. **All I am trying to say that use the same columns which are participating in the joins of the query in your answer**. – Yogendra Singh Nov 15 '12 at 04:32

score 0 · Answer 2 · answered Nov 15 '12 at 04:08

This works the way it is expected but looking forward to get the equivalent CreateCriteria Code.

    session.CreateQuery(string.Format("from VesselMasterData vm inner join fetch 
vm.AcquisitionInfo acq inner join fetch vm.BasicInfo bi inner join fetch 
vm.DimentionInfo di inner join fetch vm.SaleInfo si 
where vm.MasterDataID={0}  and bi.IsActive = 1 and di.IsActive = 1 ", masterDataID)).Future<VesselMasterData>();

NHibernate CreateCriteria with Restrictions does not work as expected

2 Answers2