Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery' to type of 'System.Web.Mvc.SelectList'

Problem:

Had a solution using the Entity Framework which required a query modification. 


Solution was:
return new SelectList(db.Offices, "Office_ID", "Office_Name");


Changed too:


public class SecurityGroup
{
public string ADGroupSID { get; set; }
public string ADGroupName { get; set; }
public Guid Office_ID { get; set; }
public string ADUser { get; set; }
}


List<FCJC.Model.SecurityGroup> userOffice = theUser.GroupMembership.FindAll(g => ControllerHelper.GetAllOffices().Contains(g.Office_ID)).ToList();









var test = (from o in db.Offices

             join oa in db.OfficeADGroups on o.Office_ID equals oa.Office_ID
           where oa.ADGroup_SID.Equals(userOffice.Select(uo => uo.ADGroupSID).ToString())
select new
{
o.Office_ID,
o.Office_Name
}).AsEnumerable();
return new SelectList(test, "Office_ID", "Office_Name");


The problem did not arise until it ran.  The query compiled but since we are dealing with LINQ the query did not execute until runtime.  Then I received the following error:

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType9`2[System.Guid,System.String]]' to type 'System.Web.Mvc.SelectList'

Solution:

The problem was the lack of support from Entity Framework.  It will only support primitive data types.  The query uses anonymous typing until it is executed.  This process then tries to resolve the data type.  This will not support the use of custom data types.  I tried a number of work arounds to include forcing the execution of the query and then applying a cast to the SelectListItem type.


Try 1 - Fail
 var test = (from o in db.Offices
  ().Select(o => o.Office_ID) on oa.Office_ID equals uo.Office_ID
                        select new
                        {
                            o.Office_ID,
                            o.Office_Name
                        }).AsEnumerable()
            .Where(o => o.Office_ID.Equals(userOffice.Select(uo => uo.Office_ID)))
         
            new SelectListItem
            {
                Value = x.Office_ID.ToString(),
                Text = x.Office_Name
            });


This will work in Linq to SQL but is not supported in the current version of Entity Framework (v6).
The inclusion of the userOffice LINQ subquery is not supported in EF which will only support constant values.  That ruled out any sort of variable or collection.


Got it finally!


Solution was to take the LINQ out and do a direct query:


   using (var ctx = new FCJCModel())
            {
                var sql = "select Office_ID,Office_Name,Address_ID,Archive,LastUpdatedDate,LastUpdatedUser from office where Office_ID in (" + ofcGuids.Replace("\"", "'") + ")";
                var kk = ctx.Offices.SqlQuery(sql).ToList();
                return new SelectList(kk, "Office_ID", "Office_Name");
            }

Source:

1. http://stackoverflow.com/questions/15211362/only-primitive-types-or-enumeration-types-are-supported-in-this-context
2. http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx