fast/right way to create Domain-Objects out of database

Question

i have in the database typical tables like user, usergroup, and they have relations.

in my domain-model i want that if you request users, you will get the users and each user has the property belongsto, which means these are the groups he belongs to. in the property i want to have the list of groups (typesafe as groups)

the same should be done on the other hand, that means each group should know which users belong to the group.

i have this classes (example):

public class User
{
    int Id { get; set; }
    string Name { get; set; }
    List<Usergroup> BelongsTo { get; set; }

    User()
    {
        BelongsTo = new List<Usergroup>();
    }
}

public class Usergroup
{
    int Id { get; set; }
    string Name { get; set; }
    List<User> Users { get; set; }

    Usergroup()
    {
        Users = new List<User>();
    }
}

internal class Relation
{
    int userId;
    int groupId;
}

now, whats the best way to implement it, and whats the fastest way to get all objects filled.

btw: i'm using subsonic to get all the database-records (for each table one select). and there are at least 1500 user and 30 groups.

my current code is based on foreach over the groups, relations.where for the groups, and then users.singleordefault to find the user and then add the user to the group and the group to the user. but it takes appx 30secs to do this.

my client-app is a webapp, so i store the results after all in a cached repository, thats why i'm doing it in this way!!

Answer 1

The best thing is to only retrieve the entity that was requested by the client but allow that entity to expose a method for retrieving all its related entities from the database. You shouldn't attempt to pull everything back at once from the database as you are not certain if the client even needs this data.

Do something like this:

public class User
{
    int Id { get; set; }
    string Name { get; set; }

    public List<Usergroup> GetUserGroups()
    {
        // go and get the user groups
    }
}

If you wanted to you could create a property that would hold this value as a member of the User class and use the GetUserGroups method to lazy-load the value for you.

Answer 2

my current code is based on foreach over the groups, relations.where for the groups, and then users.singleordefault to find the user and then add the user to the group and the group to the user.

I don't understand what you're saying here.

it takes appx 30secs to do this

Whatever it is you're doing seems too slow, given there's only 1500 users and 30 groups.

now, whats the best way to implement it, and whats the fastest way to get all objects filled.

If you wanted ...

• Either, all users, with the groups for each user
• Or, all groups, with the users for each group

... then I might suggest selecting joined records from the database.

However, because ...

• You want all data (joined in both directions)
• You only have 1500 records

... then I suggest that you select everything as simply as you can, and join in it your application.

//select users
Dictionary<int, User> users = new Dictionary<int, User>();
foreach (User user in selectUsersFromDatabase())
{
  users.Add(user.Id, user);
}
//select groups
Dictionary<int, Group> groups = new Dictionary<int, Group>();
foreach (Group group in selectGroupsFromDatabase())
{
  groups.Add(group.Id, group);
}
//select relations
//and join groups to users
//and join users to groups
foreach (Relation relation in selectRelationsFromDatabase())
{
  //find user in dictionary
  User user = users[relation.userId];
  //find group in dictionary
  Group group = groups[relation.groupId];
  //add group to user and add user to group
  user.BelongsTo.Add(group);
  group.Users.Add(user);
}