gpt4 book ai didi

c# - NHibernate fluent (QueryOver) 用相关子查询替换 HQL

转载 作者:太空狗 更新时间:2023-10-29 22:01:45 25 4
gpt4 key购买 nike

背景,使用 FluentNHibernate,使用 NHibernate 3.0 的最新开发版本。

这是 WorkIncident 的类型声明:

// Enumeration used in class below.
public enum TicketStatus
{
Open = 1,
Closed = 10,
Hold = 20
}

// Ticket class.
public class WorkIncident
{
public virtual int EntryId { get; set; }
public virtual int TicketNumber { get; set; }
public virtual string ModifierNtId { get; set; }
public virtual DateTime ModifiedDate { get; set; }
public virtual TicketStatus Status { get; set; }
public virtual int Version { get; set; }
public virtual string Title { get; set; }
public virtual string Details { get; set; }
}

// FluentNHibernate mapping
public class WorkIncidentMap : ClassMap<WorkIncident>
{
public WorkIncidentMap()
{
Table("incident_details");
Id( wi => wi.EntryId, "wiid");
Map(wi => wi.TicketNumber, "workitem_number");
Map(wi => wi.Title, "workitem_title");
Map(wi => wi.Details, "workitem_comment");
Map(wi => wi.ModifiedDate, "workitem_modified_on");
Map(wi => wi.ModifierNtId, "modified_by_worker_nt_id");
Map(wi => wi.Status, "workitem_status_lookup_id").CustomType<EnumType<Status>>();
Map(wi => wi.Version, "workitem_version");
}
}

映射工作正常,我可以毫无问题地进行如下查询:

session.QueryOver<AltirisIncident>()
.Where(ai => ai.ModifierNtId == worker.Name.Replace("\\", @"\"))
.AndRestrictionOn(ai => ai.ModifiedDate)
.IsBetween(DateTime.Today)
.And(DateTime.Today.AddDays(1))
.List<WorkIncident>();

这为我提供了特定用户在当前日期触及的所有工作项(基本上是帮助台故障单)。

但是,我一直无法将以下 HQL 翻译成流畅的声明:

from    WorkIncident as t1
where t1.ModifierNtId = :ntid
and t1.ModifiedDate between :startdate and :enddate
and t1.Status = :status
and (t1.Version = 1
or t1.TicketNumber in (
select t2.TicketNumber
from WorkIncident as t2
where t2.Status != t1.Status
and t2.TicketNumber = t1.TicketNumber
and t2.Version = t1.Version - 1))

此查询为我提供了由工作人员置于关闭状态的所有工作项的列表。考虑到工单在数据库中的存储方式(每张工单有多个记录(每次更新一个记录)并且主管通常会在工作人员关闭工单后向工单添加注释,导致我无法只查看工单的情况具有关闭状态的最后版本号,可以可靠地告诉我谁关闭了工单。

任何帮助将不胜感激,因为我更愿意尽可能地远离 HQL 和魔术字符串。

最佳答案

我认为这应该可以解决问题。困难的部分是真正处理你在那里的数学运算。你必须进入 SQLFunction 预测

session.QueryOver<WorkIncident>(() => t1Alias)
.Where(w => w.ModifierNtId == "test")
.And(w => w.ModifiedDate < DateTime.Now && w.ModifiedDate > DateTime.Now)
.And(w => w.Status == TicketStatus.Open)
.And(Restrictions.Disjunction()
.Add(Restrictions.Where<WorkIncident>(w => w.TicketNumber == 1))
.Add(Subqueries.WhereProperty(() => t1Alias.TicketNumber).In(
QueryOver.Of<WorkIncident>(() => t2Alias)
.Where(() => t2Alias.Status != t1Alias.Status)
.And(() => t2Alias.TicketNumber == t1Alias.TicketNumber)
.And(Restrictions.EqProperty(
Projections.Property<WorkIncident>(w=> w.Version),
Projections.SqlFunction(
new VarArgsSQLFunction("(","-",")"),
NHibernateUtil.Int32,
Projections.Property(()=> t1Alias.Version),
Projections.Constant(1)
)))
.Select(w => w.TicketNumber)))
).List();

在我的测试中,这生成了以下 SQL

 SELECT <snip...>
FROM incident_details this_
WHERE this_.modified_by_worker_nt_id = @p0
and (this_.workitem_modified_on < @p1 and this_.workitem_modified_on > @p2)
and this_.workitem_status_lookup_id = @p3
and (this_.workitem_number = @p4
or this_.workitem_number in
(SELECT this_0_.workitem_number as y0_
FROM incident_details this_0_
WHERE not (this_0_.workitem_status_lookup_id = this_.workitem_status_lookup_id)
and this_0_.workitem_number = this_.workitem_number
and this_0_.workitem_version = (this_.workitem_version-@p5)));
@p0 = 'test' [Type: String (0)], @p1 = 10/26/2012 11:26:24 PM [Type: DateTime (0)], @p2 = 10/26/2012 11:26:24 PM [Type: DateTime (0)], @p3 = 1 [Type: Int32 (0)], @p4 = 1 [Type: Int32 (0)], @p5 = 1 [Type: Int32 (0)]

关于c# - NHibernate fluent (QueryOver) 用相关子查询替换 HQL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5384144/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com