CODEWITHSUNDEEP
linqentity-frameworkentitysql-order-bygeneric-list
tempid
tempid

Reputation: 8228

Entity Framework 4 - List<T> Order By based on T's children's property

I have the following code -

 public void LoadAllContacts()
        {
            var db = new ContextDB();
            var contacts = db.LocalContacts.ToList();
            grdItems.DataSource = contacts.OrderBy(x => x.Areas.OrderBy(y => y.Name));
            grdItems.DataBind();
        }

I'm trying to sort the list of the contacts according to the area name that is contained within each contact. When I tried the above, I get "At least one object must implement IComparable.". Is there an easy way instead of writing a custom IComparer?

Thanks!

Upvotes: 0

Views: 3699

Answers (3)

jeroenh
jeroenh

Reputation: 26792

I was in a discussion with @AbdouMoumen but in the end I thought I'd provide my own answer :-)

His answer works, but there two performance issues in this code (both in the answer as in the original question).

First, the code loads ALL contacts in the db. This may or may not be a problem, but in general I would recommend NOT to do this. Many modern controls support paging/filtering out of the box, so you'd be better off supplying an not-yet-evaluated IQueryable<T> instead of List<T>. If however you need everything in memory, you should delay the ToList to the last possible moment.

Second, in AbdouMoumen's answer, there is a so-called 'SELECT N+1' problem. Entity Framework will by default use lazy loading to fetch additional properties. I.e. the Areas property will not be fetched from the database until it's accessed. In this case this will happen in the controls 'for loop', while it's ordering the result set by name.

Open up SQL Server Profiler to see what I mean: you will see a SELECT statement for all the contacts, and an additional SELECT statement for each contact that fetches the Areas for that contact.

A much better solution would be the following: 

public void LoadAllContacts()
{
    using (var db = new ContextDB())
    {

        // note: no ToList() yet, just defining the query 
        var contactsQuery = db.LocalContacts
            .OrderBy(x => x.Areas
                       .OrderBy(y => y.Name)
                       .First().Name);  

        // fetch all the contacts, correctly ordered in the DB
        grdItems.DataSource = contactsQuery.ToList();

        grdItems.DataBind();
    }
}

Upvotes: 2

AbdouMoumen
AbdouMoumen

Reputation: 3854

try this: 

public void LoadAllContacts()
{
    var db = new ContextDB();
    var contacts = db.LocalContacts.ToList();
    grdItems.DataSource = contacts.OrderBy(x => x.Areas.OrderBy(y => y.Name).First().Name);
    grdItems.DataBind();
}

this will order the contacts by the first area name, after ordering the areas by name.

Hope this helps :)

Edit: fixed error in code. (.First().Name)

Upvotes: 3

Rami Alshareef
Rami Alshareef

Reputation: 7170

Is it one to one relation (Contact->Area)?
if yeah then try the following :

public partial class Contact
{
    public string AreaName
    {
        get
        {
            if (this.Area != null)
               return this.Area.Name;
            return string.Empty;
        }
     }
 }

then

grdItems.DataSource = contacts.OrderBy(x => x.AreaName);

Upvotes: 1

Related Questions