Multiple databases and "associations" / "member" classes

Hi,

I am doing some proof of concept tests to see if lightspeed is right for us. My major concern is if it will play nice when my domain model is spread across multiple database platforms (SQL Server, Oracle, & MySQL).

In my test code I have a class called 'LabRequest' which has a member class called 'Customer' & vica-versa. (n-to-1 relationship from 'LabRequest' to 'Customer' and 1-to-n from 'Customer' to 'LabRequest')

My issue is that 'LabRequest' sits in  SQL db and 'Customer' is in Oracle. How can I instruct LightSpeed to automatically load each class and it's member from their respective databases. Here's some code below:

My ‘Customer’ Class:

My ‘LabRequest’ Class:

My Test Page:

The test page above loads properly but when it tries to get the member class ‘Customer’ it gets error “Message = "Invalid object name 'CUSTOMERS'."” since it is looking for the wrong table in the wrong database.

What is the best approach when working with such an architecture? I’m thinking lazy loading will be helpful here.. any thoughts and suggestions will be appreciated. Thanks.

I'm afraid what you're describing can't be done "naturally" in LightSpeed.  A set of associated entities will all be part of a single unit of work, and a unit of work is bound to a particular LightSpeedContext and therefore a single connection string.  (In fact a unit of work encapsulates a database connection.)

The only way I can think of handling this is to use multiple units of work, and sacrifice LightSpeed associations.  That is, entities from different databases would not have associations, but only foreign keys.  For example, LabRequest would have a CustomerId field but not a Customer association.  Then you would hand-build methods or properties that calculated the associations, but did not use LightSpeed persistent storage to do it.  For example:

class LabRequest {
  public Customer Customer {
    get { return SqlRepository.FindById<Customer>(this.CustomerId); }
    set { this.CustomerId = value.Id; }
  }
}
class Customer {
  public IEnumerable<LabRequest> LabRequests {
    get { return OracleRepository.LabRequests.Where(lr => lr.CustomerId == this.Id); }
  }
  public void AddLabRequest(LabRequest lr) { ... }
  public void RemoveLabRequest(LabRequest lr) { ... }
}

Because there are no persistent associations (EntityHolders or EntityCollections) here, LightSpeed will not try to put related entities into the same unit of work, so they can continue to use their own database connections.

Note that as written this requires each entity type to know which databases its associated entities are stored in.  It should be possible to get around this by hiding the multiple databases within a single repository class -- I've done it this way to make it more explicit what's going on in the wrapper properties and methods.

Thanks Ivan for your reply.

I had my doubts about being able to 'naturally' do what I suggested in my first question, but your reply is insightlful on how to work around this issue.

Can you please explain how to "hide the multiple databases within a single repository class"? So that I don't have to set a property like "defaultDB" for each entity type? Thanks in advance.

Sure.  Suppose that LabRequest lives in Oracle and Customer in SQL Server.  In my example code I wrote:

class LabRequest {
  public Customer Customer {
    get { return SqlRepository.FindById<Customer>(this.CustomerId); }
  }
}

The problem with this is that it makes LabRequest responsible for knowing which database Customer lives in.  That shouldn't be LabRequest's problem: it should be the repository's problem.

So let's imagine creating a single repository class, which knows who belongs where:

class Repository {
  // This might be done using per-request or per-thread or whatever
  // is appropriate to your application.
  public static Repository Current { get { ... } }

  // Hypothetical division of labour between SQL and Oracle --
  // just an example.  You could also use IUnitOfWork instead of
  // a strong-typed unit of work.
  private BillingUnitOfWork _sqlUnitOfWork;
  private LabUnitOfWork _oracleUnitOfWork;

  public Customer FindCustomerById(int id) {
    return _sqlUnitOfWork.FindById<Customer>(id);
  }
  public IQueryable<LabRequest> LabRequests {
    get { return _oracleUnitOfWork.LabRequests; }
  }
}

Now the queries in the LabRequest and Customer class can use the repository, and don't need to know where their associated classes are physically stored:

class LabRequest {
  public Customer Customer {
    get { return Repository.Current.FindCustomerById(this.CustomerId); }
  }
}

The Repository class figures out that customers live in SQL Server, and directs the query appropriately, without the LabRequest class having to know anything about it.

Hope this makes sense now!

One stylistic detail.  I have shown the Repository class exposing an IQueryable so that you can write e.g. Repository.Current.LabRequests.Where(lr => lr.CustomerId == this.Id) in your Customer class.  Some people prefer not to expose IQueryable from the repository and instead write specific wrapper methods e.g. Repository.GetLabRequestsForCustomer(Customer customer).  You should choose whichever idiom you prefer.

Thanks again for your help. I've been scouring your forums & the internet all day trying to see if I can find some examples (this approach is some what new to me). I'll test this out and let you know how it goes.. thanks.

Ivan,

Thanks for the sample code above. Using the approach above I think it might be too tedious if I'm working working with 10+ data bases and there are hundreds of entity classes and I need to map each of them to their default db location in a Repository class.

Instead I was wondering if it would be possible to set a "default datastore" property for each class and have Lightspeed read this property when it attempts to retrieve the data or override and it pass the default schema information.

I know this is against the concept of decoupling the business layer from the data layer but I'm not sure if there's another way of doing this. Is this possible at all to override the Lightspeed method responsible for loading the data? Do you plan on having such a functionality in the future?

Thanks for all your support.

No, there's no way to set a default datastore for each class.  The data store is set at the LightSpeedContext level.  There are no plans to change this (it would involve massive changes to the way LightSpeed works, and there is not currently sufficient demand to justify the cost and risk).

The best I can suggest is that you might be able to genericise the repository approach by using attributes or something to map entities to locations, e.g.

[DataStore("Oracle")]  // custom attribute defined by you
partial class LabRequest { ... }

class Repository {
  private Dictionary<string, IUnitOfWork> _unitsOfWork = new Dictionary<string, IUnitOfWork>();
  // TODO: initialise dictionary with units of work using appropriate
  // LightSpeedContexts.
  public T FindById<T>(object id) {
    DataStoreAttribute ds = Utils.GetAttribute<DataStoreAttribute>(typeof(T));
    IUnitOfWork uow = _unitsOfWork[ds.StoreName];
    return uow.FindById<T>(id);
  }
}
static class Utils {
  internal static T GetAttribute<T>(Type t) {
    return (T)(t.GetCustomAttributes(typeof(T), true) [0]);
  }
}