Association properties not loading on entity.

Question

I have three related entities, best described by this diagram:

When I load a user, using the following code, the User object is loaded, i.e. not null, but the Role and Department properties on the User object are null, despite User database record having valid FK values for these associations.

using (var productionEntities = new ProductionEntities())
{
    var userName = GetUserName();
    User u = (from usr in productionEntities.UserSet
              where usr.UserName == userName
              select usr).FirstOrDefault();
    if (u == null)
    {
        throw new KpiSecurityException("No local database entry found for user.") { UserName = userName };
    }
    return u.Department.DeptName;
}

ASIDE: I went through this whole mission just because the ASP.NET MembershipProvider doesn't support user 'metadata' like department, and I would have had to use the ProfileProvider as well, at the cost of immense DB bloat.

Answer 1

Hi, you must explicitly load this properties

User u = (from usr in productionEntities.UserSet
          where usr.UserName == userName
          select usr).FirstOrDefault();

if (u == null)
{
        throw new KpiSecurityException("No local database entry found for user.") { UserName = userName };
}

if (!u.RoleReference.IsLoaded)
{ u.RoleReference.Load(); }

if (!u.DeparmentReference.IsLoaded)
{ u.DeparmentReference.Load(); }

or include this entities in your query

User u = (from usr in productionEntities.UserSet.Include("Role").Include("Department")
          where usr.UserName == userName
          select usr).FirstOrDefault();

Answer 2

Since you only need the department name, just project that out; this way you query the DB only once:

using (var productionEntities = new ProductionEntities())
{
    var userName = GetUserName();
    var u  = (from usr in productionEntities.UserSet
              where usr.UserName == userName
              select new
              {
                  DeptName = usr.Department.DeptName
              }).FirstOrDefault();
    if (u == null)
    {
        throw new KpiSecurityException("No local database entry found for user.") { UserName = userName };
    }
    return u.DeptName;
}