Saturday, March 5, 2011

Fluent NHibernate - Working with Database Views


So, it turns out you can work with Views with Fluent NHibernate just as you were to work with tables. All you need to is define your entity with the name of the view, instead of the table name.

example :
public class UserEntity
{
    public virtual int UserId { get; private set; }
    public virtual String FirstName { get; set; }
    public virtual String LastName { get; set; }
    public virtual int UserStatusId { get; set; }
    public virtual String UserStatus { get; set; }
}

public class UserEntityMap : ClassMap<UserEntity>
{
    public UserEntityMap()
    {
        Table("view_Users");  // this is mapped to a view, and not a table

        Id(x => x.UserId);
        Map(x => x.FirstName);
        Map(x => x.LastName);
        Map(x => x.UserStatusId);
        Map(x => x.UserStatus);  // This field is from another table
                                 // it is from a seperate code table that describes the different statuses in the system
    }
}

An exception is thrown, when trying to update the entity that is mapped to a view. The problem is actually because when working with a view, you cannot execute an update query that updates rows on different tables. It will only work when updating rows on one table in the view.

In order to get around this, we need to tell the mapping that some properties aren't to be updated. This will solve the problem.

example :
public class UserEntityMap : ClassMap<UserEntity>
{
    public UserEntityMap()
    {
        Table("view_Users");  // this is mapped to a view, and not a table

        Id(x => x.UserId);
        Map(x => x.FirstName);
        Map(x => x.LastName);
        Map(x => x.UserStatusId);
        Map(x => x.UserStatus).Not.Update();
    }
}

Marking the mapping class with '.Not.Update()' tells FNH to return false on the update property of this field.
Likewise, we can also mark an attribute as '.Not.Insert()' and then the field will only be updatable, or mark a field as '.ReadOnly()' and the field will act as if it has a private set.

4 comments:

  1. Great!
    I'd also recommend flagging the entity's map and it's cache map to Readonly to improve performance.

    ReplyDelete
  2. It is necessary that all database transactions can operate independent of one another, while remaining invisible to one another to facilitate concurrent transactions within the same database. Such isolation is integral to facilitating concurrent control to facilitate accessibility by multiple users.kpi dashboards

    ReplyDelete
  3. Depending on the system, you might find a database engine that ranges in complexity from an Access Database to a full blown SQL database.create mysql dashboard

    ReplyDelete