使用 Load* 方法时如何在引用上设置条件/过滤器 [英] How to set conditions/filters on references when using Load* methods

问题描述

我有两个表:Customer 和 Orders.客户对订单有如下引用:

I have two tables: Customer and Orders. The customer has a reference to Orders like such:

[Reference]
public List<Order> Orders { get; set; }

Order 类有一个属性 Deleted.我想加载所有客户(或子集)，并包括订单，但不包括 Deleted=true 的订单.这可以用 LoadSelect 方法完成，或者推荐的方法是什么?

The Order class has an attribute Deleted. I'd like to load all Customers (or a subset), and include the Orders, but not the ones with Deleted=true. Can this be done with LoadSelect methods, or what is the recommended way?

大致相当于以下 SQL 的内容:

Something that would roughly equal the following SQL:

select * from Customers C 
join Orders O on O.CustomerId = C.Id 
where O.Deleted = False

这是最好的方法吗?

var orderIds = resp.Customers.Select(q => q.Id).ToList();
var allOrders = Db.Select<Order>(o => orderIds.Contains(o.CustomerId) && !o.Deleted);
foreach (var order in allOrders)
{
    var cust = resp.Customers.First(q => q.Id == order.custivityId);
    if (cust.Orders == null) cust.Orders = new List<Order>();
    cust.Orders.Add(order);
}

推荐答案

我刚刚添加了一个新的 Merge API 在此提交中 自动加入父集合及其子引用，这将使这更容易一些.

I've just added a new Merge API in this commit to automatically join a parent collection with their child references that will make this a little easier.

使用新 API，您可以分别选择客户和订单并将集合合并在一起，例如:

With the new API you can select customers and orders separately and merge the collections together, e.g:

//Select only Customers which have valid orders
var customers = db.Select<Customer>(q =>
    q.Join<Order>()
     .Where<Order>(o => o.Deleted == false)
     .SelectDistinct());

//Select valid orders
var orders = db.Select<Order>(o => o.Deleted == false);

customers.Merge(orders); //merge the results together

customers.PrintDump();   //print the results of the merged collection

此更改可从 v4.0.39+ 开始，现在 可在我的获取.

This change is available from v4.0.39+ that's now available on MyGet.

这篇关于使用 Load* 方法时如何在引用上设置条件/过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！