C# 实体框架:Linq 过滤掉某些 GrandChild 元素 [英] C# Entity Framework: Linq Filter out certain GrandChild Elements
问题描述
如何使用 Linq EF 查询过滤掉孙元素?该客户有多个交易，只需要具有特定 ProductTypeId 的子子元素.它目前带来了忽略过滤器的所有产品类型 ID.
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 更喜欢 Include 语法，而不是 linq to Sql .
*Mgmt prefers Include syntax, rather than linq to Sql .
推荐答案
要过滤相关实体，您需要使用投影.EF 实体图的目的是反映完整 数据状态.您想要的是过滤后的数据状态.这通常是为了向视图提供相关数据.这是一个单独的目的.
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();
利用 Automapper 进行投影可以大大简化这一过程，因为您可以将实体配置为查看模型映射(如果字段命名相同，则为单行)然后调用 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();
这可能是一个有吸引力的选择，因为它不需要对消费视图进行任何更改，但您必须谨慎，因为当/如果传递回控制器或任何可能假定前提是客户是完整的代表或被跟踪的实体.我相信您可以利用 <Customer, Customer> 的 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屋！
