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.

I get a DBSort Error for the projects collection. I would presume this is due to the fact that it doesn't now how to deal with the "project objects" when initiating the sort. — Aisarang, Dec 28 '14 at 06:50

Nathan Tuggy · Answer 1 · 2014-12-28T07:12:39.900

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.

edited Dec 28 '14 at 07:12

answered Dec 27 '14 at 07:50

Nathan Tuggy

2,237
27
30
38

The First threw an error and asked me to do a FirstOrDefault instead. I did then use FirstOrDefault but the rendered view doesn't have anything in that particular column. – Aisarang Dec 28 '14 at 06:53
That suggests my code isn't quite handling the structure of `Projects` properly. You may need to tweak it, but basically, if you're needing `FirstOrDefault` it's already going to give you bad data, so put in a check to be sure it's not an empty collection. Hmm, might amend my sample for that. – Nathan Tuggy Dec 28 '14 at 07:00
Hmmm interesting. I'll do just that and thanks again for all of your input. I appreciate the time you're taking to assist me in all of this. – Aisarang Dec 28 '14 at 18:41

score 1 · Answer 2 · answered Jul 21 '17 at 20:14

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.

How do you sort by a property of an ICollection?

2 Answers2