0

I have table ProjekRumah and ProjekKMDetails. When I use entities.SaveChanges() on ProjekRumah data, new row with ProjectID will yield its Identity. Suppose that number is 7.

Then I take this ProjekID=7, to create ProjekKMDetails data. Then, entities.SaveChanges(). Repeat same step Table1, Table2, Table3, Table 4 and so on with ProjectID=7 as Foreign Key.

All is fine on sunny day. What if ProjekID=7 already exists in one of these table, suppose hypothetically in Table3.

I understand this is not suppose to happen. But suppose its Friday the 13th an intern might drop all constraints and forgot to truncate Table3. Or some evil data corruption.

        using (MyDBEntities entities = new MyDBEntities())
        {
            using (var dbContextTransaction = entities.Database.BeginTransaction())
            {
                try
                {

                    var dataRumah = new ProjekRumah
                    {

                        ProjekStatus = 'SpecialScenario',
                        ProjekName = data.ProjekName,  //data passed in from UI
                    };
                    entities.ProjekRumah.Add(dataRumah);
                    entities.SaveChanges();  // the first .SaveChanges to acquire Identity increment

                    var dataKMDetails = new ProjekKMDetails
                    {

                        ProjekID = dataRumah.ProjekID, //identity ID
                        KMJPBDNo = data.KMJPBDNumber,  //data passed in from UI
                    };
                    entities.ProjekKMDetails.Add(dataKMDetails);


                   var table3Details = new Table3
                    {

                        ProjekID = dataRumah.ProjekID, //identity ID
                        Table3Stuff = data.ForTable3Stuff,  //data passed in from UI
                    };
                    entities.Table3.Add(table3Details );

                    //..... No .SaveChanges anywhere
                    //..... more tables, Tables 4, 5 6 etc
                    //..... No round trips to database

                    entities.SaveChanges();        //the second and final .SaveChanges()
                    dbContextTransaction.Commit();

                }
                catch (Exception ex)
                {
                    dbContextTransaction.Rollback();

                }
            }

My intent is to be defensive and safe side, is there a syntax to check if Table3 already have row with ProjectID = 7?

To my understanding attempting to delete anything from Table3 will require delete on same corresponding ID in ProjekRumah and ProjekKMDetails and Table4 and so on:

                    ProjekRumah kmContext = new ProjekRumah { 
                    ProjekID = 7, 
                    ProjekKMDetails = new ProjekKMDetails { ProjekID = 7 },
                    Table3= new Table3 { ProjekID = 7 }
                    };
                    entities.ProjekRumah.Attach(kmContext);
                    entities.ProjekRumah.Remove(kmContext);

                    entities.SaveChanges(); //this actually deletes from multiple tables

But at this stage there is no (should be) such record in ProjekRumah table because earlier on: entities.Table3.Add(dataTable3Details) would be illegal and caused rollback.

How do I write code to be on defensive side in case 'Foreign Key Already Exist' happens? Or such a scenario can never ever 101% to happen? If possible, I would like to cleanse with EF functions as succinct and as elegant as possible.

PS: I guess I can summarize my question as this: Suppose an orphan row was found despite Foreign Key constrains (should never happen I believe), but how do I use EF to handle this in a single transaction (preferably with no round trips such as Count, Select, FirstorDefault etc)?

Chan Fun Chiat
  • 21
  • 4
  • So your actual question is: how do I check if there is a record in some table with `ProjekID = 7`? – Gert Arnold Mar 15 '16 at 12:40
  • Ya ya correct. Basically that. But my code is actually single transaction with Commit and Rollback (code omitted) – Chan Fun Chiat Mar 15 '16 at 12:42
  • Well, that's basically `entities.Table1.Any(...)`. – Gert Arnold Mar 15 '16 at 12:46
  • .Any(...) will be my next best choice. I read it returns a Boolean, although it retrieves no record, its a round trip to database. Is there a way to do the work without retrieving or round trip to database? – Chan Fun Chiat Mar 16 '16 at 01:53
  • Originally my code attempted to do cleanse without round trips like this but the .Remove caused runtime error during .SaveChanges when ProjectID record is not found in one of the tables. I needed it to be 'silent' and without round trips `var dataKMDetails = new ProjekKMDetails { ProjekID = dataRumah.ProjekID };` `entities.ProjekKMDetails.Attach(dataKMDetails);` `entities.ProjekKMDetails.Remove(dataKMDetails);` – Chan Fun Chiat Mar 16 '16 at 02:04
  • To create clarity I added more codes and the transaction wrap – Chan Fun Chiat Mar 16 '16 at 02:23
  • It's old but still applicable: [Update Row if it Exists Else Insert Logic with Entity Framework](http://stackoverflow.com/questions/5557829/update-row-if-it-exists-else-insert-logic-with-entity-framework). You need to use "update or insert" logic, and you need to either make a stored procedure to handle the operation on the database server or make a trip to the database to find out whether the row exists so you can decide whether you need an update or insert. – Todd Knarr Mar 16 '16 at 02:45
  • Stored procedure is a good suggestion. The URL is also very good info. My quest to find a solution without round trips brought me to .AddOrUpdate but info I read from http://thedatafarm.com/data-access/take-care-with-ef-4-3-addorupdate-method/ made me abandon the idea. I think its still a round trip to database – Chan Fun Chiat Mar 16 '16 at 03:30
  • The reason I am so persistent because this SQL code does not raise error. It is 'silent' delete ProjekKMDetails where ProjekID = 7 I know I can easily reproduce what I need in stored procedures – Chan Fun Chiat Mar 16 '16 at 03:44
  • If you have a master table for project IDs, you can also enforce cleanup by setting up ON DELETEs that'll force deletion of all information associated with the project when the project's row in the master table is deleted. – Todd Knarr Mar 16 '16 at 17:47
  • I wonder ON DELETE also known as Cascade delete in Entity Framework? – Chan Fun Chiat Mar 17 '16 at 03:12
  • There's several options for ON DELETE and also ON UPDATE: cascade, restrict, no action (similar to restrict except for timing), set null, set default. The main difference is that this is set up in the database table definition and is independent of EF so it doesn't require any special coding in your application and it can't cause an inconsistent state. – Todd Knarr Mar 17 '16 at 07:44
  • Could it be these T-SQL code ? `ALTER TABLE ProjekRumah` `ADD FOREIGN KEY (ProjekID)` `REFERENCES Table3(ProjekID)` `ON DELETE CASCADE;` `ALTER TABLE ProjekRumah` `ADD FOREIGN KEY (ProjekID)` `REFERENCES ProjekKMDetails(ProjekID)` `ON DELETE CASCADE;` – Chan Fun Chiat Mar 17 '16 at 10:03
  • Oh darn i read Truncate would not trigger On Delete Cascade. I needed Truncate to reseed identity. Why can't they just make .Remove 'silent when not found' much like `delete ProjekKMDetails where ProjekID = 7` `entities.ProjekKMDetails.Attach(dataKMDetails);` `entities.ProjekKMDetails.Remove(dataKMDetails);` `entities.SaveChanges(); //raises exception Store update, insert, or delete statement affected an unexpected number of rows (0)` – Chan Fun Chiat Mar 18 '16 at 04:55

0 Answers0