Is It Possible To Dispose a LinQtoSQL Object from the Dataclass to Counter Data Concurrency?

Question

this is When a new record is inserted From Client A into server :

var Invoice = new Invoice();

foreach (DataGridViewRow  item in DataGridView1.Rows)
{
    Invoice.InvoiceDetails.Add(new InvoiceDetail
    {
        Description = txt1.Text,
        TotalPrice = txt2.Text
    });

}
dc.Invoices.InsertOnSubmit(Invoice);
dc.SubmitChanges();

ok now imagine client 2 is going to edit this record.

 var Invoice = dc.Invoices.First(p => p.ID == _InvoiceID);
 dc.InvoiceDetails.DeleteAllOnSubmit(Invoice.InvoiceDetails);
 foreach (DataGridViewRow item in DataGridView1.Rows)
 {
     Invoice.InvoiceDetails.Add(new InvoiceDetail
     {
         Description = "Description",
         TotalPrice = "10000000"
     });

 }
 dc.SubmitChanges();

now here is the thing.Client one has the first 2 invoices that IT inserted itself still residing in its DataClass.
meaning the two clients have two different DataClasses Localy on RAM.
problem starts when client 1 Goes For another edit AGAIN :
When Selecting the Invoice again using :

var Invoice = dc.Invoices.First(p => p.ID == _InvoiceID);

currently this invoice on client 1 has the 2 it added itself to the db,still on its dc.so with this select it will also add the 2 from the DB that the client 2 added from the server.
so the problem is client 1 will show you 4 Details instead of 2,containing 2 from the Server system,and 2 from its local DataClass.
i just got informed that its called Data Concurrency.
Please note that the data context is static and public and is kept alive through the whole applications lifetime. I know this is wrong and it has to be kept short but the data is massive and i cant just make a new data context on every form. Looking for solutions as im already switching to entity framework for the fix but i need to apply a solution to the current state of the application untill im done switching to the entity framework.
Also one more thing, the app creates its database so sprocs are not an option as t linq cant create em with createdatabase().

You need to show code. You need to make the effort of producing a [mcve]. — Enigmativity, Apr 17 '16 at 14:21
The thing that you're doing wrong is the "data context is static and public and is kept alive through the whole applications lifetime." You should only open the context when needed and close it as soon as possible. I think that's pretty much what Peter is suggesting for you. — Enigmativity, Apr 18 '16 at 00:33
@Enigmativity i have already stated that 1- im switching to entity frame work and 2- i already know its a mistake and 3- i want a solution not the problem :/ obviously i cant keep its life short in my scenario where i have AT LEAST 600 thousand records — Niklas, Apr 18 '16 at 00:36
"obviously i cant keep its life short in my scenario where i have AT LEAST 600 thousand records" - Why not? — Enigmativity, Apr 18 '16 at 01:01
@Enigmativity because i have over 200 tables and forms and it takes much more to create a dc than it should. Performance. — Niklas, Apr 18 '16 at 01:03
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109391/discussion-between-enigmativity-and-virgil). — Enigmativity, Apr 18 '16 at 02:56

score 2 · Accepted Answer · edited May 23 '17 at 12:16

2

You can stop your DataContext getting stale if you instantiate it as required:

using (Entities dc = new Entities())
{
  var Invoice = dc.Invoices.First(p => p.ID == _InvoiceID);
  dc.InvoiceDetails.DeleteAllOnSubmit(Invoice.InvoiceDetails);
  foreach (DataGridViewRow item in DataGridView1.Rows)
  {
     Invoice.InvoiceDetails.Add(new InvoiceDetail
     {
         Description = "Description",
         TotalPrice = "10000000"
     });

  }

  dc.SubmitChanges();
}

For some background, see, for example:

Instantiating a context in LINQ to Entities

Working with DbContext

Editted after explanation about static DbContext

The alternative is to put all SQL in stored procedures, for both clients; then you are not relying on the state of the DbContext.

edited May 23 '17 at 12:16

Community

1
1

answered Apr 17 '16 at 20:36

Peter Bill

508
3
12

Please read the end of the question again it was left out when i edited the question the first time. – Niklas Apr 17 '16 at 20:46
Stored procedures do come with downsides, basically the maintaince associated with your basic CRUD operation. Let's say for each table you have an Insert, Update, Delete and at least one select based on the Primary key, that means each table will have 4 procedures. Now take a decent size database of 400 tables, and you have 1600 procedures! And that's assuming you don't have duplicates which you probally will.i was hoping for something different :s – Niklas Apr 17 '16 at 21:40
thers also another problem with sproc and thats the problem of creating it with createdatabase() while it executes :s – Niklas Apr 17 '16 at 21:48

Is It Possible To Dispose a LinQtoSQL Object from the Dataclass to Counter Data Concurrency?

1 Answers1