New Posts New Posts RSS Feed: Queries Filtered on DateTime data type
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Queries Filtered on DateTime data type
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
btitular View Drop Down
Newbie
Newbie
Avatar

Joined: 08-Oct-2009
Location: CA
Posts: 20 		Post Options Post Options   Quote btitular Quote  Post ReplyReply Direct Link To This Post Topic: Queries Filtered on DateTime data type
    Posted: 08-Oct-2009 at 3:53pm
Just started working with the SL library and I'm having trouble retrieving data when my query is based on a datetime field.

My environment:
Workstation: Win7-64bit
SQL 2008 - 64-bit
VS2008

Here's a short snippet

DateTime testdate = Convert.ToDateTime("10/1/09");
long auditID = 5;

1.  var query = _entityManager.Audits.Where(a => a.CheckDate.CompareTo(testdate) >= 0);

2.  var query = _entityManager.Audits.Where(a => a.AuditID.Equals(auditID));

When query #1 is returned from the async callback method, no records are returned.
However, when I run query #2, I get the results I expect to receive.

Watching the SQL Profiler, I don't see any activity hitting the sql server on query #1, but I do see the t-sql code when query #2 is run.

Am I missing something?
 
Back to Top
btitular View Drop Down
Newbie
Newbie
Avatar

Joined: 08-Oct-2009
Location: CA
Posts: 20 		Post Options Post Options   Quote btitular Quote  Post ReplyReply Direct Link To This Post Posted: 09-Oct-2009 at 10:33am
Problem solved.
 
Changing the query to
 
 var query = _entityManager.Audits.Where(a => a.CheckDate >= testdate);
 
returns the expected results. Just so use to using some of Linq's extension methods!
Back to Top
GregD View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 374 		Post Options Post Options   Quote GregD Quote  Post ReplyReply Direct Link To This Post Posted: 09-Oct-2009 at 1:14pm
Not sure, btitular.

I ran a similar query, both synchronously and asynchronously, and got good results both times (161 Orders):

    private void DoIt() {
      DateTime testDate = new DateTime(1998, 3, 1);
      var query = _mgr.Orders.Where(o => o.OrderDate.CompareTo(testDate) >= 0);
      Console.WriteLine("Order count (synchronous): {0}", query.Count().ToString());

      _mgr.Clear();
      query.ExecuteAsync(GotOrders, null);
      PromptToContinue();
    }

    private void GotOrders(EntityFetchedEventArgs<Order> e) {
      Console.WriteLine("Order count (asynchronous): {0}", e.Result.Count().ToString());
    }


You might want to check the Error property of your EntityFetchedEventArgs<T> parameter to see if it tells you anything.
Back to Top
btitular View Drop Down
Newbie
Newbie
Avatar

Joined: 08-Oct-2009
Location: CA
Posts: 20 		Post Options Post Options   Quote btitular Quote  Post ReplyReply Direct Link To This Post Posted: 09-Oct-2009 at 2:00pm
Thanks for the quick reply Greg,
 
Here's the args.Error property snippet I get when I'm using the CompareTo() method:
 
{EntityServerException: Expression of type 'System.DateTime' cannot be used for parameter of type 'System.Object' of method 'Int32 CompareTo(System.Object)' ---> System.ArgumentException: Expression of type 'System.DateTime' cannot be used for parameter of type 'System.Object' of method 'Int32 CompareTo(System.Object)'
   at System.Linq.Expressions.Expression.ValidateArgumentTypes(MethodInfo method, ReadOnlyCollection`1& arguments)
   at System.Linq.Expressions.Expression.ValidateCallArgs(Expression instance, MethodInfo method, ReadOnlyCollection`1& arguments)
   at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, IEnumerable`1 arguments)
   at IdeaBlade.Linq.MethodCallExpressionBlock.<>c__DisplayClass11.<ToExpression>b__d()
   at IdeaBlade.Linq.ExpressionBlock.GetExpression(Func`1 buildExpression)
   at IdeaBlade.Linq.MethodCallExpressionBlock.ToExpression()
   at IdeaBlade.Linq.BinaryExpressionBlock.<ToExpression>b__2()
   at IdeaBlade.Linq.ExpressionBlock.GetExpression(Func`1 buildExpression)
   at IdeaBlade.Linq.BinaryExpressionBlock.ToExpression()
   at IdeaBlade.Linq.BinaryExpressionBlock.<ToExpression>b__2()
   at IdeaBlade.Linq.ExpressionBlock.GetExpression(Func`1 buildExpression)
   at IdeaBlade.Linq.BinaryExpressionBlock.ToExpression()
   at IdeaBlade.Linq.LambdaExpressionBlock.<>c__DisplayClassa.<ToExpression>b__8()
   at IdeaBlade.Linq.ExpressionBlock.GetExpression(Func`1 buildExpression)
   at IdeaBlade.Linq.LambdaExpressionBlock.ToExpression()
   at IdeaBlade.Linq.UnaryExpressionBlock.<ToExpression>b__2()
   at IdeaBlade.Linq.ExpressionBlock.GetExpression(Func`1 buildExpression)
   at IdeaBlade.Linq.UnaryExpressionBlock.ToExpression()
   at IdeaBlade.Linq.MethodCallExpressionBlock.<ToExpression>b__b(ExpressionBlock b)
   at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at IdeaBlade.Linq.MethodCallExpressionBlock.<>c__DisplayClass11.<ToExpression>b__c()
   at IdeaBlade.Linq.ExpressionBlock.GetExpression(Func`1 buildExpression)
   at IdeaBlade.Linq.MethodCallExpressionBlock.ToExpression()
   at IdeaBlade.Linq.SerializedExpression.ToExpression()
   at IdeaBlade.EntityModel.EntityQuerySurrogate.set_SerializedExpression(SerializedExpression value)
   at ReadEntityQuerySurrogateFromXml(XmlReaderDelegator , XmlObjectSerializerReadContext , XmlDictionaryString[] , XmlDictionaryString[] )
 
When I change it to a simple ">=" test, it works fine. At least for now, I've got it working.
 
Bob
 
 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down