0

I'm new to using Tables with Linq in classes in C#, So forgive me if my terminology is incorrect! Also relatively new to Databases in general, so let me know if this is a basic Database concept I have overlooked.

I have a "person" class similar to the one below and I've defined a column called Id which is the primary key - i.e. it has to be unique.

I also specified isDbGenerated = true so that I don't have to manually specify this ID.

[Table]
public class Person
{

    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id;

    ...
}

This works great when I add rows to my table.

When I delete rows from my table, it leaves gaps in the sequence of row IDs. This is fine.

The problem arises when I try saving out my data and re-loading it into the table. To save data, I manually write out the information I need to a text file.

When I re-load the data into my table, the automatically generated ID overrides the original ID I had saved the information out with. This causes issues with my larger database which refers to these rows by their IDs.

For example.

My database contains These rows:

  • Id, Name
  • 1, John
  • 2, Jim
  • 4, Mark (note #3 is missing because I deleted it at some point)
  • 5, David

I save these out to a text file. I load them back into an empty table from the text file at another time.

  • 1, John
  • 2, Jim
  • 3, Mark
  • 4, David

The IsDBGenerated feature causes these numbers to be sequential.

What is the best way for me to set this up so that I can re-load data into the table while retaining the unique IDs?

EDIT: I figured that using an auto generated key isn't making my life easier.

What is the best way to self-generate the next primary key to use? a. Just increment the number after the last entry? b. generate a GUID? c. existing function?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Psykojello
  • 49
  • 4

1 Answers1

0

You're not really missing any big concepts I don't think. You're bumping up against an inherent issue one can run into with auto generated keys.

In SQL Server (are you using SQL Server?) you can use a feature called identity insert which can be off (the database auto numbers for you, good for inserting new records) or on (you have to supply the numbers, good for loading from a file).

The meanings of on/off are easy to get confused so read the documentation SET IDENTITY_INSERT.

As for how to do this in the context of your particular ORM (is it LINQ to SQL, Entity Framework?) I'm not exactly sure - my answer to this problem in practice has been to work around it (i.e. not put myself in a situation where I'm storing auto generated IDs in a text file that I need to load back in the context of my application logic).

Aaron Anodide
  • 16,906
  • 15
  • 62
  • 121
  • I'm using a local access database (This is a windows phone app). Your suggestion makes sense. I should probably turn off auto-generated IDs and generate the ID myself. Let me poke around to see if there is an equivalent "identity insert" feature in my database. – Psykojello Jan 29 '13 at 00:07
  • according to http://stackoverflow.com/questions/7691253/equivalent-of-set-identity-insert-off-in-access, you just have to use straight SQL when inserting. This may seem "wrong" if you're working with a higher level API, but once you see the finished product and it works and is isolated, you will move on and be glad you didn't do something more complicated (this my personal perspective on "cheating" when using frameworks) – Aaron Anodide Jan 29 '13 at 01:12
  • This is how I add items back to the table: `Person p = new Person();` `p.import(dataFromTextFile); ` `People.InsertOnSubmit(p);` I'm not sure how to use straight SQL commands in the Windows Phone SDK (I don't have the System.Data.Sql library) Could you give me an example of the format? – Psykojello Jan 29 '13 at 10:26
  • sorry Windows Phone is outside of my expertise... good luck though – Aaron Anodide Jan 29 '13 at 10:27
  • Thank you for your help though, you've got me looking in the right direction! – Psykojello Jan 29 '13 at 19:22