5

Some background: We have developed an ASP.NET MVC port of a client's MS Access application. The Application used/uses MySQL as the data store via ODBC. What we're running into is that if the client uses the MS Access application to capture a new record, and soon after the MVC application captures a new record, the MVC application's record seems to overwrite the last record created by the Access application, as well as creating a second record.

So in effect, the Access application's record would look like this at first

ID | NAME | SURNAME
1  | joe  | Schmoe

but then, when the MVC application creates its one record, this happens:

ID | NAME  | SURNAME
1  | james | smith
2  | james | smith

The MVC application seems to overwrite both the last record captured by Access, as well as creating a new record.

We use Entity Framework 5. All we do to save the record is

var record = new Person(){NAME = "james", SURNAME = "smith"};
db.People.Add(record);
db.SaveChanges();

This type of thing only happens when the client has used the Access application. And it only happens when the last added record is from the Access application and the new record is from the MVC/EF application.

Any input would be appreciated.

When capturing a sequence of records via Access, this does not happen. When capturing a sequence of records via Entity Framework, it does not happen. When capturing first via Entity Framework and THEN Access, this does not happen. It only happens when the sequence is Access first, Entity Framework second.

Captain Kenpachi
  • 6,960
  • 7
  • 47
  • 68
  • Is ID defined as a primary key in the database? Also, are you sure you don't do anything else in the EF context before adding the new record? – Alex Paven May 11 '17 at 09:43
  • Yes, it's an auto-incrementing PK. No, we're doing nothing else. I simply create a new record in EF, add it, and then call SaveChanges(). It works 100% from either Access or EF, but not when using them as described above. – Captain Kenpachi May 11 '17 at 10:00
  • 1
    I would enable the query log to see the exact queries EF issues under those circumstances, maybe you can get a hint that way. https://serverfault.com/questions/358978 – Alex Paven May 11 '17 at 10:03
  • 1
    Have you specified that the key is `[DatabaseGenerated(DatabaseGenerationOption.Identity]`? – ste-fu May 19 '17 at 12:27
  • Yes. [11 more characters needed] – Captain Kenpachi May 19 '17 at 12:51
  • Do you declare `ID` property as Identity in class definition? https://forums.asp.net/t/1992323.aspx?Auto+Increment+Identity+does+not+work+in+Entity+Framework – Stoleg May 19 '17 at 16:30
  • Can you post generated sql ? – Miguel May 22 '17 at 07:33
  • Can you please post table and class definitions? – Stoleg May 22 '17 at 08:33

4 Answers4

1

Sorry but there is not enough space to write this in a comment

Some suggestions:

  • The code you posted is not enough to understand the issue. At least you could write all the code that creates/use db (is it just a using before the lines you posted?)

  • Probably the error is when you save from EF. You could start inserting a breakpoint in every SaveChanges even the ones that you are sure that is not called

  • Other thing you can do is to swap MySQL with SQL Server (your app works perfectly also with SQL Server the hardest work is to change the links from Access, EF already works) and check if you have the same issue.

bubi
  • 6,414
  • 3
  • 28
  • 45
  • It's not a code issue. When using one OR the other application, this does not happen. It only happens when the last record was created via the Access application and the next record is created via the web site. Which leads me to believe that it is a problem with how access manages autonumbers Or something. Both applications are pretty mature. They just don't work well together. – Captain Kenpachi May 16 '17 at 15:17
  • 2
    If the DBMS is MySQL there is no tricks that Access can use to write and read records. It can only run DML and SQL... – bubi May 16 '17 at 15:27
  • Access does not "manage auto numbers" My SQL does. Note, that you have issues only when EF runs after Access, not other way round. – Stoleg May 23 '17 at 12:48
  • @Stoleg, what do you mean for "auto numbers"? If you mean auto increment, Access does but in this case it does not need to because the backing store is MySQL and Access can only run INSERT queries (without specifing the Id) to add records. – bubi May 24 '17 at 07:12
  • @bubi I was using your term. Correct setup is when `AUTO_INCREMENT` is specified as a property in MySQL table. In this case database manages ID: it adds one to previous max value and inserts it into ID column, so you don't need to specify this. It also does not allow inserting duplicate values into this column under normal circumstances. Please post table and class definitions, for better guidance. – Stoleg May 24 '17 at 09:10
1

How long are you persisting your context for? I'm hoping that it's short lived, but suspect it might not be?

Have you reviewed this question? Entity Framework Refresh context?

Community
  • 1
  • 1
Steve S
  • 614
  • 9
  • 20
  • The context is scoped to the lifetime of the method, which is in the business logic layer. The process goes like this: web request -> controller action -> *new business().saverecord()* -> return View(); – Captain Kenpachi May 17 '17 at 08:22
  • Does the problem occur if Access creates a record *before* the web request initiates? – Steve S May 17 '17 at 08:24
  • I can't tell, honestly. The access application would have the NEW record open for a few minutes, whereas the web application takes one or two seconds to create a record. Is this enough info? – Captain Kenpachi May 17 '17 at 08:27
  • 1
    Honestly, I'd be surprised if the context being out of sync is the problem, but I'd definitely be looking at refreshing EF's knowledge of the data *immediately* before the add. Have you tried implementing any of the refresh suggestions? – Steve S May 17 '17 at 08:44
  • Also, have you used SQL Profiler? Similar to @bubi 's point about logging the SQL calls, Profiler will tell you *exactly* what's being invoked on the database. – Steve S May 17 '17 at 08:45
  • Yeah, profiler says I'm doing an insert, then calling SELECT @@IDENTITY (or the MySQL equivalent anyway). – Captain Kenpachi May 17 '17 at 08:50
  • I have optimised my usage of the dbcontext slightly, because it couldn't hurt. Now for another round of testing. – Captain Kenpachi May 17 '17 at 08:51
1

Please try following solution:

For the PK Property (such as ID), make sure StoreGeneratedPattern=Identity

This is taken from MySQL support forum discussion.

Stoleg
  • 8,972
  • 1
  • 21
  • 28
1

Please allow auto increment column in your SQL table and Table Definition should be look like as below.

Because entity works based on primary key. So, you must define [Key] attribute. DatabaseGenerated is used for auto increment.

You need to add two references as below.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID{ get; set; }
Rahul
  • 427
  • 4
  • 20