Home → Help and guidance →

Mapping a view without a unique id column

Frank

01 Sep, 2010 01:12 PM via web

Admin Functions

• Edit
• Remove

I see here that this issue should be fixed, but i really dont get it...

can you please post some codesnippet?

imagine the following

public class Hhst
{
    public virtual string Hkz { get; set; }
    public virtual string Kapitel { get; set; }
    public virtual string Titel { get; set; }
    public virtual string Apl { get; set; }
    public virtual string Hhpz { get; set; }
}
public class HhstMap : ClassMap<Hhst>
{
    public HhstMap()
    {
        Table("HHST"); //existing view without any keys in a oracle db

        ReadOnly();

        Id();

        Map(x => x.Hkz);
        Map(x => x.Kapitel);
        Map(x => x.Titel);
        Map(x => x.Apl);
        Map(x => x.Hhpz);
    }
}

this mapping did not work. can you please post the right one?

thx frank

1. Support Staff 2 Posted by James Gregory on 01 Sep, 2010 01:40 PM

   

   What about that doesn't work?

   Admin Functions

   • Edit
   • Remove

2. 3 Posted by frank on 01 Sep, 2010 01:46 PM

   

   i got the following error:

   could not execute query
   [ SELECT this.id as id3_0, this.Hkz as Hkz3_0, this.Kapitel as Kapitel3_0, this.Titel as Titel3_0, this.Apl as Apl3_0, this.Hhpz as Hhpz3_0 FROM HHST this_ ]

   this seems ok cause my view has no id column.

   but i found a solution on stackoverflow

   public class HhstMap : ClassMap<Hhst>
   {
       public HhstMap()
       {
           Table("HHST");
   
           ReadOnly();
   
           Not.LazyLoad();
           Id(x => x.Hkz).GeneratedBy.Assigned();
   
           Map(x => x.Hkz);
           Map(x => x.Kapitel);
           Map(x => x.Titel);
           Map(x => x.Apl);
           Map(x => x.Hhpz);
       }
   }

   now i can run my queries. but i really dont know wether its a hack or not ;)

   Admin Functions

   • Edit
   • Split
   • Remove

3. 4 Posted by Joao Fernandes on 09 Sep, 2010 03:17 PM

   

   Hi Frank,

   I'm sorry to be the bearer of bad news, but that hack doesn't work at all.

   I've had the same difficulties and after checking the retrieved objects I noticed that all objects with the same key (in your example x.Hkz) are actually copies of the first that gets retrieved from the DB.

   In my situation I solved the requirement by querying the table directly instead of the view... but if you find a solution for this, please do post it!

   Best of luck!

   Joao

   Admin Functions

   • Edit
   • Split
   • Remove

4. 5 Posted by Frank on 13 Sep, 2010 05:55 AM

   

   Joao you are right... i take the table too now. Maybe James can post or give an anwser to this subject.

   Admin Functions

   • Edit
   • Split
   • Remove

5. 6 Posted by Patrick on 15 Nov, 2010 09:31 PM

   

   Hi,

   So is there or can we get an example on how to do this?

   cheers

   Patrick

   Admin Functions

   • Edit
   • Split
   • Remove

6. 7 Posted by Joao Fernandes on 16 Nov, 2010 09:12 AM

   

   Hi Patrick,

   I did find a solution in some nHibernate manual that entails returning value objects (non-managed entities) instead of entities.

   "14.1.5. Returning non-managed entities
   It is possible to apply an IResultTransformer to native sql queries. Allowing it to e.g. return non-managed entities.
   
   sess.CreateSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
           .SetResultTransformer(Transformers.AliasToBean(typeof(CatDTO)))
   
   This query specified:
         the SQL query string
         a result transformer
   
   The above query will return a list of CatDTO which has been instantiated and injected the values of NAME and BIRTHNAME into its corresponding properties or fields. "

   Hope that helps,

   Cheers,

   J

   Admin Functions

   • Edit
   • Split
   • Remove

7. 8 Posted by O'Donnell, Patrick on 17 Nov, 2010 08:11 PM

   

   Hi Joao,

   Thanks for the pointer, I will investigate this for our application and
   let you know how I got on.

   Cheers

   Patrick

   -----Original Message-----
   From: Joao Fernandes
   [mailto:***@tenderapp.com]
   Sent: Tuesday, 16 November 2010 10:12 p.m.
   To: O'Donnell, Patrick
   Subject: Re: Mapping a view without a unique id column [Help and
   guidance]

   Admin Functions

   • Edit
   • Split
   • Remove

8. 9 Posted by O'Donnell, Patrick on 18 Nov, 2010 02:57 AM

   

   Hi Joao,

   Using your suggestion below and a lot of playing around, I have now
   managed to retrieve from my view a bunch of unmanaged objects!!

   Thanks for your help.

   Cheers

   Patrick
    

   -----Original Message-----
   From: Joao Fernandes
   [mailto:***@tenderapp.com]
   Sent: Tuesday, 16 November 2010 10:12 p.m.
   To: O'Donnell, Patrick
   Subject: Re: Mapping a view without a unique id column [Help and
   guidance]

   Admin Functions

   • Edit
   • Split
   • Remove

9. 10 Posted by Joao Fernandes on 18 Nov, 2010 09:54 AM

   

   Glad I could help.

   Cheers,

   J

   Admin Functions

   • Edit
   • Split
   • Remove

10. 11 Posted by Frank on 22 Nov, 2010 12:53 PM

    

    Hi Patrick, can you post your mapping und sample code?

    Admin Functions

    • Edit
    • Split
    • Remove

11. 12 Posted by O'Donnell, Patrick on 22 Nov, 2010 09:59 PM

    

    Certainly Frank,

    You may have to excuse the formatting - I have never contributed to a
    forum before and am unaware how to format it beautifully :)

    I have an view in the project I am working on that looks like this:

    CREATE VIEW [dbo].[Impersonation]
    AS

    SELECT DISTINCT O.UserId
    ,NULL as OutletId
    ,RetailerId
    FROM dbo.Owners O
    JOIN dbo.Staff S
    ON O.UserId = S.UserId
    WHERE (S.TerminationDate is null or S.TerminationDate >= GETDATE())

    UNION ALL

    SELECT DISTINCT S.UserId
    ,RO.OutletId
    ,RO.RetailerId
    FROM dbo.Staff S
    JOIN dbo.RetailerOutlets RO
    ON S.RetailerOutletsId = RO.RetailerOutletsId
    WHERE (S.TerminationDate is null or S.TerminationDate >= GETDATE())
    
    UNION ALL

    SELECT DISTINCT O.UserId
    ,RO.OutletId
    ,R.RetailerId
    FROM dbo.Owners O
    JOIN dbo.Staff S
    ON O.UserId = S.UserId
    JOIN dbo.Retailers R
    ON O.RetailerId = R.ParentId
    JOIN dbo.RetailerOutlets RO
    ON R.RetailerId = RO.RetailerId
    WHERE (RO.TerminationDate is null or RO.TerminationDate >= GETDATE())
    and (S.TerminationDate is null or S.TerminationDate >=
    GETDATE())

    To map that as an unmanaged entity as suggested by Joao I created an
    IRepository extension method using the following code:

    public static class ImpersonationRepository
    {
    public static IList<Impersonation>
    GetImpersonationOutletsFor(this IRepository<Impersonation> repository,
    Staff staff)
    {
    var results = repository.CreateSqlQuery(Query +
    string.Format("'{0}'", staff.Id))
    .AddEntity("Staff", typeof(Staff))
    .AddEntity("Outlet", typeof(Outlet))
    .AddEntity("Retailer", typeof(Retailer))
    
    .SetResultTransformer(Transformers.AliasToBean(typeof(Impersonation)))
    .List<Impersonation>();

    return results;
    }

    #region Private fields

    private const string Query = "SELECT {Staff.*}, {Outlet.*},
    {Retailer.*} " +
    "FROM dbo.Impersonation I " +
    "JOIN dbo.Staff Staff " +
    "ON I.UserId = Staff.UserId " +
    "LEFT JOIN dbo.Outlets Outlet " +
    "ON I.OutletId = Outlet.OutletId "
    +
    "JOIN dbo.Retailers Retailer " +
    "ON I.RetailerId =
    Retailer.RetailerId WHERE I.UserId = ";

    #endregion
    }

    My major gotcha was actually specifiying the alias in AddEntity() and
    using it correctly in the native SQL. I have property names on two
    objects that are identical and nHibernate was incorrectly populating
    both objects from the first property name it found.

    I used the following link extensively to get this to work:
    http://knol.google.com/k/fabio-maulo/nhibernate-chapter-14-native-sql/1n
    r4enxv3dpeq/17#

    Hope this helps you out.

    Cheers

    Patrick

    -----Original Message-----
    From: Frank
    [mailto:***@tenderapp.com]
    Sent: Tuesday, 23 November 2010 1:54 a.m.
    To: O'Donnell, Patrick
    Subject: Re: Mapping a view without a unique id column [Help and
    guidance]

    Admin Functions

    • Edit
    • Split
    • Remove