How to merge 2 table ?

[HakanKaraoglu]

How can I convert a linq query to nhibernate query like below?

_nhUoW.AlarmRuleRepository.FilterBy(x => x.Alarm.WorkspaceId == workspaceId && x.Alarm.Status == 1
                && x.Alarm.IsActive && x.IsActive
                && (x.Alarm.Type.Id == (int)AlarmTypeEnum.ALARM_TYPES_GENERAL || 
x.Alarm.Type.Id == (int)AlarmTypeEnum.ALARM_TYPES_CUMULATIVE || 
x.Alarm.Type.Id == (int)AlarmTypeEnum.ALARM_TYPES_CUMULATIVE_USER))
                .GroupBy(x => x.Alarm).ToList();

I did something like the following, but the number of sessions opened is increasing by the number of Alarm Rules. I dont want this.

var alarm = await _sessionFactory.OpenSession().CreateCriteria<Alarm>()
                               .Add(Restrictions.Eq("WorkspaceId", 1))
                               .Add(Restrictions.Eq("IsActive", true))
                               .Add(Restrictions.Eq("Status", 1))
                               .Add(Restrictions.Disjunction()
                                .Add(Restrictions.Eq("Type.Id", (long)AlarmTypeEnum.ALARM_TYPES_GENERAL))
                                .Add(Restrictions.Eq("Type.Id", (long)AlarmTypeEnum.ALARM_TYPES_CUMULATIVE))
                                .Add(Restrictions.Eq("Type.Id", (long)AlarmTypeEnum.ALARM_TYPES_CUMULATIVE_USER)))
                               .ListAsync<Alarm>();

            foreach (var item in alarm)
            {
                var alarmRules = await _sessionFactory.OpenSession().CreateCriteria<AlarmRule>()
                                                .Add(Restrictions.Eq("Alarm", item))
                                                .Add(Restrictions.Eq("IsActive", true)).ListAsync<AlarmRule>();
            }

can i get values in a single query

[gliljas]

Why not use Linq?

[HakanKaraoglu]

I want to learn how to do it with Nhibernate

[gliljas]

NHibernate has strong support for Linq. But, sure, if you really want to do it with Criteria, you should start from AlarmRule.

See 16.4

[HakanKaraoglu]

I looked at the document, as you said, if I start with AlarmRule, I get an error because there are no criteria in AlarmRule. I think there is something I don't understand in setting the second criterion. I am also looking at the printout of the linq profile, there is something I missed

The query I created:

CreateCriteria<Alarm>()
    .Add(Restrictions.Eq("WorkspaceId", 208))
    .Add(Restrictions.Eq("IsActive", true))
    .Add(Restrictions.Eq("Status", 1))
    .Add(Restrictions.Disjunction()
    .Add(Restrictions.Eq("Type.Id", (long)AlarmTypeEnum.ALARM_TYPES_GENERAL))
    .Add(Restrictions.Eq("Type.Id", (long)AlarmTypeEnum.ALARM_TYPES_CUMULATIVE))
    .Add(Restrictions.Eq("Type.Id", (long)AlarmTypeEnum.ALARM_TYPES_CUMULATIVE_USER)))
    .CreateCriteria("AlarmRule")
    .Add(Restrictions.Eq("Alarm","Alarm"))
        .List<AlarmRule>();

Profiler :

select
	 alarmrule0_.ID as id1_7_,
	 alarmrule0_.ISACTIVE as isactive2_7_,
	 alarmrule0_.CREATEUSER as createuser3_7_,
	 alarmrule0_.CREATETIME as createtime4_7_,
	 alarmrule0_.UPDATEUSER as updateuser5_7_,
	 alarmrule0_.UPDATETIME as updatetime6_7_,
	 alarmrule0_.EXPRESSION as expression7_7_,
	 alarmrule0_.ALARM_ID as alarm8_7_ 
from ALARM_RULE alarmrule0_ inner join ALARM alarm1_ on alarmrule0_.ALARM_ID=alarm1_.ID 
where alarm1_.WORKSPACEID=66 /* :p0 */ and
	 alarm1_.STATUS=1 /* :p1 */ and
		 alarm1_.ISACTIVE=1 and
			 alarmrule0_.ISACTIVE=1 and
				 (
					alarm1_.TYPE_ID=1 /* :p2 */ or
						 alarm1_.TYPE_ID=3 /* :p3 */ or
							 alarm1_.TYPE_ID=5 /* :p4 */)

Entites :

public class Alarm : EntityBase<long>
    {
        public virtual string Title { get; set; }
        public virtual int DeliveryPeriod { get; set; }
        public virtual AlarmType Type { get; set; }
        public virtual int WorkspaceId { get; set; }
        public virtual int Period { get; set; }
        public virtual int FeedCount { get; set; }
        public virtual int Status { get; set; }
    }

public class AlarmRule : EntityBase<long>
    {
        public virtual Alarm Alarm { get; set; }
        public virtual string Expression { get; set; }
    }