Best way to return multiple scalar/keyvaluepair

hitch · **Joined:** Wed Nov 29, 2006 12:23 pm **Posts:** 42

I have a lot of cases where I have an entity that has a lot of attributes, but for a particular scenario, a need to only retrieve 2 attributes to display on a web page (i.e. I need the id and name for an entity to populate a dropdown). At the moment I am doing something similar to this:

Code:

 List<KeyValuePair<int, string>> returnValues= new List<KeyValuePair<int, string>>();
            ArrayList codes = (ArrayList)session.CreateSQLQuery("Select Id, Name from table")
               .AddScalar("Id", NHibernateUtil.Int32)
               .AddScalar("Name", NHibernateUtil.String)
               .List();

            foreach (object[] code in codes)
            {
                returnValues.Add(new KeyValuePair<int, string>((Int32)code[0], ((string)(code[1] == null ? "" : code[1]))));
            }

            return returnValues;

But I'm wondering if there is a better/easier way.

Would it be better to subclass my entity to just have a "brief" entity with the necessary attributes and have a subclass in the mapping file so I can just return a collection of "briefEntities"?

Is there a better way to do what I'm doing? Can I provide a ResultsTransformer using SetResultsTransformer to return a typed list and then not need the foreach loop? If so, what's the appropriate syntax?

scesbron · **Joined:** Thu Sep 04, 2003 8:23 am **Posts:** 368

You can also do this with an hql query instead of sql one.
If you have two classes mapped to one table (one with only id and name and another complete) you will have to be careful because you may have two instances in memory that map to the same db row.
I don't know how big is your class but sometimes it is better to always instantiate it entirely and to improve performance with second level caching : it is more 00 than using sql queries

hitch · **Joined:** Wed Nov 29, 2006 12:23 pm **Posts:** 42

Thanks for the info. I should have said in my original post that the intention would be for the "brief" object to be read only, although I don't know that it would be possible to enforce that using mutable in the mapping, as I would want the full entity to be read/write. I have got a readonly implementation of a DAO though, so that may make it possible. It wouldn't prevent a developer from writing direct session code that updates though.

canton · **Posted:** Fri Mar 16, 2007 9:46 pm

How about this?

Code:

select new Family(mother, mate, offspr)
from Eg.DomesticCat as mother
    join mother.Mate as mate
    left join mother.Kittens as offspr

It returns IList<Family>
You can find it here: http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-select

And you don't need a hbm mapping for the class Family!

But the doc does not tell where the magic is. You need to properly set the configuration.

Code:

Configuration cfg = new Configuration();
cfg.Imports["Family"] = "Namespace.Family, Assembly";

abombss · **Posted:** Tue Mar 20, 2007 10:29 pm

I use the new Projection support and a result transformer. Nice part about this is it works nice with Ayende's Rhino-Commons and NHQG. Also, there is no overhead like some of the other examples of hydrating entities. The sql generated selects just the two columns for Name and Value and there are no random joins caused from lazy loading settings.

Code:

public class DropDownDTO<NameType, ValueType>
   {

      public static IList<DropDownDTO<NameType, ValueType>> List(ISession session, Type type, string nameProperty)
      {
         return List(session, type, nameProperty, null, false);
      }

      public static IList<DropDownDTO<NameType, ValueType>> List(ISession session, Type type, string nameProperty, string valueProperty)
      {
         return List(session, type, nameProperty, valueProperty, false);
      }

      public static IList<DropDownDTO<NameType, ValueType>> List(ISession session, Type type, string nameProperty, string valueProperty, bool distinct)
      {
         ICriteria c = session.CreateCriteria(type);
         ProjectionList pl = Projections.ProjectionList();

         if (string.IsNullOrEmpty(valueProperty))
            pl.Add(Projections.Id());
         else
            pl.Add(Projections.Property(valueProperty));
         pl.Add(Projections.Property(nameProperty));

         if (distinct)
            c.SetProjection(Projections.Distinct(pl));
         else
            c.SetProjection(pl);

         c.SetResultTransformer(new NHibernate.Transform.AliasToBeanConstructorResultTransformer(
            typeof(DropDownDTO<NameType, ValueType>).GetConstructors()[0]));
         return c.List<DropDownDTO<NameType, ValueType>>();
      }

      public DropDownDTO(ValueType value, NameType name)
      {
         _name = name;
         _value = value;
      }

      public NameType Name { get { return _name; } } NameType _name;
      public ValueType Value { get { return _value; } } ValueType _value;
   }

public void Page_Load(object sender, EventArgs args)
{
  ddl.DataSource = DropDownDTO<String, Int32>.List(_session, typeof(Contact), "DisplayName");
  ddl.NameField = "Name";
  ddl.ValueField = "Value";
}

Adam