1

Closed

Error "Non-empty collection required Parameter name: expressions"

description

I done some research on this. It appears that using a .net Integer type on a SqLite data contract is probably a bad idea. The links below suggest that regardless of how they are stored (which can be variable per value)

http://stackoverflow.com/questions/7337882/sqlite-and-integer-types-int-integer-bigint

, they are upcast to long upon retrieval.

http://stackoverflow.com/questions/13832103/sqlite-integer-value-bytes

I need to do a little more reading. but I believe the workaround for you is to use longs on your data contracts and for me to not generate data contracts with int type properties. I'll be moving this to the issue list.
Closed Sep 18, 2013 at 6:02 PM by TBone512

comments

oscarhc wrote Apr 8, 2013 at 4:21 AM

Hi Tony,

Made a test before reading your comment and agree; a workaround is setting contract properties as Long. Also experimented casting get method to Integer and found no issues. For code sanity leaved private members as Integers and Properties as Long and works fine.

As far as I know (or understand), SQLite Affinity feature converts to the right value. Now think the matter is how strict we want to be with the values. For me Integer or Long is fine.

If interested, here is the new working code:
Module Module1

    Sub Main()
        Const dbTest As String = "\TestDB.db3"
        Dim dbFile As String = System.AppDomain.CurrentDomain.BaseDirectory() & dbTest

        Dim Accessor = DbFactory.CreateAccessor(DbAccessorType.SqLite, AccessorExtension.All, Function() _
                  New SQLiteConnection("Data Source=" & dbFile))


        ' NOTE:  "Id INTEGER PRIMARY KEY" is said to be an Alias for rowid in SQLite.
        Accessor.Execute("CREATE TABLE IF NOT EXISTS itemcounter (Id INTEGER PRIMARY KEY, counter INTEGER NOT NULL UNIQUE DEFAULT 0); INSERT INTO [itemcounter] SELECT null,0 WHERE NOT EXISTS (SELECT * FROM [itemcounter])")

        'NOTE: counter increment is now  replaced with a Read/Write entity to test properties.
        'Accessor.Execute("UPDATE itemcounter SET counter =(SELECT MAX(counter) FROM itemcounter) + 1")
        Dim updateCounter As itemcounter
        updateCounter = Accessor.ReadEntity(Of itemcounter)("SELECT * FROM [itemcounter] LIMIT 1")
        Debug.Print("ReadEntity - Pre-Write: " & updateCounter.counter)
        updateCounter.counter += 1
        Accessor.WriteEntity(updateCounter)
        Debug.Print("Entity - Post-Write: " & updateCounter.counter)

        Dim count
        '  ReadAnonymous works fine ...
        ' NOTE : Added the Id property to make it work properly.
        ' Id has be Long, cannot use NULL like in above Execute method: "... SELECT null,0 ..." 
        count = Accessor.ReadAnonymous(New With { _
            .Id = 0L, .counter = 0L
            }, "SELECT * FROM [itemcounter] LIMIT 1").ToList()
        Debug.Print("ReadAnonymous: " & count(0).counter)

        ' ReadValue also works.
        ' NOTE: Cast matches property type: Long.
        count = Accessor.ReadValue(Of Long)("SELECT counter FROM [itemcounter]")
        Debug.Print("ReadValue #1: " & count)

        ' Now Statements work fine.
        count = Accessor.ReadEntity(Of itemcounter)("SELECT * FROM [itemcounter]")
        Debug.Print("ReadEntity #1: " & count.counter)
        count = Accessor.ReadEntity(Of itemcounter)("SELECT * FROM [itemcounter]").counter
        Debug.Print("ReadEntity #2: " & count)
        count = Accessor.ReadEntity(Of itemcounter)("SELECT counter FROM [itemcounter]")
        Debug.Print("ReadEntity #3: " & count.counter)
    End Sub

End Module

<DataContract()> _
<DbTable(Name:="itemcounter")> _
Partial Public Class itemcounter
    Inherits DbEntity(Of itemcounter)
    Implements INotifyPropertyChanged

    <DataMember()> _
    Private m_Id As Integer
    <DataMember()> _
    Private m_counter As Integer

    Public Sub New()
    End Sub

    <DbColumn(Name:="Id", IsPrimaryKey:=True, IsDbGenerated:=True)> _
    Public Property Id() As Long
        Get
            Return m_Id
        End Get
        Set(value As Long)
            m_Id = value
        End Set
    End Property

    <DbColumn(Name:="counter", IsPrimaryKey:=False, IsDbGenerated:=False)> _
    Public Property counter() As Long
        Get
            Return m_counter
        End Get
        Set(value As Long)
            If value = m_counter Then
                Return
            End If
            m_counter = value
            RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs("counter"))
        End Set
    End Property

    Public Event PropertyChanged As PropertyChangedEventHandler Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged
End Class
Regards, Oscar.

TBone512 wrote Sep 18, 2013 at 6:02 PM

This all seems to be working now. Not sure which version, but I've written some unit tests to verify it.