System.Data.SQLite

Database was created with 1.0.66 version and uses column type 'DATE ASC'. Utc DateTime values are there, so in code our queries returned DateTime objects with unspecified kind. We had to specify utc kind manually. Issue: in 1.0.89 version SQLite cannot more parse 'DATE ASC' and returns String objects (formatted in Utc). After conversion we get automatically localized datetime objects and still mark them as Utc, that is not correct.

Reason of issue is changed internal SQLite mappings engine. We could not find way to extend it with our own mapping rules, so we updated our code to correctly process both strings and datetimes to support old databases.

The type "DATE ASC" does not exist in the current mappings.  Can you try using
just "DATE" instead?

Yes, we can for newly created databases. But we also have to support existent ones used locally by our product users..

In the original report, you stated that you have already modified your code to
support the old databases?

As far as I can tell, the type name "DATE ASC" was never officially supported by
System.Data.SQLite (at least not judging from the source code history).  I'm not
sure how it worked for you before.

Per-connection type name mappings implemented on trunk via check-in [d84ccf015e].

It would be very helpful if you could test this feature in your environment and let us know how it works for you.

To take advantage of this feature for your use-case, you will want to do the following:

1. Use the new "UseConnectionTypes" connection flag when opening connections.

2. Add the "DATE ASC" type name to the per-connection type mappings, like so:

// create and open connection...
connection.AddTypeMapping("DATE ASC", DbType.DateTime, false);

This feature will be present in the next release (1.0.91.0), currently scheduled for mid-February.

Ok, thank you!
I'll test new SQLite version when possible. Currently we used a code patch to support old databases, so it is not a blocking issue for us.

In past, there were used several exotic column types in our databases: INTEGER UNSIGNED, LONGBLOB, INT2 UNSIGNED, DATE ASC. For 'DATE ASC' I suppose it worked because of previously used method TypeNameToDbType() in SQLite 1.0.66:
...
 if (string.Compare(Name, 0, _typeNames.typeName, 0, _typeNames.typeName.Length, StringComparison.OrdinalIgnoreCase) == 0)
            {
                return _typeNames.dataType;
            }
...
where it seems to be enough to contain 'DATE' in column type 'DATE ASC' to be mapped correctly.

SQLite 1.0.91 works great, I was able to apply custom mapping:

connection.Open();
connection.Flags |= SQLiteConnectionFlags.UseConnectionTypes;
connection.AddTypeMapping("DATE ASC", DbType.DateTime, false);

and this solved the issue.