why we can't use type `struct` as the table data type while using nuget sqlite-net-pcl 1.8.116

[jessiezh0320]

If I used type struct as the table data type,I could create and insert some items into my database ,but if I queried the items from the database, my app throws an error System.ArgumentException Message=method arguments are incompatible.

I could reproduce this problem with nuget sqlite-net-pcl 1.8.116., but if I used lower version (e.g. 1.7.335), there is no such error.
You can try to repreduce this problem with official sample here: https://docs.microsoft.com/en-us/samples/xamarin/xamarin-forms-samples/todo/

Steps:

1.Try to create a new Model with type struct ;
2.replace class TodoItem with the new model we create above, in page TodoListPage.xaml.cs, the app will try to add a new item in function OnItemAdded
3.Aftern inserting a new item, if we come back to page TodoListPage, the function OnAppearing() which will read back the new added item. Here the app will throw such error.

[bobwhitten]

I have this too., but only noticed now. Rolling back to 1.7.335 makes it work again. Should I update my struct to class?

[gindemit]

I also have the same issue in Unity editor switched to Android platform. When I use struct for the data, I get following error:

ArgumentException: method arguments are incompatible
System.Delegate.CreateDelegate (System.Type type, System.Object firstArgument, System.Reflection.MethodInfo method, System.Boolean throwOnBindFailure, System.Boolean allowClosed) (at <695d1cc93cca45069c528c15c9fdd749>:0)
System.Delegate.CreateDelegate (System.Type type, System.Object firstArgument, System.Reflection.MethodInfo method) (at <695d1cc93cca45069c528c15c9fdd749>:0)
SQLite.FastColumnSetter.CreateTypedSetterDelegate[ObjectType,ColumnMemberType] (SQLite.TableMapping+Column column, System.Func3[T1,T2,TResult] getColumnValue) (at Assets/_ThirdParty/Runtime/SQLite/SQLite.cs:3575) SQLite.FastColumnSetter.CreateNullableTypedSetterDelegate[ObjectType,ColumnMemberType] (SQLite.TableMapping+Column column, System.Func3[T1,T2,TResult] getColumnValue) (at Assets/_ThirdParty/Runtime/SQLite/SQLite.cs:3562)
SQLite.FastColumnSetter.GetFastSetter[T] (SQLite.SQLiteConnection conn, SQLite.TableMapping+Column column) (at Assets/_ThirdParty/Runtime/SQLite/SQLite.cs:3400)
SQLite.SQLiteCommand+d__121[T].MoveNext () (at Assets/_ThirdParty/Runtime/SQLite/SQLite.cs:3029) System.Collections.Generic.List1[T]..ctor (System.Collections.Generic.IEnumerable1[T] collection) (at <695d1cc93cca45069c528c15c9fdd749>:0) System.Linq.Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable1[T] source) (at <351e49e2a5bf4fd6beabb458ce2255f3>:0)
SQLite.SQLiteCommand.ExecuteQuery[T] () (at Assets/_ThirdParty/Runtime/SQLite/SQLite.cs:2975)
SQLite.SQLiteConnection.Query[T] (System.String query, System.Object[] args) (at Assets/_ThirdParty/Runtime/SQLite/SQLite.cs:1007)

SQLite version: v1.8.116 from 3 September 2021

Changing the struct to sealed class fixes the issue.

[duanrui71]

这是因为FastColumnSetter中的CreateTypedSetterDelegate函数调用了Delegate.CreateDelegate. 当第一个类型是Value类型时,CreateDelegate会失败.

// 当ObjectType 是Value类型时,无法创建Delegate
var setProperty = (Action<ObjectType, ColumnMemberType>)Delegate.CreateDelegate (
            			typeof (Action<ObjectType, ColumnMemberType>), null,
            			column.PropertyInfo.GetSetMethod ());

            	return (o, stmt, i) => {
            		var colType = SQLite3.ColumnType (stmt, i);
            		if (colType != SQLite3.ColType.Null)
            			setProperty.Invoke ((ObjectType)o, getColumnValue.Invoke (stmt, i));
            	};

[gilzoide]

Hey folks, got this error as well, using v1.9.172 on Unity.

I found out that for structs, property setter delegates should use ref ObjectType instead of ObjectType as the first parameter, that's why Delegate.CreateDelegate throws an exception saying that the types are incompatible.

I see two alternatives to fix this:

1. Add proper support for struct types in these delegates. Patching CreateNullableTypedSetterDelegate and CreateTypedSetterDelegate with something in the lines of if (typeof(ObjectType).IsValue) { return delegate that accepts structs; } may be enough.
2. Remove support for structs altogether. Use where T : class, new() in generic APIs instead of just where T : new() so that trying to call them with a struct type is a compile error.

Alternative 1 is the best in my opinion, as we don't need to change types from struct to be a class just to use SQLite-net's ORM. It's more work, though. There are other APIs that could be adjusted as well to better support struct types, like Insert receiving a ref T so that structs can be updated after insert, for [AutoIncrement] properties.

Alternative 2 is the easy fix: just block anyone trying to use struct types. I don't like this much, but it's better than the current state, in my opinion.

[bluechrismartin]

We're having to upgrade and have now been impacted by this. We have had to change a struct to a class but make no other changes.

@gilzoide Alternative 2, using "Use where T : class, new()" would mean that system types that are not classes, like int will get caught up in this. I tried this in our internal interfaces and found calls that would work because the internal code in Sqlite.cs covers those use cases and hit this issue.

[gilzoide]

Alternative 2, using "Use where T : class, new()" would mean that system types that are not classes, like int will get caught up in this.

@bluechrismartin Yes, but for system types you should be using QueryScalar instead of Query anyway, so this shouldn't be a problem. SQLite-net doesn't support system types in Query as of now, it would be ok to remove support for them there (I mean, it does support passing them as type arguments, but you'll not get the data you expect from the query).

Of course, there's alternative 3 which I implemented in PR #1266, which is just avoid the exception when using structs, so they are fully supported, although without the fast setter path.

[bluechrismartin]

@gilzoide Good point. I looked back at the call we had and I mischaracterized it - we are calling ExecuteQuery<int> in one case and it looks like this:
ICollection<int> supportedIDs = ExecuteQuery<int>(queryText);

So it's using int as the generic T not to get a single int back but to get a whole int collection.

This fails at compile time when modifying one of our internal wrappers for ExecuteQuery to include class as a constraint for the generic type T.
protected ICollection<T> ExecuteQuery<T>(string query, params object[] args) where T : class, new()

I didn't write this code or query so if this is the best/right way to make the call we are making I don't know, but this is the call that failed for me when trying to restrict callers from using structs in our own code and so it's a use case that I could see breaking for others.

That said, thanks for taking a look at this and essentially cluing us into what the issue is. I appreciate it and wish you luck in getting any change from this committed to the repro here.