如何将计算列添加到我的EF4模型？ [英] How do I add a calculated column to my EF4 model?

问题描述

给出MS SQL 2008中的用户表和登录表：

  CREATE TABLE [dbo]。 [User_User]（
 [UserID] [int] IDENTITY（1000,1）NOT NULL，
 [UserName] [varchar]（63）NOT NULL，
 [UserPassword] [varchar] 63）NOT NULL 
）
 CREATE TABLE [dbo]。[Util_Login]（
 [LoginID] [int] IDENTITY（1000,1）NOT NULL，
 [User_UserID] [ int] NOT NULL， -  FK REFERENCES [dbo]。[User_User]（[UserID]）
 [LoginDate] [datetime] NOT NULL，
）
  

如何调整我的 User_User 实体框架模型对象以包含返回MAX（LoginDate）的UserLastLogin列？

我知道我可以在SQL视图周围创建一个EF4模型：

  CREATE VIEW [v_User_User] 
 AS 
 SELECT 
 [User_User]。*，
（
 SELECT MAX（LoginDate）
 FROM [Util_Login] 
 WHERE User_UserID = UserID 
）AS UserLastLogin 
 FROM [User_User] 
  

但是有一种方法可以修改User_User模型以包含计算列吗？

编辑：我正在寻找一种方式来获取用户或列表<用户>包括单个数据库查询中的Max（Util.LastLogin）日期。

解决方案

非常好的问题，是的在EF4中有一个完美的方法：



自定义属性 是一种将计算属性提供给实体。好消息是，自定义属性不一定需要从同一个实体的其他现有属性中进行计算，通过我们即将看到的代码，它们可以从任何我们喜欢的任何内容中计算出来！



这是以下步骤：


首先创建一个部分类并定义一个自定义属性（为简单起见，我假设 User_User 表已映射到User类，并将Util_Login 映射到Util）

  public partial class User {
 public DateTime LastLoginDate {get;组; } 
} 
  

所以，你可以在这里看到，而不是创建一个LastLoginDate属性该模型将需要映射回数据存储区，我们已经在部分类中创建了属性，然后我们可以选择在 对象实现 期间填充它或 按需 ，如果您不相信每个实体对象都需要提供该信息。



在您的情况下，为每个正在实现的用户的用户定制的 LastLoginDate 自定义属性是有用的，因为我认为这个值将是访问所有（或至少大多数）正在实现的实体。否则，您应该考虑仅在必要时计算属性，而不是在对象实现过程中。



为此，我们将利用 ObjectContext.ObjectMaterialized事件 ，随着ObjectContext正在创建数据，任何时候都会从查询中返回数据实体对象从该数据。 ObjectMaterialized事件是一个实体框架4的事情。
所以我们需要做的就是创建一个事件处理程序并将其订阅到ObjectMaterialized事件。



放置此代码（订阅事件）的最佳位置位于 OnContextCreated方法 之中。该方法由上下文对象的构造函数和构造函数
重载调用，这是一个没有实现的部分方法，只是由EF代码生成器创建的方法签名。



好​​的，现在您需要为您的ObjectContext创建一个部分类。 （我假定名称是 UsersAndLoginsEntities ），并将事件处理程序（我命名为 Context_ObjectMaterialized ）订阅到对象实体化事件。

  public partial class UsersAndLoginsEntities {
 partial void OnContextCreated（）{
 this.ObjectMaterialized + = Context_ObjectMaterialized; 
} 
} 
  

最后一步（真正的工作）将是实现这个处理程序来实际填充我们的自定义属性，在这种情况下，这很简单：

  void Context_ObjectMaterialized（object sender， ObjectMaterializedEventArgs args）
 {
 if（args.Entity is User）{
 User user =（User）args.Entity; 
 user.LastLoginDate = this.Utils 
 .Where（u => u.UserID == user.UserID）
 .Max（u => u.LoginDate）; 
} 
} 
 
  

希望这有帮助。

Given a "User" table and a "Login" table in MS SQL 2008:

CREATE TABLE [dbo].[User_User](
    [UserID] [int] IDENTITY(1000,1) NOT NULL,
    [UserName] [varchar](63) NOT NULL,
    [UserPassword] [varchar](63) NOT NULL
)
CREATE TABLE [dbo].[Util_Login](
    [LoginID] [int] IDENTITY(1000,1) NOT NULL,
    [User_UserID] [int] NOT NULL, -- FK REFERENCES [dbo].[User_User] ([UserID])
    [LoginDate] [datetime] NOT NULL,
)

How do I adjust my User_User entity framework model object to include a "UserLastLogin" column that returns a MAX(LoginDate)?

I know that I can create an EF4 model around a SQL View:

CREATE VIEW [v_User_User]
AS
SELECT 
        [User_User].*, 
        (
                SELECT MAX(LoginDate) 
                FROM [Util_Login] 
                WHERE User_UserID = UserID
        ) AS UserLastLogin
FROM [User_User]

But is there a way that I can just modify the User_User model to include the calculated columnn?

EDIT: I am looking for a way to fetch a User or a List<User> including the Max(Util.LastLogin) date in a single db query.

解决方案

Very good question, and Yes, there is a perfect way to accomplish this in EF4:

Custom properties are a way to provide computed properties to entities. The good news is that Custom properties don’t necessarily need to be calculated from other existing properties on the very same entity, by the code we are about to see, they can computed from just about anything we like!

Here are the steps:
First create a partial class and define a custom property on it (For simplicity, I assumed User_User table has been mapped to User class and Util_Login to Util)

public partial class User {
    public DateTime LastLoginDate { get; set; }
}

So, as you can see here, rather than creating a LastLoginDate property in the model, which would be required to map back to the data store, we have created the property in the partial class and then we have the option to populate it during object materialization or on demand if you don’t believe that every entity object will need to provide that information.

In your case precalculating the LastLoginDate custom property for every User being materialized is useful since I think this value will be accessed for all (or at least most) of the entities being materialized. Otherwise, you should consider calculating the property only as needed and not during object materialization.

For that, we are going to leverage ObjectContext.ObjectMaterialized Event which is raised anytime data is returned from a query since the ObjectContext is creating the entity objects from that data. ObjectMaterialized event is an Entity Framework 4 thing. So all we need to do is to create an event handler and subscribe it to the ObjectMaterialized Event.

The best place to put this code (subscribing to the event) is inside the OnContextCreated Method. This method is called by the context object’s constructor and the constructor overloads which is a partial method with no implementation, merely a method signature created by EF code generator.

Ok, now you need to create a partial class for your ObjectContext. (I assume the name is UsersAndLoginsEntities) and subscribe the event handler (I named it Context_ObjectMaterialized) to ObjectMaterialized Event.

public partial class UsersAndLoginsEntities {
    partial void OnContextCreated() {
        this.ObjectMaterialized += Context_ObjectMaterialized;
    }
}

The last step (the real work) would be to implement this handler to actually populate the Custom Property for us, which in this case is very easy:

void Context_ObjectMaterialized(object sender, ObjectMaterializedEventArgs args) 
{
    if (args.Entity is User) {        
        User user = (User)args.Entity;
        user.LastLoginDate = this.Utils
                .Where(u => u.UserID == user.UserID)
                .Max(u => u.LoginDate);
    }
}

Hope this helps.

这篇关于如何将计算列添加到我的EF4模型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！