C＃实体框架：Linq过滤掉某些GrandChild元素 [英] C# Entity Framework: Linq Filter out certain GrandChild Elements
问题描述
如何使用Linq EF查询过滤出孙元素？该客户有多个交易，仅需要带有某些ProductTypeId的子元素。
How do I filter out Grandchild elements with a Linq EF Query? This Customer has multiple transactions, and only need subchild elements with Certain ProductTypeId. Its currently bringing All ProductType Ids ignoring the filter .
var result = db.Customer
.Include(b => b.Transactions)
.Where(a => a.Transactions.Select(c=> c.ProductTypeId== productTypeId).Any())
我想要的Sql查询：
select distinct c.customerName
from dbo.customer customer
inner join dbo.Transactions transaction
on transaction.customerid = customer.customerid
where transaction.ProductTypeId= 5
Customer (need 7 ProductTypeId)
Transaction ProductTypeId 2
Transaction ProductTypeId 4
Transaction ProductTypeId 5
Transaction ProductTypeId 7 <--- only need this 7 in the filter, example
Transaction ProductTypeId 7 <--- only need this 7 in the filter, example
Transaction ProductTypeId 8
Transaction ProductTypeId 8
Transaction ProductTypeId 9
* Mgmt更喜欢Sql而不是linq。
*Mgmt prefers Include syntax, rather than linq to Sql .
推荐答案
要过滤相关的实体，您需要使用投影。 EF实体图的目的是反映 complete 数据状态。您想要的是过滤后的数据状态。通常这是为了向视图提供相关数据。这是一个单独的目的。
To filter related entities you need to use projection. The purpose of an EF entity graph is to reflect the complete data state. What you want is a filtered data state. This is usually to provided relevant data to a view. That is a separate purpose.
给出一个Customer / Transaction实体，使用一个Customer / Transaction ViewModel，其中仅包含PK和视图/消费者所需的属性。例如：
Given a Customer/Transaction entity, use a Customer/Transaction ViewModel containing just the PKs and the properties that your view/consumer is going to need. For example:
[Serializable]
public class CustomerViewModel
{
public int CustomerId { get; set; }
public string Name { get; set; }
// ...
public ICollection<TransactionViewModel> ApplicableTransactions { get; set; } = new List<TransactionViewModel>();
}
[Serializable]
public class TransactionViewModel
{
public int TransactionId { get; set; }
// ...
}
然后，当您加载客户和客户时， ;过滤的交易：
Then, when you go to load your customers & filtered transactions:
var result = db.Customer
.Where(a => a.Transactions.Select(c=> c.ProductTypeId== productTypeId).Any())
.Select(a => new CustomerViewModel
{
CustomerId = a.CustomerId,
Name = a.Name,
// ...
ApplicableTransactions = a.Transactions
.Where(c => c.ProductTypeId == productTypeId)
.Select(c => new TransactionViewModel
{
TransactionId == c.TransactionId,
// ...
}).ToList();
}).ToList();
利用自动映射进行投影可以大大简化此过程，因为您可以配置实体以查看模型映射（这是一个衬里（如果字段名称相同），则调用 ProjectTo ，Automapper将解析SQL所需的字段并为您构建视图模型：
Leveraging Automapper for your projections can Simplify this considerably, as you can configure the entity to view model mapping (which are one-liners if the field naming is the same) then call ProjectTo and Automapper will resolve the fields needed for the SQL and construct the view models for you:
即
var mappingConfig = new MapperConfiguration(cfg =>
{
cfg.CreateMap<Customer, CustomerViewModel>()
.ForMember(dest => dest.ApplicableTransactions,
opt => opt.MapFrom(src => src.Transactions.Where(t => t.ProductTypeId == productTypeId)
));
cfg.CreateMap<Transaction, TransactionViewModel>();
});
var result = db.Customer
.Where(a => a.Transactions.Select(c=> c.ProductTypeId== productTypeId).Any())
.ProjectTo<CustomerViewModel>(mappingConfig)
.ToList();
对于视图模型，我将使用一个命名约定来反映您为其提供的视图，因为它们实际上仅适用服务于该观点。例如，如果这是按客户检查交易，则类似ReviewTransactionsCustomerViewModel或ReviewTransactionsCustomerVM。不同的视图可以提供不同的视图模型，而可以尝试使所有视图模型都适合。
For the view models I would use a naming convention that reflects the view you are supplying them for as they are really only applicable to serve that view. For instance if this is reviewing transactions by customer then something like ReviewTransactionsCustomerViewModel or ReviewTransactionsCustomerVM. Different views can source different view models vs. trying to have one size fit all.
或者，如果您的代码已经在向视图发送实体（强烈反对），则有两种选择，但这确实有缺点：
Alternatively if your code is already sending Entities to views (which I strongly discourage) there are a couple alternatives, but these do have drawbacks:
- 使用带有过滤子集的包装视图模型：
例如：
[Serializable]
public class ReviewTransactionsViewModel
{
public Customer Customer { get; set; }
public ICollection<Transaction> ApplicableTransactions { get; set; } = new List<Transaction>();
}
然后在选择时：
var result = db.Customer
.Where(a => a.Transactions.Select(c=> c.ProductTypeId== productTypeId).Any())
.Select(a => new ReviewTransactionsViewModel
{
Customer = a,
ApplicableTransactions = a.Transactions
.Where(c => c.ProductTypeId == productTypeId)
.ToList();
}).ToList();
然后在您的视图中，而不是@Model成为Customer，它将变成此视图模型，您只需要进行调整使用 Model.Customer。{property} 的任何引用，而不是使用 Model。{property} 的引用，重要的是，对 Model.Transactions 应该更新为 Model.ApplicableTransactions ，而不是 Model.Customer.Transactions 。
Then in your view, instead of the @Model being a Customer, it becomes this view model and you just need to tweak any references to use Model.Customer.{property} rather than Model.{property} and importantly, any references to Model.Transactions should be updated to Model.ApplicableTransactions, not Model.Customer.Transactions.
这种方法的警告是，为了提高性能，您应该在填充模型的DbContext实例上禁用延迟加载以发送回去，并且只急于加载数据您的意见将是需要的。延迟加载将被代码序列化实体触发，以发送到视图，这很容易成为主要的性能损失。这意味着对 Model.Customer.Transactions 的任何引用都将为空。这也意味着您的模型将不代表完整的实体，因此，当将此模型传递回控制器时，您需要了解这一事实，而不是尝试将其附加为完整的实体或传递给期望完整的实体的方法。
The caveat to this approach is that for performance you should disable lazy loading on the DbContext instance populating your model to send back, and only eager-load the data your view will need. Lazy loading will get tripped by code serializing entities to send to a view which can easily be a major performance hit. This means any references to Model.Customer.Transactions will be empty. It also means that your model will not represent a complete entity, so when passing this model back to the controller you need to be aware of this fact and not attempt to attach it to use as a complete entity or pass to a method expecting a complete entity.
- 将数据过滤到新实体中：（将实体作为视图模型处理）
例如：
var result = db.Customer
.Where(a => a.Transactions.Select(c=> c.ProductTypeId== productTypeId).Any())
.Select(a => new Customer
{
CustomerId = a.CustomerId,
Name = a.Name,
// ... Just the values the view will need.
Transactions = a.Transactions
.Where(c => c.ProductTypeId == productTypeId)
.ToList();
}).ToList();
这可能是一个有吸引力的选择，因为它不需要更改使用视图，但是您必须谨慎，因为此模型现在需要当/如果传递回控制器或任何可能假定提供的客户是完整表示或受跟踪实体的方法，则不能反映完整的数据状态。我相信您可以为<客户，客户> 利用Automapper配置，以帮助简化仅适用列之间的过滤和复制，而忽略不需要的相关实体等。
This can be an attractive option as it requires no changes to the consuming view but you must be cautious as this model now does not reflect a complete data state when/if passed back to the controller or any method that may assume that a provided Customer is a complete representation or a tracked entity. I believe you can leverage an Automapper confguration for <Customer, Customer> to help facilitate the filtering and copying across only applicable columns, ignoring unneeded related entities etc.
在任何情况下，这都应该为您提供一些选择来权衡风险与努力。
In any case, this should give you some options to weigh up risks vs. effort.
这篇关于C＃实体框架：Linq过滤掉某些GrandChild元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
