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