mardi 30 juin 2015

Cannot insert multiple items into SQLite database

The DB has a primary table for batch data, each Batch can have zero or more Samples. They are linked on Batch.BatchID == Samples.FK_BatchID. The Classes for these tables are shown below.

I can add a value to Batches - the autoincrement BatchID updates as expected. I can add a single value to the Samples. I cannot add multiple values to the Samples table and get an exception with

Additional information: Cannot add an entity with a key that is already in use.

If I set STOP to '1' then the db gets a new Batch with a properly referenced new Sample. What must I do to allow multiple Samples to be added for a single Batch. In addition, I'd ideally like to use the same context and a single 'SubmitChanges()' operation - but let me walk before I run.

Here is the code I've attempted:

 Int64 newbatchID = 0;
 using (var context = new Data.BatchContext(connection))
 {    // This 'chunk' work fine and the newbatchID gets the new value
      context.Log = Console.Out;
      Data.Batches newBatch = new Data.Batches {
           Created = System.DateTime.Now.ToString("u"),
           Title = "New Title",
           Varietal = "Waltz"
      };

      // Return the Batch Id if necessary...
      var qs = from c in context.sqlite_sequence
               where c.name == "Batches"
               select c.seq;
      context.Batches.InsertOnSubmit(newBatch);
      context.SubmitChanges();
      newbatchID = qs.ToList().First();
 }

 // Use the new batch ID to submit a load of new samples
 int STOP = 2;     // PROBLEM. If Stop is not 1 the following fails
 using (var context = new Data.BatchContext(connection))
 {
      context.Log = Console.Out;
      List<Data.Samples> samplelist = new List<Data.Samples>();
      for (var i = 0; i < STOP; ++i)
      {    // Just to get different time values
           System.Threading.Thread.Sleep(500);
           samplelist.Add(
                new Data.Samples {
                     // Commenting out the FK_BatchID doesn't help
                     FK_BatchID = newbatchID,
                     Created = System.DateTime.Now.ToString("u"),
                     ImageURI = String.Format("./Path/Img_{0}.jpg", i)
                });
           }
           context.Samples.InsertAllOnSubmit(samplelist);
           context.SubmitChanges();
      }

Data base classes

[Table(Name = "Batches")]
public class Batches
{
    public virtual ICollection<Batches> batches { get; set; }
    public Batches()
    {
        batches = new HashSet<Batches>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "BatchID", IsPrimaryKey = true)]
    public Int64? BatchID { get; set; }

    // Batch creation date
    [Column(Name = "Created")]
    public String Created { get; set; }

    // Other String columns the same as Created
    ...
}

[Table(Name = "Samples")]
public class Samples
{
    public virtual ICollection<Samples> samples { get; set; }
    public Samples()
    {
        samples = new HashSet<Samples>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "SampleID", IsPrimaryKey = true)]
    public Int64? SampleID { get; set; }

    // Foreign key to the Batches Table
    private EntityRef<Batches> _batch = new EntityRef<Batches>();
    [Association(Name = "FK_BatchID", IsForeignKey = true, 
                 Storage = "_batch", ThisKey = "FK_BatchID", 
                  OtherKey = "BatchID")]
    public Batches Batches
    {
        get { return _batch.Entity; }
        set { _batch.Entity = value; }
    }
    // Foreign key table entry
    [Column(Name = "FK_BatchID")]
    public Int64? FK_BatchID { get; set; }

    // Date the image was processed by the batch
    [Column(Name = "Created")]
    public String Created { get; set; }

    // Other String columns etc
    ...
 }

Aucun commentaire:

Enregistrer un commentaire