none
Preventing duplicate entries with a foreign key constraint RRS feed

  • Question

  • Hello,

    I have adapted the FetchOrCreate extension that has been posted around to try to prevent entering duplicate rows in my tables:

    public static class FetchOrCreateExtension
        {
            public static T FetchOrCreate<T>(this Table<T> table, Expression<Func<T, bool>> where, T newValue) where T : class
            {
                T existing = table.SingleOrDefault(where);

                if (existing != null)
                    return existing;

                // clone the DataContext
                Type dataContextType = table.Context.GetType();
                string ctxConStr = table.Context.Connection.ConnectionString;
                using (DataContext newDC = (DataContext)Activator.CreateInstance(dataContextType, ctxConStr))
                {
                    Table<T> writableTable = newDC.GetTable<T>();
                    writableTable.InsertOnSubmit(newValue);

                    newDC.SubmitChanges();
                }
                return table.Single(where); // fetch on the existing context so the caching behavior is consistent
            }
        }

    The problem I have is with the following code:

                application.AnimalBreed.AnimalType =
                    db.AnimalTypes.FetchOrCreate(f => f.TypeName == application.AnimalBreed.AnimalType.TypeName,
                                                 new AnimalType { TypeName = application.AnimalBreed.AnimalType.TypeName });

                application.AnimalBreed =
                    db.AnimalBreeds.FetchOrCreate(f => f.Name == application.AnimalBreed.Name,
                                                  new AnimalBreed { Name = application.AnimalBreed.Name });

    This works for AnimalType; but I am always getting duplicates for AnimalBreed.  If I switch the order around (try AnimalBreeds and then AnimalTypes), I then get duplicates for both.  As you can see, there is a relationship between AnimalTypes and AnimalBreeds (AnimalBreeds has a column that is a foreign key into AnimalTypes).

    Anyone have any idea why this isn't working?
    Friday, May 15, 2009 5:06 PM

All replies

  • I figured out the problem, for anyone else who is interested...

    My problem was that when I fetched the existing AnimalType entry from the database, it associated with AnimalBreed and all looked fine.  Then I fetched the existing AnimalBreed and again, the association looked fine.  What I didn't notice was that the AnimalBreeds associated from the AnimalType had added two AnimalBreed items.  When the Submit occurred, the duplication occurred. 

    I've temporarily set the code to only retrieve the AnimalBreed, but I'm sure there is a better solution.
    Saturday, May 16, 2009 2:04 AM
  • What is somewhat confusing here is why data that you use in the expressions already exists. e.g. application.AnimalBreed.AnimalType.TypeName or application.AnimalBreed.Name. Is it possible that whatever is populating these properties before this point is submitting them to the database?

    You can try investigating this further by using the DataContext.Log property and other tracing mechanisms to determine where in your code the multiple inserts occur. You can also check the return value of DataContext.GetChangeSet to see what's added at various points in your application. Good luck!
    Sunday, May 17, 2009 5:30 PM
    Answerer