How do I add a calculated column to my EF4 model?

Question

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.

Answer 1

I just had a situation where I needed count properties for two related entities without loading the collections. One thing I found out is that you need to have MultipleActiveResultSets=True in the connection string to avoid an exception being thrown on the ObjectMaterialized eventhandler when querying other entitycollections.

Answer 2

After much deliberation, I ended up with the following solution:

First, create a view containing all User fields plus a LastLogin date field (from my original post).

After adding the user (call it User_Model) and the user view (call it UserView_Model) to my EF model, I created a wrapper class (call it User_Wrapper) around the User_Model and added an additional DateTime property for LastLogin.

I modifed the User_Wrapper class to fetch from the UserView_Model, and then populate the underlying User_Model by reflecting over all the properties shared between the User_Model and UserView_Model. Finally, I set the User_Wrapper.LastLogin property based on the fetched User_View.

All other functions (Create,Update,Delete...) operate on the User_Model. Only the Fetch uses the UserView_Model.

---

What did all this do? I now only have one database call to populate a single User_Wrapper or a List<User_Wrapper>.

The drawbacks? I guess that because my UserView_Model does not have any associated relationships, I would not be able to do any eager loading using the EF ObjectContext. Fortunately, in my situation, I don't find that to be an issue.

Is there a better way?

Answer 3

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.