none
Entity Framework - CommitFailureHandler.ClearTransactionHistory() RRS feed

  • Question

  • I have an issue that I'm running into regarding the commitFailureHandler.ClearTransactionHistory(); method in Entity Framework 6.1.

    I used the following tutorials on how to deal with network issues when committing records to the database:
    http://visualstudiomagazine.com/articles/2014/08/01/managing-transactions.aspx?m=1
    https://entityframework.codeplex.com/wikipage?title=Handling%20of%20Transaction%20Commit%20Failures%20
    http://msdn.microsoft.com/en-us/data/dn456835.aspx
    http://msdn.microsoft.com/en-us/data/jj680699

    When I run this for the very first time, I get an exception that states dbo.EFTransactionHistory is invalid.

    InnerException: System.Data.SqlClient.SqlException
    HResult=-2146232060
    Message=Invalid object name 'dbo.EFTransactionHistory'.
    Source=.Net SqlClient Data Provider
    ErrorCode=-2146232060
    Class=16
    LineNumber=1
    Number=208
    Procedure=""
    Server=********
    State=1

    I checked in the database & of course that table does not exist. Of course we should be executing this method every time the application starts up. In this case, it is a WCF service & I made a custom Service Initializer so whenever the mex is initiated or the very first service call comes in it initializes some resources that the service will need.

    Here is what I have in my web.config for my service:

    <entityFramework codeConfigurationType="WSI.Common.DataAccess.Configuration.EntityFramework.TransactionDbConfiguration, WSI.Common.DataAccess">

    Here I extend the DbConfiguration class with the following code:

    public class TransactionDbConfiguration : DbConfiguration
         {
         public TransactionDbConfiguration()
             {
             SetTransactionHandler(SqlProviderServices.ProviderInvariantName, () => new CommitFailureHandler(c => new DbTransactionContext(c)));
             SetExecutionStrategy(SqlProviderServices.ProviderInvariantName, () => new SqlAzureExecutionStrategy());
             }
         }

    Next is my code that I extend the TransactionContext class with:

    public class DbTransactionContext : TransactionContext
            {
            public DbTransactionContext(DbConnection dbConnection) : base(dbConnection)
                {

                }

       protected override void OnModelCreating(DbModelBuilder modelBuilder)
           {
           modelBuilder.Entity<TransactionRow>().ToTable("EFTransactionHistory");
           }
       }

    Then in my ServiceInitializer class, I call a static method by passing an instance of the DbContext like so:

    protected static void Initialize(DbContext dbContext)
        {
        if (dbContext != null)
            {
            using (CommitFailureHandler commitFailureHandler = CommitFailureHandler.FromContext(dbContext))
                {
                if (commitFailureHandler != null)
                    {
                    commitFailureHandler.ClearTransactionHistory();
                    //commitFailureHandler.PruneTransactionHistory();
                    }
                }
             }
         }
     
    Of course if I remove the commitFailureHandler.ClearTransactionHistory(); & I run the application, once I update any of my entities in the database during the normal course of when the service runs, it will create the dbo.EFTransactionHistory table. Then I stop the service, I include the commitFailureHandler.ClearTransactionHistory(); method it will works.
    Some how the ClearTransactionHistory() should be checking to see if the table exists before attempting to delete the rows from the table or it triggers the OnModelCreating() method.
    Now I should note that I'm not doing the code first approach...I took a model first approach to create my database & then created my entities from that. I don't know if this makes a difference. I would think not.
    I've been working on this for a few days now & I would love to find a way to solve this issue.

    Thanks

    Wednesday, September 17, 2014 7:53 PM

Answers

  • Hi Dallas,

    >>My comment:

    Some how the ClearTransactionHistory() should be checking to see if the table exists before attempting to delete the rows from the table or it triggers the OnModelCreating() method. was merely a suggestion on how ideally it should work (a new feature) rather than how it currently works.

    Oh, sorry for misunderstanding you. As I mention, if you want some feature, you could post a wish to that site and vote it.

    >>how would I know which table I would be looking for if I define my table in the DbTransactionContext.OnModelCreating(DbModelBuilder modelBuilder) method (please see my code example in my initial question)?

    If you mean that you want to know what the table name is, you could write an extend method to get it:

    class Program
    
        {
    
            static void Main(string[] args)
    
            {
    
                #region MyRegion
    
                SqlConnection connection = new SqlConnection(@"data source=(localdb)\v11.0;initial catalog=CFDB;integrated security=True;");
    
    
                using (Sample20140918 db = new Sample20140918(connection))
    
                {
    
                    //db.Database.
    
    
                    var result = db.GetTableName<TransactionRow>();
    
                }
    
                #endregion
    
            }
    
        }
    
    
        public static class ContextExtensions
    
        {
    
            public static string GetTableName<T>(this DbContext context) where T : class
    
            {
    
                ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
    
    
                return objectContext.GetTableName<T>();
    
            }
    
    
            public static string GetTableName<T>(this ObjectContext context) where T : class
    
            {
    
                string sql = context.CreateObjectSet<T>().ToTraceString();
    
                Regex regex = new Regex("FROM (?<table>.*) AS");
    
                Match match = regex.Match(sql);
    
    
                string table = match.Groups["table"].Value;
    
                return table;
    
            }
    
        }
    

    It would return the table name as:

    [dbo].[EFTransactionHistory]

    If I misunderstand, please let me know.

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 19, 2014 6:29 AM
    Moderator

All replies

  • Hello Dallas,

    >>Some how the ClearTransactionHistory() should be checking to see if the table exists before attempting to delete the rows from the table or it triggers the OnModelCreating() method.

    From your provide link, I do not find any description that this method should firstly check if the table exists, if you consider that this should be needed feature, you could post it to this site:

    https://data.uservoice.com/forums/72025-entity-framework-feature-suggestions

    Or you could write the custom query to check if the table exists before calling this method:

    bool exists = context.Database
    
                         .SqlQuery<int?>(@"
    
                             SELECT 1 FROM sys.tables AS T
    
                             INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
    
                             WHERE S.Name = 'SchemaName' AND T.Name = 'TableName'")
    
                         .SingleOrDefault() != null;
    

    >>Now I should note that I'm not doing the code first approach...I took a model first approach to create my database & then created my entities from that. I don't know if this makes a difference. I would think not.

    I would suggest that you could try with code first which would narrow down this issue to check if this is because of using model first way since they are two different ways to generated the database.

    If I do not understand you correctly, please feel free let me know.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 18, 2014 2:54 AM
    Moderator
  • Hey Fred:

    My comment:

    >>Some how the ClearTransactionHistory() should be checking to see if the table exists before attempting to delete the rows from the table or it triggers the OnModelCreating() method.

    was merely a suggestion on how ideally it should work (a new feature) rather than how it currently works.

    The point that I was trying to make about Code First vs Model First is that transaction handling is outside the bounds of which approach you would take when defining your entities & database. It shouldn't matter because transaction failures are a runtime issue & should not be dependent on your design time approach.

    I was thinking of providing a query to check to see if the table exists but this would have to be done in my Initialization static method. From a black box approach, how would I know which table I would be looking for if I define my table in the DbTransactionContext.OnModelCreating(DbModelBuilder modelBuilder) method (please see my code example in my initial question)? I could expose a public const string property that is assigned the table name in my DbTransactionContext & I could reference that property when I do my query but that just seems like a clunky workaround in my opinion.


    • Edited by Dallas Cowboy Thursday, September 18, 2014 2:27 PM removed text
    Thursday, September 18, 2014 2:26 PM
  • Hi Dallas,

    >>My comment:

    Some how the ClearTransactionHistory() should be checking to see if the table exists before attempting to delete the rows from the table or it triggers the OnModelCreating() method. was merely a suggestion on how ideally it should work (a new feature) rather than how it currently works.

    Oh, sorry for misunderstanding you. As I mention, if you want some feature, you could post a wish to that site and vote it.

    >>how would I know which table I would be looking for if I define my table in the DbTransactionContext.OnModelCreating(DbModelBuilder modelBuilder) method (please see my code example in my initial question)?

    If you mean that you want to know what the table name is, you could write an extend method to get it:

    class Program
    
        {
    
            static void Main(string[] args)
    
            {
    
                #region MyRegion
    
                SqlConnection connection = new SqlConnection(@"data source=(localdb)\v11.0;initial catalog=CFDB;integrated security=True;");
    
    
                using (Sample20140918 db = new Sample20140918(connection))
    
                {
    
                    //db.Database.
    
    
                    var result = db.GetTableName<TransactionRow>();
    
                }
    
                #endregion
    
            }
    
        }
    
    
        public static class ContextExtensions
    
        {
    
            public static string GetTableName<T>(this DbContext context) where T : class
    
            {
    
                ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
    
    
                return objectContext.GetTableName<T>();
    
            }
    
    
            public static string GetTableName<T>(this ObjectContext context) where T : class
    
            {
    
                string sql = context.CreateObjectSet<T>().ToTraceString();
    
                Regex regex = new Regex("FROM (?<table>.*) AS");
    
                Match match = regex.Match(sql);
    
    
                string table = match.Groups["table"].Value;
    
                return table;
    
            }
    
        }
    

    It would return the table name as:

    [dbo].[EFTransactionHistory]

    If I misunderstand, please let me know.

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 19, 2014 6:29 AM
    Moderator