SQLiteConnection.Query does not work with ValueTuples

[XavierAP]

Would be really nice if it did, when using "manual SQL" instead of ORM, because in that case declaring the classes is unnecessary overhead. For example from the README:

public class Val
{
	public decimal Money { get; set; }
	public DateTime Date { get; set; }
}
public static IEnumerable<Val> QueryVals(SQLiteConnection db, long stockId) {
	return db.Query<Val>("select Money, Date from Valuation where StockId = {stockId}");
}

The following variation does not work. It runs, but the returned List is just full of default values (although the correct number of records).

db.Query<(decimal Money, DateTime Date)>("select Money, Date from Valuation where StockId = {stockId}")

The cause seems to be that sqlite-net needs columns to map to properties, not fields.

Would there be any interest in this enhancement?

[ske66]

I am a little bit confused as to how Stock fits into the table as StockId does not seem to be listed in your Val class and by extension your Val table. It may be an idea to read the Synchronous documentation in the Wiki as I talk about this ORMs.

If you have a class called Val and you want to get data out of it, you need to map your ORM;

[Table("Val")]
public class Val
{
    [PrimaryKey]
    [Column("StockId")]
    public int StockId { get; set; }

    [Column("Money")]
    public decimal Money { get; set; }

    [Column("Date")]
    public DateTime Date { get; set; }
}

Now that you have your ORM, you can now use linq to access it.

public List<Val> QueryVals(SQLiteConnection db, Stock stock) {
    return db.Table<Val>().Where(x => x.StockId == stock.Id).ToList();
}

//this could be filtered down using a select statement at the end of the where clause

// .Select(y => new {y.Money, y.Date }).ToList();

[XavierAP]

Hi, thanks for replying. My question is specifically about SQLiteConnection.Query<T>(), which accepts a manually written SQL statement as string argument. I'm aware that the main use case of the library is instead with full ORM and other methods such as Table() and Linq. Also please disregard the Stock class in my example; it's copied from the project's README, but I've edited the OP to simplify it.

It so happens that the type <T> does not need to map the whole table, only the columns selected. The example in the OP works OK, even though the table has columns that the Val class doesn't (such as StockId and possibly others). I have confirmed this.

As a next step I tried a ValueTuple, so I don't need to define any Val type at all. However this does not work. (Actually it compiles, runs and does not throw, but I get only a table of zeros or nulls.) FYI, defining Val but making Money and Date public fields instead of properties causes the same problem.

It would be nice if SQLiteConnection.Query<T>() worked with ValueTuples, at least for my use case.

P.S. Also in my opinion if something isn't supported it should throw an exception, not return garbage (zero/null/default) data silently.

[ske66]

ah, I see where you are coming from. That is maybe something the new record struct in cSharp 9 can handle, however, this part of the library would need to be configured for that. Maybe do a pull request and add the necessary try catches, it looks like you've done most of the heavy lifting by identifying the case required to cause this error.

[XavierAP]

After looking at the source code, it looks like it could be as simple as changing TableMapping's constructor to query Type.GetTypeInfo().DeclaredFields in addition to .DeclaredProperties.

The other behavior not to throw if some SQL column has no corresponding property/member in T seems to be by design in SQLiteCommand.ExecuteDeferredQuery() (continue inside for), but I wonder why, and if any tests would break if it threw instead as I'd have expected. But anyway that should probably be a separate issue/PR.

I'm not picking this up at the moment... but maybe I will if I can't fix my problem anyway else. If so I'll follow up with a PR.

(My background is, I need such a simple SQLite string .NET adapter for my project, with the extra requirement that it should work on both Xamarin and Windows, and System.Data.SQLite, which I was initially using, is giving me obscure runtime problems on the former, and Microsoft.Data.Sqlite on the latter. So I was looking into SQLite-net as an alternative, but I still have to test if it does solve my problem, if it's the simplest solution or the least work.)

[XavierAP]

I thought it would be as easy as extending TableMapping to look up by reflection not just public properties but public fields as well.
The problem is that top-level ValueTuples do not have member name information. Even if you declare (int a, int b), a and b are compile-time syntactic sugar only. At run-time they are truly called Item1 and Item2, same as if you had not named them.

I tried to go along with this and pick them by position alone; but still every TableMapping.Column needs a Name.

EDIT Wait I have another idea...

Curiously enough, when ValueTuples are members of another type, they do get decorated with TupleElementNamesAttribute; but not when they're used on their own (??).

There seems to be some demand for improving this on .NET and maybe even some work ongoing.