我遇到的问题可能很简单。
在我的数据库中,有以下表格:
tblOrder
-----------------
Id
OrderStatusId
tblOrderStatus
-----------------
Id
Name
我在项目中进行了以下映射:
[Class(NameType = typeof(Order), Table = "tblOrder")
public class Order {
[Id(-2, Name = "Id")]
[Generator(-1, Class = "native")]
public virtual long Id { get; set; }
[ManyToOne]
public virtual OrderStatus Status { get; set; }
}
[Class(NameType = typeof(OrderStatus), Table = "tblOrderStatus")]
public class OrderStatus {
[Id(-2, Name = "Id")]
[Generator(-1, Class = "native")]
public virtual long Id { get; set; }
[Property]
public virtual string Name { get; set; }
}
查询应返回
IList<OrderSummary>
。我希望类OrderSummary
具有属性Status
,其中Status
是具有Id
和Name
属性的对象。可以使用KeyValuePair
或OrderStatus
类型(以最佳且可行的方式)。获取订单不是问题,但是将OrderStatus
添加为具有上述属性的对象是我遇到的问题。我还需要将查询结果作为JSON返回给客户端。
OrderSummary
应该看起来像这样:public class OrderSummary {
public long Id { get; set; }
public OrderStatus Status { get; set; }
}
在我的第一个版本中,
OrderSummary
具有OrderStatusId
和OrderStatusName
的单独属性。这可行,但我试图避免这些单独的属性。我也尝试过使用
SelectSubQuery
进行此操作,但这会返回错误,因为它在子查询中返回多个字段。-----------------------------------更新-------------- ---------------
遵循Fredy Treboux的建议,我使用
Eager
更改了查询,导致出现以下查询:var query = session.QueryOver<OrderStatus>
.Fetch(o => o.Status).Eager
.JoinAlias(o => o.Status, () => statusAlias, JoinType.LeftOuterJoin);
我发现问题出在,不是选择数据,而是如何转换检索到的
Status
并将其分配给OrderSummary.Status?我尝试了以下方法:OrderSummary orderAlias = null;
query.SelectList(list => list
.Select(o => o.Id).WithAlias(() => orderAlias.Id)
.Select(() => statusAlias).WithAlias(() => orderAlias.Status)
).TransformUsing(Transformer.AliasToBean<OrderSummary>());
--------------------------------答案----------------- -----------------
正如我在上次编辑中所说的那样,问题似乎不是
OrderStatus
的实际选择,而是将其返回给客户端。因此,我认为这是我对NHibernate的了解不足,而与将[JsonObject]
属性添加到OrderStatus
类一样简单。我真傻我已将查询更改为以下内容:
Order orderAlias = null;
OrderSummary orderSummary = null;
OrderStatus statusAlias = null;
var query = session.QueryOver<Order>(() => orderAlias)
.JoinAlias(() => orderAlias.Status, () => statusAlias, JoinType.LeftOuterJoin);
query = query
.Select(
Projections.ProjectionList()
.Add(Projections.Property(() => orderAlias.Id).WithAlias(() => orderSummary.Id))
.Add(Projections.Property(() => orderAlias.Status).WithAlias(() => orderSummary.Status)
);
Result = query.TransformUsing(Tranformers.AliasToBean<OrderSummary>())
.List<OrderSummary>()
.ToList();
最佳答案
抱歉,我之前没有看到您的评论要求提供示例。
我将留下一些代码来解释我提到的方法,尽管它已经在其他响应中作为替代方法给出,并且我相信这是最简单的方法(根本不用变压器):
string GetOrderSummaries()
{
// First, you just query the orders and eager fetch the status.
// The eager fetch is just to avoid a Select N+1 when traversing the returned list.
// With that, we make sure we will execute only one query (it will be a join).
var query = session.QueryOver<Order>()
.Fetch(o => o.Status).Eager;
// This executes your query and creates a list of orders.
var orders = query.List();
// We map these orders to DTOs, here I'm doing it manually.
// Ideally, have one DTO for Order (OrderSummary) and one for OrderStatus (OrderSummaryStatus).
// As mentioned by the other commenter, you can use (for example) AutoMapper to take care of this for you:
var orderSummaries = orders.Select(order => new OrderSummary
{
Id = order.Id,
Status = new OrderSummaryStatus
{
Id = order.Status.Id,
Name = order.Status.Name
}
}).ToList();
// Yes, it is true that this implied that we not only materialized the entities, but then went over the list a second time.
// In most cases I bet this performance implication is negligible (I imagine serializing to Json will possibly be slower than that).
// And code is more terse and possibly more resilient.
// We serialize the DTOs to Json with, for example, Json.NET
var orderSummariesJson = JsonConvert.SerializeObject(orderSummaries);
return orderSummariesJson;
}
有用的链接:
自动映射器:http://automapper.org/
Json.NET:http://www.newtonsoft.com/json
关于c# - NHibernate使用QueryOver选择具有相关子对象的对象列表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44152318/