Property database lookup (count) with Entity Framework

I have a slightly obscure model in that Users come from Active Directory, but from then on in information arrives from the SQL db.

So, I have a UserRepository that currently allows users to search for other users from active directory - this returns a list that I bind to a grid.

I need to be able to check if each user has any Contacts (which live in the db) in order to change how the UI behaves.

How would you do this? On another page Contacts will be editable, but on the list I just need to know if there any Contacts or not. I dont see any clean way around the expense of issuing a db call to execute a stored procedure for each result to get the count, and I am getting the count rather than the list of Contacts to keep it as streamlined as possible.

I was thinking something on the lines:

/// <summary>
/// information resides in the database
/// </summary>
private int? contactsCount = null;
public int ContactsCount
{
  get
  {
    if (!contactsCount.HasValue)
      throw new ApplicationException("Error trying to access property ContactsCount before it has been initialised. The underlying repository code needs to handle the retrieval of this info.");
    return contactsCount.Value;
  }
  set { contactsCount = value; }
}开发者_运维知识库

and using the UserRepository to set the value of ContactsCount after the search for each row (using a standard sql connection), but what would be nice would be to see Entity Framework in action on the actual property but I am not sure I can bind just a property to a function if the main User object is not part of the Entity Model?

---

It's not possible directly with Entity Framework. I think this is a perfect fit for a dedicated UserRepository class, which you already have.

As a side note, I would try to avoid having a separate db call per user, instead you can solve this with a single query, something like this [warning: untested code ahead]:

 var users = GetUsersFromActiveDirectory();


 // get the nof contacts per user fill in the contacts count for each user
 // assuming a IsContactFrom property on Contact here, which corresponds to User.UserName
 // also, assuming the number of users this is called for is 'reasonable' 
 using (db = new MyObjectContext())
 {
     var userNames = users.Select(u => u.UserName).ToList(); 

     var usersWithContacts = from c in db.Contacts
                             where userNames.Contains(c.IsContactFrom)
                             group by c.IsContactFrom into ContactsPerUser
                             select new 
                             {
                                UserName = c.IsContactFrom, 
                                NofContacts = ContactsPerUser.Count()
                             };

     var dic = usersWithContacts.ToDictionary(u => u.UserName);

     foreach (var u in users)
     {
         u.ContactsCount = usersWithContacts[u.UserName].Count
     }




 }

---

I'm not quite sure what you are after. If you have a Contact table with, a column that is called Login, then you can run something along these lines

var qry = from c in ctx.Contacts
    group c by c.Login
    into grp
    select new
    {
      Login = grp.Key,
      Count = grp.Count()
    };

Assuming that you have IEnumerable<User> users that keeps a list of users from active directory, you can then do this to merge the results:

var dictionary = qry.ToDictionary(x => x.Login);
users.Foreach( x=> x.ContactsCount = dictionary.ContainsKey(x.Login) ? dictionary[x.Login].Count : 0);

This assumes that you have ContactsCount property defined on your User class, where Foreach is defined like this (an extension method that I find myself using quite often):

public static void Foreach<T>(this IEnumerable<T> enumerable, Action<T> action)
{
    foreach (T value in enumerable)
    {
        action(value);
    }
}