How to include DateTime.Compare() in a EF statement? RSS

• • Reply
  

  claudia888

  Member

  181 Points

  435 Posts

  How to include DateTime.Compare() in a EF statement?

  Mar 01, 2012 06:26 PM|LINK

  Hi,

  I'm building a MVC3 app with ASP.NET4 and EF4. I'm adding some very simple code to a EF statement involving DateTime.Compare(), but could not make it work. I searched around and saw others using similar statements; can anyone here give some hint on what is wrong - the following code is inside a method in a controller.cs:

  Old Code (WORKS):

  var row = db.table1.Single(r => r.MemberID == table2.MessageFrom && table2.MessageTo == currentUser);

  New Code (Does NOT work; it compiles ok, but "row" returns Null, even though the db table has qualified row):

  var row = db.table1.Single(r => r.MemberID == table2.MessageFrom && table2.MessageTo == currentUser

                                          && DateTime.Compare(r.JoinGroupRequestDate, table2.MessageSentDateTime) == 0);

  Note: only the underlined part is added; and both r.JoinGroupRequestDate and table2.MessageSentDateTime are "DateTime" by definition; I went to the 2 tables and see the values, they are exactly the same.

  I also tried r.JoinGroupRequestDate == table2.MessageSentDateTime but it does not work either; same reult - row returns null.

  I feel that there is a very simple reason but I don't know what it is. Can anyone help here?

  BTW - I also tried to use LINQ but encountered the same problem.

  Update - I read more and some people say that the DateTime manipulation will not work in a LINQ or EF statement. If this is true, how do I solve my problem, when I need to compare 2 DateTime values from 2 rows in 2 tables? Any sample code is greatly appreciated!

  I will wait and watch for any suggestions. Thanks!

  CLaudia

  • Edited by claudia888 on Mar 01, 2012 06:59 PM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 03, 2012 12:25 AM|LINK

  Hello：）

  Maybe it includes the DateTime。I think you can change to this：

  r.JoinGroupRequestDate.ToString("yyyy/MM/dd") == table2.MessageSentDateTime.ToString("yyyy/MM/dd")

  Plz make sure that your DateTime value has no of Null values。

• • Reply
  

  claudia888

  Member

  181 Points

  435 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 03, 2012 04:40 AM|LINK

  Hi Decker,

  Thanks for the info. Both DateTime have values, not Null.

  I tried the code you suggested, compiled it fine; but when I ran the app, it gave me this error:

  LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

  Also, I tried this in the code:

  new DateTime mTime = table2.MessageSentDateTime;  ==> where VS gave an error on "mTime": a new expression requires (), [] or {} after type.

  1) What does this message mean?

  2) If my table's definition for table2.MessageSentDateTime is "DateTIme", why is the above code not correct?

  Please let me know your thoughts?

  Thanks,

  Claudia

  • Edited by claudia888 on Mar 03, 2012 04:53 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 03, 2012 04:56 AM|LINK

  Plz show us your full statement of LINQ in C#……

• • Reply
  

  claudia888

  Member

  181 Points

  435 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 03, 2012 05:27 AM|LINK

  1) Old code, works:

  var row = db.table1.Single(r => r.MemberID == table2.MessageFrom && table2.MessageTo == currentUser);

  2) Trying your suggestion, not working

  var row = db.table1.Single(r => r.MemberID == table2.MessageFrom && table2.MessageTo == currentUser
                                   && r.JoinGroupRequestDate.ToString("yyyy/MM/dd") == table2.MessageSentDateTime.ToString("yyyy/MM/dd"));

  3) does not work:

  var row = db.table1.Single(r => r.MemberID == table2.MessageFrom && table2.MessageTo == currentUser
                                   && r.JoinGroupRequestDate == table2.MessageSentDateTime);

  4) does not work:

   var row = (from r in db.table1
                             where r.MemberID == table2.MessageFrom && table2.MessageTo == currentUser
                                   && r.JoinGroupRequestDate.ToString("yyyy/MM/dd") == table2.MessageSentDateTime.ToString("yyyy/MM/dd")
                             select r).FirstOrDefault();

  Thanks,

  Claudia

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 03, 2012 07:59 AM|LINK

  Hello again：）

  var row = (from r in db.table1.AsEnumerable()
                             where r.MemberID == table2.MessageFrom && table2.MessageTo == currentUser 
                                   && r.JoinGroupRequestDate.ToString("yyyy/MM/dd") == table2.MessageSentDateTime.ToString("yyyy/MM/dd")
                             select r).FirstOrDefault();

  • Marked as answer by claudia888 on Mar 03, 2012 04:10 PM
• • Reply
  

  claudia888

  Member

  181 Points

  435 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 03, 2012 04:14 PM|LINK

  Hi Decker,

  That works! Thanks a lot!

  Can you explain or provide some info on these - I'd like to understand exactly why this worked:

  1) AsEnumerable() - why must I use this even if I'm sure that there will only be 1 row qualified based on my query?  BTW, I'm reading it on msdn.com and other code examples, I think I understand the concept and benefit of AsEnumerable, but I still cannot understand why in this case using "AsEnumerable()" made my query work; not using it - does not work?

  2) .ToString("yyyy/MM/dd") - why can't I compare 2 DateTime values directly, just the same as comparing 2 int values or strings? I read that there are many patterns for DateTime.ToString() - is it because that there is NO Default pattern for a DateTime value, so in order to compare 2 DateTime values, we will have to format them into the same pattern, then compare? Please confirm.

  Thanks,

  Claudia

  • Edited by claudia888 on Mar 03, 2012 04:56 PM
  • Edited by claudia888 on Mar 03, 2012 04:57 PM
  • Edited by claudia888 on Mar 03, 2012 05:25 PM
  • Edited by claudia888 on Mar 03, 2012 05:53 PM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 03, 2012 11:58 PM|LINK

  claudia888

  1) AsEnumerable() - why must I use this even if I'm sure that there will only be 1 row qualified based on my query?  BTW, I'm reading it on msdn.com and other code examples, I think I understand the concept and benefit of AsEnumerable, but I still cannot understand why in this case using "AsEnumerable()" made my query work; not using it - does not work?

  Because LINQ-TO-ENTITY OR LING-TO-SQL cannot change everything to a standard SQL statement，so when you use AsEnumerable，it will convert from linq-to-sql to linq-to-object，And you can do that。

  claudia888

  2) .ToString("yyyy/MM/dd") - why can't I compare 2 DateTime values directly, just the same as comparing 2 int values or strings? I read that there are many patterns for DateTime.ToString() - is it because that there is NO Default pattern for a DateTime value, so in order to compare 2 DateTime values, we will have to format them into the same pattern, then compare? Please confirm.

  DateTime not only includes year，month and day，but time as well。I guess it's the timespan that causes the differences。So I've used ToString formation to ignore it。

  • Marked as answer by decker dong - msft on Mar 07, 2012 12:49 AM
• • Reply
  

  claudia888

  Member

  181 Points

  435 Posts

  Re: How to include DateTime.Compare() in a EF statement?

  Mar 04, 2012 06:02 PM|LINK

  Thanks a lot for your help Decker!

  Claudia