When the navigation property is null, Linq OrderBy fails.

entity-framework linq-to-entities nullable sql-order-by

Question

use four tables for work.

Users -> contains basic user information, such as a departmentid and a userid (int)
Groups -> a groupid and basic group information
GroupsMembers is a table that contains information about the many-to-many connection between a group and its members; the columns are groupid and userid.
Departments -> Deptid and other basic department information

The departmentid in the users database and the deparmtnet id in the departments table are linked via a foreign key (fk).

groupid of groups to groupid of groupsmembers FK FK groupsmembers userid from users userid

This makes it possible for the groups in the edmx to have a users navigation property that will list every group member.

var grp = grpSource.FirstOrDefault(g => g.GroupID == groupID)
if (grp.GroupID > 0)
{
    var userQuery = from u in grp.Users
                    where !u.Deleted
                    select u;
    userQuery = userQuery.OrderBy(u => u.Department.Name);
}

I have Users.Department included.

Users are not required to have a department, hence the departmentid column is nullable, which causes an issue. The orderby breaks and reads "u" if there are any users for whom the departmentid is null. No department exists. It works perfectly if there are no null departmentids. Even if there are no departmentids, I need a means to sort by Department.Name. Any recommendations?

1
22
2/18/2010 4:52:37 PM

Accepted Answer

The conditional operator may be used to determine if the department is empty:

userQuery = userQuery.OrderBy(u => (u.Department != null) ? u.Department.Name : String.Empty);

I developed the following extension approach to make things more clear:

    public static TResult IfNotNull<TSource, TResult>(this TSource obj, Func<TSource, TResult> selector, TResult defaultValue)
    {
        if (obj != null)
            return selector(obj);
        return defaultValue;
    }

You may use it as follows:

userQuery = userQuery.OrderBy(u => u.Department.IfNotNull(d => d.Name, String.Empty));
47
2/18/2010 5:05:02 PM

Popular Answer

Use the coalesce operator instead.

userQuery = userQuery.OrderBy(u => u.Department.Name ?? string.Empty);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow