Jann Roder wrote: > > The implementation of GetSchemaTable() in SQLiteDataReader seems to be > based on the properties of the columns used in the select. Obviously > uniqueness, nullness and primary key properties do not transfer when > only a subset of the columns is selected or combined with data from > other tables. >
The official MSDN docs are a bit fuzzy on these points: https://msdn.microsoft.com/en-us/library/system.data.idatareader.getschemata ble%28v=vs.110%29.aspx > > Unfortunately the .Net Datatable::Load() command uses the information > returned by SQLiteDataReader::GetSchemaTable(). This means that the > result of many queries cannot be loaded into a Datatable using the > Load() command because of non-nullable columns being null (due to a > left join) or even wrong (the Datatable just drops "duplicate" rows > when loading data). See the code below that illustrates the problem. > Yes, I've seen this behavior while tracking down several past issues. On a somewhat sunnier note, the built-in ADO.NET support classes seem remarkably good at adapting to [some of] the of idiosyncrasies of the various ADO.NET providers. However, as you have seen, there are some inconsistencies. > > I'm not sure how other DB drivers solve this problem, but it seems to > me the SQLite driver should stop trying to be clever and just always > set the IsUnique and IsKey columns to false and also always allow null > values. > Unfortunately, changing this method in such a fundamental way would not be backwards compatible. Also, in some cases, it can be quite difficult to determine what the "correct" behavior should be. -- Joe Mistachkin
