Linq At least one object must implement IComparable

22,597

You cannot apply ordering to lists by default. You need to write up a custom class (sort of EquatableList etc.) or use LINQ Except & Intersect operators to compare lists.

But based on your comment, if you're looking for the LINQ equivalent of:

select * from Person p join PersonHobby ph 
on ph.PersonId = p.PersonId join Hobby h 
on h.HobbyId = ph.HobbyId order by h.Name

then that can be achieved as:

var query = people.SelectMany(p => p.PersonHobbies)
                  .Join(hobbies, ph => ph.HobbyId, h => h.HobbyId, 
                        (ph, h) => new 
                        { 
                         Person = ph.Person, PersonHobby = ph, Hobby = h 
                        })
                  .OrderBy(r => r.Hobby.Name);

basically we join person, person hobbies and hobby on the keys, and project all columns and sort it by the hobby.name field, as mentioned in your SQL.

Share:
22,597
Shiloh
Author by

Shiloh

Updated on April 15, 2020

Comments

  • Shiloh
    Shiloh about 4 years

    I am trying to order a List of Entities that contains another list of Entities. I have implemented IComparable for all entities and still get the exception. All of the examples I have seen address the issue where you have one list and you order by a given field in that list but not where you have a list of lists. This issue is happening for Linq to Objects per below and also for Linq to Entities. What am I missing?

    [TestClass]
    public class OrderBy
    {
        [TestMethod]
        public void OrderByTest()
        {
            var hobbies = new Collection<Hobby> { new Hobby { HobbyId = 1, Name = "Eating" }, new Hobby() { HobbyId = 2, Name = "Breathing" } };
    
            var p1 = new Person
            {
                PersonId = 1,
                Name = "A",
                PersonHobbies = new Collection<PersonHobby> { new PersonHobby() { PersonHobbyId = 1}}
            };
            var p2 = new Person
            {
                PersonId = 2,
                Name = "Z",
                PersonHobbies = new Collection<PersonHobby> { new PersonHobby() { PersonHobbyId = 2 }}
            };
    
            var people = new List<Person> { p1, p2 };
            var pplEnumerable = people.AsEnumerable();
            pplEnumerable = pplEnumerable.OrderByDescending(r => r.PersonHobbies.OrderByDescending(p => p.Hobby.Name));
            foreach (var person in pplEnumerable)
            {
                Console.WriteLine(person.Name);
            }
        }
        public class Person : IComparable
        {
            public int PersonId { get; set; }
            public string Name { get; set; }
            public virtual ICollection<PersonHobby> PersonHobbies { get; set; }
            public int CompareTo(object obj)
            {
                if (obj == null) return 1;
                var otherPerson = obj as Person;
                return PersonId.CompareTo(otherPerson.PersonId);
            }
        }
        public class PersonHobby : IComparable
        {
            public int PersonHobbyId { get; set; }
            public int HobbyId { get; set; }
            public virtual Person Person{ get; set; }
            public int PersonId { get; set; }
            public virtual Hobby Hobby { get; set; }
            public int CompareTo(object obj)
            {
                if (obj == null) return 1;
                var otherPersonHobby = obj as PersonHobby;
                return PersonHobbyId.CompareTo(otherPersonHobby.PersonHobbyId);
            }
        }
        public class Hobby : IComparable
        {
            public int HobbyId { get; set; }
            public string Name { get; set; }
            public int CompareTo(object obj)
            {
                if (obj == null) return 1;
                var otherHobby = obj as Hobby;
                return HobbyId.CompareTo(otherHobby.HobbyId);
            }
        }
    }