Folks, just in case anyone was wondering ... my experiments so far indicate that SQLite works nicely with Entity Framework 4. I expected horrors, but I guess the authors of the SQLite ADO provider have obeyed the rules. A couple of little irritations have surfaced.
The INTEGER type in SQLite which is used for primary keys is an Int64 whereas under SQL Server and SQL CE it was an Int32. I had to adjust my code to prevent cast errors on ExecScalar calls. Bulk inserts of a thousand rows or so into SQLite tables seems excruciatingly slow. Perhaps there is some bulk insert trick or transaction gotcha affecting this that I'm not aware of yet. I have UNIQUEIDENTIFIER columns in the SQL Server tables I'm migrating to SQLite, but my reading so far is confusing about what underlying string value is expected to be stored by the caller in such a column in SQLite. I guess it's any string value which can be TypeConveter'd to and from a GUID. That's fine, but I can't find a NEWID() equivalent and some people seem to have invented their own ones by a messy combination of functions. I gave up and used a BIGINT SQLite column instead and filled with DEFAULT (ABS(RANDOM()). This seems like a good replacement for a GUID. I'm still unsure about the corresponding CLR type widths for INT, INTEGER, BIGINT, etc. More reading will sort this out I guess. I haven't pushed EF4 really hard yet other than proving it works, but it looks promising for more intense work. This is great, as I'm planning to re-jig one of my apps so that it uses SQLite and has absolutely no install footprint like it used to have with SQL CE. Greg
