While I no longer work at RCM Technologies, I still watch the discussion forums for the Kinetic Framework. Recently, a question popped up, and since it’s gone unanswered for a few days, I figured I’d chime in. Essentially, the person is wondering if there’s a way to get an entity back that is based on several tables rather than the typical Active Record pattern used by default. The answer? Yes, you can do that.

As a matter of fact, I recently had the opportunity to do just that with a new feature we added to Tourneytopia.com. I'll talk about the new features over there in the future, but as part of one of the new things, we needed to have a virtual entity - it was based on data across a few tables.

As it turns out, this isn't that hard to accomplish. It also highlights some of the areas where some renaming is needed, but that's another story! Anyway, all you have to do is inherit from our read-only base class, add some attributes to the properties, override a method from the base, and ensure you structure your query to match your columns. Here's a contrived example that pulls data from a couple of tables and a user defined function, and puts it all into one entity:

   1: public class UserSummary : EntityBasereadOnly

   2: {

   3:     private string _userName;

   4:     [DatabaseColumn]

   5:     public string UserName

   6:     {

   7:         get { return _userName; }

   8:         set { _userName = value; }

   9:     }

  10:     

  11:     private string _fullName;

  12:     [DatabaseColumn]

  13:     public string FullName

  14:     {

  15:         get { return _fullName; }

  16:         set { _fullName = value; }

  17:     }

  18:     

  19:     private int _orderCount;

  20:     [DatabaseColumn]

  21:     public int OrderCount

  22:     {

  23:         get { return _orderCount; }

  24:         set { _orderCount = value; }

  25:     }

  26:     

  27:     public static EntityListReadOnly<UserSummary> GetUserSummaries()

  28:     {

  29:         string commandText = @"

  30:                                 select u.UserName,

  31:                                        up.FirstName + ' ' + up.LastName as 'FullName',

  32:                                        dbo.GetOrderCount(u.UserId) as 'OrderCount'

  33:                                 from Users u 

  34:                                 inner join up 

  35:                                  on u.UserId = up.UserId

  36:         ";

  37:         

  38:         return EntityBaseReadOnly

  39:             .GetListReadOnly<UserSummary>(commandText, new List<SqlParameter>());

  40:     }

  41:     

  42:     protected override void EnsureParentProperties() { }

  43: }

There's one major thing to notice here: the use of the [DatabaseColumn] attribute on the properties. As long as the columns returned by your query match up with the property name in your query, then when you call EntityBaseReadOnly.GetListReadOnly(), it will be able to populate your entity automatically. Since this inherits from EntityBaseReadOnly, this entity can't be saved, which in most cases is just fine.