这个存储库模式对于 LINQ-to-SQL 是否有效? [英] Is this Repository pattern efficient with LINQ-to-SQL?

问题描述

我目前正在阅读 Pro Asp.Net MVC 框架这本书.在书中，作者建议使用类似于以下内容的存储库模式.

[Table(Name = "Products")]公开课产品{[列(IsPrimaryKey = true，IsDbGenerated = 真,AutoSync = AutoSync.OnInsert)]公共 int ProductId { 获取；放;}[列] 公共字符串名称 { get;放;}[列] 公共字符串 说明 { get;放;}【栏目】公开十进制价格{获取；放;}[列] 公共字符串类别{ get;放;}}公共接口 IProductsRepository{IQueryable<产品>产品{得到;}}公共类 SqlProductsRepository : IProductsRepository{私人表<产品>产品表；公共 SqlProductsRepository(string connectionString){productsTable = new DataContext(connectionString).GetTable();}公共 IQueryable产品{得到 { 返回产品表;}}}

然后以下列方式访问数据:

public ViewResult List(string category){var productsInCategory = (category == null) ?productsRepository.Products : productsRepository.Products.Where(p => p.Category == category);返回视图(productsInCategory)；}

这是访问数据的有效方式吗?是要从数据库中检索整个表并在内存中进行过滤，还是链接的 Where() 方法会导致某些 LINQ 魔法基于 lambda 创建优化查询?

最后，当通过 LINQ-to-SQL 连接时，C# 中 Repository 模式的其他哪些实现可能会提供更好的性能?

解决方案

我能理解 Johannes 的愿望更严格地控​​制 SQL 的执行，并且通过我有时称之为惰性锚点"的实现，我已经能够在我的应用程序中做到这一点.

我使用自定义 LazyList 和 LazyItem 类的组合来封装延迟初始化:

• LazyList 包装了 IList 集合的 IQueryable 功能，但最大化了一些 LinqToSql 的延迟执行函数和
• LazyItem 将使用 LinqToSql IQueryable 或通用的 Func 方法包装单个项目的惰性调用延迟执行其他代码.

这是一个例子 - 我有这个模型对象 Announcement 它可能有一个附加的图像或 pdf 文档:

公开课公告://..{公共 int ID { 获取；放;}公共字符串标题{获取；放;}公共公告Category Category { get;放;}公共字符串正文{获取；放;}公共 LazyItem图像 { 得到;放;}public LazyItemPdfDoc { 得到;放;}}

Image 和 PdfDoc 类从一个类型 File 继承，该类型包含 byte[] 包含二进制数据.这个二进制数据很重，每次我想要Announcement 时，我可能并不总是需要从数据库返回它.所以我想保持我的对象图锚定"而不是填充"(如果你愿意).

所以如果我做这样的事情:

Console.WriteLine(anAnnouncement.Title);

..我可以知道我只从数据库加载了直接 Announcement 对象的数据.但如果在以下行我需要这样做:

Console.WriteLine(anAnnouncement.Image.Inner.Width);

..我可以确定 LazyItem 知道如何去获取其余的数据.

另一个很大的好处是，这些懒惰"的类可以隐藏底层存储库的特定实现，因此我不一定非要使用 LinqToSql.对于我从中剪切示例的应用程序，我(使用 LinqToSql)很容易插入另一个数据源(甚至可能不使用存储库模式的完全不同的数据层).

LINQ 但不是 LinqToSql

您会发现有时您想要执行一些奇特的 LINQ 查询，当执行向下流到 LinqToSql 提供程序时，会发生在 barf 上.这是因为 LinqToSql 的工作原理是将有效的 LINQ 查询逻辑转换为 T-SQL 代码，有时这并不总是可行的.

例如，我有这个函数，我想要一个 IQueryable 结果来自:

 private IQueryableGetLatestSortedEvents(){//待办事项:警告:大量 SQL 查询！使固定返回 this.GetSortedEvents().ToList().Where(ModelExtensions.Event.IsUpcomingEvent()).AsQueryable();}

为什么该代码不转换为 SQL 并不重要，但请相信我，IsUpcomingEvent() 谓词中的条件涉及许多 DateTime 比较对于 LinqToSql 转换为 T-SQL 来说太复杂了.

通过使用 .ToList() 然后条件 (.Where(..) 然后 .AsQueryable() 我有效告诉 LinqToSql 我需要所有 .GetSortedEvents() 项目，即使我要过滤它们.这是我的过滤器表达式无法正确呈现给 SQL 的一个实例，所以我需要过滤它在内存中.就延迟执行和延迟加载而言，这将是我可能称之为 LinqToSql 性能的限制 - 但我只有少数这些 WARNING: HEAVY SQL QUERY! 块我的应用程序和我认为进一步的智能重构可以完全消除它们.

最后，如果您愿意，LinqToSql 可以在大型应用程序中成为一个很好的数据访问提供程序.我发现为了得到我想要的结果并抽象和隔离我需要在这里和那里添加代码的某些东西.在我想要更多地控制来自 LinqToSql 的实际 SQL 性能的地方，我添加了智能以获得所需的结果.因此，恕我直言，LinqToSql 非常适合需要数据库查询优化的重型应用程序，前提是您了解 LinqToSql 的工作原理.我的设计最初基于 Rob 的 Storefront 教程，因此如果您需要更多解释，您可能会发现它很有用关于我上面的咆哮.

如果你想使用上面那些懒惰的类，你可以得到它们此处 和此处.>

I'm currently reading the book Pro Asp.Net MVC Framework. In the book, the author suggests using a repository pattern similar to the following.

[Table(Name = "Products")]
public class Product
{
    [Column(IsPrimaryKey = true, 
            IsDbGenerated = true, 
            AutoSync = AutoSync.OnInsert)]
    public int ProductId { get; set; }
    [Column] public string Name { get; set; }
    [Column] public string Description { get; set; }
    [Column] public decimal Price { get; set; }
    [Column] public string Category { get; set; }
}

public interface IProductsRepository
{
    IQueryable<Product> Products { get; }
}

public class SqlProductsRepository : IProductsRepository
{
    private Table<Product> productsTable;

    public SqlProductsRepository(string connectionString)
    {
        productsTable = new DataContext(connectionString).GetTable<Product>();
    }

    public IQueryable<Product> Products
    {
        get { return productsTable; }
    }
}

Data is then accessed in the following manner:

public ViewResult List(string category)
{
    var productsInCategory =  (category == null) ? productsRepository.Products : productsRepository.Products.Where(p => p.Category == category);

    return View(productsInCategory);
}

Is this an efficient means of accessing data? Is the entire table going to be retrieved from the database and filtered in memory or is the chained Where() method going to cause some LINQ magic to create an optimized query based on the lambda?

Finally, what other implementations of the Repository pattern in C# might provide better performance when hooked up via LINQ-to-SQL?

解决方案

I can understand Johannes' desire to control the execution of the SQL more tightly and with the implementation of what i sometimes call 'lazy anchor points' i have been able to do that in my app.

I use a combination of custom LazyList<T> and LazyItem<T> classes that encapsulate lazy initialization:

• LazyList<T> wraps the IQueryable functionality of an IList collection but maximises some of LinqToSql's Deferred Execution functions and
• LazyItem<T> will wrap a lazy invocation of a single item using the LinqToSql IQueryable or a generic Func<T> method for executing other code deferred.

Here is an example - i have this model object Announcement which may have an attached image or pdf document:

public class Announcement : //..
{
    public int ID { get; set; }
    public string Title { get; set; }
    public AnnouncementCategory Category { get; set; }
    public string Body { get; set; }
    public LazyItem<Image> Image { get; set; }
    public LazyItem<PdfDoc> PdfDoc { get; set; }
}

The Image and PdfDoc classes inherit form a type File that contains the byte[] containing the binary data. This binary data is heavy and i might not always need it returned from the DB every time i want an Announcement. So i want to keep my object graph 'anchored' but not 'populated' (if you like).

So if i do something like this:

Console.WriteLine(anAnnouncement.Title);

..i can knowing that i have only loaded from by db the data for the immediate Announcement object. But if on the following line i need to do this:

Console.WriteLine(anAnnouncement.Image.Inner.Width);

..i can be sure that the LazyItem<T> knows how to go and get the rest of the data.

Another great benefit is that these 'lazy' classes can hide the particular implementation of the underlying repository so i don't necessarily have to be using LinqToSql. I am (using LinqToSql) in the case of the app I'm cutting examples from, but it would be easy to plug another data source (or even completely different data layer that perhaps does not use the Repository pattern).

LINQ but not LinqToSql

You will find that sometimes you want to do some fancy LINQ query that happens to barf when the execution flows down to the LinqToSql provider. That is because LinqToSql works by translating the effective LINQ query logic into T-SQL code, and sometimes that is not always possible.

For example, i have this function that i want an IQueryable result from:

    private IQueryable<Event> GetLatestSortedEvents()
    {
        // TODO: WARNING: HEAVY SQL QUERY! fix
        return this.GetSortedEvents().ToList()
            .Where(ModelExtensions.Event.IsUpcomingEvent())
            .AsQueryable();
    }

Why that code does not translate to SQL is not important, but just believe me that the conditions in that IsUpcomingEvent() predicate involve a number of DateTime comparisons that simply are far too complicated for LinqToSql to convert to T-SQL.

By using .ToList() then the condition (.Where(..) and then .AsQueryable() i'm effectively telling LinqToSql that i need all of the .GetSortedEvents() items even tho i'm then going to filter them. This is an instance where my filter expression will not render to SQL correctly so i need to filter it in memory. This would be what i might call the limitation of LinqToSql's performance as far as Deferred Execution and lazy loading goes - but i only have a small number of these WARNING: HEAVY SQL QUERY! blocks in my app and i think further smart refactoring could eliminate them completely.

Finally, LinqToSql can make a fine data access provider in large apps if you want it to. I found that to get the results i want and to abstract away and isolate certain things i've needed to add code here and there. And where i want more control over the actual SQL performance from LinqToSql, i've added smarts to get the desired results. So IMHO LinqToSql is perfectly ok for heavy apps that need db query optimization provided you understand how LinqToSql works. My design was originally based on Rob's Storefront tutorial so you might find it useful if you need more explanation about my rants above.

And if you want to use those lazy classes above, you can get them here and here.

这篇关于这个存储库模式对于 LINQ-to-SQL 是否有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！