LINQ Inserts - Avoid Duplicates

Question

Hello,

I have an application with two classes, Beer and Brewery.  I have these classes mapped to tables in my database via LINQ mapping.  These two classes are outlined below;

Brewery Class

    [Table(Name = "dbo.tFYB_Brewery")]
    public class Brewery
    {
        public Brewery() { }

        public Brewery(Account account, StreetAddress streetAddress, PhoneNumber phoneNumber, string name, string webUrl)
        {
            this._parentAccount = account;
            this._parentStreetAddress = streetAddress;
            this._parentPhoneNumber = phoneNumber;
            this.name = name;
            this.webURL = webUrl;
        }

        [Column(Name = "tFYB_Brewery_Key", IsDbGenerated = true, IsPrimaryKey=true)]
        public int key { get; set; }

        [Association(Name = "FK_tFYB_Brewery_tFYB_Account", Storage = "_parentAccount", ThisKey = "key", OtherKey = "key", IsForeignKey = true)]
        private Account _parentAccount;

        public Account parentAccount
        {
            get { return this._parentAccount; }
            set { this._parentAccount = value; }
        }

        [Column(Name = "tFYB_Account_Key")]
        private int accountKey
        {
            get { return this._parentAccount.key; }
            set { this._parentAccount.key = value; }
        }

        [Association(Name = "FK_tFYB_Brewery_tFYB_StreetAddress", Storage = "_parentStreetAddress", ThisKey = "key", OtherKey = "key", IsForeignKey = true)]
        private StreetAddress _parentStreetAddress;

        public StreetAddress parentStreetAddress
        {
            get { return this._parentStreetAddress; }
            set { this._parentStreetAddress = value; }
        }

        [Column(Name = "tFYB_StreetAddress_Key")]
        private int streetAddressKey
        {
            get { return this._parentStreetAddress.key; }
            set { this._parentStreetAddress.key = value; }
        }

        [Association(Name = "FK_tFYB_Brewery_tFYB_PhoneNumber", Storage = "_parentPhoneNumber", ThisKey = "key", OtherKey = "key", IsForeignKey = true)]
        private PhoneNumber _parentPhoneNumber;

        public PhoneNumber parentPhoneNumber
        {
            get { return this._parentPhoneNumber; }
            set { this._parentPhoneNumber = value; }
        }

        [Column(Name = "tFYB_PhoneNumber_Key")]
        private int phoneNumnberKey
        {
            get { return this._parentPhoneNumber.key; }
            set { this._parentPhoneNumber.key = value; }
        }

        [Column(Name = "BreweryName")]
        public string name { get; set; }

        [Column(Name = "WebUrl")]
        public string webURL { get; set; }
    }

Beer Class

    public enum BeerSubCategory
    {
        Barleywines = 1,
        BelgianAles,
        Bitters,
        IndiaPaleAles,
        Porters,
        Stouts,
        WheatBeers,
        Bocks,
        DarkLagers,
        PaleLagers,
        Pilsners,
        Ciders,
        Meads
    }

    [Table(Name="dbo.tFYB_Beer")]
    public class Beer
    {
        public Beer() { }

        public Beer(Brewery brewery, BeerSubCategory beerSubCategory, string name)
        {
            this._parentBrewery = brewery;
            this.beerSubCategory = beerSubCategory;
            this.beerSubCategoryKey = (int)beerSubCategory;
            this.name = name;
        }

        public Beer(Brewery brewery, BeerSubCategory beerSubCategory, string name, string imageUrl)
        {
            this._parentBrewery = brewery;
            this.beerSubCategory = beerSubCategory;
            this.beerSubCategoryKey = (int)beerSubCategory;
            this.name = name;
            this.imageURL = imageUrl;
        }

        [Column(Name = "tFYB_Beer_Key", IsDbGenerated = true, IsPrimaryKey = true)]
        public int key { get; set; }

        [Association(Name = "FK_tFYB_Beer_tFYB_Brewery", Storage = "_parentBrewery", ThisKey = "key", OtherKey = "key", IsForeignKey = true, IsUnique=true)]
        private Brewery _parentBrewery;

        public Brewery parentBrewery
        {
            get { return this._parentBrewery; }
            set { this._parentBrewery = value; }
        }

        [Column(Name = "tFYB_Brewery_Key")]
        private int BreweryKey
        {
            get { return this._parentBrewery.key; }
            set { this._parentBrewery.key = value; }
        }

        [Column(Name = "tFYB_BeerSubCategory_Key")]
        private int beerSubCategoryKey;

        public BeerSubCategory beerSubCategory { get; set; }

        [Column(Name = "BeerName")]
        public string name { get; set; }

        [Column(Name = "ImageUrl")]
        public string imageURL { get; set; }
    }

I can successfully insert a Brewery into the database using a DataContext... no problem.  If I insert a Beer the same way, it works fine as well, however my problem is I need a check to see if the Brewery for the current Beer object is already in the database, because if so I don't want to insert it again.  The Brewery "name" should be used for uniqueness.  

Any help is appreciated!

Thanks,

Chris

Answer 1

Hi,

If you want the uniqueness of the column, please define the unique constraint for the column in the database rather than in code.

That would be much better and consistent. The data model is the most important part in any application/project. So keep your data clean and let DB take care of it.

---

Always mark the answers if the post answers your question. Prabhu R

Answer 2

Okay, so I thought that may be the answer I was going to get, but there has to be away to enforce that in the code as well.  If I place the unique constraint on the DB, then my insert for the Beer fails, as it has a Brewery which already exists in the database and I have to imagine that something in the way I coded the Beer's association to Brewery is causing the Brewery to be inserted again when inserting the Beer, regardless of whether or not the Beer's Brewery is already in the database.

There is no way to stop this attempt at inserting a duplicate value?

Answer 3

Your question's technical is EF & Linq aspect, so I moved it to this appropriate forum.

If there's any concern, please feel free to let me know.

Best wishes,

---

Mike Zhang[MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi CorbinTech,

Welcome to MSDN Forum.

Based on this issue, my understanding is when you insert a "Beer" record, you have assigned a "Brewery" to the "parentBrewery" property, and after calling submitChanges, a new record of "Brewery" has been inserted into database along with the "Beer" record. You want to check whether the "Brewery" has already in the database, if yes, prevent to insert it. In this scenario, the common solution is, before assigning a "Brewery" to a "Beer", you need to check whether it has already in the database, if yes, assign the existing record to the "Beer", then when you do insert operation, the "Brewery" will not been inserted into database. Below is a demo, please refer to it.

static void Main(string[] args)
        {
            using (myContext context = new myContext())
            {
                Player p = new Player();
                p.Age = 21;
                p.PlayerName = "testPlayer";
                Team t = new Team();
                t.TeamName = "testTeam";
                t.Coach = "testCoach";
                t.City = "testCity";
                Team team = context.Teams.Where(x => x.TeamName==t.TeamName&&x.Coach==t.Coach&&x.City==t.City).SingleOrDefault();
                if (team == null)
                {
                    p.Team = t;
                }
                else
                {
                    p.Team = team;
                }
                context.Players.InsertOnSubmit(p);
                context.SubmitChanges();
            }
        }

If I misunderstand, please feel free to let me know.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 5

Hi CorbinTech,

I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

Have a nice day.

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Hello Allen,

Sorry for the delayed response... I took a few days away from this problem to see if the answer would come to me.  I think your suggestion works for avoiding the duplicate entries, but it has lead me to another error.  So, I used this code to follow your suggestion;

                Brewery brewery = new Brewery(account, streetAddress, phoneNumber, "Green Flash Brewery", "www.greenflash.com");
                BreweryRepository.addBrewery(brewery, "Server=CTI002;Initial Catalog=FYB;Integrated Security=SSPI;");

                var vBrewery =
                    from b in dc.GetTable<Brewery>()
                    where b.name == "Green Flash Brewery"
                    select b;


                Beer westCoastIPA = new Beer(vBrewery.Count() == 0 ? brewery : vBrewery.First(), BeerSubCategory.IndiaPaleAles, "West Coast IPA");
                BeerRepository.addBeer(westCoastIPA, "Server=CTI002;Initial Catalog=FYB;Integrated Security=SSPI;");

However when I try to run it, I am getting an exception, it appears the above code has detected correctly that the Brewery already exists, but one of the setter methods on the Brewery class is failing.  I have added a comment in the class definition below on the line of code that is throwing the exception.  

    [Table(Name = "dbo.tFYB_Brewery")]
    public class Brewery
    {
        public Brewery() { }

        public Brewery(Account account, StreetAddress streetAddress, PhoneNumber phoneNumber, string name, string webUrl)
        {
            this._parentAccount = account;
            this._parentStreetAddress = streetAddress;
            this._parentPhoneNumber = phoneNumber;
            this.name = name;
            this.webURL = webUrl;
        }

        [Column(Name = "tFYB_Brewery_Key", IsDbGenerated = true, IsPrimaryKey=true)]
        public int key { get; set; }

        [Association(Name = "FK_tFYB_Brewery_tFYB_Account", Storage = "_parentAccount", ThisKey = "key", OtherKey = "key", IsForeignKey = true)]
        private Account _parentAccount;

        public Account parentAccount
        {
            get { return this._parentAccount; }
            set { this._parentAccount = value; }
        }

        [Column(Name = "tFYB_Account_Key")]
        private int accountKey
        {
            get { return this._parentAccount.key; }
            set { this._parentAccount.key = value; } // Exception :: Object reference not set to an instance of an object.
        }

        [Association(Name = "FK_tFYB_Brewery_tFYB_StreetAddress", Storage = "_parentStreetAddress", ThisKey = "key", OtherKey = "key", IsForeignKey = true)]
        private StreetAddress _parentStreetAddress;

        public StreetAddress parentStreetAddress
        {
            get { return this._parentStreetAddress; }
            set { this._parentStreetAddress = value; }
        }

        [Column(Name = "tFYB_StreetAddress_Key")]
        private int streetAddressKey
        {
            get { return this._parentStreetAddress.key; }
            set { this._parentStreetAddress.key = value; }
        }

        [Association(Name = "FK_tFYB_Brewery_tFYB_PhoneNumber", Storage = "_parentPhoneNumber", ThisKey = "key", OtherKey = "key", IsForeignKey = true)]
        private PhoneNumber _parentPhoneNumber;

        public PhoneNumber parentPhoneNumber
        {
            get { return this._parentPhoneNumber; }
            set { this._parentPhoneNumber = value; }
        }

        [Column(Name = "tFYB_PhoneNumber_Key")]
        private int phoneNumnberKey
        {
            get { return this._parentPhoneNumber.key; }
            set { this._parentPhoneNumber.key = value; }
        }

        [Column(Name = "BreweryName")]
        public string name { get; set; }

        [Column(Name = "WebUrl")]
        public string webURL { get; set; }
    }

Any ideas?

Thanks,

Chris

Answer 7

Hi CorbinTech,

Based on the exception, I think it caused by the "parentAccount" property is null, so when you set value to parentAccount.Key, the exception is thrown. Please add a breakpoint to check whether the parentAccount has already been set a value.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 8

Hi CorbinTech,

Have you solved the issue? I look forward to hearing from you. If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance. :)

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us