代码之家 › 专栏 › 技术社区 › Chandra Mohan

nhibernate 3.0 iqueryover group by并在每个组中获取一条满足条件的完整记录

nhibernate-queryover nhibernate linq c#

Chandra Mohan · 技术社区 · 6 年前

我的要求是获取SiteItemOnHand记录,按BusinessUnitID、InventoryItemID分组,并获取每个组中最大值(LastModifiedTimeStamp)的整个SiteItemOnHand记录。

我正在尝试为下面的SQL查询编写NHibernate等效查询

Select x.* from 
(
Select  businessunitid,inventoryitemid,max(lastmodifiedtimestamp) as maxvalue from InventoryItemBUOnHand  
group by businessunitid,inventoryitemid HAVING inventoryitemid in (939) and businessunitid=829
) as x
inner join
(
    Select  businessunitid,inventoryitemid,lastmodifiedtimestamp,inventoryitemonhandid from InventoryItemBUOnHand 
    where inventoryitemid in (939) and businessunitid=829
) as y
on x.businessunitid=y.businessunitid and  x.inventoryitemid =y.inventoryitemid and x.maxvalue=y.lastmodifiedtimestamp

我有很多限制。我只允许使用nhibernate 3.0版本,并且严格禁止编写SQL查询(条件)。我只写了下面查询的一半。任何帮助都将不胜感激。


var query= _session.QueryOver<SiteItemOnHand>()
      .Where(x => x.Businessunitid == siteID)
      .Where(x => x.End == endDate)
      .Where(x => x.CountID != filterCount.ID)
      .WhereRestrictionOn(x => x.ItemID).IsIn(itemIdList.ToArray())
      .SelectList(list => list
      .SelectMax(x => x.LastModifiedTimestamp)
      .SelectGroup(x => x.Businessunitid)
      .SelectGroup(x => x.ItemId));

上面的查询正在生成下面的SQL查询,它只返回三列,但我需要整个记录,因为在检索记录之后,我需要更新。我需要获取满足上述查询的所有SiteItemOnHand记录。

SELECT max(this_.lastmodifiedtimestamp) as y0_, this_.businessunitid as y1_, this_.inventoryitemid as y2_ 
FROM InventoryItemBUOnHand this_ 
WHERE this_.businessunitid = 567 and this_.enddate = '1/31/2019 1:18:17 AM' and not (this_.itemcountid = 958) 
and this_.inventoryitemid in (744,755)
GROUP BY this_.businessunitid, this_.inventoryitemid

任何帮助都将不胜感激。如果您需要更多信息,请告诉我。

1 回复 | 直到 6 年前

Florian Lim 6 年前

如果我正确理解你,你有这样的数据,你想要有标记的记录。我不知道是否 iventoryitemonhandid 是 id 所以我不依赖它。

Table InventoryItemBUOnHand (mapped as SiteItemOnHand)

| businessunitid|inventoryitemid|lastmodifiedtimestamp|inventoryitemonhandid|
|            829|            939|  2019-01-01 00:00:00|                  100| <--
|            829|            940|  2019-01-02 00:00:00|                  101| 
|            829|            940|  2019-01-03 00:00:00|                  102| <-- 
|            829|            950|  2019-01-04 00:00:00|                  103| 
|            829|            950|  2019-01-10 00:00:00|                  104| <--
|            829|            950|  2019-01-06 00:00:00|                  105|

如果是,那么我将使用如下问题中的子查询: SO: NHibernate - Select full records from aggregates

根据您的需求修改(未测试)如下:

int siteID = 829;
List<int> itemIdList = new List<int>() { 939, 940, 950 };
SiteItemOnHand siAlias = null;

var subQuery = QueryOver.Of<SiteItemOnHand>()
    .Where(x => x.Businessunitid == siAlias.Businessunitid)
    .And(x => x.ItemID == siAlias.ItemID)
    .Select(Projections.Max<SiteItemOnHand>(y => y.lastmodifiedtimestamp));

var siteItems = Session.QueryOver<SiteItemOnHand>(() => siAlias)
    .Where(x => x.Businessunitid == siteID)
    .AndRestrictionOn(x => x.ItemID).IsIn(itemIdList.ToArray())
    .WithSubquery.Where(x => siAlias.lastmodifiedtimestamp == subQuery.As<DateTime>())
    .List();

这里的目标是使用子查询筛选每个组的正确最大日期,然后使用该查询筛选实际记录。

生成的SQL如下所示:

SELECT <field_list> FROM InventoryItemBUOnHand 
WHERE Businessunitid = 829
AND inventoryitemid in (939, 940, 950)
AND this_.lastmodifiedtimestamp = 
   (SELECT max(this_0_.lastmodifiedtimestamp) as y0_ 
       FROM InventoryItemBUOnHand this_0_ 
       WHERE this_0_.Businessunitid = this_.Businessunitid and this_0_.ItemID = this_.ItemID)

警告: 比较 lastmodifiedtimestamp 当两个记录具有相同的值时,可能会导致不需要的结果 businessunitid 和 inventoryitemid . 如果可能,您可以添加 OrderBy 只选择第一条记录。如果 inventoryitemonhandid 是唯一索引。