How to fix "LINQ to Entities does not recognize the method 'System.DateTime GetValueOrDefault()'"?

Question

I have this code. Basically i need to change the date and tell user that today, the store will be open from 10 am to 10 pm. So the time here is fixed.

from:-

today_date = "2020-03-23T00:00:00"

start_hour_time = "1900-01-01T10:00:00"

end_hour_time = "1900-01-01T22:00:00"

change to :-

start_newdatetime = "2020-03-23T10:00:00"

end_newdatetime = "2020-03-23T22:00:00"

DateTime todaysdate = DateTime.Now.Date;       

var find_date = bdb.tbl_store
        .Join(bdb.tbl_hour, a => a.start_hour_id, b => b.hour_id, (a, b) => new { a, b })
        .Join(bdb.tbl_hour, a => a.a.end_hour_id, b => b.hour_id, (a, b) => new { a, b })
        .Where(x => x.a.a.store_id == store_id )
        .Select(x => new {

        x.a.a.store_id,
        start_hour_time = x.a.b.hour_time,
        end_hour_time = x.b.hour_time,
        start_newdatetime = todaysdate.Date+ x.a.b.hour_time.GetValueOrDefault().TimeOfDay,
        end_newdatetime = todaysdate.Date + x.b.hour_time.GetValueOrDefault().TimeOfDay

    }) .ToList();

the problem now, it is error. Because i want it to be on the same var.

"Message": "An error has occurred.", "ExceptionMessage": "LINQ to Entities does not recognize the method 'System.DateTime
GetValueOrDefault()' method, and this method cannot be translated into a store expression.", "ExceptionType": "System.NotSupportedException",

but it will be okey if i create another var to return the new value.

Judging by [this answer](https://stackoverflow.com/a/26684466/3181933), it looks like you should be able to use `(x.a.b.hour_time ?? TimeSpan.Zero)`. — ProgrammingLlama, Mar 27 '20 at 04:42
I have tried it from that answer, it does not work. I cannot simply add ?? after the x.a.b.hour_time as it will return error. — user13132969, Mar 27 '20 at 04:46
Operator '??' cannot be applied to operands of type 'DateTime?' and 'TimeSpan' — user13132969, Mar 27 '20 at 04:48
Oh, my bad. Sorry. `(x.b.hour_time ?? DateTime.MinValue).TimeOfDay` — ProgrammingLlama, Mar 27 '20 at 04:49
Thank you for replying my question. But it return error. --- 'Message": "An error has occurred.", "ExceptionMessage": "The specified type member 'TimeOfDay' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.","ExceptionType": "System.NotSupportedException", — user13132969, Mar 27 '20 at 04:53
In that case I'll vote to re-open but you ought to edit your question to mention this new error as well. :) — ProgrammingLlama, Mar 27 '20 at 04:54
Side note: it would be far easier to just run that operation client side (not in SQL)... — Alexei Levenkov, Mar 27 '20 at 04:56

score 0 · Accepted Answer · answered Mar 27 '20 at 05:46

Your expression does not seem to work, since the members GetValueOrDefault and TimeOfDay are not available for EF DB query translation, therefor you'll somehow have to work around the issue somehow. At the moment I'm not able to verify, but I think the following should be possible

var find_date = bdb.tbl_store
        .Join(bdb.tbl_hour, a => a.start_hour_id, b => b.hour_id, (a, b) => new { a, b })
        .Join(bdb.tbl_hour, a => a.a.end_hour_id, b => b.hour_id, (a, b) => new { a, b })
        .Where(x => x.a.a.store_id == store_id )
        .ToList()
        .Select(x => new {
            x.a.a.store_id,
            start_hour_time = x.a.b.hour_time,
            end_hour_time = x.b.hour_time,
            start_newdatetime = todaysdate.Date + x.a.b.hour_time.GetValueOrDefault().TimeOfDay,
            end_newdatetime = todaysdate.Date + x.b.hour_time.GetValueOrDefault().TimeOfDay
    }).ToList();

This way, your query is run when calling ToList and everything else (the Lambda expression passed to Select) is executed as plain C# on the client, hence there's really no reason for Entity to complain. Admittedly, I do not know for sure, whether these objects are fully resolved when calling ToList. If it's not the case, you could do something like this

var find_date = bdb.tbl_store
        .Join(bdb.tbl_hour, a => a.start_hour_id, b => b.hour_id, (a, b) => new { a, b })
        .Join(bdb.tbl_hour, a => a.a.end_hour_id, b => b.hour_id, (a, b) => new { a, b })
        .Where(x => x.a.a.store_id == store_id)
        .Select(x => new {
            x.a.a.store_id,
            start_hour_time = x.a.b.hour_time,
            end_hour_time = x.b.hour_time 
        })
        .ToList()
        .Select(x => new {
            store_id,
            start_hour_time,
            end_hour_time,
            start_newdatetime = todaysdate.Date + start_hour_time.GetValueOrDefault().TimeOfDay,
            end_newdatetime = todaysdate.Date + end_hour_time.GetValueOrDefault().TimeOfDay
        }).ToList();

How to fix "LINQ to Entities does not recognize the method 'System.DateTime GetValueOrDefault()'"?

1 Answers1