3

Platform:

Working

I had a Table

public class CommandData
{
    [PrimaryKey, AutoIncrement]
    [Column("Id")]
    public Guid Id { get; set; }

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

Breaking change

But, SQLite returns the DateTime's Kind as Unspecified and treated as Local; I had to change the table to always return UTC Time (I also tried DateTime.SpecifyKind(this.TimeStampStore , DateTimeKind.Utc), where TimeStampStore is type DateTime) :

public class CommandData
{
    [PrimaryKey, AutoIncrement]
    [Column("Id")]
    public Guid Id { get; set; }

    [SQLite.Ignore]
    public DateTime TimeStamp
    {
        get
        {
            return DateTime.Parse(this.TimeStampStore).ToUniversalTime();
        }
        set
        {
            TimeStampStore = value.ToUniversalTime().ToString("s") + "Z";
        }
    }

    [Column("TimeStamp")]
    public string TimeStampStore { get; set; }
}

This change broke the following:

SQLite.SQLiteConnection.Table<CommandData>().OrderBy(x=>x.TimeStamp) 

Stack trace:

at SQLite.TableQuery'1.AddOrderBy[U](Expression'1 orderExpr, Boolean asc)

at SQLite.TableQuery'1.OrderBy[U](Expression'1 orderExpr)

Reason

After a bit on investigation, I found that the reason for the System.NullReferenceException is the dot operator on the Table.FindColumnWithPropertyName(mem.Member.Name) in SQLite.cs

 private TableQuery<T> AddOrderBy<U> (Expression<Func<T, U>> orderExpr, bool asc)
 {
     ...
                var q = Clone<T> ();
                if (q._orderBys == null) {
                    q._orderBys = new List<Ordering> ();
                }
                q._orderBys.Add (new Ordering {
                    ColumnName = Table.FindColumnWithPropertyName(mem.Member.Name).Name,
                    Ascending = asc
                });
                return q;
 }

 public Column FindColumnWithPropertyName (string propertyName)
 {
     var exact = Columns.FirstOrDefault (c => c.PropertyName == propertyName);
     return exact;
 }

The propertyName is "TimeStamp", but

Columns[].PropertyName is "TimeStampStore"

Columns[].Name is "TimeStamp"

and and thus FindColumnWithPropertyName returns a null

Probable Fix

If I change SQLite.cs's AddOrderBy function:

Table.FindColumnWithPropertyName(mem.Member.Name).Name 

to

Table.FindColumn(mem.Member.Name).Name

then my code works.

But I would rather guess that I am doing something wrong since this code was written by people with much more experience than me (especially because I could not find a related post)

Question

Am I doing something wrong with my setup? I can read and write perfectly but this one thing is giving me quite a headache.

Thank you.

Edit

I implemented my probable fix (changing the function AddOrderBy), which solved my issue.

To use SQLite.Net Extensions-PCL 1.3.0 I moved to SQLite.Net PCL 3.1.1 and it's generating the exact same issue;

at SQLite.Net.TableQuery1.AddOrderBy[TValue](Expression1 orderExpr, Boolean asc)

at SQLite.Net.TableQuery1.OrderBy[TValue](Expression1 orderExpr)

But now I'm unable able to apply my previous fix since I'm no longer presented with the .cs files (it's a .dll now).

Any ideas?

Barnstokkr
  • 2,904
  • 1
  • 19
  • 34
  • It might be me but I'm not quite sure what you are trying to achieve. Could you elaborate? – Barptad Mar 08 '16 at 11:54
  • @Barptad the application of the application is quite trivial: get items from the DB that are ordered by the `TimeStamp`. There are multiple ways to get this; the easiest is probably just read them out to anything that implements the `IEnumerable` interface and use Linq to `OrderBy` them. The application is however not the question, but rather my SQLite implementation that isn't working. – Barnstokkr Mar 08 '16 at 12:28
  • I have run into similar SQLite and DateTime issues. And as you just said I ended up making a query to select the objects and then just do an OrderBy with Linq on the result. Altough it might be a bit late at this point but I found the SQLiteNetExtensions Nuget package very helpful when I have to deal with SQLite. It is based on slite-net and it comes very handy to set up relations and perform most of the standard operations. – Barptad Mar 08 '16 at 13:21
  • This could help someone else who arrives here, I hit this issue when using .OrderBy(x=>x.Y) where Y was a valid property but marked [Ignore] so it was not a valid column. – Rhys Jones Aug 10 '20 at 14:34

0 Answers0