System.Data.SQLite

I'm currently working on a LINQ provider for the VS2008 SP1 beta.  There are a few hurdles to overcome, but when its finished, it'll be really nice.

Before I talk about the progress, let me talk about EdmGen -- the tool that Linq uses to generate the schema from a database.

EdmGen uses a provider schema manifest to collect schema information about a database in order to gen the csdl, ssdl, etc.  This schema manifest is designed around the idea that one can call SQL queries to collect schema information.  This is not the case with SQLite to the extent required by the tool.  Instead, the heavy-duty schema information requires a lot of programmatic interference to generate.  EdmGen and LINQ in general is not very flexible on this.  As a matter of fact, EdmGen uses LINQ calls to combine the queries in the schema definition with outer queries to get the schema -- so not having the schema queryable is going to be a crippler for many database engines.

So my tasks are ...

1. Figure out a way to make EdmGen work with SQLite
2. Write up, and rig up all the schema mapping queries so EdmGen can get schema information
3. Write the Sql Generation code and modify it for SQLite
4. Make whatever design changes are needed in the core library to allow for Linq to work, while still targeting .NET 2.0.
5. Rig up all the datatypes and mapping schemas.

I'm currently on step 5 and hitting some roadblocks due to the lack of documentation on the new bits in LINQ.  I've actually gotten as far as having EdmGen actually generate all the files for my SQLite Northwind database -- but it throws lots of type mapping warnings that I need to sort out.  Once those are taken care of, I will then have to go back and make sure the rest of the schemas are working and that LINQ has properly mapped and related all the tables to one another properly.

So basically I'm looking at another 20-30hrs worth of work.

This was encouraging!

C:\src\foo>edmgen /c:"Data Source=c:\src\northwind.db" /prov:System.Data.SQLite /mode:FullGeneration /project:foo /entitycontainer:fooEF

Microsoft (R) EdmGen version 3.5.0.0
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Loading database information...
Writing ssdl file...
Creating conceptual layer from storage layer...
Writing msl file...
Writing csdl file...
Writing object layer file...
Writing views file...

Generation Complete -- 0 errors, 0 warnings

The above 0 errors 0 warnings was initially encouraging, but was missing all the relationships of the foreign keys.

I've spent the rest of the day getting all that working, and am back to 0 errors 0 warnings again after a long period of failures.

I could have an updated LINQ provider as early as tomorrow, if everything else goes perfect.  In order to run all the unit tests in the MS-provided sample provider, I need to rebuild Northwind using their data model exactly.  My northwind database was a port of an earlier version of theirs, and is missing some things.

So once I get that ported over and EdmGen works, I can run some unit tests!

Very encouraging.  Thank you very much for the updates, and more importantly, the effort.

Hi,

Can I already use it to start testing?

Sure thing.  Insert/update changes don't work in 1.0.51, but I have that fixed for the next release.

Robert Simpson:

Sure thing.  Insert/update changes don't work in 1.0.51, but I have that fixed for the next release.

When is the next release scheduled? I just nabbed 1.0.51 but am quite keen to use LINQ on this project.

Entity Framework support is going to be in beta for a while yet.  I plan on releasing version 52 sometime next week.

Hi,

I have got an error:

C:\WINDOWS\Microsoft.NET\Framework\v3.5>edmgen /c:"Data Source=d:\test.db3" /prov:System.Data.SQLite /mode:FullGeneration /project:test /entitycontainer:testEF
Microsoft (R) EdmGen version 3.5.0.0
Copyright (C) Microsoft Corporation 2007. All rights reserved.

error 7001: The specified store provider 'System.Data.SQLite' cannot be found in
 the configuration or it's not valid.
        Unable to find the requested .Net Framework Data Provider.  It may not b
e installed.

Generation Complete -- 1 errors, 0 warnings

 How can I fix this?

Thanks

Did you run the install.exe and check the box for VS2008?  The provider needs to be registered in the GAC and properly configured (install.exe does this) for it to work.

Hi,

ok. That whas my problem :)

Do you have examples already?

I do the following:

Dim c As ModelDB.Countries

End Using

It will executed fine, but it is not inserted into the database.

I did find some bugs in the DML SQL generation that prevented inserts/updates/deletes from working -- however you're doing it wrong :)

AcceptAllChanges(), just like the DataTable version, takes all modifications you've made and clears their "pending" flags without actually saving anything to the database.

You need to call db.SaveChanges() instead.

You'll have much better results in the 53 release, coming this week.