System.Data.SQLite

Hello, I was using the conversion code from System.Data.SQLite.SQLiteConvert (SQLiteConvert.cs) to convert some types and I'm a bit puzzled over two types: "DATETIME" and "BOOLEAN":

• The "DATETIME" from/to DbType.DateTime conversion:
  

• SQLiteConvert.DbTypeToTypeName(DbType.DateTime) returns "DATETIME"
• SQLiteConvert.TypeNameToDbType("DATETIME") returns DbType.Object (??!!)
  

It seems that "DATETIME" --> DbType.DateTime is missing. Shouldn't that last one return DbType.DateTime by adding the following line to _typeNames (around line 674 in SQLiteConvert.cs):

new SQLiteTypeNames("DATETIME", DbType.DateTime),

• Also, it seems "BOOLEAN" --> DbType.Boolean might be missing (expected around line 668 in SQLiteConvert.cs):

new SQLiteTypeNames("BOOLEAN", DbType.Boolean),

Thanks for any reply concerning this!

If you look carefully, the String.Compare() in the TypeNameToDbType function uses the length of the typeNames.Name string as the max length to compare.  So typeNames "BOOL" will match on "BOOLEAN" and "DATE" will match on "DATETIME".

Robert

I feel slightly ashamed to have overlooked this. Thanks for the quick reply!

Hi,

I'm using the latest release. I have a very simple view with this query:

SELECT SubjectId, MIN(Date), MAX(Date) FROM Records GROUP BY SubjectId

 Running this query gives three (3) columns: subject id, earliest record date, and latest record date.

In Visual Studio Server Explorer if I select 'Retrieve Data', I only get SubjectId column. If I look at the properties of the other columns, I can see they don't have a Data Type. How can I force a type for the columns, so I can see them when I retrieve data and subsequently use them in my Entity Model? Or should I somehow change the view?

Best regards,

   - Jussi

See the following:

http://sqlite.phxsoftware.com/forums/p/462/1988.aspx#1988

http://sqlite.phxsoftware.com/forums/p/665/2928.aspx#2928

Also read the CHM help file and look at for the TYPES keyword in the SQL commands.

Of course.. I'm sorry I even had to ask, but I didn't exactly know what to look for. Thanks for a great library and great support!

One more thing. Is is actually possible to use TYPES inside CREATE VIEW? 

I have now tried e.g. the following:

CREATE VIEW RecordRange AS TYPES integer, date, date; SELECT SubjectId, MIN(Date) first, MAX(Date) last FROM Records GROUP BY SubjectId;

Which doesn't create a view in the database. If I modify it I always get one of these results: either the view is not created or it doesn't have type information and can't be used with Entity Framewor. Is there something else I can do?

TYPES is a .NET extension for the provider, so it knows what to do .... its parsed separately and isn't a SQLite construct.  So you can't put it into the view -- you have to put it ahead of your SELECT on the view.

Unfortunately there's no way to do this with the entity framework and a view.  SQLite's type support is extremely limited, and there's just no way for me to glean relevent type information from a view that uses scalar or aggregate functions.

Right, I eventually did realise TYPES lives outside SQLite, I was just hoping there was a workaround. :-) Since there's not, I'll do the queries in code. Thanks again!

Is it  instead of relying on the inferred data type, can i use the typed accessor methods of the DataReader? does this methods will give better performance?

 Looks complicated,hope i can duplicate this!

 How did you"solve" the problem: do you actually avoid (totally) entity framework or do you use entity framework avoiding views? (and you actually do selects instead)

FireDragonDoL:

 How did you"solve" the problem: do you actually avoid (totally) entity framework or do you use entity framework avoiding views? (and you actually do selects instead)

 I use mainly EF, but also execute some queries with manually coded SQL. Not very elegant, but good enough for us.