How do I search for date only from a datetime field in entity framwork?

Question

0.00/5 (No votes)

See more:

Hi All,
I am querying from table that contains a date and time i.e "2016-08-16 09:26:30.413"

when i query using the table using c# like below i get no results .. for example;

C#

DateTime date = "2016-08-16"
DateTime date2 = "2016-08-16 09:26:30.413"
var getdate = context.Table.Where(x => x.Datefield == date).FirstOrDefault(); //This returns no results
var getdate = context.Table.Where(x => x.Datefield == date)2.FirstOrDefault(); // thsi returns the results.

How would i go about in searching for Date Only using Entitty Framwork?

What I have tried:

I have just been searching online on how i can go about in achieving this but still no luck!

Posted 14-Nov-17 21:45pm

1Future

Updated 15-Nov-17 6:51am

Add a Solution

2 solutions

Solution 2

There are a number of options you can use to find the records without loading the entire table into memory.

For example, this one works and will take advantage of an index on the date column:

C#

DateTime date = new DateTime(2016, 8, 6);
DateTime nextDay = date.AddDays(1);

var entity = context.Table.FirstOrDefault(x => x.Datefield >= date && x.Datefield < nextDay);

This one won't be able to use an index, but will still work:

C#

DateTime date = new DateTime(2016, 8, 6);
var entity = context.Table.FirstOrDefault(x => DbFunctions.TruncateTime(x.Datefield) == date);

Posted 15-Nov-17 6:51am

Richard Deeming

Comments

Karthik_Mahalingam 15-Nov-17 22:33pm

5

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-11-14T21:54:00

Solution 1

try

C#

string inputdate = "2016-08-16";
var val = context.Table.ToList().Where(k => k.DateField.ToString("yyyy-MM-dd") == inputdate).FirstOrDefault();

please refer Richard's solution for alternate and efficient way to achieve this

Posted 14-Nov-17 21:54pm

Karthik_Mahalingam

Updated 15-Nov-17 14:55pm

v3

Comments

1Future 15-Nov-17 4:08am

Hi , Thank you for your time.. i'm now getting the folllowing 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.

Karthik_Mahalingam 15-Nov-17 4:11am

DateField is string or DateTime Type ?

1Future 15-Nov-17 4:12am

Its DateTime Type

Karthik_Mahalingam 15-Nov-17 4:17am

 string inputdate = "2016-08-16";
context.Table.ToList().Where(k => k.DateField.ToString("yyyy-MM-dd") == inputdate).FirstOrDefault();

1Future 15-Nov-17 4:20am

Hi , am i missing out anything different in this from the first solution you provided? I'm still getting the same error unfortunately

Karthik_Mahalingam 15-Nov-17 4:22am

.ToList()

1Future 15-Nov-17 4:41am

Thank you .. its now working

Karthik_Mahalingam 15-Nov-17 4:42am

welcome

Richard Deeming 15-Nov-17 12:47pm

Sorry, but that seems like a terrible idea!

You're pulling the entire data-set into memory, and then converting the date to a string in order to compare it.

There are much better approaches you can use. :)

Karthik_Mahalingam 15-Nov-17 20:59pm

Yes Iam aware of it. I was thinking from the exact question point of view.. my solution won't work for date comparison also, the OP was bothered of exact date so posted this..
I have mentioned to refer your solution that would help him or anyone in the future who visits this thread.
Thanks for pointing out..