如何将LINQ映射到SQL以启用预先加载，返回EntitySet或ICollection? [英] How to map LINQ To SQL to enable eager loading, return EntitySet or ICollection?
问题描述
This is related (but fairly independent) to my question here: Why SELECT N + 1 with no foreign keys and LINQ?
我尝试使用DataLoadOptions强制进行急切加载，但是我无法使其正常工作.
I've tried using DataLoadOptions to force eager loading, but I'm not getting it to work.
我正在手动编写我的LinqToSQL映射，并且首先遵循了本教程:
I'm manually writing my LinqToSQL mappings and was first following this tutorial: http://www.codeproject.com/Articles/43025/A-LINQ-Tutorial-Mapping-Tables-to-Objects
现在，我已经找到了本教程: http://msdn.microsoft .com/en-us/library/bb386950.aspx
Now I've found this tutorial: http://msdn.microsoft.com/en-us/library/bb386950.aspx
我可以发现至少有一个主要区别.第一个教程建议返回ICollection，第二个EntitySet.由于遇到问题，我尝试切换代码以返回EntitySet，但是随后出现问题，需要在我的视图和控制器中引用System.Data.Linq.我试图做到这一点，但是没有使它起作用.我也不确定这是个好主意.
There's at least one major difference that I can spot. The first tutorial suggest returning ICollection's and the second EntitySet's. Since I'm having issues I tried to switch my code to return EntitySet's, but then I got issue with needing to reference System.Data.Linq in my Views and Controllers. I tried to do that, but didn't get it to work. I'm also not sure it's a good idea.
在这一点上，我只想知道我应该使用哪种返回类型进行良好的设计?我可以有一个好的设计，仍然能够在特定情况下强制进行急切加载吗?
At this point, I just want to know which return type I'm supposed to use for a good design? Can I have a good design and still be able to force eager loading in specific cases?
推荐答案
许多尝试和错误最终导致了解决方案.返回ICollection或IList，或者在某些情况下返回IEnumerable是可以的.有些人认为返回EntitySet或IQueryable是个坏主意，我同意，因为它暴露了很多数据源/技术.返回IEnumerable的某些东西是一个坏主意，似乎取决于情况.问题在于它可以用于延迟加载，这可能是好事，也可能不是好事.
A lot of trial and error finally lead to the solution. It's fine to return ICollection or IList, or in some cases IEnumerable. Some think returning EntitySet or IQueryable is a bad idea, and I agree because it exposes to much of the datasource/technology. Some thing returning IEnumerable is a bad idea and it seems like it depends. The problem beeing that it can be used for lazy loading, which may or may not be a good thing.
一个经常发生的问题是返回分页结果，并计算页面外的总项目数.可以通过创建CollectionPage<T>( http://www.codetunnel.com/blog/post/104/how-to-properly-return-a-paged-result-set-from-your-repository )
One reoccuring issue is that of returning paged results with a count for the total items outside the page. This can be solved by creating a CollectionPage<T> ( http://www.codetunnel.com/blog/post/104/how-to-properly-return-a-paged-result-set-from-your-repository )
有关从存储库中返回的内容的更多信息:
More on what to return from repositories here:
http://www .codetunnel.com/blog/post/103/should-you-return-iqueryablet-from-your-repositories
http://www.shawnmclean.com/blog/2011/06/iqueryable-vs-ienumerable-in-the-repository-pattern/
用于业务逻辑或DAL返回的IEnumerable与IQueryable类型
列表，IList，IEnumerable，IQueryable，ICollection ，哪一种是最灵活的返回类型?
更重要的是，DataLoadOptions 可以进行急切的加载！我现在已经重组了代码太多，以至于我不能百分百确定我做错了什么导致DataLoadOptions无法正常工作.就我所收集的而言，如果我在使用DataContext之后尝试将其添加到DataContext中，应该会得到一个异常，但是没有.不过，我发现的是在工作单位"模式中进行思考.但是，出于我的需要(并且因为我不想从存储库中返回EntitySet或IQueryable)，我不会实现跨存储库的工作单元.相反，我只是将存储库方法视为自己的小型工作单元.我确信这有不好的事情(例如，在某些更新方案中，它可能导致数据库往返更多)，并且将来我可能会考虑.但这是一个简单的干净解决方案.
Even more important, DataLoadOptions can do the eager loading! I've now restructured my code so much I'm not 100% sure what I did wrong to cause DataLoadOptions not to work. As far as I've gathered I should get an exception if I tried to add it to the DataContext after the DataContext has been used, which it didn't. What I've found out though is to think in the Unit of Work-pattern. However, for my needs (and because I don't want to return EntitySet or IQueryable from my repositories) I'm not going to implement a cross-repository Unit of Work. Instead I'm just thinking about my repository methods as their own small Unit of Work. I'm sure there's bad things about this (for instance it might cause more round-trips to the database in some update scenarios), and in the future I might reconcider. However it's a simple clean solution.
更多信息在这里:
https://stackoverflow.com/a/7941017/1312533
This is what I ended up with in my repository:
public class SqlLocalizedCategoriesRepository : ILocalizedCategoriesRepository
{
private string connectionString;
private HttpContextBase httpContext;
public SqlLocalizedCategoriesRepository(string connectionString, HttpContextBase httpContext) // Injected with Inversion of Control
{
this.connectionString = connectionString;
this.httpContext = httpContext;
}
public CollectionPage<Product> GetProductsByLocalizedCategory(string category, int countryId, int page, int pageSize)
{
// Setup a DataContext
using (var context = new DataContext(connectionString)) // Because DataContext implements IDisposable it should be disposed of
{
var dlo = new System.Data.Linq.DataLoadOptions();
dlo.LoadWith<Product>(p => p.ProductSubs); // In this case I want all ProductSubs for the Products, so I eager load them with LoadWith. There's also AssociateWith which can filter what is eager loaded.
context.LoadOptions = dlo;
context.Log = (StringWriter)httpContext.Items["linqToSqlLog"]; // For logging queries, a must so you can see what LINQ to SQL generates
// Query the DataContext
var cat = (from lc in context.GetTable<LocalizedCategory>()
where lc.CountryID == countryId && lc.Name == category
select lc.Category).First(); // Gets the category into memory. Might be some way to not get it into memory by combining with the next query, but in my case my next step is that I'm also going to need the Category anyway so it's not worth doing because I'm going to restructure this code to take a categoryId parameter instead of the category parameter.
var products = (from p in context.GetTable<Product>()
where p.ProductCategories.Any(pm => pm.Category.CategoryID == cat.CategoryID)
select p); // Generates a single query to get the the relevant products, which with DataLoadOptions loads related ProductSubs. It's important that this is just a query and not loaded into memory since we're going to split it into pages.
// Return the results
var pageOfItems = new CollectionPage<Product>
{
Items = products.Skip(pageSize * (page - 1)).Take(pageSize).ToList(), // Gets the page of products into memory
TotalItems = products.Count(), // Get to total count of items belonging to the Category
CurrentPage = page
};
return pageOfItems;
}
}
}
这篇关于如何将LINQ映射到SQL以启用预先加载，返回EntitySet或ICollection?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
