SQLite, .NET, and DataTable constraints

The problem



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();
dt.Load(reader);
reader.Close();


The problem is that
dt.Rows.Count
is way lower than what running the same statement in the SQLite CLI produces.

The reason



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.

The workaround



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.

Comments

Popular posts from this blog

ADSL Router Model CT-5367 user and pass (VIVACOM)

Installing Gentoo with full disk encryption

FreeIPA cluster with containers