无法将多个项目插入SQLite数据库 [英] Cannot insert multiple items into SQLite database

问题描述

数据库具有用于批处理数据的主表，每个批处理可以具有零个或多个样本.它们链接在Batch.BatchID == Samples.FK_BatchID上.这些表的类如下所示.

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.

我可以为Batchs添加一个值-自动增量BatchID会按预期更新. 我可以在样本"中添加一个值. 我无法将多个值添加到Samples表中并获得

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.

如果我将STOP设置为"1"，则数据库将获得一个带有正确引用的新Sample的新Batch.我必须怎么做才能为一个批次添加多个样品.另外，理想情况下，我想使用相同的上下文和单个"SubmitChanges()"操作-但让我在运行前走动.

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.

这是我尝试的代码:

 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();
      }

数据库类

[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
    ...
 }

---

在尝试对Column装饰的修改失败(即IsDbGenerated和/或DbType等)之后，我实现了使用下面显示的sqlite_sequence的工作.这用于查找各种表ID的值，然后在创建新对象时使用这些值.然后，所有这些都可以包装在一个交易中

---

After unsuccessfully trying modifications to the Column adornment (i.e. IsDbGenerated and/or DbType etc) I've implemented a work around using sqlite_sequence shown below. This is used to find values for the various table ID's which are then used when creating the new objects. These can then all be wrapped up in a single transaction

 // Start by getting the latest autoincrement values
 var aiQuery = (from c in context.sqlite_sequence
                select c).ToList();

 Int64 batchId = 1 + aiQuery.Find(q => q.name == @"Batches").seq;
 Int64 sampleId = 1 + aiQuery.Find(q => q.name == @"Samples").seq;

 // Create objects etc
 ...

 // Then update the db once only
 context.Batches.InsertOnSubmit(newBatch);
 context.SubmitChanges();

如果没有更好的解决方案，那么我会接受这个答案

If there are no better solutions then I'll accept this as the answer

推荐答案

在没有适当解决方案的情况下，这是我的解决方法.

In the absence of a proper solution here is my workaround.

使用sql_sequence建立自动增量ID值，然后在要创建的对象中显式使用该值.

Use sql_sequence to establish autoincrement Id values which are then used explicitly in the objects being created.

var aiQuery = (from sequence in sqlite_sequence
               select sequence).ToList();

BatchId = 1 + aiQuery.Find(q => q.name == @"Batches").seq;
SampleId = 1 + aiQuery.Find(q => q.name == @"Samples").seq;

为方便起见，我将其放在我的类中，它们来自Linq.DataContext

For convenience I placed this in my class deriving from Linq.DataContext

然后可以根据以下伪代码在单个事务中添加多个插入.

Then multiple inserts can be added in a single transaction as per the following pseudo code.

Batch batch = new Batch { id = BatchId, ... }
foreach (int i=0; i<n; ++i)
{
   batch.AddSample(new Sample { id = SampleId+i, batchid = BatchId, ... });
}

// Update db
context.Batches.InsertOnSubmit(batch);
context.SubmitChanges();

这篇关于无法将多个项目插入SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！