这是库模式有效使用LINQ到SQL？ [英] Is this Repository pattern efficient with LINQ-to-SQL?

问题描述

我目前正在读的书临Asp.Net MVC框架。在这本书中，笔者建议使用类似如下的仓库模式。

  [表（名称=产品）]
公共类产品
{
    [列（IsPrimaryKey = TRUE，
            IsDbGenerated = TRUE，
            自动同步= AutoSync.OnInsert）
    公众诠释产品编号{搞定;组; }
    [专栏]公共字符串名称{;组; }
    [专栏]公共字符串描述{搞定;组; }
    [专栏]公共十进制价格{搞定;组; }
    [专栏]公共字符串等级{搞定;组; }
}公共接口IProductsRepository
{
    IQueryable的＆LT;产品与GT;产品{搞定; }
}公共类SqlProductsRepository：IProductsRepository
{
    私人表＆LT;产品与GT; productsTable;    公共SqlProductsRepository（字符串的connectionString）
    {
        productsTable =新的DataContext（的connectionString）.GetTable＆LT;产品及GT;（）;
    }    公众的IQueryable＆LT;产品与GT;制品
    {
        {返回productsTable; }
    }
}
 

数据随后以下列方式获得：

 公开的ViewResult列表（串类）
{
    VAR productsInCategory =（类别== NULL）？ productsRepository.Products：productsRepository.Products.Where（P =＆GT; p.Category ==类）;    返回查看（productsInCategory）;
}
 

这是访问数据的有效手段？要被从数据库中检索并在存储器过滤整个表去或在链状凡（）方法将导致某些LINQ魔基于所述lambda来创建一个优化的查询？

最后，什么在C＃中存储库模式可以提供通过时迷上了性能更好的其他实现LINQ到SQL？

解决方案

我可以理解约翰内斯'愿望更加紧密地控制SQL的执行，并与我有时称之为实施懒惰锚点我已经能够做到这一点在我的应用程序。

我使用自定义的组合 LazyList＆LT; T＆GT; 和 LazyItem＆LT; T＆GT; 封装延迟初始化类：

• LazyList＆LT; T＆GT; 包装了一个的IList <的的IQueryable 功能/ code>集合，但最大限度地提高一些LinqToSql的延迟执行功能和


• LazyItem＆LT; T＆GT; 将包装使用LinqToSql 的IQueryable 或一般单个项目的懒调用 Func键＆LT; T＆GT; 执行其它code递延法

下面是一个例子 - 我有这个模型对象公告这可能有一个附带的图像或PDF文档：

 公共类公告：// ..
{
    公众诠释ID {搞定;组; }
    公共字符串名称{搞定;组; }
    公共AnnouncementCategory类别{搞定;组; }
    公共字符串车身{搞定;组; }
    公共LazyItem＆lt;图像＆GT;图片{搞定;组; }
    公共LazyItem＆LT; PdfDoc＆GT; PdfDoc {搞定;组; }
}
 

的图片和 PdfDoc 类继承形成一种文件包含字节[] 包含二进制数据。这二进制数据很重，我可能并不总是需要从数据库中每一次回来我希望有一个公告。所以我要保持我的对象图'挂靠'，但不是'人口'（如果你喜欢）。

所以，如果我做这样的事情：

  Console.WriteLine（anAnnouncement.Title）;
 

..我可以知道，我只从装有由DB数据为眼前的公告对象。但是，如果在下面的行，我需要做到这一点：

  Console.WriteLine（anAnnouncement.Image.Inner.Width）;
 

..我可以肯定的是， LazyItem＆LT; T＆GT; 知道如何去获取数据的其余部分。

另一大好处是，这些懒人类可以隐藏特定的实现底层存储库的，所以我并不一定要使用LinqToSql。我的应用我切割的例子，从的情况下（使用LinqToSql），但它会很容易堵塞另一个数据源（甚至是完全不同的数据层，也许不使用Repository模式）。

LINQ但不LinqToSql

您会发现，有时候你想要做一些花哨的LINQ查询当这种情况发生的执行流下来到LinqToSql提供商BARF。这是因为LinqToSql作品通过翻译有效LINQ查询逻辑到T-SQL code，有时就是不可能的。

例如，我有这个功能，我想从的IQueryable 结果：

 私人的IQueryable＆LT;事件＆GT; GetLatestSortedEvents（）
    {
        // TODO：警告：重型SQL查询！固定
        返回this.GetSortedEvents（）了ToList（）
            。凡（ModelExtensions.Event.IsUpcomingEvent（））
            .AsQueryable（）;
    }
 

为什么code不转换为SQL并不重要，而只是相信我，在那个条件 IsUpcomingEvent（） predicate涉及多个的的DateTime 的比较，仅仅是过于复杂，LinqToSql转换成T-SQL。

通过使用 .ToList（）则条件（。凡（.. ），然后 .AsQueryable（）即时有效地告诉）LinqToSql，我需要所有的 .GetSortedEvents的（项目甚至尽管我是那么要过滤。这是一个实例，其中，所以我需要在内存中对其进行过滤我的过滤器前pression不会呈现到SQL正常。这将是我可能就调用LinqToSql性能的限制，延迟执行和懒加载去 - 但我只有少量这些警告：重型SQL查询在我的应用程序块，我想进一步的智能重构可以完全消除它们。

最后，LinqToSql可以成为一个好数据访问提供在大型的应用程序，如果你想让它。我发现，要得到我想要的结果，并抽象掉并隔离某些事情我已经需要在这里和那里增加code。而且，我想在从LinqToSql实际的SQL性能更多的控制，我已经添加到智慧得到理想的结果。因此，恕我直言LinqToSql对于那些需要数据库查询优化只要你明白LinqToSql如何工作繁重的应用程序完全确定。我的设计最初是基于罗布的店面教程，所以你可能会发现它有用的，如果您需要了解更多的解释我咆哮以上。

如果你想使用上述那些慵懒的课程，你可以让他们的这里和这里。

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到SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！