Converting string to nullable DayOfWeek field using Linq cause an error

Question

In my sql database WorkDay field is in string format and in model it is nullable DayOfWeek, i.e public DayOfWeek? WorkDay { get; set; }. While Converting database WorkDay field into model WorkDay field it will generate an error like:

Could not translate expression 'Table(StaffSchedule)' into SQL and could not treat it as a local expression.

I have also tried to create three different linq statements which are as below.

1) Retrieve Data from StaffSchedule table.
2) Apply select operation on it.
3) Apply AddRange operation on selected data.

results.AddRange(dataContext.StaffSchedules
                            .Where(x => !x.Excluded)
                            .Where(x => x.DistrictID == districtId && x.District.Active && (x.Position == positionTeacher || x.Position == positionDirector || x.Position == positionAssistant || x.Position == positionAssistantDirector))
                           .Select(x => new Core.StaffSchedule()
                            {
                                ID = x.ID,
                                Staff = x.Staff.SelectSummary(),
                                Position = (StaffPosition)Enum.Parse(typeof(StaffPosition), x.Position, true),
                                Class = refs.Class,
                                District = x.District.SelectSummary(),
                                Time = null,
                                Reoccurring = false,
                                Inherited = true,
                                ReoccourringStart = x.ReoccourringStart,
                                ReoccourringEnd = x.ReoccourringEnd,
                                WorkDay = x.WorkDay == null ? (DayOfWeek?)null : (DayOfWeek)Enum.Parse(typeof(DayOfWeek), x.WorkDay, true)
                            }));

This is the conversion code for string to nullable DayOfWeek field. Which cause an error in my case.

WorkDay = x.WorkDay == null ? (DayOfWeek?)null : (DayOfWeek)Enum.Parse(typeof(DayOfWeek), x.WorkDay, true)

I have already gone through below link.

How to solve issue "Could not translate expression ...into SQL and could not treat it as a local expression."

Why does it say 'Table(StaffSchedule)' in the exception message? What is the *exact* type of `dataContext.StaffSchedules`? — Gert Arnold, Mar 20 '18 at 14:35

score 1 · Answer 1 · answered Mar 20 '18 at 14:06

Try to convert dataContext.StaffSchedules to IEnumerable by calling ToList() method before making the query like this

results.AddRange(dataContext.StaffSchedules.ToList()
                        .Where(x => !x.Excluded)....the rest of you query

Search for difference between IEnumerable and IQueryable for more detailed explain

score 1 · Answer 2 · answered Mar 20 '18 at 14:21

You can't translate any C# code to SQL so x.WorkDay == null ? (DayOfWeek?)null : (DayOfWeek)Enum.Parse(typeof(DayOfWeek), x.WorkDay, true) won't work in Linq to Entities.

Try to select your data after the query execution by writing AsEnumerable() before Select. Don't do it at the beginning of the query because you will fetch all the data from a db table.

results.AddRange(dataContext.StaffSchedules
    //everything (well almost) from this point is going to be translated into SQL
    .Where(x => !x.Excluded)
    .AsEnumerable() //everything from here is going to be executed after the query ends so it can be any C# code
    .Select(x => new Core.StaffSchedule()
    {
        //now this should work
        WorkDay = x.WorkDay == null ? (DayOfWeek?)null : (DayOfWeek)Enum.Parse(typeof(DayOfWeek), x.WorkDay, true)
    });

Converting string to nullable DayOfWeek field using Linq cause an error

2 Answers2