投影领域模型的孩子算到没有额外的查询视图模型 [英] Projecting domain model child count onto view model without extra queries

问题描述

假设我用下面的实体框架的实体：

Assume I'm using the following Entity Framework entities:

public class Country : DomainObject<int>
{
    private ICollection<StateOrProvince> _statesOrProvinces;

    public string Name { get; set; }
    public string Abbreviation { get; set; }

    public virtual ICollection<StateOrProvince> StatesOrProvinces
    {
        get { return _statesOrProvinces ?? (_statesOrProvinces = new List<StateOrProvince>()); }
        protected set { _statesOrProvinces = value; }
    }
}

public class StateOrProvince : DomainObject<int>
{
    public int CountryId { get; set; }
    public virtual Country Country { get; set; }
    public string Name { get; set; }
    public string Abbreviation { get; set; }
}

我要投射到我的presentation层以下视图模型：

I want to project onto the following view model for my presentation layer:

public class CountryListModel
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public string Abbreviation { get; set; }

    [Display(Name = "States/Provinces")]
    public int StatesOrProvincesCount { get; set; }
}

正如你所看到的，我有一个 StateOrProvincesCount 在我的模型，应该重新present在 StatesOrProvinces 名单在我的国家实体的集合count属性

As you can see, I have a StateOrProvincesCount property on my model that is supposed to represent an aggregate count of the StatesOrProvinces list on my Country entity.

为了保持我的MVC控制器code瘦，我创造了我的域实体静态映射扩展方法。一个映射到的 CountryListModel 的是这样的：

In order to keep my MVC controller code lean, I created static mapping extension methods for my domain entities. The one for mapping to the CountryListModel looks like this:

public static CountryListModel MapToListModel(this Country country) 
{
    return new CountryListModel 
    { 
        Id = country.Id, 
        Name = country.Name, 
        Abbreviation = country.Abbreviation, 
        StatesOrProvincesCount = country.StatesOrProvinces.Count
    };
 }

我然后试图使用它，如下所示：

I then attempted to use it as follows:

var models = _countries.OrderBy(x => x.Name).Select(x => x.MapToListModel()).ToList();

不过，这引发异常的 LINQ到实体无​​法识别方法CountryListModel MapToListModel（国家）的方法，而这种方法不能被翻译成店前pression。我的假设是，这是与问题的IQueryable 暂时无法到code转换为SQL。

However, this threw the exception LINQ to Entities does not recognize the method 'CountryListModel MapToListModel(Country)' method, and this method cannot be translated into a store expression. My assumption was that this was an issue with IQueryable being unable to convert the code to SQL.

于是我尝试：

var models = _countries.AsEnumerable().OrderBy(x => x.Name).Select(x => x.MapToListModel()).ToList();

这并不会引发错误，给我我想要的结果。但是，查看SQL防爆preSS探查时，我可以看到，这导致的（N + 1）查询被发送到数据库中。首先，它会查询得到的国家名单，然后查询选择所有每个国家的州/省的。

This did not throw an error and gave me the results I wanted. However, when viewing SQL Express Profiler I can see that this results in (N+1) queries being sent to the database. First it queries to get the list of countries, then queries to select all of the States/Provinces for each country.

如果你扔出去直接在。选择方法映射扩展和项目只有一个查询，并将其执行计数直接在SQL语句而不是返回州/省则不算那些

If you throw out the mapping extension and project directly within the .Select method there is only one query and it performs the Count directly in the SQL instead of returning the States/Provinces then counting those:

var models = _countries.OrderBy(x => x.Name).Select(x => new CountryListModel 
{ 
    Id = x.Id, 
    Name = x.Name, 
    Abbreviation = x.Abbreviation, 
    StatesOrProvincesCount = x.StatesOrProvinces.Count 
}).ToList();

这是太棒了，然而，这仅仅是一个快速的原型。从长远来看，我想要的东西分层。（例如 - 核心，数据，业务，presentation）

That is fantastic, however this is just a quick prototype. In the long run I want to separate things into layers (e.g. - Core, Data, Business, Presentation).

这是在这个过程中我学到的东西，看来我对投影视图模型将必须由数据层，以有效地查询闻名。在这样的情况下做的视图模型中的核心/常用项目与域实体一起属于？我应该创建额外的DTO对象，并映射到那些？你如何在您的项目处理呢？

From what I've learned during this process, it appears that the View Model I'm projecting on will have to be known by the Data layer in order to query efficiently. In cases such as this do the View Models belong in the Core/Common project along with the Domain entities? Should I create additional DTO objects and map onto those? How do you handle this in your projects?

推荐答案

我不认为你的解决方案是错误的。我以前一直用它，有时结束了写我自己的SQL，并传递给EF。这是比试图弯曲EF创建一个你想要的，有时更容易。在这种情况下，EF根本无法做很多关于您的扩展方法。对于在评论中提到的解决方案，你打，因为 country.StatesOrProvinces.Count N + 1的扩展方法。如果你不能拉平了，然后写你的SQL可能是一个不错的选择。

I don't think your solution is wrong. I have used it before and sometimes ended up writing my own SQL and passed to EF. It is much easier sometimes than trying to flex EF to create the one you want. In this case, EF simply can't do much about your extension method. For the solutions mentioned in the comments, you are hitting N+1 because of country.StatesOrProvinces.Count in the extension method. If you can't flatten that then writing your SQL could be a good option.

从长远来看我想要的东西分层。（例如 - 核心，数据，业务，presentation）

In the long run I want to separate things into layers (e.g. - Core, Data, Business, Presentation).

我曾尝试在过去的项目来实现这一几次，用各种，UnitOfWorks，的ViewModels等的存储库。其结果是不能令人十分满意。这往往造成臃肿的数据访问层和业务层到处违反单一职责原则，增加了更多的维护headaches.This的由罗布科纳帖子让我有不同的想法。入住简单的比你不需要教条式的优化更为重要。双方讨论了解决方案，命令/查询对象（不一定全CQRS）和使用的DataContext的请求工作生活良好，与该项目的生活很好地成长。

I have tried to achieve this several times in past projects, used Repositories of all kinds, UnitOfWorks, ViewModels,etc. The result was not quite satisfactory. It often resulted in bloated data access layers and service layers violating single responsibility principle everywhere, adding more maintenance headaches.This post by Rob Conery made me think differently. Staying simple is more important than dogmatic optimizations you don't need. Both discussed solutions,Command/Query Objects (Not necessarily full CQRS) and Using DataContext for the life of the request work well and grow nicely with the life of the project.

在这样的情况下做的视图模型中的核心/常用项目属于与域实体一起？我应该创建额外的DTO对象，并映射到那些？你如何在您的项目处理呢？

In cases such as this do the View Models belong in the Core/Common project along with the Domain entities? Should I create additional DTO objects and map onto those? How do you handle this in your projects?

我强烈建议采取看看吉米·博加德的作为在 Contoso大学应用。他使​​用调停。这是很好的分离。这是值得一试。

I highly recommend taking a look at Jimmy Bogard's take on Contoso University App. He is using MediatoR. It is very well decoupled. It is worth a shot.

有什么灵丹妙药。开始收紧code之前，我会记住的事情是简单，可测试性和性能。有始终把共同code通过几件事情访问一些地方的诱惑。建立这样的实体之前，我会想出一个理由改变。

There is no silver bullet. Before starting to tighten the code, the things I'd keep in mind are simplicity, testability and performance. There is always a temptation of putting "common code" in some place accessed by several things. I'd think of a reason for change before creating such entities.

我希望这有助于。

这篇关于投影领域模型的孩子算到没有额外的查询视图模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！