none
Help with my data model please, and avoiding linq2objects with my methods? RRS feed

  • Question

  • I have a code first model similar to the below.  I have many objects that can change over time.  For such objects, all of the data fields are moved into a History object.  My initial idea is to have an AsOf and Current property on the parent object such as below.  I have also considered implementing some sort of generic extension method instead.  So Person might instead implement IHistoryContainer and there would be Current and AsOf extension methods which operates on IHistoryContainer, and IHistoryContainer would have a single property called Histories so the AsOf and Current would always query those and expect the child objects in the collection to implement IHistory which has the Starts and Ends dates, but essentially the code would be very similar, and allow me to avoid duplicate code across objects which are History Containers.

    I have two main concerns about all of these designs.  Usability, and performance.  (Disclaimer: I acknowledge that I'm using the method based snytax for queries, and maybe this isn't technically linq syntax, so my apologies for maybe using the term linq loosely.)

    From a performance standpoint, how would I implement these AsOf or Current properties and/or extension methods so that they can be used in queries and ensure the query can be delegated to the DB server.  I.e. I want to avoid something where doing db.Persons.Find(id).Current().Where(p=>p.City == "Chicago") would cause part of the query to use linq2objects instead of linq2entities because of the .Current() call.  In other words, I want .AsOf to behave like .Where such that it composes the chain of calls as one query, and doesn't make multiple queries/roundtrips, or cause .AsOf to use linq2objects such that the rest of the chained methods use linq2objects instead of linq2entities.  My guess is that I should be returning IQueryable somehow from Current, or will this happen automatically?

    As for usability, I want to avoid having something like the last usage example, where every object in my object graph that involves history requires that I call AsOf or Current on it.  I would prefer something where I can specify the AsOf datetime once, and have that used throughout my entire model for that query(imagine a more complex model where there's other objects, which could implement some pattern that allows them to be sliced at a point-in-time globally).

    public class Person{
      public ICollection<PersonHistory> PersonHistories{get;set}
      public ICollection<Person> Relatives{get;set;}
      public PersonHistory AsOf(DateTime asOf) {   
        return PersonHistories.SingleOrDefault(p=>p.Starts <= asOf && p.Ends > asOf);}
      public PersonHistory Current { get{ return AsOf(DateTime.Now); }}
    }
    
    public class PersonHistory:IHistory{
      public string Name {get;set;}
      public string Street {get;set;}
      //etc.
      
      //IHistory members:
      public DateTime? Starts {get;set;}
      public DateTime? Ends {get;set;}
    }

    Alternative design if I moved AsOf and Current to extension methods:

    public interface IHistoryContainer<T>{
      public ICollection<T> Histories{get;set}
    }
    
    public class Person:IHistoryContainer<PersonHistory>{
      public ICollection<Person> Relatives{get;set;}
      
      //IHistoryContainer member
      public ICollection<PersonHistory> Histories{get;set}
      //AsOf and Current moved to extension methods that operate on IHistoryContainer
    }

    Usage:

    An example that involves traversing into other objects that also involve history,such as relatives in this example(could be something besides a Person, but for simplicity of the example I used Relatives so it would also be a PersonHistory also).  Notice the repetition of .AsOf.  I can't start with Persons.Find(id).AsOf(...) because I need access to Relatives and PersonKey, which would not be available if I started off by drilling into the PersonHistory.  (I considered avoiding this by moving Relatives into the PersonHistory object instead, but it makes the model much more complicated to do updates, as a new entry in PersonHistory would require rewiring relationships in both directions, and it seems like this would get exponentially more complicated if there were other relationships at that level.)

    db.Persons.Find(id).OrderBy(p => p.AsOf(someDate).Name)
                .Select(p => new
                {
                  id = p.PersonKey,
                  cell =
                  new string[] { 
                @"<a href=""" + Url.Content("~/Person/Details/" + p.PeronsKey) + @""">" +
                p.AsOf(someDate).Name
                + "</a>"
                , p.AsOf(someDate).Name , p.AsOf(someDate).City, p.AsOf(someDate).County, p.AsOf(someDate).Status, 
                string.Join(",", p.relatives.Select( r => r.AsOf(someDate).Name ?? "" ).Distinct())
              }

    Ideal usage, but I'm not sure how to accomplish this(I don't really care where the SetAsOf is called, just that it is only needed once per query and affects all objects which are flagged in some way as History Containers):

    db.SetAsOf(someDate).Persons.Find(id).OrderBy(p => p.Name)
                .Select(p => new
                {
                  id = p.PersonKey,
                  cell =
                  new string[] { 
                @"<a href=""" + Url.Content("~/Person/Details/" + p.PeronsKey) + @""">" +
                p.Name
                + "</a>"
                , p.Name , p.City, p.County, p.Status, 
                string.Join(",", p.relatives.Select( r => r.Name ?? "" ).Distinct())
              }

    A third design would involve creating NotMapped getter properties on the Person class which emulate all the Name, Street, etc. data properties on the child history class, adding a local variable to the Person class which is a AsOf date, and basically to use this you would assign the AsOf date and then when you access the properties of the Person class, it would then query the Histories collection and grab the specific property value.  I am not really concerned with the extra code involved in doing this.  The main issue with this design is the sort of mysterious way these property's behavior depends on you setting the AsOf property.  By default it could assume DateTime.Now, it still seems error prone if one sets the property on one object but forgets for another.  It also would make for some odd looking queries to have to set a property on the root object before referencing child objects.  When you query different objects in a object graph, using default DateTime.Now, then each will be slightly different time when accessing each object.  As opposed to setting some global AsOf date to Datetime.Now, and then all objects using that set date, to insure there are no funky race conditions because one object was retrieved with a slightly different DateTime than another object.  So I am imagining something similar to a Inversion of control design, where I set an AsOf date on some global object and all other objects that involve history would reference that global AsOf date when deciding what slice of history to return.  I am kind of clueless on how to do this though so that it is scoped only to the current method(imagine an controller action method querying the model to produce a view model, I wouldn't want something at to global of a level that might effect other users hitting the webpage at the same time).

    public class Person{
      public ICollection<PersonHistory> Histories{get;set}
      public ICollection<Person> Relatives{get;set;}
      public DateTime AsOf { get;set;} 
      //or some global IoC kind of design or retrieving AsOf,
      //IDK how I would do this though.  
      //Getter could default to returning the DateTime.Now if not set
    
      public string Name {get{ 
        return Histories.SingleOrDefault(p=>p.Starts <= AsOf && p.Ends > AsOf).Name;  }}
      public string Street {get{  
        return Histories.SingleOrDefault(p=>p.Starts <= AsOf && p.Ends > AsOf).Street;  }}
      //etc.
    }

    This design would allow me to get closer to the second, more ideal usage example.

    Maybe to improve performance, to avoid .Name and .Street from duplicating both the SingleOrDefault call if they were both referenced in the same query, I could have a private variable that grabs the current History in the setter of the AsOf property, and assigns the selected History object to the private variable for reuse across all the data properties.

    I have never done anything quite this complicated involving linq2entities or extension methods and am not sure if I am walking into a mine field, so any ideas/insight would be welcome.  I have a bad feeling though, this might be one of those things where I'm not going to get a nice usable API and performance at the same time. 

    Something I'm confused about is alot of extension method examples/libraries I see operate on IEnumerable, and my concern is if my extension method does something that can happen in the DB(i.e. not using any methods not available in the DB engine) will it run the query on the DB, or run it on the client using linq2objects?

    Thanks in advance.


    • Edited by AaronLST Wednesday, May 30, 2012 7:22 PM
    Wednesday, May 30, 2012 6:54 PM

All replies