wpf 实体框架和数据库表的本地缓存

Entity framework and local cache of database tables

提问by AppleMoose

I am developing an application that uses Entity Framework, but I have some performance issues. Imagine a user interface (C#, WPF) with some combo boxes and a data grid. Each time a value is selected in a combo box it changes the conditions for the data to show in the grid. It looks like the entity framework is not as flexible as I thought when it comes to caching. Because of the changed conditions the underlying sql will always be sligthly different (= no EF caching) and each cell update will result in a request to the database.

我正在开发一个使用实体框架的应用程序，但我有一些性能问题。想象一个带有一些组合框和数据网格的用户界面（C#、WPF）。每次在组合框中选择一个值时，它都会更改数据在网格中显示的条件。看起来实体框架在缓存方面没有我想象的那么灵活。由于条件发生变化，底层 sql 将始终略有不同（= 没有 EF 缓存），并且每个单元格更新都会导致对数据库的请求。

Is there any way I can cache the tables locally (with working navigation properties) and still use linq for selection etc. without generating any requests for the database?

有什么方法可以在本地缓存表（具有工作导航属性）并且仍然使用 linq 进行选择等，而不会生成对数据库的任何请求？

• The tables are small so I don't expect any performance problems. (I guess local sorting etc. otherwise could be a problem since the database indexes are not used.)
• I don't need write access to the tables so if there is an easy way to make a deep copy of the tables and detach from the database connection that would perhaps be ok.
• I am not allowed to install any third party tools.

• 表很小，所以我不希望有任何性能问题。（我猜本地排序等。否则可能是一个问题，因为没有使用数据库索引。）
• 我不需要对表的写访问权限，所以如果有一种简单的方法来制作表的深层副本并与数据库连接分离，那可能没问题。
• 我不允许安装任何第三方工具。

Perhaps entity framework was a bad choice from the beginning, but it's really convenient to use those generated classes and linq instead of manually writing a lot of classes and sql. (That I still would have to implement some cache for.)

或许实体框架从一开始就是个糟糕的选择，但是使用那些生成的类和 linq 确实很方便，而不是手动编写大量的类和 sql。（我仍然需要为其实现一些缓存。）

回答by Gert Arnold

Is there any way I can cache the tables locally?

有什么办法可以在本地缓存表吗？

This is what a DbContextdoes by default and there is an easy way for you to use that feature. Here is the basic pattern to follow:

这是 aDbContext默认情况下所做的，并且有一种简单的方法可以让您使用该功能。这是要遵循的基本模式：

context.Products.Where(p => <some intial condion>).Load();
var dataSource = context.Product.Local.Where(p => <some flexible condition>);

Note that in line 2 the Localcollection is used. This is a DbSetproperty that returns entities from the context's cache.

请注意，在第 2 行Local中使用了集合。这是一个DbSet从上下文缓存中返回实体的属性。

with working navigation properties

具有工作导航属性

Any related entities that are loaded by the Load()statement will be automatically connected to one another by relationship fixup. So if a Producthas a collection Components, you can do:

该Load()语句加载的任何相关实体将通过关系修复自动相互连接。所以如果 aProduct有一个集合Components，你可以这样做：

context.Components.Where(c => <some intial condion>).Load();

If this loads all components of the products that were loaded above, you'll see that their Componentscollection are now populated.

如果这加载了上面加载的产品的所有组件，您将看到它们的Components集合现在已填充。

An alternative combining both steps is:

结合这两个步骤的替代方法是：

context.Products.Where(p => <some intial condion>)
       .Include(p => p.Components)
       .Load();

If there are many related tables you have to find a balance between individual Loadstatements and Loadstatements with Include, because many Includesin one statement may hit performance.

如果有许多相关表，您必须在单个Load语句和使用 的Load语句之间找到平衡Include，因为Includes一个语句中的多个表可能会影响性能。

and still use linq for selection

并且仍然使用 linq 进行选择

As shown above: the flexible condition.

如上图：弹性条件。

without generating any requests for the database

不生成对数据库的任何请求

If you will always address Localcollections only, these statements will never query the database. However, addressing navigation properties may still cause lazy loading. If you do ...

如果您总是Local只处理集合，这些语句将永远不会查询数据库。但是，解决导航属性可能仍会导致延迟加载。如果你这样做...

context.Products.Where().Load();
context.Components.Where().Load();

... this does populate product.Componentscollections, but doesn't mark them as loaded, whereas context.Products.Include(p => p.Components)does. So in the first case, addressing product.Componentswill trigger lazy loading. Similarly, addressing navigation properties for which the entities aren't loaded at all will also trigger lazy loading. So to be absolutely sure that no database interaction is triggered, you should disable lazy loading, either by ...

...这确实会填充product.Components集合，但不会将它们标记为已加载，而context.Products.Include(p => p.Components)会。所以在第一种情况下，寻址product.Components会触发延迟加载。类似地，寻址根本没有加载实体的导航属性也将触发延迟加载。因此，为了绝对确保不会触发任何数据库交互，您应该禁用延迟加载，或者通过...

context.Configuration.LazyLoadingEnabled = false;

... or ...

... 或者 ...

context.Configuration.ProxyCreationEnabled = false;

The latter option forces EF to create simple POCO objects that are not capable of lazy loading.

后一个选项强制 EF 创建不能延迟加载的简单 POCO 对象。

So using these techniques, you can use your context as a local cache of connected entities. Which is an exception to the rule that a context should be short-lived.

因此，使用这些技术，您可以将上下文用作连接实体的本地缓存。这是上下文应该是短暂的规则的一个例外。

One caution

一警告

Relationship fixup doesn't work for many-to-many associations. Suppose there is a m:nrelationship between Productand Manufacturer, then ...

关系修复不适用于多对多关联。假设和m:n之间存在关系，那么...ProductManufacturer

context.Products.Where().Load();
context.Manufacturers.Where().Load();

... won't populate product.Manufacturersand manufacturer.Products. Many-to-many associations should be loaded by Include:

... 不会填充product.Manufacturers和manufacturer.Products。多对多关联应该通过以下方式加载Include：

context.Products.Where()
       .Include(p => p.Manufacturers)
       .Load();

回答by djangojazz

Let me take a crack at this a little bit as I also have production apps I work on in WPF and follow an MVVM pattern. You may not, I suggest it if you don't know what I am talking about. Say I have a database table that has a person table and it only has three columns: PersonId, FirstName, LastName. I only have two rows currently, my name and my wife's name. I want to retrieve the data ONLY once but then I may want to alter it later. This is a simplified example of course:

让我稍微解释一下，因为我也有在 WPF 中工作并遵循 MVVM 模式的生产应用程序。你可能不会，如果你不知道我在说什么，我建议你这样做。假设我有一个包含人员表的数据库表，它只有三列：PersonId、FirstName、LastName。我目前只有两行，我的名字和我妻子的名字。我只想检索数据一次，但稍后我可能想更改它。这当然是一个简化的例子：

XAML:

XAML：

<StackPanel>
    <DataGrid ItemsSource="{Binding People}" AutoGenerateColumns="False">
      <DataGrid.Columns>
        <DataGridTextColumn Header="PersonId" Binding="{Binding PersonId}" />
        <DataGridTextColumn Header="First Name" Binding="{Binding FirstName}" />
        <DataGridTextColumn Header="Last Name" Binding="{Binding LastName}" />
      </DataGrid.Columns>
    </DataGrid>
    <TextBox Text="{Binding Text}" />
    <Button Command="{Binding CommandGetFirstName}" Height="30" Content="Get By First Name Above" />
</StackPanel>

This is bound using MVVM so my MainViewModel would be this:

这是使用 MVVM 绑定的，所以我的 MainViewModel 是这样的：

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Input;

namespace WPFCSharpTesting
{
  public class MainWindowViewModel : INotifyPropertyChanged
  {
    private string _text;

    public string Text
    {
      get { return _text; }
      set
      {
        _text = value;
        OnPropertyChanged(nameof(Text));
      }
    }

    private ObservableCollection<tePerson> _people;

    public ObservableCollection<tePerson> People
    {
      get { return _people; }
      set
      {
        _people = value;
        OnPropertyChanged(nameof(People));
      }
    }

    private readonly List<tePerson> _allPeople;


    public MainWindowViewModel()
    {
      Text = "Brett";       
      using (var context = new TesterEntities())
      {
        _allPeople = context.tePerson.ToList();
      }

      People = new ObservableCollection<tePerson>(_allPeople);
    }

    public event PropertyChangedEventHandler PropertyChanged;

    private void OnPropertyChanged(String info)
    {
      PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(info));
    }

    DelegateCommand _CommandGetFirstName;

    public ICommand CommandGetFirstName
    {
      get
      {
        if (_CommandGetFirstName == null)
        {
          _CommandGetFirstName = new DelegateCommand(param => this.CommandGetByFirstNameExecute());
        }
        return _CommandGetFirstName;
      }
    }

    private void CommandGetByFirstNameExecute()
    {
      var newitems = _allPeople.Exists(x => x.FirstName == Text) ? _allPeople.Where(x => x.FirstName == Text)?.ToList() : _allPeople;
      People = new ObservableCollection<tePerson>(newitems);
    }

The key piece here is what is happening in my constructor. I am taking a readonly variable, _allPeople, that is private and storing the info there that I want to manipulate later. Once _allPeople has the data, I do not need to touch the 'context' again to hit the database. I just can now monkey with _allPeople as it's own collection detached for what I need. When I want to expose to my front end WPF what the user sees they will see an observable collection I can update as needed from my cached set up. This is a super simple over simplification of it. Typically many developers end up doing a whole repository pattern where they have a project or projects related to ONLY being for storing data and performing CRUD operations. This is generally a preferred method IMHO as you can piece together other things as needed.

这里的关键部分是我的构造函数中发生的事情。我正在使用一个只读变量 _allPeople，它是私有的，并在那里存储我以后想要操作的信息。一旦 _allPeople 有了数据，我就不需要再次触摸“上下文”来访问数据库。我现在可以和 _allPeople 一起玩，因为它是我自己的收藏品，可以满足我的需要。当我想向我的前端 WPF 公开用户看到的内容时，他们将看到一个可观察的集合，我可以根据需要从我的缓存设置中更新。这是一个超级简单的过度简化。通常，许多开发人员最终会做一个完整的存储库模式，其中他们有一个或多个与仅用于存储数据和执行 CRUD 操作相关的项目。恕我直言，这通常是首选方法，因为您可以根据需要将其他东西拼凑在一起。