Home → Help and guidance →

Retrieving distinct values in mapping

Original Post

Colin Bowern

06 Mar, 2010 12:50 AM via web

Admin Functions

• Edit
• Remove

I have an existing view which has too much data in it. Unfortunately I cannot get rid of it so I need to try to work around it using my NHibernate mapping. The idea is to have NH issue the following query:

SELECT DISTINCT User_Id, Module_Id, Application_Id, RightsMask
FROM V_UserApplicationPermissions
WHERE User_Id = ?

My current mapping for this list of AccessControlEntry types looks like this:

HasMany(x => x.Rights)

.WithTableName("V_UserApplicationPermissions") 
.KeyColumnNames.Add("User_Id") 
.Component(c => 
{ 
    c.Map(x => x.Module, "Module_Id"); 
    c.Map(x => x.Application, "App_Id"); 
    c.Map(x => x.Rights, "RightsMask").CustomTypeIs<ApplicationRightsType>(); 
}) 
.Not.LazyLoad();

Any thoughts on how to have NHibernate put a DISTINCT keyword in there during the query?

1. Support Staff 2 Posted by Paul Batum on 07 Mar, 2010 05:51 AM

   

   Do you really need to map this as a domain object? Why not use the criteria api or hql to perform this query?

   I'm not aware of a way to tell nhibernate to issue a distinct command when loading domain objects. If it does exist, we can provide you assistance with doing it using our fluent API.

   Admin Functions

   • Edit
   • Remove

2. 3 Posted by Colin Bowern on 11 Mar, 2010 04:39 PM

   

   Olivier Coanet from the NHUsers mailing list suggested hacking it into the WithTableName which worked:

   HasMany(x => x.Rights)
   

   .WithTableName("(SELECT DISTINCT User_Id, Module_Id, App_Id, RightsMask FROM V_UserApplicationPermissions)")   
   .KeyColumnNames.Add("User_Id")   
   .Component(c =>   
   {   
       c.Map(x => x.Module, "Module_Id");   
       c.Map(x => x.Application, "App_Id");   
       c.Map(x => x.Rights, "RightsMask").CustomTypeIs<ApplicationRightsType>();   
   })
   

   Admin Functions

   • Edit
   • Split
   • Remove

3. Support Staff 4 Posted by James Gregory on 12 Mar, 2010 09:29 AM

   

   That's an interesting technique I haven't seen before. Glad you got it working.

   Admin Functions

   • Edit
   • Remove

4. James Gregory resolved this discussion on 12 Mar, 2010 09:29 AM.

Comments are currently closed for this discussion. You can start a new one.