[QUESTION] Change datatype of the primary key

[DaNeubi]

Hey there,

I really love your database but just recently can into a problem with my initial setup of the database.

This is my current table structure:

{
  "_id": {"$numberLong": "1"},
  "From": {"$numberLong": "123"},
  "To": {"$numberLong": "124"},
  "Reason": "No permission to execute the command",
  "Number": {"$numberLong": "31"},
  "WarningType": "Warning",
  "Time": {"$date": "2020-12-10T19:56:15.8480000Z"},
  "GuildId": {"$numberLong": "1234"}
}

I'd love to change the datatype from System.Int64 _id to System.String

I'm aware of How do I migrate data structures ? #524, but this issue is a bit old and I can't translate it on my own into the new engine system.

Thanks ahead for any help.

[fubar-coder]

I see two possible (untested) ways:

Using a temporary collection

var collectionSource = db.GetCollection("CollectionName");
var collectionTemp = db.GetCollection("CollectionName_Temp");
var documents = collectionSource.FindAll();
foreach (var document in documents) {
  document["_id"] = new BsonValue(document["_id"].AsInt64.ToString());
  collectionTemp.Insert(document);
}
db.DropCollection("CollectionName");
db.RenameCollection("CollectionName_Temp", "CollectionName");

1. Copy the data (with the new ID column) to a temporary collection
2. Delete the old collection
3. Rename the temporary collection to the name of the old collection

Using BsonDocument

Use the "untyped" LiteDbCollection, e.g.:

var collection = db.GetCollection("CollectionName");
var documents = collection.FindAll().ToList();
foreach (var document in documents) {
  collection.Delete(document["_id"]);
  document["_id"] = new BsonValue(document["_id"].AsInt64.ToString());
  collection.Insert(document);
}

[DaNeubi]

Thanks! I used your second method and can confirm it works properly.

You saved me. Thank you so much.

[radev8]

This worked for me. The primary key is an Int64 Public Class ClientRec Public Property ID As Int64 Public Property Name As String End Class

Sub Main() Dim FSDb = New LiteDB.LiteDatabase(DBFileStream) Dim Clients = FSDb.GetCollection(Of ClientRec) Dim Client.EnsureIndex(Of Int64)(Function(x) x.ID) End Sub