I am trying to insert data in bulk, database is MySQL connection is done using Devart LinqConnect. Here is a code:
DataContext db = new DataContext();
List<XYData> XYDList = new List<XYData>(); // Data Type
List<xydata> xyToBeInsrted = new List<xydata>(); // Database table
XYDList = XYData.genXYData(12, 1234); // Generates 12 example records
foreach (XYData oneXY in XYDList)
{
// Create clear row representation
xydata xy_row = new xydata();
// Assign data from object
xy_row.id = oneXY.Id;
xy_row.Batchid = (int)oneXY.BatchId;
xy_row.Ch = oneXY.Channel;
xy_row.Value = oneXY.Value;
xy_row.Pos = (int)oneXY.Position;
xy_row.Dtm = oneXY.Time;
// Add to list of rows to be inserted
xyToBeInsrted.Add(xy_row);
}
db.xydatas.InsertAllOnSubmit<xydata>(xyToBeInsrted);
db.SubmitChanges();
Last line gives an error "Entity with the same key '0' already added." When I lower number of items to be generated to 1 then it works. Anything above 1 gives error.
Table has set Auto Increment on record Id field.
Trying to solve that for 2 hours without success.
EDIT: Data Class:
public class XYData
{
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int Id { get; set; }
public int BatchId { get; set; }
public int Channel { get; set; }
public String Value { get; set; }
public double Position { get; set; }
public DateTime Time { get; set; }
private static Random rand = new Random();
public XYData(int channel = 0, string val = "")
{
Channel = channel;
Value = val;
Position = 0;
Time = DateTime.Now;
}
public static List<XYData> genXYData(int howMany, int batchId)
{
List<XYData> _allXYData = new List<XYData>();
for (int i = 0; i < howMany; i++)
{
XYData _singleXY = new XYData();
_singleXY.BatchId = batchId;
for (int j = 64 * (1 << i); j > 0; j--)
{
uint k = (uint)rand.Next(int.MaxValue);
_singleXY.Value += k.ToString("X8");
}
_allXYData.Add(_singleXY); // Add to list
}
return _allXYData; // Return list of generated data.
}
}
Something like this may be needed on your model definition -
http://www.devart.com/linqconnect/docs/PrimaryKeyMapping.html