This method is not supported against a materialized query result

Question

Take a look at my code here:

public static ItemType GetItem(int id)
{
    ItemType it = new ItemType();
    using (var context = matrix2.matrix2core.DataAccess.Connection.GetContext())
    {
        var q = (from ci in context.Item
                 where ci.ID == id
                 let TemplateID = ci.TemplateID
                 let Groups = from x in context.CriteriaGroup
                              where x.TemplateID == TemplateID
                              select new
                              {
                                  x
                              }
                 let CriteriaItems = from x in context.CriteriaItem
                                     where Groups.Select(y => y.x.ID).Contains(x.CriteriaGroupID)
                                     select new
                                     {
                                         x
                                     }
                 select new
                 {
                     ci.ID,
                     ci.Name,
                     ci.CategoryID,
                     ci.Description,
                     ci.ItemValue,
                     TemplateID,
                     Groups,
                     CriteriaItems,
                     ItemValues = from x in context.ItemValue
                                  where x.ItemID == id
                                  select new
                                  {
                                      x,
                                      CriteriaID = x.CriteriaItem.Criteria.ID
                                  }
                 }).FirstOrDefault();

        if (q != null)
        {
            it.ID = q.ID;
            it.CategoryID = q.CategoryID;
            it.Name = q.Name;
            it.TemplateID = q.TemplateID;
            it.Description = q.Description;
            it.CriteriaGroups = new List<CriteriaGroupType>();
            it.CriteriaItems = new List<CriteriaItemType>();
            it.ItemValues = new List<ItemValueType>();

            foreach (var x in q.ItemValues)
            {
                ItemValueType ivt = new ItemValueType();
                ivt.CriteriaItemID = x.x.CriteriaItemID;
                ivt.CriteriaID = x.CriteriaID;
                ivt.Data = x.x.Data;
                ivt.ID = x.x.ID;
                ivt.ItemID = x.x.ItemID;
                it.ItemValues.Add(ivt);
            }

            /////////error when I added the orderby clause
            foreach (var x in q.Groups.OrderBy(x => x.x.SortOrder))
            {
                CriteriaGroupType cgt = new CriteriaGroupType();
                cgt.ID = x.x.ID;
                cgt.Name = !string.IsNullOrEmpty(x.x.Name) ? x.x.Name : "Group" + x.x.ID;
                cgt.SortOrder = x.x.SortOrder;
                cgt.TemplateID = x.x.TemplateID;
                it.CriteriaGroups.Add(cgt);
            }

            /////////error when I added the orderby clause 
            foreach (var temp in q.CriteriaItems.OrderBy(x => x.x.SortOrder))
            {
                CriteriaItemType cit = new CriteriaItemType();
                cit.ID = temp.x.ID;
                cit.CriteriaGroupID = temp.x.CriteriaGroupID;
                cit.GroupName = (temp.x.Name != null) ? temp.x.Name : "Group" + temp.x.ID;
                cit.CriteriaID = temp.x.CriteriaID;
                cit.CriteriaName = temp.x.Criteria.Name;
                cit.Name = !string.IsNullOrEmpty(temp.x.Name) ? temp.x.Name : temp.x.Criteria.Name;
                cit.Options = temp.x.Options;
                it.CriteriaItems.Add(cit);
            }
        }
    }
    return it;
}

Instead of letting SQL handle the sorting (OrderBy) I wanted asp.net to do the sorting instead. I took the sorting out of the SQL linq query and put it on the foreach loop. When I did that I got the error. Is there a way to fix this?

Answer 1

You should be able to go from IQueryable to IEnumerable with a simple

var q2 = q.ToList();

What I meant of course was :

var groups = q.Groups.ToList();