Execute a SQlite command with Entity Framework

Question

Hi everybody,

I use a SQLite database and Entity Framework (with .net framework 3.5). I'm trying to execute a simple SQL non query command to create a new table in this datase. My Entity Framework already contains the object model for this table : I just want to generate the corresponding table using a command.

(By the way, there is maybe a better way to do this. Any ideas someone :)

My problem is that I'm not able to execute any command, even the simple commands.

Here is my code :

EntityConnection entityConnection = new EntityConnection(entitiesConnectionString);
Entities db = new Entities(entityConnection);

DbCommand command = db.Connection.CreateCommand();
command.CommandText ="CREATE TABLE MyTable (Id int NOT NULL, OtherTable_Id nchar(40)         REFERENCES OtherTable (Id) On Delete CASCADE On Update NO ACTION, SomeData nvarchar(1024) NOT NULL, Primary Key(Id) );";
command.ExecuteNonQuery();

I got this error :

System.Data.EntitySqlException: The query syntax is not valid., near identifier 'TABLE', line 1, column 8.
   at System.Data.Common.EntitySql.CqlParser.yyerror(String s)
   at System.Data.Common.EntitySql.CqlParser.yyparse()
   at System.Data.Common.EntitySql.CqlParser.Parse(String query)
   at System.Data.Common.EntitySql.CqlQuery.Parse(String query, ParserOptions parserOptions)
   at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree)
   at System.Data.EntityClient.EntityCommand.MakeCommandTree()
   at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()
   at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition)
   at System.Data.EntityClient.EntityCommand.GetCommandDefinition()
   at System.Data.EntityClient.EntityCommand.InnerPrepare()
   at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)

It's seem to be a syntax error, but I can't figure where is the problem and how to resolve it. The entityConnection is ok because I can use any entities generated with EF.

I tried with another simple command, but it throw another exception :

DbCommand command = db.Connection.CreateCommand();
command.CommandText = "SELECT COUNT(Id) From OtherTable;";
int result = (int)command.ExecuteScalar();

And I got this error, witch is not the same, but may help :

System.Data.EntitySqlException: 'Groupe' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier, line 1, column 23.
   at System.Data.Common.EntitySql.CqlErrorHelper.ReportIdentifierError(Expr expr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertIdentifier(Expr expr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessAliasedFromClauseItem(AliasExpr aliasedExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClause(FromClause fromClause, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQuery(Expr expr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertRootExpression(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertGeneralExpression(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.CqlQuery.AnalyzeSemantics(Expr astExpr, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables)
   at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree)
   at System.Data.EntityClient.EntityCommand.MakeCommandTree()
   at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()
   at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition)
   at System.Data.EntityClient.EntityCommand.GetCommandDefinition()
   at System.Data.EntityClient.EntityCommand.InnerPrepare()
   at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)

Answer 1

Hi,

Why are you writing SQL commands against an Entity Collection? If you want to do that use the ADO.NET components for SQLite, otherwise use LINQ to Entities to query data from your SQLite database.

Answer 2

I think the problem may be that you are using EnitityConnection. Have you tried using OleDbConnection or OdbcDbConnection?

OdbcDbConnection entityConnection = new OdbcDbConnection(entitiesConnectionString); 
Entities db = new Entities(entityConnection); 

 DbCommand command = db.Connection.CreateCommand(); 
command.CommandText ="CREATE TABLE MyTable (Id int NOT NULL, OtherTable_Id nchar(40)         REFERENCES OtherTable (Id) On Delete CASCADE On Update NO ACTION, SomeData nvarchar(1024) NOT NULL, Primary Key(Id) );"; 
command.ExecuteNonQuery(); 

There's also an ADO.Net provider for SqLite located here: http://sourceforge.net/projects/sqlite-dotnet2/ I haven't used it, but it looks like you could use:

SQLiteConnection cnn = new SQLiteConnection(entitiesConnectionString);

Answer 3

Thank you for yours answers, you saved my day :)

I wanted to limit the number of connection and I used the current EntityConnection to check the database before trying to modify it. But I tried this code and it work fine right now :

SQLiteConnection connection = new SQLiteConnection(sqliteConnString);
connection.Open();

DataRow[] result = connection.GetSchema("Tables").Select("Table_Name = 'MyTable'");
if (result == null || result.Length == 0)
{                  
    SQLiteCommand cmd = new SQLiteCommand(
                        "CREATE TABLE MyTable (Id int NOT NULL, OtherTable_Id nchar(40) REFERENCES OtherTable (Id) On Delete CASCADE On Update NO ACTION, SomeData nvarchar(1024) NOT NULL, Primary Key(Id) );"
    , connection);

    cmd.ExecuteNonQuery();
}

connection.Close();