Distributed transactions, EF 4 & the Large Objects table

Distributed transactions, EF 4 & the Large Objects table

Postby TonyV » Tue 01 Nov 2011 19:28

Working with a PostgreSql 9 database that has 2 tables that are related:

  • The Reads table is the parent table; its key column is a UUID called ReadId
  • The Images table is the child table & relates to the Reads table with a UUID column called ReadId
  • The Images table has a column called ImageOid of type int. This column contains the OID of a row in the Large Objects table that contains the bytes for a JPEG image.


The Large Objects table is not included in my entity model at this time.

My program uses C# code to write the image bytes to the Large Objects table before it creates the row in the Images table. According to the documentation, the large object table code needs to be inside of a local transaction. Everything has been working fine up until today.

Today, I discovered that I needed to create a TransactionScope object to encompass a number of operations that my code has to do. Here's some code to illustrate what's going on:

Code: Select all
using ( CarSystemEntities context = new CarSystemEntities() ) {
    try {
        TransactionScope transaction = new TransactionScope();

        foreach ( IDableObject idObjet in message ) {
           

            context.SaveChanges();
        }

        transaction.Complete();

    } catch ( Exception ex ) {
       
    }
}


The "process object here" involves determining what type the object is and then calling the proper method in a DataAccessor class specific to that type that has code like the below in it:

Code: Select all
public static void SaveDomain( CarSystemEntities context, Properties.Domain data ) {
    if ( context == null )
        // We do not.  Throw an error
        throw new ArgumentNullException( "context", "You must pass a non-null CarSystemEntities instance." );

    if ( data == null )
        throw new ArgumentNullException( "data", "You must pass a non-null Domain instance." );

    try {
        CarSystem.Domain domain = null;

        if ( data.ID == Guid.Empty ) {
            // We do not.  Create one now
            data.ID = Guid.NewGuid();
        }

        if ( !context.Domains.Any( d => d.DomainGuid == data.ID && d.IActive == 1 ) ) {
            domain = new CarSystem.Domain {
                CreatedDate = DateTime.Now,
                DomainGuid  = data.ID,
                DomainId    = data.DomainId,
                DomainName  = data.Name,
                IActive     = 1
            };
           
            context.Domains.AddObject( domain );
        } else {
            domain = QueryDomains( context).Where( d => d.DomainGuid == data.ID ).Single();

            bool changed = false;
            if ( domain.DomainGuid != data.ID )       { changed = true; domain.DomainGuid = data.ID; }
            if ( domain.DomainId   != data.DomainId ) { changed = true; domain.DomainId   = data.DomainId; }
            if ( domain.DomainName != data.Name )     { changed = true; domain.DomainName = data.Name; }

            if ( changed )
                domain.ModifyDate = DateTime.Now;
        }

    } catch ( Exception ex ) {
        throw new DataAccessException( DataAccessOperation.SaveDomain, FailureReason.DatabaseError, ex );
    }
}


Here's the method that writes the image's bytes to the Large objects table:

Code: Select all
public static int PutImageBytes( byte[] image ) {
    try {
        using ( PgSqlConnection conn = new PgSqlConnection( ConfigurationManager.ConnectionStrings[ "PgSqlServices" ].ConnectionString ) ) {
            conn.Open();
            PgSqlLargeObject  lo = new PgSqlLargeObject( conn );
            PgSqlTransaction trans = conn.BeginTransaction();
            lo.Create();
            lo.Open();
            lo.Write( image, 0, image.Length );
            lo.Close();
            trans.Commit();
            return lo.Oid;
        }
    } catch ( Exception ex ) {
        throw new DataAccessException( DataAccessOperation.PutImageBytes, FailureReason.DatabaseError, ex );
    }
}


When I didn't have the TransactionScope, all of the code worked fine. But now that I added the TranactionScope, I get an error message that says, "Local transaction can not be started while in a distributed transaction."

How do I correct this code to work in this configuraton?

Tony
TonyV
 
Posts: 74
Joined: Wed 25 May 2011 15:03

Found a better way for this application

Postby TonyV » Wed 02 Nov 2011 18:35

I've found a better way to deal with the issue for my application. This post can be marked "Resolved."

What I'm doing is taking advantage of the Connection property of the object model context. Here's a few lines to illustrate:

Code: Select all
using ( CarSystemEntities context = new CarSystemEntities() ) {
    if ( context.Connection.State != ConnectionState.Open ) {
        context.Connection.Open();
    }

    DbTransaction transaction = context.Connection.BeginTransaction();
   
    try {
       

        transaction.Commit();

    } catch ( Exception ex ) {
        transaction.Rollback();

       
    }            
}


Combined with calling SaveChanges() as needed after inserting & updating rows, everything works exactly as I need it to work. That is:

  • Each row is inserted into the database as each object is processed, so the .Any calls return true if a row with a given ID has already been inserted into the table.
  • All operations are performed in one transaction that gets rolled back as a unit if anything goes wrong while processing one record.


Tony
TonyV
 
Posts: 74
Joined: Wed 25 May 2011 15:03


Return to dotConnect for PostgreSQL