I am trying to use LINQ with System.Data.SQLite (version 1.0.64.0) in VS2008 (no SP), and am unable to insert a row into a table. The code below generates the error I am seeing. Besides the two columns that appear in the code and log, there is one more named CustomerKey which is the integer primary key for the table, and is modeled in my Customer class by the following property. (All other columns are "normal" data columns with only the Storage property specified.)
[Column(Storage="customerKey", IsPrimaryKey=true, IsDbGenerated=true, CanBeNull=false)]
public int CustomerKey
{
get { return this.customerKey; }
set { this.customerKey = value; }
}
When I set IsDbGenerated=false above, the code below executes without an exception, although CustomerKey is forced to zero in the inserted row, which is not what I need.
With IsDbGenerated=true, an exception for a SQLite syntax error is thrown. The column is omitted from the INSERT as it should, but then LINQ issues the SELECT for SCOPE_IDENTITY (a T-SQL function) to retrieve the generated value for updating my Customer object. I think it should be using SQLite's last_insert_rowid function instead. Could it be that the SQLite version of the LINQ provider is not coming into play for some reason?
I've done a ton of digging before resorting to this post, and haven't found anything to suggest doing more than what I'm doing now. Thanks for looking at this.
private void testSQLiteLinqInsert()
{
DataContext picSureDB = null;
string results = "";
try {
SQLiteFactory factory =
(SQLiteFactory)DbProviderFactories.GetFactory("System.Data.SQLite");
using(SQLiteConnection sqLite = (SQLiteConnection)factory.CreateConnection()) {
sqLite.ConnectionString =
@"Data Source=C:\PicSureBackend\Database\PicSureBackend.Test.db";
sqLite.Open();
picSureDB = new DataContext(sqLite);
picSureDB.Log = new StringWriter(); //### LINQ DEBUG
Table<Customer> customerTable = picSureDB.GetTable<Customer>();
Customer customer = new Customer();
customer.Name = "Monroe Jr. High";
customer.AdminName = "Dr. Bakic";
customerTable.InsertOnSubmit(customer);
picSureDB.SubmitChanges();
results += "Monroe JHS added to table Customer\n";
results += picSureDB.Log + "\n";
MessageBox.Show(results + "Database insert succeeded!");
}
}
catch(Exception exception) {
if(picSureDB != null)
results += "Logged SQL:\n\n" + picSureDB.Log + "\n"; //### LINQ DEBUG
results += "ERROR! " + exception.Message;
File.WriteAllText(@"c:\picsurebackend\error.txt", results);
}
}
Logged SQL:
INSERT INTO [Customer]([Name], [AdminName]) VALUES (@p0, @p1)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [Monroe Jr. High]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [Dr. Bakic]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
ERROR! SQLite error
near "SELECT": syntax error