Entity Framework M:1 relationship resulting in primay key duplication

Question

I'm somewhat new to EF 6.0 so I'm pretty sure I'm doing something wrong here. there are two questions related to the problem

1. what am I doing wrong here
2. what's the best practice to achieve this

I'm using a code first model, and used the edmx designer to design the model and relationships, the system needs to pull information periodically from a webservice and save it to a local database (SQL Lite) in a desktop application

so I get an order list from the API, when I populate and try to save Ticket, I get a duplicate key exception when trying to insert TicketSeatType -

how do I insert the ticket to dbContext, so that It doesn't try and re-insert insert TicketSeatType and TicketPriceType, I have tried setting the child object states to unchanged but it seems to be inserting

secondly, what would be the best practice to achieve this using EF ? it just looks very inefficient loading each object into memory and comparing if it exists or not

since I need to update the listing periodically, I have to check against each object in the database if it exists, then update, else insert

code:

//read session from db
                if (logger.IsDebugEnabled) logger.Debug("reading session from db");
                dbSession = dbContext.SessionSet.Where(x => x.Id == sessionId).FirstOrDefault();

                //populate orders
                List<Order> orders = (from e in ordersList
                                      select new Order {
                                          Id = e.OrderId,
                                          CallCentreNotes = e.CallCentreNotes,
                                          DoorEntryCount = e.DoorEntryCount,
                                          DoorEntryTime = e.DoorEntryTime,
                                          OrderDate = e.OrderDate,
                                          SpecialInstructions = e.SpecialInstructions,
                                          TotalValue = e.TotalValue,
                                          //populate parent refernece
                                          Session = dbSession
                                      }).ToList();

                //check and save order
                foreach (var o in orders) {
                    dbOrder = dbContext.OrderSet.Where(x => x.Id == o.Id).FirstOrDefault();
                    if (dbOrder != null) {
                        dbContext.Entry(dbOrder).CurrentValues.SetValues(o);
                        dbContext.Entry(dbOrder).State = EntityState.Modified;
                    }
                    else {
                        dbContext.OrderSet.Add(o);
                        dbContext.Entry(o.Session).State = EntityState.Unchanged;
                    }
                }
                dbContext.SaveChanges();

                //check and add ticket seat type
                foreach (var o in ordersList) {
                    foreach (var t in o.Tickets) {
                        var ticketSeatType = new TicketSeatType {
                            Id = t.TicketSeatType.TicketSeatTypeId,
                            Description = t.TicketSeatType.Description
                        };
                        dbTicketSeatType = dbContext.TicketSeatTypeSet.Where(x => x.Id == ticketSeatType.Id).FirstOrDefault();
                        if (dbTicketSeatType != null) {
                            dbContext.Entry(dbTicketSeatType).CurrentValues.SetValues(ticketSeatType);
                            dbContext.Entry(dbTicketSeatType).State = EntityState.Modified;
                        }
                        else {
                            if (!dbContext.ChangeTracker.Entries<TicketSeatType>().Any(x => x.Entity.Id == ticketSeatType.Id)) {
                                dbContext.TicketSeatTypeSet.Add(ticketSeatType);
                            }
                        }
                    }
                }
                dbContext.SaveChanges();

                //check and add ticket price type
                foreach (var o in ordersList) {
                    foreach (var t in o.Tickets) {
                        var ticketPriceType = new TicketPriceType {
                            Id = t.TicketPriceType.TicketPriceTypeId,
                            SeatCount = t.TicketPriceType.SeatCount,
                            Description = t.TicketPriceType.Description
                        };
                        dbTicketPriceType = dbContext.TicketPriceTypeSet.Where(x => x.Id == ticketPriceType.Id).FirstOrDefault();
                        if (dbTicketPriceType != null) {
                            dbContext.Entry(dbTicketPriceType).CurrentValues.SetValues(ticketPriceType);
                            dbContext.Entry(dbTicketPriceType).State = EntityState.Modified;
                        }
                        else {
                            if (!dbContext.ChangeTracker.Entries<TicketPriceType>().Any(x => x.Entity.Id == ticketPriceType.Id)) {
                                dbContext.TicketPriceTypeSet.Add(ticketPriceType);
                            }
                        }
                    }
                }
                dbContext.SaveChanges();

                //check and add tickets
                foreach (var o in ordersList) {
                    dbOrder = dbContext.OrderSet.Where(x => x.Id == o.OrderId).FirstOrDefault();
                    foreach (var t in o.Tickets) {
                        var ticket = new Ticket {
                            Id = t.TicketId,
                            Quantity = t.Quantity,
                            TicketPrice = t.TicketPrice,
                            TicketPriceType = new TicketPriceType {
                                Id = t.TicketPriceType.TicketPriceTypeId,
                                Description = t.TicketPriceType.Description,
                                SeatCount = t.TicketPriceType.SeatCount,
                            },
                            TicketSeatType = new TicketSeatType {
                                Id = t.TicketSeatType.TicketSeatTypeId,
                                Description = t.TicketSeatType.Description
                            },
                            Order = dbOrder
                        };
                        //check from db 
                        dbTicket = dbContext.TicketSet.Where(x => x.Id == t.TicketId).FirstOrDefault();
                        dbTicketSeatType = dbContext.TicketSeatTypeSet.Where(x => x.Id == t.TicketSeatType.TicketSeatTypeId).FirstOrDefault();
                        dbTicketPriceType = dbContext.TicketPriceTypeSet.Where(x => x.Id == t.TicketPriceType.TicketPriceTypeId).FirstOrDefault();                            

                        if (dbTicket != null) {
                            dbContext.Entry(dbTicket).CurrentValues.SetValues(t);
                            dbContext.Entry(dbTicket).State = EntityState.Modified;
                            dbContext.Entry(dbTicket.Order).State = EntityState.Unchanged;
                            dbContext.Entry(dbTicketSeatType).State = EntityState.Unchanged;
                            dbContext.Entry(dbTicketPriceType).State = EntityState.Unchanged;

                        }
                        else {
                            dbContext.TicketSet.Add(ticket);
                            dbContext.Entry(ticket.Order).State = EntityState.Unchanged;
                            dbContext.Entry(ticket.TicketSeatType).State = EntityState.Unchanged;
                            dbContext.Entry(ticket.TicketPriceType).State = EntityState.Unchanged;
                        }
                    }
                }
                dbContext.SaveChanges();

UPDATE:

Found the answer, it has to do with how EF tracks references to objects, in the above code, I was creating new entity types from the list for TicketPriceType and TicketSeatType:

 foreach (var o in ordersList) {
                    dbOrder = dbContext.OrderSet.Where(x => x.Id == o.OrderId).FirstOrDefault();
                    foreach (var t in o.Tickets) {
                        var ticket = new Ticket {
                            Id = t.TicketId,
                            Quantity = t.Quantity,
                            TicketPrice = t.TicketPrice,
                            TicketPriceType = new TicketPriceType {
                                Id = t.TicketPriceType.TicketPriceTypeId,
                                Description = t.TicketPriceType.Description,
                                SeatCount = t.TicketPriceType.SeatCount,
                            },
                            TicketSeatType = new TicketSeatType {
                                Id = t.TicketSeatType.TicketSeatTypeId,
                                Description = t.TicketSeatType.Description
                            },
                            Order = dbOrder
                        };
....

in this case the EF wouldn't know which objects they were and try to insert them.

the solution is to read the entities from database and allocate those, so it's referencing the same entities and doesn't add new ones

foreach (var t in o.Tickets) {

                            //check from db 
                            dbTicket = dbContext.TicketSet.Where(x => x.Id == t.TicketId).FirstOrDefault();
                            dbTicketSeatType = dbContext.TicketSeatTypeSet.Where(x => x.Id == t.TicketSeatType.TicketSeatTypeId).FirstOrDefault();
                            dbTicketPriceType = dbContext.TicketPriceTypeSet.Where(x => x.Id == t.TicketPriceType.TicketPriceTypeId).FirstOrDefault();

                            var ticket = new Ticket {
                                Id = t.TicketId,
                                Quantity = t.Quantity,
                                TicketPrice = t.TicketPrice,
                                TicketPriceType = dbTicketPriceType,
                                TicketSeatType = dbTicketSeatType,
                                Order = dbOrder
                            };
...}

Answer 1

Don't you think that you are trying to write very similar codes for defining the state of each entity? We can handle all of these operations with a single command.

You can easily achieve this with the newly released EntityGraphOperations for Entity Framework Code First. I am the author of this product. And I have published it in the github, code-project (includes a step-by-step demonstration and a sample project is ready for downloading) and nuget. With the help of InsertOrUpdateGraph method, it will automatically set your entities as Added or Modified. And with the help of DeleteMissingEntities method, you can delete those entities which exists in the database, but not in the current collection.

// This will set the state of the main entity and all of it's navigational 
// properties as `Added` or `Modified`.
context.InsertOrUpdateGraph(ticket); 

By the way, I feel the need to mention that this wouldn't be the most efficient way of course. The general idea is to get the desired entity from the database and define the state of the entity. It would be as efficient as possible.