UPSERT 1-M DATA EXAMPLE - insert or update within a transaction

Upsert_OneManyExample.cs

//UPSERT 1-M DATA EXAMPLE - insert or update within a transaction
//---------------------------------------------------------------
//
private void Upsert_OneManyExample(OneManyDto myDtoObject)
{
	/* ASSUME DTO Object has the form:
		myDtoObject = {
			oneHeader = ... some object here
		}	manyLines = ... some array of objects here...
	*/
	
	
	HeaderType hdr = null;
	long pkHeaderID = -1;

	//#1. initial validations of input DTO
	if (myDtoObject != null)
	{

		if (myDtoObject.oneHeader != null)
		{
			hdr = myDtoObject.oneHeader;
			pkHeaderID = long.Parse(hdr.headerNo);
		}
		else
		{
			throw new Exception("Bad Header record; oneHeader prop not found.");
		};


		if (myDtoObject.manyLines != null)
		{
			LineType[] items = myDtoObject.manyLines;
			if (items.Length < 1)
			{
				throw new Exception("Bad Children record; NO line items found.");
			};
		}
		else
		{
			throw new Exception("Bad Children record; manyLines prop not found.");
		};
	}
	else
	{
		throw new Exception("Bad myDtoObject Record; ... etc...");
	};



	//#2. Save to DB-Entities
	TB_ENTITY_HEADER entityHeader = this.mapDtoHeaderToEntity(myDtoObject.oneHeader); //validated above.. HEADER exists !

	using (var dbCtx = new CTXEntities())
	{

		using (var dbTxt = dbCtx.Database.BeginTransaction()) //Save HEADER/Lines within DB-Tx
		{
			//check if exists; this also loads the record found into dbCtx
			//IQueryable<TB_ENTITY_HEADER> query = dbCtx.TB_ENTITY_HEADER.Where(e => e.headerNo == pkHeaderID);
			//TB_ENTITY_HEADER existing = query.FirstOrDefault();
			//ALT: 
			bool found = dbCtx.TB_ENTITY_HEADER.Any(e => e.headerNo == pkHeaderID);

			//if (existing != null)
			if (found)
			{
				//dbCtx.Entry(existing).State = EntityState.Detached; //NOT REQUIRED (unless using FirstOrDefault() commented out above)

				//update
				dbCtx.TB_ENTITY_HEADER.Attach(entityHeader);
				dbCtx.Entry(entityHeader).State = EntityState.Modified; //<== NB: Edit all props, in the whole HeaderObject (not just a few!)
				//so you have to fill all props, not leave them blank... whether you edited them or not.
			}
			else
			{
				//insert
				dbCtx.TB_ENTITY_HEADER.Add(entityHeader);  //<== NOT FOUND, ADD NEW row...
			}


			try
			{
				//#2a.
				dbCtx.SaveChanges(); //<====  Save HEADER

				//always delete Children, and re-add them. (avoids messing around with comparisons for 1.whats new; 2.what to edit; 3. what to remove)
				dbCtx.Database.ExecuteSqlCommand("DELETE FROM TB_ENTITY_ITEMS where headerNo=" + pkHeaderID);

				//save Lines
				LineType[] items = myDtoObject.manyLines; //already validated above.. it HAS AT LEAST ONE line !
				foreach (LineType line in items)
				{
					TB_ENTITY_ITEMS entityLine = this.mapDtoLineToEntity(line, pkHeaderID);

					//insert EACH Line Item, AND loop to next Line
					dbCtx.TB_ENTITY_ITEMS.Add(entityLine);
				}
				
				//#2b.
				dbCtx.SaveChanges(); //<====  Save LINES

				
				//#2c.
				dbTxt.Commit(); //<====  DB-COMMIT
			}
			catch (DbEntityValidationException dbErr)
			{
				//#2d.
				dbTxt.Rollback(); //<====  DB-ROLLBACK

				string errMsg = "";
				foreach (var eve in dbErr.EntityValidationErrors)
				{
					errMsg += string.Format("Entity of type {0} in state {1} has the following validation errors:",
						eve.Entry.Entity.GetType().Name, eve.Entry.State) + Environment.NewLine;
					foreach (var ve in eve.ValidationErrors)
					{
						errMsg += string.Format("- Property: {0}, Error: {1}",
							ve.PropertyName, ve.ErrorMessage) + Environment.NewLine;
					}
				}
				Utils.LogMsg(errMsg);
				throw;
			}
			catch (Exception ex)
			{
				//#2d.
				dbTxt.Rollback(); //<====  DB-ROLLBACK

				Utils.LogMsg(ex.Message);
				throw;
			}

		} //end-Using DB-Tranx / dbTxt

	} //end-Using Ctx-Entities
}