System.Data.SQLite

anonymous added on 2015-06-22 08:38:30: (text/x-fossil-plain)

The EF to SQL translator includes a wrong ";" into SQL when the tables primary key consists of more than 1 field.

I just added a single entity to the table (EntitySet) and called SaveChanges(), below is the log created by a delegate attached to DbContext.Database.Log property:

INSERT INTO [CategoryValues]([CategoryId], [Index], [Value], [IsCustom], [XmlId], [Tooltip], [Icon], [IsDefault], [SortOrder])
 VALUES (@p0, @p1, @p2, @p3, NULL, NULL, NULL, @p4, @p5);
SELECT [rowid]
FROM [CategoryValues]
WHERE last_rows_affected() > 0 AND [CategoryId] = @p0;
 AND [Index] = @p1;
-- @p0: '3' (Type = Int64)
-- @p1: '0' (Type = Int16)
-- @p2: '*' (Type = String)
-- @p3: 'False' (Type = Boolean)
-- @p4: 'True' (Type = Boolean)
-- @p5: '0' (Type = Int32)
-- Executing at 2015-06-22 10:15:42 +02:00

notice the trailing ";" at the line before the line starting with " AND" which doesn't belong there, and the problem did not exist with v94 or v96.

Callstack + message:
System.Data.SQLite.SQLiteException
       HResult=-2147467259
       Message=SQL logic error or missing database
near "AND": syntax error
       Source=System.Data.SQLite
       ErrorCode=1
       StackTrace:
            at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
            at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
            at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
            at System.Data.SQLite.SQLiteDataReader.NextResult()
            at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
            at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()

anonymous added on 2015-06-22 08:45:12:

Table definition:

CREATE TABLE [CategoryValues] (
    [CategoryId] integer NOT NULL REFERENCES [Categories] ([CategoryId]),
    [Index] smallint NOT NULL,
    [Value] nvarchar(100) NOT NULL,
    [IsCustom] bit NOT NULL DEFAULT 0,
    [XmlId] nvarchar(64),
    [Tooltip] ntext,
    [Icon] image,
    [IsDefault] bit NOT NULL DEFAULT 0,
    [SortOrder] int NOT NULL DEFAULT 0,
    PRIMARY KEY ([CategoryId], [Index])
);

anonymous added on 2015-06-22 15:39:44: (text/x-fossil-plain)

Maybe I forgot an implementation detail which explains why it does "SELECT [rowid]":

I "faked" the always implicitly available rowid column into the edmx file:

SSDL:
        <EntityType Name="CategoryValues">
          <Key>
            <PropertyRef Name="CategoryId" />
            <PropertyRef Name="Index" />
          </Key>
          <Property Name="rowid" Type="integer" Nullable="true" StoreGeneratedPattern="Identity" />
[...]

C-S mapping:
          <EntitySetMapping Name="CategoryValues">
            <EntityTypeMapping TypeName="DoPiMo.CategoryValue">
              <MappingFragment StoreEntitySet="CategoryValues">
                <ScalarProperty Name="CategoryValueId" ColumnName="rowid" />
[...]

But this worked like a charm using 1.0.94/96

mistachkin added on 2015-06-22 22:35:23: (text/x-fossil-plain)

Found the root cause of the bug.  Working on fix now.  Thanks for the report.

mistachkin added on 2015-06-23 00:57:27: (text/x-fossil-plain)

Fixed on trunk via check-in [01a3da88e7].

anonymous added on 2015-06-23 12:59:23: (text/x-fossil-plain)

Thanks, that was quick.

Are there any chances for a 1.0.97.1 NuGet build before August?

mistachkin added on 2015-06-23 16:44:35: (text/x-fossil-plain)

It's unlikely, as it would not just be the NuGet packages.  I would have to do an
entire release.