System.Data.SQLite

Hello,

while programming a roleplay related database, i got a "Error preparing CommandDefinition" Exception for what should be a valid EF Query. The whole trace alongside the relevant table definitions is found at the end of this post.

At the moment i'm using System.Data.SQLite 1.0.65.0 with the corresponding System.Data.SQLite.Linq DLLs integrated in VS2008. As i looked at the generated SQL for the working version of the query, it was already quite large and complicated. So it may be related to that.

I had no problems whatsoever with the generated EntityModels. The whole database is represented exactly as planned and everything else works. I have whipped up a simplified UML Diagram for the relevant database portion, to explain the table relations.

<-Click to enlarge

The exception occurs when i try to call this property on my main view model:

        private CthulhuDatabase _db = new CthulhuDatabase();
        private ReadOnlyCollection<ViewModel.CharacterViewModel> _creatures;

        /// <summary>
        /// Gets all creatures.
        /// </summary>
        public ReadOnlyCollection<ViewModel.CharacterViewModel> Creatures
        {
            get
            {
                if (_creatures == null)
                {
                    try
                    {
                        var character = _db.Character
                            .Include("NPC.Creature.InvestigationalInfo.Ability") //this line produces the exception.
                            .Include("CharacterAbility.Ability")
                            .Where(c => c.NPC != null && c.NPC.Creature != null)
                            .AsEnumerable() // Needed workaround since otherwise only parameterless constructors are accepted.
                            .Select(e => ViewModel.CharacterViewModel.CreateModel(e)) //returns the correct view model.
                            .OrderBy(m => m.Type)
                            .ThenBy(m => m.SortName)
                            .ToList()
                        ;

                        _creatures = new ReadOnlyCollection<Keeper_Toolset.ViewModel.CharacterViewModel>(character);
                    }
                    catch (Exception e)
                    {
                        ExceptionMessaging.DisplayException(e);
                    }
                }

                return _creatures;
            }
        }

As noted the call to Include("NPC.Creature.InvestigationalInfo.Ability"), produces the exception. It seems to be a problem with the depth, since the CommandDefinition builds when i shorten it to Include("NPC.Creature").

As for now, that poses no real problem, since the view models i wrote check the needed references in the database models and lazy load them if needed. The performance is acceptable, but the lazy loading may slow down the program in the future. So it would be really nice, if that eager loading problem could be solved somehow,

Best regards,

Schuck

Addendum 1:
The whole exception:
######################
# EXCEPTION OCCURRED #
######################
Exception occured at Mittwoch, 3. Februar 2010; 17:04:23;
In System.Data.EntitySystem.Data.SQLite.Linq

;
In Method: EntityCommandDefinition..ctorSqlChecker.Visit

;
With the following message: Fehler bei der Vorbereitung der Befehlsdefinition. Details finden Sie in der internen Ausnahme.apply expression

;
and the following trace:    bei System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   bei System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   bei System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   bei System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
   bei System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span)
   bei System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   bei System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   bei System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   bei System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   bei System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   bei System.Linq.OrderedEnumerable`1.<GetEnumerator>d__0.MoveNext()
   bei System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   bei System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   bei Keeper_Toolset.ToolsetModel.get_Creatures() in D:\Programming\c#\Keeper_s_Toolset\Keeper_Toolset\ToolsetModel.cs:Zeile 658.   bei System.Data.SQLite.SqlChecker.Visit(DbApplyExpression expression)
   bei System.Data.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   bei System.Data.SQLite.SqlChecker.VisitExpression(DbExpression expression)
   bei System.Data.SQLite.SqlChecker.Visit(DbProjectExpression expression)
   bei System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   bei System.Data.SQLite.SqlChecker.VisitExpression(DbExpression expression)
   bei System.Data.SQLite.SqlChecker.VisitBinaryExpression(DbBinaryExpression expr)
   bei System.Data.SQLite.SqlChecker.Visit(DbUnionAllExpression expression)
   bei System.Data.Common.CommandTrees.DbUnionAllExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   bei System.Data.SQLite.SqlChecker.VisitExpression(DbExpression expression)
   bei System.Data.SQLite.SqlChecker.Visit(DbSortExpression expression)
   bei System.Data.Common.CommandTrees.DbSortExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   bei System.Data.SQLite.SqlChecker.VisitExpression(DbExpression expression)
   bei System.Data.SQLite.SqlChecker.Visit(DbProjectExpression expression)
   bei System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   bei System.Data.SQLite.SqlChecker.Rewrite(DbQueryCommandTree tree)
   bei System.Data.SQLite.SqlGenerator.GenerateSql(DbQueryCommandTree tree)
   bei System.Data.SQLite.SqlGenerator.GenerateSql(SQLiteProviderManifest manifest, DbCommandTree tree, List`1& parameters, CommandType& commandType)
   bei System.Data.SQLite.SQLiteProviderServices.CreateCommand(DbProviderManifest manifest, DbCommandTree commandTree)
   bei System.Data.SQLite.SQLiteProviderServices.CreateDbCommandDefinition(DbProviderManifest manifest, DbCommandTree commandTree)
   bei System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
   bei System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)

Addendum 2:
The relevant Table definitions
CREATE TABLE IF NOT EXISTS [main].[AbilityType] (
    [Id]    integer    NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT,
    [Name]    text    NOT NULL DEFAULT '< New >',
    [Description]    text    NOT NULL DEFAULT '< Description >'
);

CREATE TABLE IF NOT EXISTS [main].[Ability] (
    [Id]    integer    NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT,
    [Name]    text    NOT NULL DEFAULT '< New >',
    [Description]    text    NOT NULL DEFAULT '< Description >'
);

CREATE TABLE IF NOT EXISTS [main].[Ability_Has_Type] (
    [LeftId]    integer    NOT NULL,
    [RightId]    integer    NOT NULL,
    CONSTRAINT [pk_Ability_Has_Type]
        PRIMARY KEY ([LeftId], [RightId]) ON CONFLICT IGNORE,
    CONSTRAINT [fk_Ability]
        FOREIGN KEY ([LeftId]) REFERENCES [Ability] ([Id])
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT [fk_AbilityType]
        FOREIGN KEY ([RightID]) REFERENCES [AbilityType] ([Id])
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS [main].[Character] (
    [Id]    integer    NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT,
    [Name]    text    NOT NULL DEFAULT '< New >'
);

CREATE TABLE IF NOT EXISTS [main].[CharacterAbility] (
    [LeftId]    integer    NOT NULL,
    [RightId]    integer    NOT NULL,
    [Rating]    text    NOT NULL DEFAULT '',
    [Used]    text    NOT NULL DEFAULT '',
    CONSTRAINT [pk_CharacterAbility]
        PRIMARY KEY ([LeftId], [RightId]) ON CONFLICT IGNORE,
    CONSTRAINT [fk_Character]
        FOREIGN KEY ([LeftId]) REFERENCES [Character] ([Id])
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT [fk_Ability]
        FOREIGN KEY ([RightID]) REFERENCES [Ability] ([Id])
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS [main].[NPC] (
    [Id]    integer    NOT NULL,
    [Description]    text    NOT NULL DEFAULT '< Description >',
    PRIMARY KEY ([Id]),
    FOREIGN KEY ([Id]) REFERENCES [Character] ([Id])
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS [main].[Creature] (
    [Id]    integer    NOT NULL ,
    [HitThreshold]    text    NOT NULL DEFAULT '',
    [StealthMod]    text    NOT NULL DEFAULT '',
    [AlertnessMod]    text    NOT NULL DEFAULT '',
    [Weapon]    text    NOT NULL DEFAULT '',
    [Armor]    text    NOT NULL DEFAULT '',
    [StabilityLoss]    text    NOT NULL DEFAULT '',
    PRIMARY KEY ([Id]),
    FOREIGN KEY ([Id]) REFERENCES [NPC] ([Id])
        ON DELETE CASCADE
        ON UPDATE CASCADE

);

CREATE TABLE IF NOT EXISTS [main].[InvestigationalInfo] (
    [LeftId]    integer    NOT NULL,
    [RightId]    integer    NOT NULL,
    [Information]    text    NOT NULL DEFAULT '',
    CONSTRAINT [pk_InvestigationalInfo]
        PRIMARY KEY ([LeftId], [RightId]) ON CONFLICT IGNORE,
    CONSTRAINT [fk_Creature]
        FOREIGN KEY ([LeftId]) REFERENCES [Creature] ([Id])
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT [fk_Ability]
        FOREIGN KEY ([RightID]) REFERENCES [Ability] ([Id])
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);