How to implement a DataAccess class that abstracts away LightSpeed?

 Hi, I am having some problems with the following - I am trying to make a typed Data Access Layer.  So when I call CustomerDataAcces<Customer>.GetAll() it gets all the customers in the database.

I have an interface that implements the 4 simple crud operations, and a few extra things.  This is what it looks like:

    public interface IDataAccess<T> : IDisposable
    {
        string ConnectionString { get; set; }
        IEnumerable<T> GetAll();
        IQueryable<T> QueryableGetAll();
        T GetByPrimaryKey(object primaryKey);

        void Delete(IEnumerable<T> data);
        void Update(IEnumerable<T> data);
        void Add(IEnumerable<T> data);

        void Delete(T obj);
        void Update(T obj);
        void Add(T obj);
    }

The problem I am having is how to actually implement this using LightSpeed.  For example, this is a problemn because the unitOfWork is disposed at the end of the using statement:

        public IQueryable<T1> QueryableGetAll()
        {
            using (IUnitOfWork unitOfWork = context.CreateUnitOfWork())
                return unitOfWork.Query<T1>();
        }

 That will throw an error when you actually try to iterate over the results, since unitOfWork has been disposed already.

So how do I actually do this?

What you need, I think, is something that encapsulates the unit of work in a way that keeps it around for long enough but no longer -- in other words, a lifetime strategy for the UOW.  We provide two of these out of the box, PerThreadUnitOfWorkScope, which keeps the UOW around for the lifetime of the thread, and PerRequestUnitOfWorkScope, which keeps the UOW around for the lifetime of one HTTP request and is appropriate for Web applications.  (Each of these automatically creates a UOW when one is needed.)  You can of course create your own either ad hoc or by deriving from UnitOfWorkScopeBase.

Your implementation can then look something like:

public IQueryable<T> QueryableGetAll() {
  return _unitOfWorkScope.Current.Query<T>();
}

If you can say a bit more about your usage context then we may be able to offer more specific advice.

 Hi, this is for a WinForms application that interacts with many tables in multiple databases on multiple servers.  Actually here is the entire class as it is currently implemented:

*Couple of notes:  The ConnectionManager is a singleton class that holds the info on what server you are currently connected to.  We have the exact same databases on multiple servers, and the user can select which server they want to connect to from a drop-down box.  When they select a different server, it fires an event in the ConnectionManager class.  When your connection changes, a new Context is created using the new connection string.

 Also, the "CleanLightSpeedEntityStringProperties" simply changes string values from null to string.Empty

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using Mindscape.LightSpeed;
using Mindscape.LightSpeed.Linq;
using Mindscape.LightSpeed.Logging;
using Ninject.Core;
using SpinMaster.Library;
using SpinMaster.Library.DataAccess;

namespace SpinMaster.LightSpeed
{   
    public class LightSpeedDataAccess<T1, T2> : IDataAccess<T1> where T1 : Mindscape.LightSpeed.Entity<T2>
    {
        protected Mindscape.LightSpeed.LightSpeedContext context;
        protected Mindscape.LightSpeed.IUnitOfWork unitOfWork;
        string connectionString;

        [Inject]
        public LightSpeedDataAccess(ConnectionManager connectionManager)
        {
            ConnectionString = connectionManager.ConnectionString;
            connectionManager.ConnectionStringChanged += delegate
            {
                ConnectionString = connectionManager.ConnectionString;
            };
        }

        public LightSpeedDataAccess(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public IdentityMethod IdentityMethod
        {
            set { context.IdentityMethod = value; }
        }




        #region IDataAccess<T1> Members

        public string ConnectionString
        {
            get { return connectionString; }
            set
            {
                connectionString = value;
                context = new Mindscape.LightSpeed.LightSpeedContext
                {
                    ConnectionString = connectionString,
                    DataProvider = Mindscape.LightSpeed.DataProvider.MySql5,
                    Logger = new ConsoleLogger(),
                    IdentityMethod = Mindscape.LightSpeed.IdentityMethod.IdentityColumn
                };

                unitOfWork = context.CreateUnitOfWork();

                Console.WriteLine(string.Format("Connection changed to {0} in LightSpeedDataAccess<{1}>",
                    connectionString, typeof(T1).Name));
            }
        }

        public IEnumerable<T1> GetAll()
        {
            return unitOfWork.Query<T1>();
        }

        public IQueryable<T1> QueryableGetAll()
        {
            return unitOfWork.Query<T1>();
        }

        public T1 GetByPrimaryKey(object primaryKey)
        {                                     
            return unitOfWork.FindOne<T1>(primaryKey);
        }

        public void Delete(IEnumerable<T1> data)
        {
            foreach (T1 sp in data)
            {
                unitOfWork.Remove(sp);
            }

            unitOfWork.SaveChanges();
        }

        public void Update(IEnumerable<T1> data)
        {
            CleanLightSpeedEntityStringProperties(data);

            try
            {
                unitOfWork.SaveChanges();
            }
            catch (System.InvalidOperationException e)
            {
                Console.WriteLine(e.Message);
            }
            catch (Mindscape.LightSpeed.Validation.ValidationException e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public void Add(IEnumerable<T1> data)
        {
            CleanLightSpeedEntityStringProperties(data);

            foreach (T1 o in data)
            {
                unitOfWork.Add(o);
            }

            try
            {
                unitOfWork.SaveChanges();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        public void Delete(T1 obj)
        {
            unitOfWork.Remove(obj);
            unitOfWork.SaveChanges();
        }

        public void Update(T1 obj)
        {
            CleanLightSpeedEntityStringProperties(new[] { obj });
            unitOfWork.SaveChanges();
        }

        public void Add(T1 obj)
        {
            CleanLightSpeedEntityStringProperties(new[] { obj });
            unitOfWork.Add(obj);

            try
            {
                unitOfWork.SaveChanges();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }
 
        #endregion




        private static void CleanLightSpeedEntityStringProperties(IEnumerable<T1> data)
        {
            Type t = typeof(T1);
            List<PropertyInfo> pi = LightSpeedUtility.LightSpeedEntityPropertyCleaner(t);

            foreach (var o in data)
            {
                foreach (PropertyInfo property in pi)
                {
                    if (property.PropertyType == typeof(string))
                        if (property.GetValue(o, null) == null)
                            property.SetValue(o, string.Empty, null);
                }
            }
        }




        #region IDisposable Members

        public void Dispose()
        {
            if (unitOfWork != null)
                unitOfWork.Dispose();
        }

        #endregion
    }
}

What you have there should work fine from the point of view of keeping the UOW alive, but I had a couple of observations:

* As currently written, the unit of work will last for the lifetime of the application (well, until the user changes the connection string or you dispose the data access object).  This is fine as long as users are not going to need to refresh their data during the program run -- for example, if User A displays Record 1 in a dialog, then closes the dialog, then User B edits Record 1, and then User A re-opens the Record 1 dialog, User A will see their original values: the entity will not be reloaded.  To avoid this you would need to use a short-lived unit of work, which brings you back to your original problem.  Let us know if you need advice on this scenario.

* Because you will have different data access instances for different entity types, you may run into confusion because you will have multiple UOWs around.  For example, suppose you have Person and Company classes, and therefore DataAccess<Person> and DataAccess<Company> instances.  Things loaded through the different DA instances will be in different UOWs.  For example, if you load a Person directly (daPerson.GetByPrimaryKey(...)), they will be in daPerson's UOW, but if you load them via a Company and an association (daCompany.GetByPrimaryKey(...).BigBoss) then they will be in daCompany's UOW.  Thus they will be different object instances, will not compare as identical, changes to one will not affect the other, there is the potential for contending updates, etc.  This probably won't be a problem if you always start from the same place (e.g. the "home screen" displays companies and everything else is reached via associations), but even then there's a potential maintenance risk.  You may therefore want to consider sharing a UOW across the different data access classes, though this requires further care if users can have connections to different DBs at the same time.

Finally, a minor style suggestion: in your ConnectionString setter you might want to check whether there is an existing unit of work and if so dispose it.

Hope this helps!

 Ok, I've made it so that it is one UnitOfWork for the lifetime of the user's application.

However, just as you said, if anyone else makes a change to the data in the database, those changes won't show up even when the user refreshes the data and hits the database again!!

This is a HORRIBLE design decision.  Why is the data from the database cached permanently in a way that does not allow easy refreshing??

 The whole UnitOfWork concept is designed so poorly here that's it's caused obscure problems for the last 4 months.

If I use a short-lived UnitOfWork then I can't take ANY advantage of Linq's Queryable<T> interface (since it's evaluated in a lazy manner, and the UnitOfWork will have been disposed of by the time the Queryable<T> is evaluated.

 If I use a global UnitOfWork then I have the problem of user's not being able to refresh data from the database.

 If I use a 1 UnitOfWork per table in the database, I have the wonderfully obscure problem of "Nested transactions are not supported" any time I try to update or add an item into the table!!

Very frustrated right now...

I'm sorry you're finding the UOW design frustrating, and we'd like to work with you to help you resolve the issues you're running into.  We may need to discuss the specifics of the application in order to fully resolve the problems, but I think this is down to trying to identify where the boundaries are -- what are the "units of work" (in the business sense) of the application.

For example, suppose your application contains a "List Orders" screen and an "Edit / Create Order" dialog.  Rather than trying to retain a single UOW across the lifetime of the List Orders screen and all instances of the Edit screen, you could keep the UOWs shorter using a strategy such as:

* Create a UOW to load and populate the List screen.

* If the user hits refresh on the List screen, create another UOW to repopulate it.

* Create a UOW for the duration of each Edit dialog -- i.e. create it on screen load, and dispose it when the user OKs or Cancels the screen.

Now I think your original design was trying to go this kind of route but you were foiled by LINQ deferred execution.  One possible way out of that is of course to never defer -- call ToList() on the query before disposing the UOW.  This could be hidden inside the repository.  You do need to be cautious in this case to eager-load associations where they will be required, or to instantiate a new UOW for loading associated entities when lazy.  For example, if you have a Customers screen, and the user can navigate from a Customer to their Orders, you would want to create a new UOW to load the Orders.  Again the normal pattern is to hide this within the repository, e.g. via a GetOrdersByCustomerId method.

Another possible approach is to put lifecycle control in the hands of the application code while still retaining your desired abstraction away from the unit of work.  Create a class along the lines of:

class ScopedQueryable<T> : IQueryable<T>, IDisposable {
  private IQueryable<T> _query;
  private IDisposable _disposable;

  public UowQueryable(IQueryable<T> query, IDisposable disposable { ... }

  // forward all IQueryable methods to _query
  // forward Dispose method to _disposable
}

Then have your repository methods return instances of this:

public ScopedQueryable<Order> GetAllOrders() {
  IUnitOfWork uow = _context.CreateUnitOfWork();
  IQueryable<Order> query = uow.Query<T>();
  return new ScopedQueryable(query, uow);
}

(You can obviously wrap this up into a generic method rather than repeating it for every type if required.)

The calling code can now enumerate the LINQ query in its own good time, then dispose the UOW when it has finished doing so:

using (ScopedQueryable<Order> orders = Repository.GetAllOrders())
{
  valuableOrdersList.ItemsSource = orders.Where(o => o.Value > 100).ToList();
}

In this way you can have short-lived UOWs and have a measure of abstraction from them in your application code while still keeping them alive long enough to avoid the LINQ issue.  Note that again some caution is required around associations -- if the application code disposes the ScopedQueryable after it has finished enumerating customers, it still needs to be careful not to traverse the Orders association directly but instead go back to the repository for a new ScopedQueryable.

Regarding your comment about wanting to be able to refresh data in a live unit of work, we have had several requests for this recently and we are thinking hard about it, but there are some design issues we need to consider e.g. associations.  In the meantime I hope the above suggestions reduce the need for this feature.

 Hi, thank you very much for the response, and I really do appreciate your help.  And overall I am 95% happy with LightSpeed as a whole.  You have the best entity editor/modeler, it is incredibly easy to set up relationships between entities, and your databinding abilities are infinitely better than any other ORM I've come across.

I've build my code to utilize the DAO pattern, in a generic manner (as above).  However, after doing research today I realized it's generally difficult to map an ORM that uses the concept of unit's of work to a DAO.

My application is a Winforms application, and most of the editing for each table is done in a DataGridView.  When the user clicks the "Save" button, all the changes they made are saved to the database (which means I grab the list of all items that were edited and call the IDataAccess<T>.Update(IEnumerable<T> updated) and then grab the list of all items that are new and call IDataAccess<T>.Add(IEnumerable<T> new).

If the user hits the refresh button, I would like to ignore their changes and simply refresh the data from the database using the IDataAccess<T>.QueryableGetAll() method that I have.

If the user deletes a row from the DataGridView, it is immediately deleted from the database.

Please keep in mind that my GUI has NO idea about the LightSpeedDataAccess<T> that I have written.  It simply uses the generic interface at all times.  The LightSpeedDataAccess<T> is injected into the GUI via dependency injection (Ninject).  So in my GUI code, there is no notion of a UnitOfWork or a UnitOfWorkProvider or anything else LightSpeed dependent (other than the entities themselves).

 If there was simply support for having 1 UnitOfWork for the lifetime of the application this would work perfectly (as long as the connection is not held open the entire time).  It would be ideal if this were the case, because that is really the most simple and easiest model for a WinForms application.

In order for that to work, you would have to create a new kind of Update function.

 The UnitOfWork interface has the following atomic operations:

Query<T1>()

FindOne<T1>(primaryKey)

Remove(entity)

Add(entity)

There is no Update(entity) function though!

If you had a version of UnitOfWork that was truly atomic, meaning you could call Add(entity)/Remove(entity)/Update(entity) and that entity was saved/removed/added immediately to the database (without having to call unitOfWork.SaveChanges() afterwards) then I think this would be the easiest to use framework on the market, bar none (as long as you keep Linq support).

The other alternative is, that I would have to rewrite my application to use the unitOfWork concept instead of the DAO concept.  I don't know how to do this, do you know of any good tutorials on what the uow pattern is like in general?  (Keep in mind I still want to abstract away any notion of how LightSpeed interacts with the database in my GUI).

Also, I forgot to mention, is there any way you could create functions in the UnitOfWork class that lets us close and open connections whenever we want without having to dispose of the unitOfWork instance?

This way we can keep a UOW alive long-term without it taking up a slot in the connection pool?

Regarding the Update(entity) function, this is being discussed -- we recognise that it is essential for many "long-running unit of work" scenarios, and that such scenarios are likely to arise in a rich client environment which does not impose the clear request boundaries of a Web application.

It sounds like the appropriate pattern for your application may be something like "unit of work per screen" -- you create a new UOW when the user loads a new screen, and dispose it when the user leaves that screen or when they press Refresh (disposing the UOW will throw away all pending changes).  (In fact it sounds like you're manually loading changes into the UOW when the user presses Save.  Is that correct?  Is this because you want to keep the list of deltas at the application level rather than in the data access layer?  If so you may want to rethink this design because one of the jobs of the UOW in the "unit of work" style is to track that stuff for you.)

Immediate delete can be handled within your repository / data access class by calling UOW.SaveChanges immediately after UOW.Remove.  This will not interfere with your ability to dispose the UOW if the user hits refresh -- the deletes are already committed.

How to abstract a "UOW per screen" kind of pattern away from the application?  This is a bit of a tricky one.  It may depend on your application exposing or implementing *some* idea of "UOW boundaries" (or "screen boundaries," or "business transaction boundaries").  A bit vague I know, and not ideal in that it requires application logic, but you may be able to do something with it (and we would be happy to discuss).

Ultimately I think you have hit the nail on the head when you identify the "UOW style" and the "DAO style."  These do seem to be two distinctly different styles and it may be a bit of a battle to completely abstract away the UOW style, with its emphasis on well-bounded business transactions, compared to the DAO style, with its more atomic "objects as conduits to the database" approach.  In particular the unit of work is almost a business-logic-level concept (because it refers to the set of objects participating in a business-logic-level activity) and therefore the UOW lifecycle may be hard to pull down into the data access layer -- it is inherently a higher level concept.

I can't help you with tutorials I'm afarid though hopefully Google will be able to turn something up.  If you can get hold of a copy of Fowler's "Patterns of Enterprise Application Architecture" then I would strongly recommend having a look at that (there are fragments on his Web site but they don't go into detail).

Finally, we are hoping to provide much better guidance around this stuff, including WPF/WinForms samples, in LightSpeed 3.0 -- we know a lot of our existing samples are targeted to Web apps and the rich client is a very different environment.  We'll aim to share these over the next few weeks and months.  In the meantime, I hope this gives you some ideas and useful pointers, and please feel free to follow up on any design issues or ideas you want to bounce around -- we are always happy to help!

Regarding managing the connection lifecycle, we will consider this, though for the time being our recommendation is to try to shorten the life of the UOW -- but we realise this may not be feasible in your case.

 Hi, thank you for the responses.

The only thing "expensive" that a UnitOfWork keeps is a Connection that is open all the time correct?

So if we had a way to control when the connection is closed and when it is opened, could we then have as many UOWs as we want without running out of available connections in the connection pool?  That would solve that problem...

And the only problem remaining would be a way of refreshing the data on the same UOW.

If you added those 2 things, it would be perfect :)

Yes, the connection is the only "expensive" resource that the UnitOfWork holds.  But wouldn't controlling the opening and closing the connection defeat the point of abstracting away the data access layer?  Unless LightSpeed internally figured out when to open and close the connection... but this gets very complex because we need to consider things like transactions in progress (see discussion at http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=1570&PostID=3309 for more detailed discussion, though this is in a Web context and a lot of it doesn't apply in your environment).

We'll keep you informed about the "refresh" feature -- we're definitely hearing the requests for this one!

 My data access layer would control when the connection is opened and closed, namely:

Open connection, retrieve record(s), close connection.

Open connection, update record(s), close connection.

Open connection, delete record(s), close connection.

Open connection, insert new record(s), close connection.

Those are the only 4 operations I have in my DAL anyways :)  And it's generic so I only have to write it once, as opposed to having to write one separately for each class.  I can then leverage the power of LINQ to perform custom querying (i.e. get all customers with username "Bob" is very easy to do in LINQ, and LightSpeed offers excellent support for LINQ).

Isn't there a danger that this will just bring us back to where we were with the problem with deferred LINQ queries in the "retrieve records" case?  I.e. the DAL open the connection, returns the IQueryable (which doesn't actually perform the query), and closes the connection.  The app code then starts to enumerate the IQueryable, but now the connection is closed.

I'm not trying to make difficulties here, just wanting to get it straight in my own mind whether it will actually solve your problem!

 Good point, I hadn't thought of that ...

I am reading up on the UnitOfWork pattern right now, trying to wrap my mind around how to integrate it into my code.