How to correctly use Linq-to-SQLite?

Question

Hello.

I have downloaded and installed linq-to-sqlite from http://sqlite.phxsoftware.com/ But the following test code:

[ Table( Name = "items" ) ]
public class MyItem
{
  [ Column( IsPrimaryKey = true ) ]
  int MyId;
}

public class MyDb : DataContext
{
  public MyDb( IDbConnection c ) : base( c ) {}
  public Table< MyItem > myItems;
}

var connection = new SQLiteConnection( "Data Source=my.db;" )
var database = new MyDb( connection );
database.CreateDatabase();

Fails in "CreateDatabase" with exception:

SQLite error
near "DATABASE": syntax error

What i'm doing wrong?

Answer 1

It seems that 'DataContext' base class in Microsoft SQL specific. SQLite ADO .NET support LINQ, but the recommended way to do it is to generate a database access class using GUI wizard. Such class will not subclass 'DataContext' and will allow to use LINQ on it.

Answer 2

You're confusing Linq to SQL (which is for SQL Server only) and Linq to Entities (aka Entity Framework). The SQLite ADO.NET provider supports Entity Framework, not Linq to SQL. And the DataContext class is for Linq to SQL, not Entity Framework...

As suggested by Eye of the Hell, you should use the entity model designer. It is possible to create the mapping manually, but it's not really easy...

By the way, Entity Framework doesn't have a CreateDatabase method, so you won't be able to create your database that way (and anyway SQLite doesn't have a CREATE DATABASE instruction)