Question: Tips & Joint query

[Vinko90]

Henlo!! I wanted to compliment for developing this amazing product <3

In our company we are migrating our DB infrastructure code from EF Core to RepoDB, and we have some questions before we move forward. (Our RepoDB code is with Repository+UnitOfWork pattern).

1. When using repository pattern and unitofwork, do we need to create a repository also for the POCO class of the Many-To-Many tables or is not necessary?

2. Are there any best practice for creating POCO's for Many-to-Many entities? What I did so far is the following:

public class Users
{
    [Key]
    public int Id { get; set; }

    public string Username { get; set; }

    public string Password { get; set; }
  
    public virtual ICollection<UserRoles> UserRoles { get; set; }

    public virtual ICollection<UserTokens> UserTokens { get; set; }
}

public class Roles
{
    [Key]
    public int Id { get; set; }
    
    public string RoleName { get; set; }
    
    public virtual ICollection<UserRoles> UserRoles { get; set; }
}

public class UserRoles //many-to-many table
{
    [Key]
    public int UserId { get; set; }
    [Key]
    public int RoleId { get; set; }
    
    public virtual Users User { get; set; }
    
    public virtual Roles Role { get; set; }
}

3. What is the best practice with RepoDB to perform a joint query? Let's take as an example the classes above and this code:

    public Task<List<Role>> FindUserRolesAsync(int userId)
    {
        var userRolesQuery = from role in _roles
            from userRoles in role.UserRoles
            where userRoles.UserId == userId
            select role;
        return userRolesQuery.OrderBy(x => x.Name).ToListAsync();
    }

How should I handle this code migration with RepoDB? Shall I create a manager class and then from the unitofwork query userroles first and then roles, or shall I have this method in either Users or Roles repository?

[mikependon]

@Vinko90 - thanks!

When using repository pattern and unitofwork, do we need to create a repository also for the POCO class of the Many-To-Many tables or is not necessary?

The unit-of-work is not embedded on this bare-metal ORM so you need to create it by your own. We strongly suggest that you create a dedicated repository that implements your own UoW. We have a reference implementation pertaining to this from this documentation.

You can also see this issue 43 from our documentation site and download the sample project named APIProjectWithUnitOfWork.zip that implements the UoW.

Are there any best practice for creating POCO's for Many-to-Many entities? What I did so far is the following:

The library does not support the Joined Quries, so ensure that you read our limitation page before implementing the ORM for use. But I remember few users are using the following below.

public Task<List<Role>> FindUserRolesAsync(int userId)
{
	var userRolesQuery = from role in _repository.QueryAll<Roles>()
		from userRoles in _repository.Query<UserRole>(ur => ur.UserId == userId)
		where userRoles.UserId == userId
		select new Roles
		{
			Id = role.Id,
			RoleName = role.RoleName
			UserRoles = userRoles
		};
	return userRolesQuery.OrderBy(x => x.Name).ToListAsync();
}

The query above executes 2 statement from the database, so be aware of this behavior.

You can also use multiple query via Fluent or Raw execution.

using (var connection = new SqlConnection(connectionString))
{
    var tuple = connection.QueryMultiple<Roles, UserRoles>(u => u.Id >= 0,
		ur => ur.UserId == userId);
	
	// Do the property assignment logic here
	//user.UserRoles = tuple.Item2.ToList();
	
	return tuple.Item2.ToList(); // <-- UserRoles
}

Note: Queries above are not tested, nor verified. It is just an inspiration where to do the tricks when using this library.

[Vinko90]

Thank you, I perfectly understand the options we have to perform joined queries!

About UnitOfWork:
Our playground implementation right now is basically the one from the documentation, and that require the user to Begin() a transaction before accessing data.
Is it best practice to always begin a transaction even for a simple SELECT command? Or, shall we extend the repositories code in such a way that Query operations can be done without transactions and all the rest with transactions?

I tried documenting myself on the topic, unfortunately it's very confusing. I understand there are lot of wrong implementations of EF with UnitOfWork so it's not very clear how developers should work/not work with transactions.

[mikependon]

@Vinko90 the UoW is mainly used when pushing a change (i.e.: Update, Insert, Merge, Delete, etc) to the underlying data store (database), not during the retrieval of the data (i.e.: Query). It is not advisable to use the UoW during SELECT/Query executions. However, it is advisable to put UoW transaction in the query if there are activities on the data you're trying to retrieve, specially if that data is under a transaction context.

The repository pattern we provided is default passing the UoW transaction during the actual operation.

The overall code base are here.

[Vinko90]

Thank you for the explanation, everything is clear now :)