c# - SQL to LINQ Query with date in where clause is not working -

i try query database item via linq not working. exception just:

object reference not set instance of object.

the stacktrace unhelpful exception itself:

at sqlite.tablequery1.compileexpr(expression expr, list1 queryargs) in d:\xx\xx\xx\xx\xx\sqlite.cs:line 2383 @ sqlite.tablequery1.compileexpr(expression expr, list1 queryargs) in d:\xx\xx\xx\xx\xx\sqlite.cs:line 2388 @ sqlite.tablequery1.compileexpr(expression expr, list1 queryargs) in d:\xx\xx\xx\xx\xx\sqlite.cs:line 2308 @ sqlite.tablequery1.compileexpr(expression expr, list1 queryargs) in d:\xx\xx\xx\xx\xx\sqlite.cs:line 2308 @ sqlite.tablequery1.compileexpr(expression expr, list1 queryargs) in d:\xx\xx\xx\xx\xx\sqlite.cs:line 2308 @ sqlite.tablequery1.generatecommand(string selectionlist) in d:\xx\xx\xx\xx\xx\sqlite.cs:line 2274 @ sqlite.tablequery1.getenumerator() in d:\xx\xx\xx\xx\xx\sqlite.cs:line 2521 @ system.collections.generic.list1..ctor(ienumerable1 collection)
@ system.linq.enumerable.tolist[tsource](ienumerable`1 source) @ kapital.datamodel.datamanageronetimeexpense.<>c__displayclass5.b__4() in d:\xx\xx\xx\xx\xx\datamodel\datamanageronetimeexpense.cs:line 47
@ sqlite.sqliteconnection.runintransaction(action action) in d:\xx\xx\xx\xx\xx\sqlite.cs:line 906 @ kapital.datamodel.datamanageronetimeexpense.retrieveitems(int32 month, int32 year, boolean ispaid) in d:\xx\xx\xx\xx\xx\datamodel\datamanageronetimeexpense.cs:line 39 @ unittestkapital.database.testdatamanageronetimeexpense.testretrieveitemsbymonthyearispaid() in d:\xx\xx\xx\xx\xx\unittestkapital\database\testdatamanageronetimeexpense.cs:line 154

however here linq query:

public list<onetimeexpense> retrieveitems(int month, int year, bool ispaid) {     var onetimeexpenses = new list<onetimeexpense>();      connection.runintransaction(() =>     {         var items = s in connection.table<onetimeexpense>()                     let converteddate = (datetime)s.paymentdate                     (converteddate.month == month)                        && (converteddate.year == year)                        && (s.ispaid == ispaid)                     select s;         onetimeexpenses = items.tolist();     });      return onetimeexpenses; } 

it has todo date because following method working (basically same method without date stuff):

public list<onetimeexpense> retrieveitems(bool ispaid) {     var onetimeexpenses = new list<onetimeexpense>();      connection.runintransaction(() =>     {         var items = s in connection.table<onetimeexpense>()                     (s.ispaid == ispaid)                     select s;         onetimeexpenses = items.tolist();     });      return onetimeexpenses; } 

and here comes fun part: had same issue few month ago winrt application. managed solve same way shown above.

i'm using sqlite 3.7.16.2. linq provider sqlite-net.

what else? visual studio 2012, c#, .net 4.5. wpf application.

edit: here data object, simple poco. paymentdate initialized datetime.today , therefore never null.

public class onetimeexpense : notifypropertychanged {     /**      * int id      * string name      * datetime paymentdate      * decimal amount      * boolean ispaid      * */     #region getters , setters      private int id;     [primarykey, autoincrement]     public int id     {         { return id; }         set         {             this.id = value;             this.onpropertychanged("id");         }     }      private datetime paymentdate = datetime.today;     public datetime paymentdate     {         { return this.paymentdate; }         set         {             paymentdate = value;             this.onpropertychanged("paymentdate");         }     }      private bool ispaid;     public bool ispaid     {         { return this.ispaid; }         set         {             this.ispaid = value;             this.onpropertychanged("ispaid");         }     }      #endregion      #region constructor      public onetimeexpense(string name, decimal amount, datetime paymentdate, bool ispaid)     {         this.name = name;         this.paymentdate = paymentdate;         this.amount = amount;         this.ispaid = ispaid;     }      public onetimeexpense()     {     }      #endregion } 

---

edit2 gert arnold suggested use work around. working , has far understood better performance query. nevertheless, know whats wrong query above.

public list<onetimeexpense> retrieveitems(int month, int year, bool ispaid) {     var onetimeexpenses = new list<onetimeexpense>();      var lowerbound = new datetime(year, month, 1);     var upperbound = lowerbound.addmonths(1);      connection.runintransaction(() =>     {         var items = s in connection.table<onetimeexpense>()                     s.paymentdate >= lowerbound                        && s.paymentdate < upperbound                        && s.ispaid == ispaid                     select s;         onetimeexpenses = items.tolist();     });      return onetimeexpenses; } 

not direct solution, can circumvent issue filtering differently. suppose want filter records may, 2013:

var lowerbound = new datetime(2013,5,1); var upperbound = new datetime(2013,6,1);  var items = s in connection.table<onetimeexpense>()             s.paymentdate >= lowerbound                && s.paymentdate < upperbound                && s.ispaid == ispaid             select s; 

this more evading issue. has potential of making query more efficient when there index on paymentdate. expressions converteddate.year translated datepart(year, [t0].[paymentdate]). such expressions not sargable, i.e. database engine can't use index lookup.