CODEWITHSUNDEEP
c#sqlitewin-universal-app
eitan barazani
eitan barazani

Reputation: 1123

Why using SQLITE where clause does not work?

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

Upvotes: 0

Views: 427

Answers (2)

eitan barazani
eitan barazani

Reputation: 1123

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!

Upvotes: 0

whymatter
whymatter

Reputation: 775

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;

Upvotes: 1

Related Questions