Context Attach Method Exception

Question

Ok, I'm not sure what I missing here, so here it goes. I'm filling up a MySQL database using the Entity Framework. I either update or add new records to the database. First, I search for a record and if it returns null then I know I have to add the record. If the search returns the record then I make changes to the record.

So that's a brief summary. Originally I had kept the Configuration properties to their default and ran SaveChanges() when needed, however I read to speed up my program turning them to false is better. My issue occured when I turned these off:

context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;

Below is my code that is giving me trouble. Once I run the Attach method and set the Modified state, then run WFContext.SaveChanges() a System.Data.Entity.Infrastructure.DbUpdateException is thrown. I thought I was doing it right? Apparently not...

 if (add)
 {
    WFContext.SecuritiesEntitySet.Add(security);
 }
 else
 {
    WFContext.SecuritiesEntitySet.Attach(security);
    WFContext.Entry(security).State = EntityState.Modified;
 }

What exactly am I missing here?

Here are the trace:

A first chance exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at MyContext.SaveChanges() in MyContext.cs:line 24
   at ContextConnection.RecreateWFContext() in ContextConnection.cs:line 26
   at AbstractRecords.CheckRecordCount() in AbstractRecords.cs:line 46
   at SecuritiesRecord.Parser() SecuritiesRecord.cs:line 74
An error occurred while updating the entries. See the inner exception for details.

Brief breakdown of what you see in the trace and beyond:

• ContextConnection is a static class that manages the connection to MyContext. RecreateWFContext disposes of the current context and then initializes again.
• AbstractRecords provides properties in methods that all the record classes can use.
• SecuritiesRecord.Parser parses a string and loads up all the properties in the security object.

This is the SecurityInfo Class, which is instantiated and referenced by security in the above example of my problem code.

public partial class SecurityInfo
    {
        public System.DateTime ImportDate { get; set; }
        public string CUSIP { get; set; }
        public string Symbol { get; set; }
        public string SecurityType { get; set; }
        public Nullable<System.DateTime> PriceDate { get; set; }
        public Nullable<decimal> PriceClose { get; set; }
        public Nullable<decimal> DividendRate { get; set; }
        public Nullable<System.DateTime> ExDate { get; set; }
        public Nullable<System.DateTime> PayableDate { get; set; }
        public string PaymentFrequency { get; set; }
        public Nullable<System.DateTime> FirstCallDate { get; set; }
        public Nullable<decimal> FirstCallRate { get; set; }
        public Nullable<System.DateTime> SecondCallDate { get; set; }
        public Nullable<decimal> SecondCallRate { get; set; }
        public Nullable<int> Industry { get; set; }
        public Nullable<decimal> EquityDividendRate { get; set; }
        public Nullable<System.DateTime> EquityRecordDate { get; set; }
        public Nullable<System.DateTime> EquityPayableDate { get; set; }
        public Nullable<System.DateTime> EquityExDividendDate { get; set; }
        public string EquitySplitRate { get; set; }
        public string OSISymbol { get; set; }
        public Nullable<System.DateTime> OSIExpirationDate { get; set; }
        public string OSIOptionType { get; set; }
        public Nullable<decimal> OSIStrikePrice { get; set; }
        public Nullable<decimal> OptionContractLotSize { get; set; }
        public Nullable<decimal> AnnualCouponRate { get; set; }
        public Nullable<System.DateTime> MaturityDate { get; set; }
        public Nullable<System.DateTime> PaymentDate { get; set; }
        public Nullable<decimal> GNMAFactor { get; set; }
        public string SPRating { get; set; }
        public string MoodyRating { get; set; }
    }

UPDATE I took these out:

context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;

And I still get the same exception. I think it must be within the ContextConnection class. Here is what it looks like:

public static class ContextConnection
{
    private static MyContext WFContext;

    public static MyContext GetWFContext()
    {
        return WFContext;
    }

    public static void NewWFContext()
    {
        WFContext = new MyContext();
        //WFContext.Configuration.AutoDetectChangesEnabled = false;
        //WFContext.Configuration.ValidateOnSaveEnabled = false;  
    }
    public static void RecreateWFContext()
    {
        WFContext.SaveChanges();
        DisposeWFContext();
        NewWFContext();
    }

    public static void DisposeWFContext()
    {
        WFContext.Dispose();
    }
}

When the program starts up I call ContextConnection.NewWFContext() and in the finally section of the try-catch block I call DisposeWFContext

Just for good measure. Here is the method in the AbstractRecords class where I recreate the context when 100 records have been changed:

protected void CheckRecordCount()
{
    RecordsChangedCount++;

    if (RecordsChangedCount == 100)
    {
        ContextConnection.RecreateWFContext();
        RecordsChangedCount = 0;
    }
}

Any help would be greatly appreciated!

thanks, Justin

Answer 1

My Solution

Thanks everyone for helping me out. Finally got it figured out yesterday. The deal is that for certain tables, like the Securities table, I check to see if the record already exists, if not then I add the record. If the record does exist then I update the record. So you can see that this could be a problem if I'm waiting to push up my changes to the database after every 100 records have changed and I'm checking the database whether a record has been added or not. So basically I was trying to add two records with the same unique keys and only when SaveChanges was executed did the program figure out that there were duplicate records and throw the exception.

So for the meantime. I'm not disposing of my context after ever 100 records, but rather I'm not disposing of it until after I've finished importing the text file into my MySQL database. I'll have to write some logic in the future that can handle the add/update, but for now just doing this:

context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;

Just doing this reduced the time to import astronomically, from around 13 minutes to around 1 minute for 9,000 records. So for now that's good enough

Thanks for the help! It all led me to figure out this problem.

Justin