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.

Answer 1

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();