How do you sort by a property of an ICollection?

Question

I'm trying to figure out the display or LINQ call to an ICollection of a model parameter. Specifically here is my model for PurchReq (purchase requisitions):

public enum FiscalYear
{
    [Display(Name="2013")]
    LastYear,
    [Display(Name="2014")]
    ThisYear,
    [Display(Name="2015")]
    NextYear
}

public class PurchReq
{
    [Key]
    public int PurchReqID { get; set; }
    [Display(Name="Fiscal Year")]
    public FiscalYear FiscalYear { get; set; }
    //[ForeignKey("Project")]
    //public int ProjectID { get; set; }
    [Required]
    [Display(Name="Purchase Requisition Number")]
    public string PurchReqNum { get; set; }
    public string GLCode { get; set; }
    public string Fund { get; set; }
    public string Division { get; set; }
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Purch Req Created")]
    public DateTime? DateCreated { get; set; }
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Purch Req Submitted")]
    public DateTime? DateSubmitted { get; set; }

    //many projects to many purchReqs
    //public virtual Project Project { get; set; }
    public virtual ICollection<Project> Projects { get; set; }

    public PurchReq()
    {
        DateCreated = System.DateTime.Now;
        Projects = new HashSet<Project>();
    }

And here is my PurchReqController for the index method:

  var purchReqs = from p in db.PurchReqs
                        select p;

        if (!string.IsNullOrEmpty(searchString))
        {
            purchReqs = purchReqs.Where(s => s.Projects.Any(item => item.Vendor.VendorName.Contains(searchString)));
        }
        switch (sortOrder)
        {
            case "ProjectName_desc":;
                purchReqs = purchReqs.OrderByDescending(s => s.Projects);
                    //Any(item => item.Vendor.VendorName).ThenBy(n => n.Project.Description));
                break;
            case "PurchReqNum_desc":
                purchReqs = purchReqs.OrderByDescending(s => s.PurchReqNum);
                break;
            case "PurchReqNum_asc":
                purchReqs = purchReqs.OrderBy(s => s.PurchReqNum);
                break;
            case "FiscalYear_desc":
                purchReqs = purchReqs.OrderByDescending(s => s.FiscalYear);
                break;
            case "FiscalYear_asc":
                purchReqs = purchReqs.OrderBy(s => s.FiscalYear);
                break;
            case "GLCode_desc":
                purchReqs = purchReqs.OrderByDescending(s => s.GLCode);
                break;
            case "GLCode_asc":
                 purchReqs = purchReqs.OrderBy(s => s.GLCode);
                break;
            case "Fund_desc":
                purchReqs = purchReqs.OrderByDescending(s => s.Fund);
                break;
            case "Fund_asc":
                purchReqs = purchReqs.OrderBy(s => s.Fund);
                break;
            case "Division_desc":
                purchReqs = purchReqs.OrderByDescending(s => s.Division);
                break;
            case "Division_asc":
                purchReqs = purchReqs.OrderBy(s => s.Division);
                break;
            case "DateCreated_desc":
                purchReqs = purchReqs.OrderByDescending(s => s.DateCreated);
                break;
            case "DateCreated_asc":
                purchReqs = purchReqs.OrderBy(s => s.DateCreated);
                break;
            case "DateSubmitted_desc":
                purchReqs = purchReqs.OrderByDescending(s => s.DateSubmitted);
                break;
            case "DateSubmitted_asc":
                purchReqs = purchReqs.OrderBy(s => s.DateSubmitted);
                break;
            default:
                purchReqs = purchReqs.OrderBy(s => s.Projects);
                //purchReqs = purchReqs.OrderBy(s => s.Projects.Any(item => item.Vendor.VendorName)).ThenBy(n => n.Project.Description);
                break;
        }
        return View(purchReqs);

The controller above throws a DbSort error because it can't sort the ICollection Projects, which is understandable. I need to sort the collection by Projects.Project.Vendor.VendorName. This originally worked when PurchReq to Project was many-to-one but has recently changed to many-to-many. Any advice and help would be greatly appreciated.

Answer 1

I know I'm super late to this thread and am no expert in MVC, but as far as I understood about ordering collections, it works by taking one value and comparing it to another from the same column. Basically, you need to Select one field from the desired collection and supply it to the OrderBy lambda.

As simple as possible, it gets kind of like this:

IQueryable purchReq = context.PurchReq.OrderBy(p => p.Projects.Select(pj => pj.Project).FirstOrDefault().Select(v => v.Vendor.VendorName).FirstOrDefault());

If Project has a 1 to n relationship to Vendor as well, replace

Select(v => v.Vendor.VendorName);

by

Select(v => v.Vendor).FirstOrDefault().VendorName

Hope this helps.

Answer 2

Try this:

purchReqs = purchReqs.OrderBy(pr => pr.Projects.Count > 0 ? pr.Projects.First().Vendor.VendorName : "")
                     .ThenBy (pr => pr.Projects.Count > 0 ? pr.Projects.First().Description       : "");

Here we use First instead of Any; it gives us some suitable element (it doesn't matter which, of course, but Any just gives us a Boolean and there's nothing else specific that lets us pick one at semi-random).

In C# 6, you'll be able to do something like this, I think, using the null-propagating member access operator:

purchReqs = purchReqs.OrderBy(pr => pr.Projects.FirstOrDefault()?.Vendor?.VendorName)
                     .ThenBy (pr => pr.Projects.FirstOrDefault()?.Description);

Either way, the same structure is used for both sorting calls, assuming that a Project has a Description property.