Why using SQLITE where clause does not work?

Question

I am trying to create a SQLITE query like this (first approach):

        int count;
        using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
            {
            count = ( from p in db.Table<TickRecord>()
                      where (p.TickStartDate.LocalDateTime >= start && p.TickEndtDate.LocalDateTime <= end )
                      select (int)p.DurationInSeconds ).Sum();
            }
        return count;

When running the query the application crash on the where clause.

I was able to achieve it like this (second approach):

        ObservableCollection<TickRecord> records;

        // Create a new connection
        using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
           {
           records = new ObservableCollection<TickRecord>( db.Table<TickRecord>().Select( i => i ) );
           }

        int count = records.Where( record => record.TickStartDate.LocalDateTime >= start && record.TickEndDate.LocalDateTime <= end ).Sum( record => record.DurationInSeconds );

Is there a way to achieve the same using my first approach?

Thx

Exception thrown: 'System.NotSupportedException' in SQLite.Net.dll Member access failed to compile expression — eitan barazani
– eitan barazani, Commented Apr 29, 2016 at 22:47

whymatter · Accepted Answer · 2016-04-29 23:05:17Z

1

You should not use the member access '.LocalDateTime' within your query. The Linq processor is not able to convert '.LocalDateTime' into a sqlite query, simple because there is no equivalent function in sqlite.

As a result is throws an exception as you sad:

[...] member access failed [...].

If you need the functionality of '.LocalDateTime', then you should try to get all entries of the table from your database and use the where query later on, if you have already received all data.

int count;
using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
{
    var results = db.Table<TickRecord>().ToList();
    count = ( from p in results
              where (p.TickStartDate.LocalDateTime >= start && p.TickEndtDate.LocalDateTime <= end )
              select (int)p.DurationInSeconds ).Sum();
}
return count;

answered Apr 29, 2016 at 23:05

whymatter

7859 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eitan barazani · Accepted Answer · 2016-04-29 23:22:36Z

0

Modified the code per whymatter:

        int count;
        using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
            {
            count = ( from p in db.Table<TickRecord>()
                      where ( p.TickStartDate >= start && p.TickEndDate <= end )
                      select (int)p.DurationInSeconds ).Sum();
            }
        return count;

Thx!

answered Apr 29, 2016 at 23:22

eitan barazani

1,1233 gold badges18 silver badges34 bronze badges

Collectives™ on Stack Overflow

Why using SQLITE where clause does not work?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related