SQLite, .NET, and DataTable constraints
Here's a simplified snippet of what illustrates the problem I had today.
SQLiteCommand cmd = cnn.CreateCommand("SELECT a.a1, b.b1 FROM a INNER JOIN b USING (b1)");
SQLiteDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
The problem is that
dt.Rows.Countis way lower than what running the same statement in the SQLite CLI produces.
The reason is that DataTable complains about an invalid constraint on the "b1" column. Why? Because "b1" has a unique index in "b" and for some reason DataTable doesn't like it when you get to entries with the same value for that column regardless that it is perfectly legal when joining tables.
I have no idea if this is a problem with SQLite or the AdoNet SQLite wrapper, and I haven't tried other databases to see if this is not a problem with SQLite at all. The workaround, however, is this:
SELECT a.a1, b.b1 + 0 FROM a INNER JOIN b USING (b1)
That's an ugly workaround but at least it works.