Sevket Seyalioglu

From msdn magazine article (a bit old but valid) : http://msdn.microsoft.com/en-us/magazine/ff898427.aspx

After getting frustrated with the fact that Entity Framework and NHibernate does not resolve my needs, I am writing my own ORM tool to finalize one very important part of enterprise architecture.

Here are the features that are included (hope to finish by tomorrow)

1. Mutliple entity classes (one per each)
2. Single Data Provider Class
1. One Interface IDataProvider
2. One Class which inherits I[Model]DataProvider (SQL[Model]DataProvider)
1. [Model] to be renamed by the model name provided
3. One mocked class using moq framework (http://code.google.com/p/moq/)
3. SQL[Model]DataProvider will also implement retry logic in case of certain cases such as
1. Deadlock
2. Connection Timeout etc.
3. In a nutshell the following error codes : -1,-2,2,53,1222,1205
4. Will obviously generate related stored procedures and utilize them
5. Entities will be able to track simultaneous changes and will be able to throw exception if data changed before update!
6. Entities will implement [DataContract] & [DataMember] so that usage in WCF will be a lot easy
7. As you may see from here generated codes will implement Abstract Factory (Provider) design pattern and possibility of Singleton is also there.

After doing enough research on both, what we’ve end up doing is; not using any ORM tool.

ORMs are designed to do rapid development for RIA (Rich Internet Applications) where performance is not a big concern.
Yes, ORM can speed up your development such as EF 4.0 or NH but they all lack one very important thing which is support for Stored Procedures for general select which shall be by default. All ORMs are focused on eliminating that and trying to optimize queries without SP.

If you ask me, SP are most useful when you use for select in general because you can really optimize the execution plan and it will be cached on SQL server. So in all subsequent calls to the database with same routine, SQL server will always know the execution plan and it will save time. [SP's execution plans are cached on SQL server.]

Therefore not using ORM is my choice.

Here are what I lose by not using ORM

1. oData
2. Extra quick development
3. Default behavior of throwing exception on an update where data was already changed before the update (concurrency handling)

We are comparing to select the next robust data layer and almost in the end

Here are the requirements

1. Needs to be available as a service
1. Very important to segment out security to limited resources [PCI]
2. Very helpful when testing [helps in mocking also]
2. Needs to be easy to adopt
1. If possible build in minutes
3. Needs to be flexible in migrations/versions
4. Needs to work with existing stored procedures
5. Shall force developers write better code

I’ve always been big fan of using stored procedures due to the fact that you have a lot more control over your database and query plans to be cached but surprised for certain facts that EF can come very close to the level you want and in certain cases can perform much better [with data caching options of course].

Still huge fan of using stored procs to keep my DBA happy.

Want to know what others also think!

Checkout this for more details

A very simple implementation with 2 different databases (distributed transactions):

http://www.luisrocha.net/2011/08/managing-transactions-with-entity.html

using (TransactionScope scope = new TransactionScope())
{
    // Save changes but maintain context1 current state.
    context1.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Save changes but maintain context2 current state.
    context2.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Commit succeeded since we got here, then completes the transaction.
    scope.Complete();

    // Now it is safe to update context state.
    context1.AcceptAllChanges();
    context2.AcceptAllChanges();
}

A bit more complex looking but another effective way

http://social.msdn.microsoft.com/Forums/en/adodotnetdataservices/thread/e66651c0-9202-4049-a8f4-55971b8b359d

public class WcfDataService1 : DataService<WebApplication2.AdHocContainer7StorageEntities>, IDisposable
    {
        private TransactionScope transaction;
        private bool disposed;
        private bool error;

        public WcfDataService1()
        {
            this.ProcessingPipeline.ProcessingChangeset += new EventHandler<EventArgs>(ProcessingPipeline_ProcessingChangeset);
            this.ProcessingPipeline.ProcessedChangeset += new EventHandler<EventArgs>(ProcessingPipeline_ProcessedChangeset);
        }

        ~WcfDataService1()
        {
            Dispose(false);
        }

        public static void InitializeService(DataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
            config.UseVerboseErrors = true;
        }

        private void ProcessingPipeline_ProcessingChangeset(object sender, EventArgs args) 
        {
            transaction = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 30));
        }

        private void ProcessingPipeline_ProcessedChangeset(object sender, EventArgs args) 
        {
            if (!error)
            {
                transaction.Complete();
            }
            this.Dispose(); // will rollback the transaction if it has not been commited
        }

        protected override void  HandleException(HandleExceptionArgs args)
        {
            error = true;
 	        base.HandleException(args);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if(!disposed)
            {
                if(disposing)
                {
                    if (transaction != null)
                    {
                        transaction.Dispose();
                    }
                }

                disposed = true;
            }
        }
    }