System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 46166bd492423f42ccd1060c86ac883f9aa8b2ea
Title: View columns not supported in Entity Framework v 6.0
Status: Closed Type: Feature_Request
Severity: Important Priority: Blocker
Subsystem: Connection Resolution: Fixed
Last Modified: 2015-05-16 02:33:51
Version Found In: 1.0.96
User Comments:
anonymous added on 2015-04-17 16:15:10:
Hello

I have encountered a problem with literal values in a view not being mapped by Entity Framework.

Any column that provides a literal value or result of a function, produces the following error when doing database first Entity generation in Visual Studio:
Error Code 6005 (The data type '' is currently not supported for the target Entity Framework version)

The end result is that not all columns in my view are available to my application.

This seems to be limited to columns where literal values are provided or the column is the result of a function. This also happens in a UNION query where the first query may provide a value stored in a table, but the next query provides a literal value for the same column.

I have posted a question at stack overflow as well
(http://stackoverflow.com/questions/29395943/view-column-types-not-supported-entity-framework) One other user has experienced this issue and even tried declaring data types in the view, but this did not work for them.

Thank You

mistachkin added on 2015-04-21 15:56:17:
Could you provide an example schema that demonstrates the issue?  Also, it would
be helpful if there was a small C# example as well.

mistachkin added on 2015-04-28 16:54:31:

The SQLite core command line utility produces the following results:

sqlite> pragma table_info(VW_Vehicles);
cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           Id          INTEGER     0                       0
1           Type        TEXT        0                       0
2           Make        TEXT        0                       0
3           Model       TEXT        0                       0
4           Year        INTEGER     0                       0
5           Mpg                     0                       0
6           YearsOld                0                       0


mistachkin added on 2015-04-28 16:58:09:
Since the SQLite core library is not reporting type information for those two
columns, I'm not sure how System.Data.SQLite should handle it differently.  In
the past, when this issue has come up, see ticket [3c00ec5b521b], a workaround
has been used, namely setting the environment variables:

    Use_SQLiteConvert_DefaultDbType
    Use_SQLiteConvert_DefaultTypeName

Also see the full list of supported environment variables, here:

    [/artifact?ci=trunk&filename=Doc/Extra/Provider/environment.html]

mistachkin added on 2015-05-06 03:53:35:
To enable easier use of the connection flags related to type handling, e.g.
DetectTextAffinity and DetectStringType, I've added support for a new environment
variable, named "DefaultFlags_SQLiteConnection" that may be set to any string
that can be converted to a SQLiteConnectionFlags enumerated value, e.g.
"DetectTextAffinity, DetectStringType" (note the embedded comma).  This should
permit easier integration with Entity Framework for columns without explicit
types.

See check-in [719a56624a8129c7] on trunk.

mistachkin added on 2015-05-07 04:36:40:
See also ticket [3c00ec5b521b71073b74].

mistachkin added on 2015-05-16 02:33:35:
This is addressed via the new "DefaultFlags_SQLiteConnection" environment
variable that allows the connection flags to be set on a process-wide basis.

When this new environment variable is set to:

    "DetectTextAffinity, DetectStringType"

It should return appropriate data types for otherwise untyped columns, for
use with EF6, etc.