Print Page | Close Window

Queries Filtered on DateTime data type

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1513
Printed Date: 08-Apr-2025 at 2:28am


Topic: Queries Filtered on DateTime data type
Posted By: btitular
Subject: Queries Filtered on DateTime data type
Date 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?
 


Replies:
Posted By: btitular
Date 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!

Posted By: GregD
Date 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.

Posted By: btitular
Date 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
 
 


Print Page | Close Window