在IQueryable LINQ to Entities查询中获取字典值 [英] Get Dictionary value in IQueryable LINQ to Entities query

问题描述

我必须在生产应用程序中支持多种语言.

I have to support multiple languages in production application.

有很多Entity Framework查询，它们从数据库中获取数据作为延迟的IQueryable列表，如下所示:

There are lot of Entity Framework queries that gets data from database as deferred IQueryable list like this:

public IQueryable<Request> GetDeferredRequests()
{
    return _dbContext.Set<Request>();
}   

POCO类如下:

public partial class Request
{
    public int RequestID { get; set; }

    public string StatusName { get; set; }

    public string RequestType { get; set; }
}

数据传输对象如下:

public class RequestDTO
{
    public int RequestID { get; set; }

    public string StatusName { get; set; }

    public string RequestType { get; set; }
}

此后，我将EF POCO实体映射到数据传输对象.为了支持多种语言，我想像下面的方法那样通过映射中的数据库值来获取资源值:

After that I map the EF POCO entity to Data Transfer Object. To support multiple languages I want to get resource value by database value in mapping like the following method:

public IQueryable<RequestDTO> MapRequests(IQueryable<Request> requests)
{
      Dictionary<string, string> resoures = new Dictionary<string, string>();

      System.Resources.ResourceSet resources = DatabaseResources.ResourceManager.GetResourceSet(new System.Globalization.CultureInfo("en"), true, true);

      foreach (DictionaryEntry resource in resources)
      {
          resoures.Add(resource.Key.ToString(), resource.Value.ToString());
      }

      return requests.Select(c => new RequestDTO()
      {
          RequestID = c.RequestID,
          StatusName =  resoures.Single(r => r.Key == c.StatusName).Value,
          RequestType = resoures.Single(r => r.Key == c.RequestType).Value
      });
}

问题在于最后一条命令引发以下异常:

The problem is that the last command throws the following exception:

LINQ to Entities does not recognize the method 'System.String get_Item(System.String)' method, and this method cannot be translated into a store expression.

不幸的是，通过ToList()将IQueryable转换为IEnumerable并不是一种选择，因为我不想将列表移动到内存中.

Unfortunatelly the converting IQueryable to IEnumerable by ToList() is not an option, because I do not want to move list to memory.

推荐答案

您必须了解IQueryable和IEnumerable的区别.

You have to be aware of the difference of IQueryable and IEnumerable.

实现 IEnumerable 的对象表示一个序列.它拥有获取序列中第一个元素的所有内容，一旦有了一个元素，只要有一个元素，就可以获取下一个元素.

An object that implements IEnumerable represents a sequence. It holds everything to get the first element of the sequence and once you've got an element you can get the next element as long as there is an element.

在最低级别上，通过调用GetEnumerator()并重复调用MoveNext()来完成此序列的枚举.每当MoveNext返回true时，您就有一个元素.可以使用属性Current来访问此元素.

At lowest level, enumerating over this sequence is done by calling GetEnumerator() and repeatedly calling MoveNext(). Every time MoveNext returns true, you've got an element. This element can be accessed using property Current.

很少执行此最低级别的枚举.通常，您枚举使用foreach或不返回IEnumerable的LINQ函数之一:ToList()，Count()，Any()，FirstOrDefault()等.在最深层，它们都调用GetEnumerator和MoveNext/Current.

Enumerating at this lowest level is seldom done. Usually you enumerate using foreach, or one of the LINQ functions that don't return IEnumerable: ToList(), Count(), Any(), FirstOrDefault(), etc. At the deepest level they all call GetEnumerator and MoveNext / Current.

尽管实现IQueryable的对象看起来像IEnumerable，但它并不表示对象本身的序列.它表示创建IEnumerable序列的潜力.

Although an object that implements IQueryable seems like an IEnumerable, it does not represent the sequence of object itself. It represents the potential to create an IEnumerable sequence.

为此，IQueryable包含一个表达式和一个提供程序.表达式表示必须查询哪些数据.提供者知道向谁查询日期(通常是数据库管理系统)，以及该DBMS使用哪种语言(通常是某种SQL).

For this, the IQueryable holds an Expression and a Provider. The Expression is a representation of what data must be queried. The Provider knows who to query for the date (usually a database management system) and what language this DBMS speaks (usually some sort of SQL).

串联IQueryable LINQ语句不执行查询.它仅更改表达式.要执行查询，您需要开始枚举.

Concatenating IQueryable LINQ statements does not execute the query. It only changes the Expression. To execute the query you need to start enumerating.

一旦开始使用GetEnumerator枚举IQueryable，则将表达式发送给提供程序，该提供程序会将表达式转换为SQL并在DBMS上执行查询.返回的数据表示为IEnumerable，其中调用了GetEnumerator.

Once you start enumerating the IQueryable using GetEnumerator, the Expression is sent to the Provider who will translate the Expression into SQL and execute the query at the DBMS. The returned data is represented as an IEnumerable, of which GetEnumerator is called.

问题是，提供者不知道您的函数MapRequests.因此，它无法将其转换为SQL.实际上，即使是几个标准的LINQ函数也无法转换为SQL.请参阅

The problem is, that the Provider does not know your function MapRequests. Therefore it can't translate it into SQL. In fact even several standard LINQ functions can't be translated into SQL. See Supported and Unsupported LINQ methods.

解决此问题的一种方法是将选定的数据移至本地进程.本地进程知道MapRequests函数，并且知道如何执行它.

One way to solve this, is to move the selected data to your local process. The local process knows function MapRequests and knows how to Execute it.

可以使用ToList()将数据移动到本地进程.但是，如果在此之后仅需要几个元素(例如Take(3)或FirstOrDefault())，则将浪费处理能力.

Moving data to the local process can be done using ToList(). However, this would be a waste of processing power if after this you will only need a few elements, like Take(3), or FirstOrDefault().

可枚举进行救援！

您的提供者知道AsEnumerable.它将数据移动到您的本地进程.一些愚蠢的提供者将通过获取所有数据来做到这一点.聪明的提供者将每页"获取数据.一页包含查询数据的子集，例如仅50行.如果仅使用FirstOrDefault()仍然很浪费，但至少不会吸引数百万的客户.

Your Provider knows AsEnumerable. It will move the data to your local process. Some dumb providers will do this by fetching all data. Smarter Providers will fetch the data "per page". One page consists a subset of the queried data, for instance only 50 rows. It is still a waste if you only use FirstOrDefault(), but at least you won't have fetched millions of Customers.

如果您将MapRequests更改为扩展方法，那就太好了.参见扩展方法已神秘化

It would be nice if you changed MapRequests to an extension method. See Extension Methods Demystified

public static class MyIEnumerableExtensions
{
    public static IEnumerable<RequestDTO> ToRequestDTO( this IEnumerable<Request> requests)
    {
        // your code
        ...
        return requests.Select(request => new RequestDTO
        {
           RequestId = request.RequestId,
           ...
        });
    }

用法:

IEnumerable<RequestDto> requestDTOs = GetDeferredRequests()

    // only if you don't want all requests:
    .Where(request => ...)

    // move to local process in a smart way:
    AsEnumerable()

    // Convert to RequestDTO:
    .ToRequestDTO();

注意:直到您调用GetEnumerator()(或foreach，ToList()，Count()等)后，查询才会执行.您甚至可以添加其他IEnumerable函数:

Note: the query is not executed until your call GetEnumerator() (or foreach, ToList(), Count(), etc). You can even add other IEnumerable functions:

    .Where(requestDTO => requestDTO.StatusName == ...);

但是请注意，这些语句不是由数据库管理系统执行的，而是由您的本地进程执行的.

Be aware though, that the statements are not executed by the Database Management System, but by your local process.

但是可能可以.您必须将资源传输到数据库，并使用简单的数据库函数将Request转换为RequestDTO.如果与必须转换的请求数量相比，有很多资源，那么这样做可能是不明智的.但是，例如，如果您必须使用100个资源转换成千上万个Request，并且在转换后对另一个表执行Where或GroupJoin，则让DBMS进行转换可能是明智的.

Yet it probably can. You'll have to transport the resources to the database and use simple database functions to convert Request to RequestDTO. If there are many resources in comparison to the number of Requests that you'll have to convert, then it is probably not wise to do. But if for instance you'll have to convert thousands of Request with 100 resources, and after conversion you'll do a Where, or a GroupJoin with another table, it is probably wise to let the DBMS do the conversion.

似乎每个资源都有一个键和一个值.

It seems that every Resource has a Key and a Value.

• StatusName的资源"的键值应等于request.StatusName
• RequestType的资源"的键值应等于request.RequestType.

因此，让我们将MapRequests重写为IQeryable的扩展方法:

So let's rewrite MapRequests into an extension method of IQeryable:

public IQueryable<RequestDTO> ToRequestDto( this IQueryable<Request> requests,
      IEnumerable<KeyValuePair<string, string>> resources)
{
     // TODO: exception if requests == null, resources == null

     return requests.Select(request => new RequestDTO
     {
         RequestId = request.RequestId,

         // from resources, keep only the resource with key equals to StatusName
         // and select the FirstOrDefault value:
         StatusName = resources
                      .Where(resource => resource.Key == request.StatusName)
                      .Select(resource => resource.Value)
                      .FirstOrDefault(),
         // from resources, keep only the resource with key equals to RequestType
         // and select the FirstOrDefault value:
         RequestType = resources
                      .Where(resource => resource.Key == request.RequestType)
                      .Select(resource => resource.Value)
                      .FirstOrDefault(),
     }

用法:

IEnumerable<KeyValuePair<string, string> resources = ...
var requestDTOs = GetDeferredRequests()
    .Where(request => ...)
    .ToRequestDTO(resources)

    // do other database processing
    .GroupJoin(myOtherTable, ...)
    .Where(...)
    .Take(3);

现在，完整的语句将由数据库管理系统执行. 与您的流程相比，大多数DBMS都经过了优化，可以从序列中选择特定项目.除此之外，看起来更加整洁.

Now the complete statement will be executed by the Database management system. Most DBMSs are much more optimized to select specific items from a sequence than your process. Besides this looks much neater.

这篇关于在IQueryable LINQ to Entities查询中获取字典值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！