Linq/Entities: Everything Or Nothing (in Database)

Question

I am trying to implement a way of putting data (entities object) into the database only if no error occurs ('everything'). If an error occurs, nothing is created into the database ('or nothing'). What I call "everything or nothing".

The problem is that I have to create entities that depends on other entities. Here's an example :

0) I create a context

DBContext ctx = new DBContext();

1) I create an Invoice entity object and add it to the context:

Invoice inv1 = new Invoice();
inv1 .Number = "Invoice-2546432";

if(!ctx.Invoice.Exists(i => i.Number == "Invoice-2546432")) // problem n°1
   ctx.AddObject(inv1 );
else 
   throws new Exception('blah blah');

2) An invoice has invoice lines:

InvoiceLine line = new InvoiceLine();
line .ID_INVOICE = in1.ID; // problem n°2
line .Label = "Line 1";
line .Ammount = 5.3;
ctx.AddObject(line );

3) Finally :

ctx.SaveChanges();

So that, if everything went well, I have something like this :

   Table INVOICE
=====================
ID  |      NUMBER
_____________________
 0  |  Invoice-2454876
_____________________
 1  |  Invoice-2487432
_____________________
 2  |  Invoice-2546432


           Table INVOICE_LINE
=========================================
ID  |  ID_INVOICE  |   LABEL   |  AMOUNT 
_________________________________________
 0  |       0      |   Line 1  |   2.6    
_________________________________________
 1  |       0      |   Line 2  |   7.6    
_________________________________________
 2  |       1      |   Line 1  |   7.6    
_________________________________________
 3  |       2      |   Line 1  |   8.6    
_________________________________________

As mentioned in the comments, there are two problems :

Problem n°1:

The existence test always returns false because it checks only in the database and not in the context itself where I just added the new object.

Problem n°2:

Because the invoice is only added to the context and not in the database yet, I don't have the future ID of the invoice. So I can't set it to the invoice line.

1) Do you know how to do that, basically, a secure way to add data onto the database ? I am developing a financial application and I want to make sure that there is no corrupted data inserted into the database (example : if inserting an invoice line failed, I want the whole invoice not to be inserted into the database)

2) I didn't find any design pattern related to that kind of thing. Do you know one, by any chance ?

3) I am not sure to have well understood context objects in the entity framework : Here's my understanding :

A context is (basically) a connection to the database. When I add an entity, it is saved somewhere in memory and inserted into the database once I call SaveChanges(). Is this correct ? Do you know any website or book covering in detail how the ADO /entity framework works ?

Answer 1

It seems that you are still thinking in a sql or record mindset - EF is an ORM and does much of the low-level housekeeping that you have described.

Ideally, since it seems clear that there is a 1:many relationship between Invoice and InvoiceLine, you should model this 1:N relationship in EF and then let EF worry about fixing / assigning the identity column, i.e.:

Invoice invoice = new Invoice();
invoice.InvoiceLines.Add(new InvoiceLine() { ... set fields here })
... add more details

// Now add just the invoice to the context. The lines will be added automatically
ctx.AddObject(invoice);
ctx.SaveChanges();

To answer your second question, you can consider using TransactionScope() to aggregate several database operations under a single unit of work.

To answer your last question - You shouldn't think of the context as a database connection - it is a higher level concept which manages the state of entities which are managed by it.

Also note that SaveChanges() by itself should ensure that all changes to the context will be persisted under a transaction, so you typically don't need additional transaction control if you are using a single context.

These SO posts here and here should help?

Answer 2

To answer your sec question you can add an extra field to invoice table like Status as bit. If all of your invoice lines saved without any exception finally you update the related invoice and set the Status = true.

using TransactionScope makes the logic very complex. transactionscope isn't intelligent enough to figure everything out.