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

Answer 2

Take a look at this link.
Maybe the advice made by Julie Lerman will be helpful in your case.