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, Jan 21 '13 at 17:55
check http://stackoverflow.com/questions/1324199/sequence-contains-no-elements — spajce, Jan 21 '13 at 18:20
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, Jan 21 '13 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, Jan 21 '13 at 18:44

spajce · Accepted Answer · 2013-01-21T18:21:54.520

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 '13 at 18:21

answered Jan 21 '13 at 17:53

spajce

7,044
5
29
44

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. – dotnetnoob Jan 21 '13 at 18:18
try to replace `.Find()` to `.FirstOrDefault()` i updated my answer – spajce Jan 21 '13 at 18:22
dt = a.DateOfBirth - I've just tested, so I must be doing something dopey! – dotnetnoob Jan 21 '13 at 18:37
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 Jan 21 '13 at 18:42
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. – dotnetnoob Jan 21 '13 at 19:13

score 0 · Answer 2 · answered Oct 14 '15 at 01:59

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 '15 at 01:59

Serj Sagan

28,927
17
154
183

Entity Framework datetime vs SQL Server date

2 Answers2