External Data Access Best Practice

Dec 14, 2010 at 1:28 PM

Is there a recomended best practise for accessing other DB's inside a Orchard module / widget?

e.g. If i have a existing DB with products, what is the best way to access this...

1, Create a sepreate service project and releated data access, add a reference to the project and create a new instance of the service?

2, Some how use the existing Orchard data access framework to map the tables and create a service?

 

Also do modules / widgets have any hooks to add external references to the IOC container?

Developer
Dec 15, 2010 at 6:56 PM

Hi,

Had the same need a while ago, so maybe I can help. I think the best solution is based on 1. Trying to manually map (somehow) existing data to Orchard is very painful and can easily lead to unexpected errors/site corruption. Besides, at this moment a performance can also be the issue...

My solution:

1. Create your DAL as a separate project.

2. Create custom module and reference your DAL dll in it (or keep it in the same solution and just add project reference).

3. In your module create a service (interface + implementation) that talks to your DAL. Add this service to the IoC container. You asked how to add something to the IoC container? It is pretty simple - all you need is to decorate your service interface with an appropriate interface: IDependency, ISingletonDependency, IUnitOfWorkDependency (depending of the service lifetime you want). That's all. Now, you can pass your IService to eg. controller, and Orchard will inject your concrete implementation.

Cheers, Piotr

Dec 15, 2010 at 8:42 PM

There might be a posibility to use another repository with a second connection for NHibernate.

Or something else, if you like a business layer, you might use Csla businessobjects http://www.lhotka.net to write your business and handle data logic. In that case the Orchard.Data won't be needed for.

Dec 15, 2010 at 9:13 PM

I created my own DAL layer by copying the orchard code around data access and replaced the orchard specific parts with the standard FluentNHibernate mappings to my DB.

I actually originally did this for another project independent of orchard as I liked the way it was implemented. This worked well. But once I tried to combine this with orchard I got errors about nested transactions. I'm not really a NHibernate guru, any ideas on how to correctly nest the transactions? I would have thought that with 2 independent NHibernate sessions and connections this would work...

Coordinator
Dec 15, 2010 at 10:06 PM

If it helps, Orchard’s transaction is managed by the Orchard.Data.TransactionManager

There is a per-request System.Transactions.TransactionScope that is created around the nhibernate session.

_scope = new TransactionScope(TransactionScopeOption.Required);

And later it is marked complete and disposed to flush all changes

void IDisposable.Dispose() {

if (_scope != null) {

if (!_cancelled) {

Logger.Debug("Marking transaction as complete");

_scope.Complete();

}

Logger.Debug("Final work for transaction being performed");

_scope.Dispose();

Logger.Debug("Transaction disposed");

}

}

The system.transactions have thread affinity, and when an nhibernate session is created if it sees this ambient transaction it will enlist in it. As the transaction is being commit nhibernate will flush all of its changes.

That said – I would have assumed if you have two session factories creating sessions they would both enlist in the same ambient transaction and it would just work.

Maybe your session is being created before the TransactionManager creates the ambient transaction scope? Could you try having ITransactionManager dependency injected, and call Demand() before you create your ISession if that’s the case?

If you are also creating a transaction for your session explicitly, I’d suggest letting it join the ambient one if possible.

Dec 16, 2010 at 10:04 AM

Since the DAL I have created uses a copy paste of the orchard code:

        public ISession For(Type entityType) {
            Logger.Debug("Acquiring session for {0}", entityType);

            if (_session == null) {

                var sessionFactory = _sessionFactoryHolder.GetSessionFactory();

                _transactionManager.Demand();

                Logger.Information("Openning database session");
                _session = sessionFactory.OpenSession(new SessionInterceptor(this));
            }
            return _session;
        }

 

There is a Demand() call right before the ISession is created, with the debugger i have come up with the series of events:

1, Orchard creates a ISession

2, My service creates a ISession

3, My service successfully reads from the DB

4, "TransactionException: The operation is not valid for the state of the transaction" thrown when orchard tries to execute a query.

5, My service Dispose() calls TransactionScope Complete()

6, Orchard Dispose() calls TransactionScope Complete()

Note: that there is also a publish later service running in a seprate thread but this should not matter.

 

Coordinator
Dec 17, 2010 at 12:47 AM

I’m curious about step 5 and 6…

5, My service Dispose() calls TransactionScope Complete()

6, Orchard Dispose() calls TransactionScope Complete()

Do you have a copy of the transaction manager, or is there a second transaction scope being managed by your code?

From: jrmurdoch [email removed]
Sent: Thursday, December 16, 2010 3:04 AM
To: Louis DeJardin
Subject: Re: External Data Access Best Practice [orchard:238319]

From: jrmurdoch

Since the DAL I have created uses a copy paste of the orchard code:

        public ISession For(Type entityType) {
            Logger.Debug("Acquiring session for {0}", entityType);
 
            if (_session == null) {
 
                var sessionFactory = _sessionFactoryHolder.GetSessionFactory();
 
                _transactionManager.Demand();
 
                Logger.Information("Openning database session");
                _session = sessionFactory.OpenSession(new SessionInterceptor(this));
            }
            return _session;
        }

There is a Demand() call right before the ISession is created, with the debugger i have come up with the series of events:

1, Orchard creates a ISession

2, My service creates a ISession

3, My service successfully reads from the DB

4, "TransactionException: The operation is not valid for the state of the transaction" thrown when orchard tries to execute a query.

5, My service Dispose() calls TransactionScope Complete()

6, Orchard Dispose() calls TransactionScope Complete()

Note: that there is also a publish later service running in a seprate thread but this should not matter.

Dec 17, 2010 at 1:06 PM

Hi,

I'm not really sure I understand your question, their are 2 transaction managers, 1 from my DAL and one from Orchard. Their is 2 of everthing,

This is a basic run down of what I have created:

I create my service in a seprate project as follows:

 

    public class SecurityService : ISecurityService {
        private readonly IContainer _container;
        private readonly IRepository<Security> _securityRepository;

        public SecurityService() {
            var builder = new ContainerBuilder();
            builder.RegisterFramework();
            builder.RegisterFASystemsMapping();
            _container = builder.Build();

            _securityRepository = _container.Resolve<IRepository<Security>>();
        }

RegisterFramwork() adds the DAL that exists in a seprate project

        public static void RegisterFramework(this ContainerBuilder builder)
        {
            var assembly = Assembly.GetExecutingAssembly();
            builder.RegisterAssemblyTypes(assembly).Where(t => typeof(IDependency).IsAssignableFrom(t)).AsImplementedInterfaces().InstancePerLifetimeScope();
            builder.RegisterAssemblyTypes(assembly).Where(t => typeof(ISingletonDependency).IsAssignableFrom(t)).AsImplementedInterfaces().SingleInstance();
            builder.RegisterAssemblyTypes(assembly).Where(t => typeof(ITransientDependency).IsAssignableFrom(t)).AsImplementedInterfaces().InstancePerDependency();

            builder.RegisterModule(new DataModule());
            builder.RegisterModule(new LoggingModule());
        }

 

So that orchard will inject my service I create a new class in the orchard module that inherits from my serivce and IDependency, this injects it into the controller.

 

 

May 9, 2011 at 7:40 AM

Hi

I have the same problem trying to read data from my own database and I think I know what happen even if I couldnt resolve it.

If you have 2 copies of the transaction manager, you will end with 2 different transaction scopes (one for your session and another one for orchard session).

Then, the steps will be:

1- Orchar open a new transaction scope (calling Demand()). This will be the scope 1.

2- Your SessionLocator open another transaction scope. This will be scope 2.

3- Orchard transaction manager enter to dispose and try to close the scope 1. Scope 2 was not closed yet => nested transactions error.

As loudej said having both sessions inside the same transaction scope should work.

But, and here is my problem, even if I am not replicating the TransactionManager, I am getting different instances of it inside my SessionLocator and Orchard SessionLocator (I have my own SessionFactoryLocator and SessionLocator to hold my session and then I am reusing Respository and TransactionManager).

In theory I should have just one instance per request because ITransaction extends IDependency.

Anybody know how to resolve this? 

May 9, 2011 at 10:44 AM

"In theory I should have just one instance per request because ITransaction extends IDependency."

I don't think that's true; I've seen situations where dependencies were instanced many times per request. So either there's a bug, or "LifetimeScope" does not necessarily mean "Request".

Developer
May 9, 2011 at 11:03 AM

Try injecting Work<T>, eg. Work<ITransactionManager> instead of a pure object. It should ensure that the instance inside is coming from the current work context. Btw - you can use this technique even to inject context-bound dependencies (like Repository<>) into singleton ones.

- Piotr

May 9, 2011 at 11:03 AM

I could not get this to work with both connections running in the same transaction (MSDTC)

What i have to do and its not ideal is:

- Have 2 transaction managers, one for each connection/session

I had to make the following change to TransactionManager.cs

From:

 

        void ITransactionManager.Demand() {
            if (_scope == null)
            {
                Logger.Debug("Creating transaction on Demand");
                _scope = new TransactionScope(TransactionScopeOption.Required);
            }
        }

 

To:

        void ITransactionManager.Demand(ISession session)
        {
            _session = session;
            if (_transaction == null && _session != null)
            {
                Logger.Debug("Creating transaction on Demand");
                _transaction = _session.BeginTransaction();
            }
        }

Hence the removal of MSDTC

I also had to make a few other minor changes, to where mogrations are done to work around the change in transactions work, but these are minor.

Hope this helps, if anyone else has a better fix please let me know....

 

 

 

 

May 10, 2011 at 12:51 AM

Hi,

Thanks for your quick answer.

Well, both sessions running in the same transaction work for me but I still have problems trying to use the Work<T>

I changed my SessionLocator like this:

 

public MySessionLocator(ISessionFactoryHolder sessionFactoryHolder, Work<ITransactionManager> transactionManagerWork) {
	_transactionManager = transactionManagerWork.Value;
	...
}

 

but transactionManagerWork.Value is allways null.

This is the right way to do it?

Any idea?


Jan 3, 2012 at 9:23 PM

I have an external database that already has a DAL written in Entity Framework 4 Code First.  If I was to implement the service method in a module as mentioned above, would this help me get around the transaction scope issue?  Is there a problem mixing, EF and NHibernate?

Thanks!

Coordinator
Jan 3, 2012 at 10:58 PM

EF and nHib should work ok if you opt out of the nHib transaction while working with EF.

Jan 4, 2012 at 3:44 PM

@bertrandleroy In the module that we are building now, we've attempted to call our EF Code First data layer with the TransactionScopeOption.Suppress option as you had suggested on a StackOverflow post.

We have found that the "main entity" query seems to return data but any lazy-loaded navigation properties on that entity cause TransactionScope errors.  This happens when we've retrieved an entity from within the TransactionScope in the controller and then passed the entity on to our view.  The view causes the lazy-loaded properties to requery outside of the transaction scope.  We did avoid this by using .Include() methods to eager load the navigation properties. 

We were able to use the .Include() methods because we are currently querying the EF context directly but we plan on moving the calls to a Service layer and possibly a Repository implementation.  This would "force" us to know all of the properties that we need to include for every situation which is less than ideal and I'd like to avoid eager loading everything for performance reasons.

Does a service layer help avoid these transaction scope issues if the "service" is something that runs in the same process as the module or do we have to create a "over-the-wire" type of service like WCF, REST, etc to completely remove the queries from the Orchard TransactionScope?

Thanks,
Brian

Jan 5, 2012 at 12:45 PM
Edited Jan 5, 2012 at 12:47 PM

If it is acceptable for your case, you can opt out of transactions inside the views to work around the lazy loading issue. I posted some code about how I did this a week or two ago. It's not ideal but it might be better overall than eager loading manually coding all the stuff each view will need to access. 

Edit: here is the post

Coordinator
Jan 5, 2012 at 7:42 PM

OK, so here's my opinion, so obligatory grain of salt.

We are building web applications, not desktop. The unit of work is the request, yes, but data access should only be stretched across it if that makes sense, and if the different requests in the transaction are not tightly related. Lazy loading in my opinion and in this context is rarely beneficial and mostly detrimental. Sébastien actually made some changes recently to increase eagerness of the application, with great success. It's cheaper to bring a few more joins in up front than to wait until you need the data to query it. At least in this context.

In this case, what I would recommend is to apply the old principle to connect late, close early, which in modern parlance would be to get the data you know you'll need, all of it, as fast as possible and then get the hell out of there equally fast. If it takes having overloads and overrides in your service classes that take specific sets of parameter and return a specifc data set, then fine. If you need the laziness, which is rarely the case, there are other ways to implement it. The idea is to constrain your database interaction to those code blocks where you suppressed the transaction. Any object that gets out of there should be free of ties to the database.

My 2 cents.