从控制器访问数据库实体

本文介绍了从控制器访问数据库实体的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在一个不错的设计。要访问数据库在单独的业务逻辑处理层（在一个asp.net MVC模式），或者是确定通过的IQueryable s或的DbContext 对象到控制器？

In a good design. Should accessing the database be handled in a separate business logic layer (in an asp.net MVC model), or is it OK to pass IQueryables or DbContext objects to a controller?

为什么呢？各有什么利弊？

Why? What are the pros and cons of each?

我要建在C＃中的ASP.NET MVC应用程序。它采用作为一个ORM。

I'm building an ASP.NET MVC application in C#. It uses EntityFramework as an ORM.

让我们简化这种情况一点。

Let's simplify this scenario a bit.

我有一个可爱的毛绒绒的小猫的数据库表。每个小猫小猫图片链接，小猫蓬松指数，小猫的名字和小猫ID。这些地图的EF POCO生成一个名为小猫。我可能会使用这个类中的其他项目，而不仅仅是asp.net MVC项目。

I have a database table with cute fluffy kittens. Each kitten has a kitten image link, kitten fluffiness index, kitten name and kitten id. These map to an EF generated POCO called Kitten. I might use this class in other projects and not just the asp.net MVC project.

我有一个 KittenController 应取在最新的蓬松小猫/小猫。它可能包含一些逻辑选择的小猫，但没有太多的逻辑。我一直在一个有关如何实现这个朋友吵架，我不会透露双方：）

I have a KittenController which should fetch the latest fluffy kittens at /Kittens. It may contain some logic selecting the kitten, but not too much logic. I've been arguing with a friend about how to implement this, I won't disclose sides :)

public ActionResult Kittens() // some parameters might be here
{
   using(var db = new KittenEntities()){ // db can also be injected,
       var result = db.Kittens // this explicit query is here
                      .Where(kitten=>kitten.fluffiness > 10)
                      .Select(kitten=>new {
                            Name=kitten.name,
                            Url=kitten.imageUrl
                      }).Take(10);
       return Json(result,JsonRequestBehavior.AllowGet);
   }
}

选项2：单独的模型

public class Kitten{
   public string Name {get; set; }
   public string Url {get; set; }
   private Kitten(){
        _fluffiness = fluffinessIndex;
   }

   public static IEnumerable<Kitten> GetLatestKittens(int fluffinessIndex=10){
        using(var db = new KittenEntities()){ //connection can also be injected
            return db.Kittens.Where(kitten=>kitten.fluffiness > 10)
                     .Select(entity=>new Kitten(entity.name,entity.imageUrl))
                     .Take(10).ToList();
        }
    } // it's static for simplicity here, in fact it's probably also an object method
      // Also, in practice it might be a service in a services directory creating the
      // Objects and fetching them from the DB, and just the kitten MVC _type_ here

}

//----Then the controller:
public ActionResult Kittens() // some parameters might be here
{
    return Json(Kittens.GetLatestKittens(10),JsonRequestBehavior.AllowGet);
}

注： GetLatestKittens 不太可能在code其它地方使用，但它可能会。它可以使用小猫而不是一个静态的建筑方法和更改小猫类的构造函数。基本上它应该是层以上的的数据库实体因此控制器不必知道实际的数据库，映射器，或实体框架。

Notes: GetLatestKittens is unlikely to be used elsewhere in the code but it might. It's possible to use the constructor of Kitten instead of a static building method and changing the class for Kittens. Basically it's supposed to be a layer above the database entities so the controller does not have to be aware of the actual database, the mapper, or entity framework.

什么是每个设计的一些优点和缺点？

是否有一个明显的赢家？为什么呢？

What are some pros and cons for each design?
Is there a clear winner? Why?

注：当然，另一种方法是的非常的价值作为答案太

Note: Of course, alternative approaches are very valued as answers too.

澄清1：这是的不的实践中一个微不足道的应用程序。这是几十控制器和数千个$ C $的c线的应用，以及实体不仅在这里，但在数十其他C＃项目的使用。这里的例子是的降低测试用例的

Clarification 1: This is not a trivial application in practice. This is an application with tens of controllers and thousands of lines of code, and the entities are not only used here but in tens of other C# projects. The example here is a reduced test case.

推荐答案

选项1和2是有点极端，像魔鬼和深蓝色的大海之间，但如果选择我有两个我想$ P $之间做出选择PFER选项1。

Option 1 and 2 are bit extreme and like the choice between the devil and the deep blue sea but if I had to choose between the two I would prefer option 1.

首先，选择2将抛出一个运行时异常，因为实体框架不支持投射到一个实体（选择（E =＆gt;新建小猫（...））和它不允许使用构造与在投影参数。现在，本说明似乎在这方面有点迂腐，而是由伸入实体并返回一个猫（或小猫所组成的枚举），你用这种方法隐藏真正的问题。

First of all, option 2 will throw a runtime exception because Entity Framework does not support to project into an entity (Select(e => new Kitten(...)) and it does not allow to use a constructor with parameters in a projection. Now, this note seems a bit pedantic in this context, but by projecting into the entity and returning a Kitten (or an enumeration of Kittens) you are hiding the real problem with that approach.

显然，你的方法返回要在视图中使用的实体的两个属性 - 小猫的名称和图片网址。因为这些是唯一的全小猫的选择属性返回（半满）小猫实体是不恰当的。那么，是什么类型实际上是从这个方法返回？

Obviously, your method returns two properties of the entity that you want to use in your view - the kitten's name and imageUrl. Because these are only a selection of all Kitten properties returning a (half-filled) Kitten entity would not be appropriate. So, what type to actually return from this method?

您可以返回对象（或的IEnumerable＆LT;对象＆gt; ）（这就是我理解您的评论对的对象的方法的），如果传递的结果为 JSON（...）来在Javascript中后期加工这是罚款。但是，你将失去所有的编译时类型信息，我怀疑，一个对象结果类型为别的有用的。

您可以返回只包含两个属性的一些命名的类型 - 也许所谓的KittensListDto

You could return object (or IEnumerable<object>) (that's how I understand your comment about the "object method") which is fine if you pass the result into Json(...) to be processed in Javascript later. But you would lose all compile time type information and I doubt that an object result type is useful for anything else.
You could return some named type that just contains the two properties - maybe called "KittensListDto".

现在，这是一个视图只有一个方法 - 视图列出小猫。然后，你有一个详细信息视图显示单个的小猫，那么编辑视图，然后删除确认可能视图。四个视图为现有猫实体，其每个可能需要不同的属性每一个和将需要一个单独的方法和投影和一个不同的DTO类型。同为狗实体，更多的项目100实体和你也许400的方法和400的返回类型。

Now, this is only one method for one view - the view to list kittens. Then you have a details view to display a single kitten, then an edit view and then a delete confirm view maybe. Four views for an existing Kitten entity, each of which needs possibly different properties and each of which would need a separate method and projection and a different DTO type. The same for the Dog entity and for 100 entities more in the project and you get perhaps 400 methods and 400 return types.

和最有可能没有一个将永远在任何其他地方比这个特定视图重用。你为什么要以 10的小猫刚名称和图片网址随时随地第二次？你有第二个小猫列表视图？如果是这样，这将有一个理由和查询都仅偶然相同，现在，如果一个人改变另一个不一定，否则列表视图是不正确重用，不应该存在的两倍。或者是由Excel导出可能使用同一个列表？但也许是Excel用户希望拥有1000小猫明天，而认为应该仍然只显示10或视图应该显示小猫的年龄明天，但Excel的用户不要不想有这种，因为他们的Excel宏将无法正常与改变运行了。只是因为code两片是相同的，他们没有被因子而成为一个公共可重用部件如果它们是在不同的上下文或具有不同语义。你最好把它一个 GetLatestKittensForListView 和 GetLatestKittensForExcelExport 。或者你最好不要有在你所有的服务层这样的方法。

And most likely not a single one will be ever reused at any other place than this specific view. Why would you want to Take 10 kittens with just name and imageUrl anywhere a second time? Do you have a second kittens list view? If so, it will have a reason and the queries are only identical by accident and now and if one changes the other one does not necessarily, otherwise the list view is not properly "reused" and should not exist twice. Or is the same list used by an Excel export maybe? But perhaps the Excel users want to have 1000 kittens tomorrow, while the view should still display only 10. Or the view should display the kitten's Age tomorrow, but the Excel users don't want to have that because their Excel macros would not run correctly anymore with that change. Just because two pieces of code are identical they don't have to be factored out into a common reusable component if they are in a different context or have different semantics. You better leave it a GetLatestKittensForListView and GetLatestKittensForExcelExport. Or you better don't have such methods in your service layer at all.

在这些考虑远足到披萨店作比喻，为什么第一种方法是优越的光：）

In the light of these considerations an excursion to a Pizza shop as an analogy why the first approach is superior :)

欢迎来到BigPizza，自定义披萨店，我可以点菜了吗？嗯，我想有顶部和奶酪，橄榄比萨，但番茄酱在底部和烘烤在烤箱90分钟，直到它是黑色的，坚硬如花岗岩平石。 OK，爵士，自定义比萨饼是我们的专业，我们会做到。

"Welcome to BigPizza, the custom Pizza shop, may I take your order?" "Well, I'd like to have a Pizza with olives, but tomato sauce on top and cheese at the bottom and bake it in the oven for 90 minutes until it's black and hard like a flat rock of granite." "OK, Sir, custom Pizzas are our profession, we'll make it."

收银员去厨房。有在柜台一个心理，他希望有一个比萨饼......它与......等待...我们需要先有一个名字的花岗岩石头，他告诉厨师。

The cashier goes to the kitchen. "There is a psycho at the counter, he wants to have a Pizza with... it's a rock of granite with ... wait ... we need to have a name first", he tells the cook.

不！，库克的尖叫声，不要再！你知道我们的努力已经。他把与400页一摞纸，我们这里有从2005年的花岗岩的岩石，但是......它没有橄榄，但paprica，而不是...或在这里的顶部番茄的...但客户希望它只是烤半分钟。也许我们应该把它的 TopTomatoGraniteRockSpecial 的？但它没有考虑奶酪在底部进去......收银员：那是的特殊的应该是前preSS。但是，具有形成像金字塔比萨岩石将是特别的还有厨师回答。嗯......这是很难......时，收银员绝望说。

"No!", the cook screams, "not again! You know we tried that already." He takes a stack of paper with 400 pages, "here we have rock of granite from 2005, but... it didn't have olives, but paprica instead... or here is top tomato ... but the customer wanted it baked only half a minute." "Maybe we should call it TopTomatoGraniteRockSpecial?" "But it doesn't take the cheese at the bottom into account..." The cashier: "That's what Special is supposed to express." "But having the Pizza rock formed like a pyramid would be special as well", the cook replies. "Hmmm ... it is difficult...", the desparate cashier says.

是我的PIZZA已在烤箱？，突然大喊穿过厨房的门。让我们停止这场讨论，只是告诉我如何使这个比萨，我们是不会有这样的比萨第二次，库克决定。 OK，这是用橄榄比萨，但在顶部和奶酪底部番茄酱和烤在烤箱90分钟，直到它是黑色的，坚硬如花岗岩平石。

"IS MY PIZZA ALREADY IN THE OVEN?", suddenly it shouts through the kitchen door. "Let's stop this discussion, just tell me how to make this Pizza, we are not going to have such a Pizza a second time", the cook decides. "OK, it's a Pizza with olives, but tomato sauce on top and cheese at the bottom and bake it in the oven for 90 minutes until it's black and hard like a flat rock of granite."

如果选项1违反使用视图层的数据库上下文选项2违反由服务或业务层，其presentation中心查询逻辑原理相同的原则关注点分离。从技术角度来看它并没有，但它最终会与一个服务层，它比presentation层的可重复使用的外别的。它有因为你必须创建服务，方法和返回类型的控制器操作所需的每一块数据高得多的开发和维护成本。

If option 1 violates a separation of concerns principle by using a database context in the view layer the option 2 violates the same principle by having presentation centric query logic in the service or business layer. From a technical viewpoint it does not but it will end up with a service layer that is anything else than "reusable" outside of the presentation layer. And it has much higher development and maintenance costs because for every required piece of data in a controller action you have to create services, methods and return types.

现在，实际上的可能的是经常重复使用，这就是为什么我认为选项1是几乎一样选择2极端查询或查询的部分 - 例如在哪里由键子句（将在细节，编辑将可能使用和删除确认视图），过滤出软删除的实体，由承租人在多租户架构过滤或禁用更改跟踪等对于这样真正的repetetive查询逻辑，我可以想像，这提取到一个服务或库层（但也许只有可重复使用的扩展方法）可能是有意义的，像

Now, there actually might be queries or query parts that are reused often and that's why I think that option 1 is almost as extreme as option 2 - for example a Where clause by the key (will be probably used in details, edit and delete confirm view), filtering out "soft deleted" entities, filtering by a tenant in a multi-tenant architecture or disabling change tracking, etc. For such really repetetive query logic I could imagine that extracting this into a service or repository layer (but maybe only reusable extensions methods) might make sense, like

public IQueryable<Kitten> GetKittens()
{
    return context.Kittens.AsNoTracking().Where(k => !k.IsDeleted);
}

还有其他跟随后 - 状凸出性质 - 是查看具体，我不希望把它在这一层。为了使这种方法可能的IQueryable＆LT; T＆GT; 必须从服务中心/存储库公开。这并不意味着选择必须在控制器动作直接。尤其是脂肪和复杂的预测（即可能是通过导航属性加入其他实体，进行分组等）可以移到扩展方法的IQueryable＆LT; T＆GT; 被收集在其他文件，目录甚至另一个项目，但仍然是一个项目，是一个附录到presentation层，而不是服务层更接近它。然后，操作可能是这样的：

Anything else that follows after - like projecting properties - is view specific and I would not like to have it in this layer. In order to make this approach possible IQueryable<T> must be exposed from the service/repository. It does not mean that the select must be directly in the controller action. Especially fat and complex projections (that maybe join other entities by navigation properties, perform groupings, etc.) could be moved into extension methods of IQueryable<T> that are collected in other files, directories or even another project, but still a project that is an appendix to the presentation layer and much closer to it than to the service layer. An action could then look like this:

public ActionResult Kittens()
{
    var result = kittenService.GetKittens()
        .Where(kitten => kitten.fluffiness > 10)
        .OrderBy(kitten => kitten.name)
        .Select(kitten => new {
            Name=kitten.name,
            Url=kitten.imageUrl
        })
        .Take(10);
    return Json(result,JsonRequestBehavior.AllowGet);
}

或者是这样的：

public ActionResult Kittens()
{
    var result = kittenService.GetKittens()
        .ToKittenListViewModel(10, 10);
    return Json(result,JsonRequestBehavior.AllowGet);
}

使用 ToKittenListViewModel（）之中：

public static IEnumerable<object> ToKittenListViewModel(
    this IQueryable<Kitten> kittens, int minFluffiness, int pageItems)
{
    return kittens
        .Where(kitten => kitten.fluffiness > minFluffiness)
        .OrderBy(kitten => kitten.name)
        .Select(kitten => new {
            Name = kitten.name,
            Url = kitten.imageUrl
        })
        .Take(pageItems)
        .AsEnumerable()
        .Cast<object>();
}

这只是一个基本的想法和另一种解决方案可能是选择1和2之间的中间草图。

That's just a basic idea and a sketch that another solution could be in the middle between option 1 and 2.

嗯，这一切都取决于整体架构和要求，所有我上面写的可能是无用的，错误的。你必须考虑的ORM或数据访问技术能够在未来是否会改变？莫不是控制器和数据库之间的物理边界，则控制器从上下文断开连接并执行数据需要通过例如在未来的Web服务要获取？这将需要一个非常不同的方法，它能够对选项2较瘦。

Well, it all depends on the overall architecture and requirements and all what I wrote above might be useless and wrong. Do you have to consider that the ORM or data access technology could be changed in future? Could there be a physical boundary between controller and database, is the controller disconnected from the context and do the data need to be fetched via a web service for example in future? This would require a very different approach which would more lean towards option 2.

这样的结构是如此不同， - 在我看来， - 你根本不能说可能或不是现在，但可能它可能是未来的一个要求，或者可能也不会。这是该项目的利益相关者来定义，然后才能与架构决策进行，因为它会增加开发成本大幅提升的东西，它将我们在开发和维护冤枉钱，如果可能变成了永远不会成为现实。

Such an architecture is so different that - in my opinion - you simply can't say "maybe" or "not now, but possibly it could be a requirement in future, or possibly it won't". This is something that the project's stakeholders have to define before you can proceed with architectural decisions as it will increase development costs dramatically and it will we wasted money in development and maintenance if the "maybe" turns out to never become reality.

我只谈论查询或GET其中有很少的东西，我称之为商业逻辑在所有的web应用程序的请求。 POST请求和修改数据是一个完全不同的故事。如果它是被禁止的订单可以改变它发票为例之后，这是一个一般的业务规则，通常适用无论哪个查看或Web服务或后台进程或任何试图更改订单。我一定会把对订单状态这样的检查到业务服务或任何通用的组件，从来没有到控制器。

I was talking only about queries or GET requests in a web app which have rarely something that I would call "business logic" at all. POST requests and modifying data are a whole different story. If it is forbidden that an order can be changed after it is invoiced for example this is a general "business rule" that normally applies no matter which view or web service or background process or whatever tries to change an order. I would definitely put such a check for the order status into a business service or any common component and never into a controller.

有可能是反对使用参数的IQueryable＆LT; T＆GT; 控制器中的作用，因为它连接到LINQ到实体，它将使单元测试困难。但究竟什么是单元测试要在一个控制器动作不包含任何业务逻辑，即获得在传递的参数测试通常是通过模型绑定或路由来自一个观点 - 不包括单元测试 - 使用一个嘲弄库/服务返回的IEnumerable＆LT; T＆GT; - 数据库查询和访问，未经测试 - 并且返回查看 - 正确的视图渲染没有测试？

There might be an argument against using IQueryable<T> in a controller action because it is coupled to LINQ-to-Entities and it will make unit tests difficult. But what is a unit test going to test in a controller action that doesn't contain any business logic, that gets parameters passed in that usually come from a view via model binding or routing - not covered by the unit test - that uses a mocked repository/service returning IEnumerable<T> - database query and access is not tested - and that returns a View - correct rendering of the view is not tested?

这篇关于从控制器访问数据库实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！