Entity Framework datetime vs SQL Server date

Question

I'm using Entity Framework 4.3. In my SQL Server 2008 db, I have some dates stored as dates rather than datetime.

I need to retrieve data using the date field, here a test code sample:

    public static Applicant Create()
    {   
        var dt = new DateTime(1967, 08, 03);

        var r = new CrudRepo<Applicant>(UowHelper.GetUow().Context);

        return r.Find(a => a.DateOfBirth == dt).Single();
    }

However, I'm getting an issue with 'missing sequence'.

Any ideas as to how I get around this?

I'll also need to update the database too at some point.

Thanks.

On which line do get the error? have you stepped into the crudrepo code? — Quinton Bernhardt
– Quinton Bernhardt, Commented Jan 21, 2013 at 17:55
Can you elaborate more on what 'missing sequence' is? Is it 'Sequence contains no elements'? If this is the case then it is because .Find did not find any element. What is the .Find method and the r btw? — Pawel
– Pawel, Commented Jan 21, 2013 at 18:24
@Pawel - please see comments for spajce lower down. Basically when I try the match using linq I get no match - when I return the row then test a.DateOfBirth against dt - it works!!! — dotnetnoob
– dotnetnoob, Commented Jan 21, 2013 at 18:44

spajce · Accepted Answer · 2013-01-21 18:21:54Z

3

The actual result of var dt = new DateTime(1967, 08, 03); is 8/3/1967 12:00:00 AM

The DataType of the DateOfBirth is Date so we need to TruncateTime the time.

msdn: EntityFunctions.TruncateTime Method

public static Applicant Create()
    {   
        var dt = new DateTime(1967, 08, 03);

        var r = new CrudRepo<Applicant>(UowHelper.GetUow().Context);

        return r.FirstOrDefault(a => a.DateOfBirth == EntityFunctions.TruncateTime(dt));
    }

or remove the .Single() method instead use the .FirstOrDefault() Method

edited Jan 21, 2013 at 18:21

answered Jan 21, 2013 at 17:53

spajce

7,0905 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dotnetnoob Over a year ago

Made the change but still getting the same issue - I've posted the error above. Find is a custom method that returns Iqueryable, so single() required.

spajce Over a year ago

try to replace .Find() to .FirstOrDefault() i updated my answer

dotnetnoob Over a year ago

dt = a.DateOfBirth - I've just tested, so I must be doing something dopey!

dotnetnoob Over a year ago

ok here's the weird thing. If I try to select using a.DateOfBirth = dt, then nothing is returned. If I return the row using another variable, then test against a.DateOfBirth against dt, it works!! Odd!!

dotnetnoob Over a year ago

Got it! I saud it would be something dopey - well it is, sort of. When creating the DateTime variable dt - I got the month/year the wrong way round, however when I output dt and a.DateOfBirth, both came out in the format I wanted to see 08/03/1967, hence I didnt notice that dt was actually 03/08/1967. Your truncate function worked for me after this was corrected. Thanks.

Serj Sagan · Accepted Answer · 2015-10-14 01:59:49Z

0

FYI, to add to spajce's answer,

System.Data.Entity.Core.Objects.EntityFunctions

has been deprecated to

System.Data.Entity.DbFunctions

answered Oct 14, 2015 at 1:59

Serj Sagan

30.6k17 gold badges159 silver badges190 bronze badges

Collectives™ on Stack Overflow

Entity Framework datetime vs SQL Server date

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related