DAL Repository pattern joins using dapper

Question

Im tring to implement the Repository pattern and all of the examples I have found are very simple (ignoring joins) or using entity framework.

My DB tables are as follows

tblUser {id, fname, lname, email, password, dateAdded}
tblAccount {id, name, isActive, dateAdded}
tblAccountUser {userId, accountId, isActive, dateAdded}

A user can have multiple accounts and an account can have multiple users. The tblUserAccount has a boolean that tells us if the user is active for the account and when the user was added.

my pocos map directly to the tables with an additional property for the relations. is there a better way to do this part? I could not think of a better way for my repository to return relations on something like GetUsersWithAccounts(userId).

tblUser {
    guid id, 
    string fname, 
    string lname, 
    string email, 
    string password, 
    date dateAdded, 
    IList<tblAccount> accounts
}

tblAccount {
    guid id, 
    string name, 
    bool isActive, 
    date dateAdded, 
    IList<tblUser> users
}

//should i have a tblAccountUser poco with something like
//{tblUser user, tblAccount account, bool isActive, date dateAdded}

I have the following repositories:

UserRepository {
    Add()...
    ...
    tblUser GetById(guid userId) {} 
    IEnumerable<tblUser> GetAll() {}

    //was unsure if Account repo should retrieve a user's accounts or if the user repo should.
    //using uow.User.GetAccounts(user.id) seems natural but please feel free to let me know what you think
    IEnumerable<tblAccount> GetAccounts(guid userId){} 

    //this is the one i was really unsure about
    //this would return tblUser obj with its tblUser.Accounts filled.
    IEnumerable<tblUser> GetAllWithAccounts()
}

AccountRepository{
    Add()
    ...
    AddUser(guid userId) //only adds relation to tblAccountUser Makes sense? 
    
}

//Should i have a repository for the Account <-> User relations?

Questions are all over the place, to sum up:

1. When returning pocos from my repos how should i return relations. as i have shown in my pocos above or is there a better way?
2. Should my relation tables get their own pocos? If so, is it only when they have additional data like isActive and user/account specific settings.
3. In my repositories im unsure of which repo should handle specific requests when it comes to relations.
4. Should i have another repo for account/user relations?

criticism, links, help all welcome Thanks.

Edit:

Additional note: Should have mentioned. The reason i want to get the user/accounts together is because they will be in a grid where we can activate/deactivate and modify values for user or their account/s.

Answer 1

A 1 & 3 When your UserRepository method returns a tblUser object, it doesn't need to populate the accounts. All non-collection properties should be handled by UserRepository . In the getter of accounts property, call methods in an AccountRepository to make the database call to get all accounts for the user first time the getter is being called.

A 2 Relation tables don't need their own pocos.

A 4 Create another repository to handle accounts separately.

Answer 2

Perhaps part of the answer is to de-stress. The important part of the repository pattern is that you have a class that wraps your database calls, that returns pocos, not ADO classes, letting you unit test. The cosmetic part is the grouping of insert/update/delete methods in the same class with the "select" methods, letting you forget you're dealing with a database. Why you want to forget is another question.

Once you have your repo class, extract it's interface. Roughly, 1 repository corresponds to 1 query corresponds to 1 flat result set. There is no notion of joins within a repository. Joins in your database may correspond to collections in your app code : an instance of User will contain a List or Dictionary or IEnumerable of Accounts, and that doesn't stop an instance of Accounts having a collection of Users. But User and Account are not repositories. They might be the POCOs returned by repositories. More usually, they are defined in another layer of your app, and encapsulate the POCOs returned by the data layer. This brings up the obvious problem of consistency, which is left for you to solve :-) The repository pattern won't help you here. If your users pick accounts on a User screen, your User object might call the method that inserts into AccountUser, on some repository somewhere. If you pick Users on an Account screen, the Account object might call the same method. The method might be in the UserRepo, or AccountRepo or SomeOtherRepoAltogether.

It would be a mistake to try and use Dapper to emulate Entity Framework. Don't imagine that you can write your repositories once then never touch them. Dapper is for folk who love their database, and speak to it often. Repos may overlap each other.