0

I'm using this model:

public partial class users_logged
{
    public string UserName { get; set; }
    public System.DateTime Date { get; set; }
    public Nullable<double> TimeSpent { get; set; }
}

And I'm trying to get the data of the users logged yesterday with this Linq query:

var innerJoinQuery = (from t in context.users_logged
                      where t.Date.Date == DateTime.Now.Date.AddDays(-1)
                      select new
                      {
                          UserName = t.UserName,
                          Date = t.Date,
                          TimeSpent = t.TimeSpent
                      }).ToList();

(users_logged.Date or t.Date is a datetime field, I just want to get the day from this)

But when I'm running it, I get this exception:

[NotSupportedException: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.]

I understand the error but I'm not able to solve it. I tried to use DbFunctions.TruncateTime but my MySQL DB doesn't support it. Any tips?

Igor
  • 60,821
  • 10
  • 100
  • 175
Jose M Martin
  • 373
  • 1
  • 3
  • 19
  • `where t.Date.Date` why the 2nd .Date? – EpicKip Mar 08 '17 at 13:20
  • @EpicKip - attempt to trim the time. The 1st date is just a name of the property. – Igor Mar 08 '17 at 13:21
  • Ah ok was wondering, thanks @Igor – EpicKip Mar 08 '17 at 13:21
  • @EpicKip Because t.Date is a Datetime field/value and I just want the Date. – Jose M Martin Mar 08 '17 at 13:22
  • I renamed your title of the question to make it clear on what you are actually trying to do and nominated it for a re-open. – Igor Mar 08 '17 at 13:24
  • @Christos - with the updated question `How to truncate time from date in EF with MySql DB`, do you still see a duplicate? – Igor Mar 08 '17 at 13:25
  • Just to be clear, you have tried `DbFunctions.TruncateTime(x.Date) == DateTime.Now.Date.AddDays(-1)` correct? – Igor Mar 08 '17 at 13:26
  • 1
    @Igor I read again the question and I would vote to reopen provided that Jose verify that neither of the solutions provided in the link I have mentioned solves his problem. – Christos Mar 08 '17 at 13:26
  • @Christos - completely agree. Thanks. – Igor Mar 08 '17 at 13:27
  • 1
    @Igor You are very welcome. Regarding that I mentioned before, if you check the link there is a similar approach like that you have written `DbFunctions.TruncateTime`. Once more, if Jose verify that none of the solutions provided there solves his problem, I would vote to repopen the question. – Christos Mar 08 '17 at 13:29
  • @Igor @Christos I tried with that instruction already and I get this error: `LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.` – Jose M Martin Mar 08 '17 at 13:33
  • `var dateCompare = DateTime.Now.Date.AddDays(-1);` and then in the query `where DbFunctions.TruncateTime(t.Date) == dateCompare`. The issue is that the store is trying to translate `DateTime.Now.Date.AddDays`, so move that out of the linq expression (above it). That should fix the first issue. – Igor Mar 08 '17 at 13:35
  • @Igor I get this error: `[MySqlException (0x80004005): FUNCTION mydb.TruncateTime does not exist]` because I'm working in a MySQL database. – Jose M Martin Mar 08 '17 at 13:37
  • Have you seen this answer yet? http://stackoverflow.com/a/19731414/1260204 – Igor Mar 08 '17 at 13:38
  • @Igor yes I saw it. I was considering that as the last option, because I prefer not to modify the database. But if there is no other option... – Jose M Martin Mar 08 '17 at 13:41

0 Answers0