Repository pattern usage

I need some advice regarding repository pattern. Let’s assume i have UserBal which stands for business logic which uses UserRepositoryDal where all database logic for that entity stands (queries to table T_Users) . In my database i have table T_Users. There is also table T_UserTypes. In T_User table there is relation to T_UserTypes so means every user has his own user type. In my applciation i’ve created:

UserBal - for business logic and uses UserRepositoryDal UserRepositoryDal - all database logic to get data for 'T_Users' 

Repository have common functionality like: GetAll(), Insert etc..

My entites are:

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

I am not sure at this point whether should i create also repository for table T_UserTypes like: UserTypeRepositoryDal and for business logic UserTypeBal which would use UserTypeRepositoryDal ?

or should i just modify my User class instead of: public int FK_UserType { get; set; } change to: public UserType UserType { get; set; }

so there would not be UserTypeBal and UserTypeRepositoryDal at all

Nevertheless when in application i would like to get list of UserType for instance for combobox control or whatever i would not have UserTypeRepositoryDal with GetAll() so where should i put logic for UserTypes GetAll(). Should it be in UserRepositoryDal?

If something unclear let me know. Thanks for answers.

Which pattern to use for a model to have different variations of? Generic FK, multi-table, inheritance, others?

I am having trouble deciding how to structure my models for a particular data structure.

The models I have would be Posts, Groups, Users.

I want the Post model that can be posted from a groups page or user page and potentially more, like an events page.

Posts would contain fields for text, images(fk), user, view count, rating score (from — a reference to where ever it was posted from like user or group page, though I am unsure how to make this connection yet)

I thought about using a Generic Foreign Key to assign a field to different models but read articles suggesting to avoid it. I tried the suggested models, but I wasn’t unsure if they were the right approach for what I required.

At the moment I went with Alternative 4 – multi-table inheritance

class Group(models.Model):     name = models.CharField(max_length=64)     created_by = models.ForeignKey(         settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='_groups')     members = models.ManyToManyField(         settings.AUTH_USER_MODEL)      def __str__(self):         return f'{} -- {self.created_by}'      def save(self, *args, **kwargs):         #         created = self._state.adding         super(Group, self).save(*args, **kwargs)         if created:             if not self.members.filter(                 self.members.add(self.created_by)   class Post(models.Model):     content = models.TextField(blank=True, default='')     created_by = models.ForeignKey(         settings.AUTH_USER_MODEL,         on_delete=models.CASCADE,         related_name="%(app_label)s_%(class)s_posts",         related_query_name="%(app_label)s_%(class)ss")      # class Meta:     #     abstract = True      def __str__(self):         return f'{self.content} -- {self.created_by}'   class PostImage(models.Model):     image = models.ImageField(upload_to=unique_upload)     post = models.ForeignKey(         Post, related_name='images', on_delete=models.CASCADE)      def __str__(self):         return '{}'.format(  class UserPost(models.Model):     post = models.OneToOneField(         Post, null=True, blank=True, related_name='_uPost', on_delete=models.CASCADE)   class GroupPost(models.Model):     post = models.OneToOneField(         Post, null=True, blank=True, related_name='_gPost', on_delete=models.CASCADE)     group = models.ForeignKey(Group, on_delete=models.CASCADE)  

To do some specific filters ex:

Filter specific group post


Filter specific user post

Post.objects.filter(created_by=user) # exclude groups with ```_gPost__isnull=False```  

Create post to user/group

p = Post.objects.create(...) up = UserPost.objects.create(post=p) gp = GroupPost.objects.create(post=p) 

Really I am wondering if this is a sensible approach. The current way of a filter and creating feel odd. So only thing making me hesitant on this approach is just how it looks.

So, is Generic ForeignKey the place to use here or the current multi-table approach. I tried going with inheritance with abstract = True and that was unable to work as I need a foreign key to base post model. Even with no abstract, I got the foreign key reference, but filter became frustrating.

Design Pattern for Data Enrichment vs Similarity Matching

I have some things (JSON) coming from various sources (Search engine, Crawled HTML, APIs…). I’m trying to match similar things from the different sources as follows:

[1] Collect -> [2] Prepare -> [3] Match Similar 

With [2] Prepare I do some data manipulation to facilitate matching:

  • Normalization: formatting/cleaning values (eg trimming/lowercasing names)
  • Enrichment: extrapolating data (eg get domains from URLs: and are different URLs but both have the same domain so these things are related)

With [3] Match I compare values found in things and match those with most similarities together.

Issues with [3] Match:

  • if I skip enrichment (eg don’t add domains from URLs) there’s just too much noise in the data for the similarity matching to be accurate enough (eg I tried string distance algorithms on URLs which doesn’t work, eg is closer to than although the last 2 are more likely to be related as they have the same domain)
  • if I do enrichment: I get much better results HOWEVER the level of matching becomes influenced by how much enrichment I do versus how much of the original data is similar (eg if I add 3 domain fields from the URL to increase the chances I’ll find a match – (1) domain, (2) without any subdomains, (3) without www – then I have potentially 3 matches, whereas 2 similar things which don’t have URLs but only a name will only have 1 match even if it’s a very unique name with a perfect match)

Are there common design patterns to match enriched datasets by taking into account how much of the match is related to original data vs. enrichment data?

Recognising a Factoring Pattern

I am trying to identify a pattern in the following set of equations;

$ $ N_0=y$ $ $ $ N_1=y^2+z$ $ $ $ N_2=y^3+2yz$ $ $ $ N_3=y^4+3y^2z+z^2$ $ $ $ N_4=y^5+4y^3z+3yz^2$ $ $ $ N_5=y^6+5y^4z+6y^2 z^2+z^3$ $

Essentially, I need to be able to produce an equation for $ N_h$ . I’ve been staring at it for quite some time now, and have been unable to identify the pattern. Any assistance would be greatly appreciated. Is anybody aware of a particular methodology which could be applied here?

What’s the benefit of Proxy and Stub pattern?

i’m implementing an Application where a hub connects to an agent to get all the processes running (remote Task Manager). I provide an own written Process Protocol Library where all Message Types are defined as well as interfaces for Hub and Agent. My question is: Would it be good to implement a proxy and a stub who are performing the serialization etc? If so, why? What would the proxy do and what the stub?

Is the sequence diagram for the pull model for observer pattern the same as push model?

I’ve been looking at examples of sequence diagrams for observer pattern but most implement push model where the subject updates the observers. In a pull model how would it be different because technically the subject updates the observers still when there is a change just that the observer triggers the data extraction?

TradOffs between Testability and Performance for CQRS Pattern

My team has recently decided to adopt the CQRS pattern similar to the one used in the famous Microsoft EShopsOnContainers repository. In this solution, the queries are made in Query classes using Dapper and Commands are done in command handlers using EF Core.

The queries that I am doing on my domain are quite complex and require a lot of filtering of the data. Without using LINQ. I am relying on using purely dynamic SQL statements (not my strong point) to query my domain model with multiple WHERE clauses etc.

This approach would make it extremely hard to unit test my queries.

Here are the two approaches I am considering:

Approach 1:

string sql = @"     SELECT * FROM [dbo].[vw_LogisticsManagement]      WHERE ([Status] NOT IN @WorkOrderStatuses )     AND ([PickupDate] IS NOT NULL)      AND (0 = (DATEDIFF (day, [PickupDate], getdate()))     AND (0 != (DATEDIFF (day, [CheckInDate],  getdate())))     ORDER BY [PickupDate]";  //Add parameters etc... var result = await connection.QueryAsync<viewModel>(sql, parameters); 


  • Very Performant


  • Difficult to test
  • Difficult to write (for someone without much SQL experience)

Approach 2

string sql = @" SELECT * FROM [dbo].[vw_LogisticsManagement]  var result = await connection.QueryAsync<viewModel>(sql, parameters);  var wos = result.Where(wo => wo.PickupDate.HasValue                 && wo.PickupDate.Value.Date == DateTime.Now.Date                 && wo.CheckInDate.Value.Date != DateTime.Now.Date                 && wo.Status != Status.Shipped                  && wo.Status != Status.PartiallyShipped)                 .OrderBy(wo => wo.PickupDate).ToList(); 


  • Very easy to write (someone with a lot of LINQ experience)
  • Easy to test filters: I could mock the result that Dapper returns (no need for in memory db) and then have a predefined list that I could apply the filters using LINQ and have an expected result


  • Not as performance. Enough to even matter?

So basically I am trying to weigh out the pros and cons of each, in my opinion testability outweighs the potential performance boost in this case, however not everyone from my team would be likely to agree.

Ultimately approach 1, with proper testing in place would be ideal as many of the queries I am dealing with are far more complicated.

Repository pattern with unit of work ADO.NET

Iam implementing concrete MySQL(ADO.NET) data access for simple abstracted data layer. So I have simple “Model” and repository interface “IModelRepository”. Now Iam creating concrete implementation of the repository for MySQL database.

The thing is I want to be able to use transactions well, so each method from the repository opens db connection only for this one operation and closes it right afer. And also I want to be able to call the method to use existing connection with existing transaction to be able to commit or rollback its changes later.

public class Model {     public long id { get; set; } }  public interface IModelRepository {     Model GetById(long id); } 

So I have this DbSession class which is IDisposable and holds IDbConnection and UnitOfWork

public class DbSession : IDisposable {     private readonly IDbConnection _connection;     private readonly UnitOfWork _unitOfWork;      public DbSession(IDbConnection connection)     {         _connection = connection;         _connection.Open();         _unitOfWork = new UnitOfWork(connection);     }      public UnitOfWork UnitOfWork => _unitOfWork;      public void Dispose()     {         _unitOfWork.Dispose();         _connection.Dispose();     } }  public interface IUnitOfWork : IDisposable {     IDbConnection Connection { get; }     IDbTransaction Transaction { get; }     void Begin();     void Commit();     void Rollback(); }  public sealed class UnitOfWork : IUnitOfWork {     private IDbConnection _connection;     private IDbTransaction _transaction;      internal UnitOfWork(IDbConnection connection)     {         _connection = connection;     }      public IDbConnection Connection => _connection;     public IDbTransaction Transaction => _transaction;      public void Begin()     {         _transaction = _connection.BeginTransaction();     }      public void Commit()     {         _transaction.Commit();         Dispose();     }      public void Rollback()     {         _transaction.Rollback();         Dispose();     }      public void Dispose()     {         _transaction?.Dispose();         _transaction = null;     } } 

For concrete Db connection I have DbAccessor which creates DbSession:

public interface IDbAccessor {     DbSession CreateSession(); }  public class DbAccessor : IDbAccessor {     private readonly string _connectionString;      public DbAccessor(DbCredentials credentials, IConnectionStringBuilder customConnectionStringBuilder = null)     {         if (credentials.ConnectionString != string.Empty)             this._connectionString = credentials.ConnectionString;         else             this._connectionString = customConnectionStringBuilder != null ?                 customConnectionStringBuilder.BuildConnectionString(credentials) :                 new ConnectionStringBuilder().BuildConnectionString(credentials);     }      public DbSession CreateSession()     {         var connection = new Devart.Data.MySql.MySqlConnection(_connectionString);         return new DbSession(connection);     } } 

Now the repository implementation takes something what I called IConnectionContextProvider which is responsible for creating IConnectionContext.

IConnectionContext then is responsible for providing unit of work to methods of the repository and is disposable. So there is my separation of whether my connection context should be one connection without using of transactions only or keeping that connection with transactions for more methods. Like this:

public interface IConnectionContextProvider {     IConnectionContext Get(); }  public interface IConnectionContext : IDisposable {     IUnitOfWork GetUnitOfWork(); } 

I have simple connection context and its provider:

public class SimpleConnectionContext : IConnectionContext {     private readonly IDbAccessor _dbAccessor;     private DbSession _dbSession;      public SimpleConnectionContext(IDbAccessor dbAccessor)     {         _dbAccessor = dbAccessor;     }      public IUnitOfWork GetUnitOfWork()     {         _dbSession = _dbAccessor.CreateSession();         return _dbSession.UnitOfWork;     }      public void Dispose()     {         _dbSession?.Dispose();     } }  public class SimpleConnectionContextProvider : IConnectionContextProvider {     private readonly IDbAccessor _dbAccessor;      public SimpleConnectionContextProvider(IDbAccessor dbAccessor)     {         _dbAccessor = dbAccessor;     }      public IConnectionContext Get()     {         return new SimpleConnectionContext(_dbAccessor);     } } 

And I have unit of work connection context and its provider:

public class UnitOfWorkConnectionContext : IConnectionContext {     private readonly IUnitOfWork _unitOfWork;      public UnitOfWorkConnectionContext(IUnitOfWork unitOfWork)     {         _unitOfWork = unitOfWork;     }      public IUnitOfWork GetUnitOfWork()     {         return _unitOfWork;     }      public void Dispose()     {     } }  public class UnitOfWorkConnectionContextProvider : IConnectionContextProvider {     private readonly IUnitOfWork _unitOfWork;      public UnitOfWorkConnectionContextProvider(IUnitOfWork unitOfWork)     {         _unitOfWork = unitOfWork;     }      public IConnectionContext Get()     {         return new UnitOfWorkConnectionContext(_unitOfWork);     } } 

And finally there is the repository:

public class ModelRepository : IModelRepository {     private readonly IConnectionContextProvider _connectionContextProvider;      public ModelRepository(IConnectionContextProvider connectionContextProvider)     {         _connectionContextProvider = connectionContextProvider;     }      public Model GetById(long id)     {         using (var connectionContext = _connectionContextProvider.Get())         {             var unitOfWork = connectionContext.GetUnitOfWork();              // NOT IMPLEMENTED YET              throw new NotImplementedException();         }     } } 

Now with that I can make more models and more repositories and some custom services, where I could do some operations on these repositories while having working unit of work transaction. And I also can directly use the repositories from domain logic having them only initialized on the beginning. Example what I mean:

Creating one repository for usage the whole time application is running:

var accessor = new DbAccessor(credentials); IConnectionContextProvider connectionContextProvider = new SimpleConnectionContextProvider(accessor); IModelRepository repository = new ModelRepository(connectionContextProvider); 

And also using that repository method for multiple database edits with one transaction:

var accessor = new DbAccessor(credentials);         using (var session = accessor.CreateSession())         {             var unitOfWork = session.UnitOfWork;             unitOfWork.Begin();              IConnectionContextProvider contextProvider = new UnitOfWorkConnectionContextProvider(unitOfWork);             SecaproDataModel.Repositories.IFirmyRepository repository = new Repositories.FirmyRepository(contextProvider);              try             {                 // repository.DoStuff();                 // otherRepository.DoStuff();                 unitOfWork.Commit();             }             catch             {                 unitOfWork.Rollback();                 throw;             }         } 

I want to apologize for my explaining, english is not my primary language. I would like to know if I didnt get lost in my head while programming this and if there is some more elegant way to get to the same functionality. Thank you.

How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?

I have the following sheet:

I would like to calculate “Total Work” by deriving it from my own notation. When I do 6 sets of 10 reps of benchpress at 200lbs, I want to write and see 6x10x200, but then in addition, see a “total work” computation that recognizes the pattern NUMxNUMxNUM, or NUMxNUMxNUM, NUMxNUMxNUM

If this problem would become much easier to solve with some adjustments to my notation, for example, NUM*NUM*NUM, this would work perfectly fine.