-4

I have an SQL server database with a column named "Time" of format "datetime".

In C# Entity Framework, I want to get all rows whose date is after 8am of today:

    using (var db = new intrabaseEntities())
        {
            var getQ = db.intrads.Where(s => s.Time.Value.TimeOfDay >= TimeSpan.Parse("8:00") & s.Time >= DateTime.Now.Date);

            foreach (var x in getQ)
            {
                MessageBox.Show(x.ToString());
            }
        }

The code shows no error in the compiler but throws an exception:

System.NotSupportedException {"The specified type member 'TimeOfDay' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."}

How can I get the query to select all values whose date is after 8am of today?

This code retrieves the rows at the date of today, but I also need to differentiate by the daytime after 8am of today:

 var getQ =
                db.intrads.Where(s => DbFunctions.TruncateTime(s.Time) == DbFunctions.TruncateTime(DateTime.Today));

closed as off-topic by Shawn Melton, Mark Storey-Smith, Michael Green, Max Vernon, Tom V Nov 30 '15 at 6:20

If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Overall I think you would be better suited by deleting the question here and reposting on StackOverflow because this is more a question about entity framework than the database. – Erik Nov 29 '15 at 23:22
  • @Erik Since this question is about a query to an SQL database, it is correctly posted in the Database Admin site. – emcor Nov 29 '15 at 23:29
  • 3
    Your question belongs on SO, you may be querying a SQL Server database but you are using entity-framework which does not use traditional t-sql language format. You can see looking at the other questions under the same tag on this site (50 questions) that there is not much attention put toward this topic, compared to SO (52k questions). – Shawn Melton Nov 30 '15 at 0:58
6

Your answer will probably look something like this:

// I don't know why you weren't using UTC time in your sample. That leaves you at the mercy
// of your server's time zone which is rarely a good policy. If you must though you can
// replace `UtcNow` with `Now`.
var getQ = db.intrads.Where(s => s.Time >= DateTime.UtcNow.Date.AddHours(8));

I don't know anything about the Entity Framework and I don't really know what your models/schema looks like. This is just a shot in the dark based on my knowledge of C# and LINQ in general. Based on the error message you provided I think Entity Framework was telling you that the TimeOfDay property doesn't match anything in your database.

Finally it appears as if your Time property is nullable. Your original query would blow up if you tried to run it with a null value. If you have to get a value out of a Nullable<T> then it is a much better plan to check for null or use the GetValueOrDefault() method instead of Value.

Not the answer you're looking for? Browse other questions tagged or ask your own question.