How to map a view without an identity column in NHibernate?

Question

I have a view that I am going to only read from (no writes). This view does not have any unique key (not event composite).

So how can I map this view in NHibernate without touching the view? I do not want to add a new column to the view to generate a unique identity for me. Is there a way to map this view and generate the identity column on the NHibernate side?

I can generate a GUID in my entity class like:

public class MyViewClass
{
    private Guid _id = new Guid();
    public virtual Guid Id { get { return _id; } set { _id = value; } }
}

But how can I make the mapping work? The following code does not work:

public class MyViewClass: ClassMapping<MyViewClass>
{
    public MyViewClass()
    {
        Mutable(false);
        Id(x => x.Id, m => m.Generator(Generators.Guid));
    }

}

It expects to have the Id column in view and throws:

System.Data.SqlClient.SqlException: Invalid column name 'Id'.

BTW, I am using NHibernate 3.2 and mapping by code.

I suppose you could create a composite key and then, when overriding `GetHashCode` and `Equals`, just provide the base implementations? — lbergnehr, Dec 19 '11 at 21:28
I would bite the bullet and add `newid() as Id` as an additional column onto my view. — Rippo, Dec 20 '11 at 15:23
@seldon: composite key out of what? database columns? there is no real composite key out of database columns that I can use as a unique key. Also I cannot just use the property Id that I have defined in the MyViewClass for the composite key because it still looks for this column in database (which does not exist). So can you please elaborate a bit about your solution? tnx : > — kaptan, Dec 21 '11 at 01:19
@kaptan - if there is no composite key to the view then you're returning duplicate data. What use is this data? It sounds to be like the view is flawed. — Phill, Dec 21 '11 at 01:40
@Phill: ok, the only unique key would be a key of all columns. there are no duplicates. — kaptan, Dec 21 '11 at 20:18

Firo · Answer 1 · 2011-12-21T08:51:08.583

1

Update: to use it in LINQ map all columns as CompositeId and Mutable(false) then override Equals and GetHashCode with default implementation.

public class MyViewClass
{
    public override bool Equals(object obj)
    {
        return base.Equals(obj);
    }

    public override int GetHashCode()
    {
        return base.GetHashCode();
    }
}

original answer:

i wouldnt map it at all if you dont want to insert/update it

public class MyViewClass
{
    public virtual string Prop1 { get; set; }
    public virtual int Prop2 { get; set; }
}

var viewObjects = session.CreateSQLQuery("SELECT col1 as Prop1, col2 as Prop2 FROM MyView")
    .SetResultTransformer(Transformers.AliasToBean<MyViewClass>())
    .List<MyViewClass>();

edited Dec 21 '11 at 08:51

answered Dec 20 '11 at 17:02

Firo

30,626
4
55
94

But I want to use LINQ to NHibernate. So I guess I need to create the mapping. – kaptan Dec 20 '11 at 20:55
Yes; this would work, but I was looking for a smarter way to do it. Also, I don't think you ever want to implement Equals method like return base.Equals(obj); unless you know that your base has implemented Equals properly. – kaptan Dec 22 '11 at 00:44
@kaptan i was just answering your question, assuming that even all columns do not form a unique key. if they are unique then implement Equals and GetHashCode properly. Question: What do you mean with smarter way? can you formulate an sql-query which would return the results in the smarter way? – Firo Dec 22 '11 at 08:14

score 0 · Answer 2 · answered Jul 27 '23 at 13:38

From what I understand, NHibernate needs an Id column to be able to map a view to the entity. So to fulfill this requirement, there are 2 ways:

Add an Id column. In my case, since my view is a summary of a table with an Id column, I just added the Id column also in the view definition.
Add a composite Id from multiple columns in the mapping class.

In the mapping class I also needed to add SchemaAction(.), otherwise NHibernate will complain on runtime that it cannot map the entity to a table/view.

The mapping class would look like this (Either choose option 1 or 2):

public class EntityMap : ClassMapping<Entity> {

  public EntityMap() {
    SchemaAction(NHibernate.Mapping.ByCode.SchemaAction.None);
    Table("NameOfTheView");
    Mutable(false);

    // Option 1
    Id(e => e.Id, map => map.Column("Id"));

    // Option 2
    // NOTE: With ComposedId you need to override Equals() and GetHashCode() in the entity class
    ComposedId(map => {
      map.Property(e => e.Column1);
      map.Property(e => e.Column2);
    });

    // Additional mapping code
  }
}

I also prefer mapping the view to an entity because then I can use QueryOver or Query (LINQ) API to eliminate the use of magic strings (table/column names) in a normal SQL query.

How to map a view without an identity column in NHibernate?

2 Answers2