Generate SQL for SQLite database from Entity Framework Model

Question

Is it possible to generate a SQLite database from the model with entity framework? I created a SQLite connection and created a model, but when I click "Generate database from model" I get the following, which looks like MS SQL and makes errors if executed with SQLite (Just the beginning of the file):

-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/25/2010 00:26:41
-- Generated from EDMX file: G:\Foo\Bar\Model1.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [foobar.sqlite];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO
...

My connection string looks like the following, so I definitely chose the right database type:

'metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SQLite;provider connection string="data source=G:\foo\bar\baz"'

Shouldn't it work this way?

EDIT:

Since nobody seem to know an answer I'll make it easier: Is it possible to generate SQL code with EF for any database other than the MICROSOFT SQL SERVER?

toxvaerd · Accepted Answer · 2011-06-14 11:20:30Z

11

I was looking for a solution to this, when I stumbled upon this link: http://code.msdn.microsoft.com/Demo-of-ADONET-POCO-with-140ad3ad

Place the SSDLToSQLite3.tt file in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen and you should be able to choose this as the DDL Generation Template in the Entity Designer.

Once you do that, the model will create SQL suited for SQLite.

answered Jun 14, 2011 at 11:20

toxvaerd

3,8623 gold badges27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ivan Over a year ago

SSDLToSQLite3.tt seems to do te job. Thanks. And there were no need to do any configuration files modifications described in the article as I have latest version of the full System.Data.SQLite distribution installed. The only thing not obvious was to find how to apply it - I didn't new it is to be selected in the model properties (right click on the designer free space and choose properties there).

dax · Accepted Answer · 2013-09-25 16:04:41Z

2

The SSDLToSQLite3.tt file seems to have a bug where primary keys are not defined for tables that have a single primary key that is not of the INTEGER PRIMARY KEY AUTOINCREMENT variety.

The simplest change I found that does the trick is to change line 105 of the .tt file from:

if (keyCount > 1)

to:

if (keyCount > 1 | (keyCount > 0 & autoIncreaseFieldName == string.Empty))

edited Sep 25, 2013 at 16:04

dax

11k8 gold badges55 silver badges87 bronze badges

answered Sep 25, 2013 at 15:40

dan

492 bronze badges

Collectives™ on Stack Overflow

Generate SQL for SQLite database from Entity Framework Model

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related