As you may know, repository pattern is intended to create an abstraction layer between BLL (business logic layer) and DAL (data access layer). Repository pattern is to query the data source and maps it to the BLL.
But what if you extend your database with some more tables? If you’re not using generic repository pattern, you must modify your repository code too to be able to use it in BLL.
To create a good and maintainable generic repository pattern, you should design your database (and tables) first. Because of the abstraction, try to use as many common properties, as you can. In this example all the SQL tables have Id column of integer (and autoincrement identity). As far as it appears in all tables, you can refactor it into an abstract class (abstract, because you don’t want to instantiate this class of only 1 Id property).
Then our tables can be separated into 2 additional groups: “main entities” and “data entities”.
Main entity tables have one more common property: Name. These tables are: Store, Product and Category.
Data entity tables are used for example to fill comboboxes (or any dropdownlists). I don’t like to use enums for this, becase user cannot maintain it from frontend: if the user wants one more item into the combobox, a developer is needed to hardcode it in enums. These data entity tables can be managed through any admin panel by users with right privileges.
These data entities also have Id property (inherited from base entity), Name property (can be inherited from the second level abstract class if you want to) and an Active property. This property is responsible for the appearance of the item in the combobox or any list. If it’s false, user cannot select it.

Here you can find the SQL queries to create all the tables and relationships in correct order:

After successful table creation, fill up your product and category tables with sample data from here:

The table hierarchy should look like this:

Now let’s create all the abstract classes for common properties

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

public abstract class DataEntity : BaseEntity
{
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
    public bool Active { get; set; }
}

public abstract class MainEntity : BaseEntity
{
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
}

To create the rest of the entities, the best practice is to start from “1” followed by “many” relationship: first create Category, DataStoreState and DataUserRole classes:

[Table("Categories")]
public class Category : MainEntity
{
    public Category()
    {
        Products = new HashSet();
    }

    [StringLength(255)]
    public string Description { get; set; }

    [Column(TypeName = "image")]
    public byte[] Picture { get; set; }

    public virtual ICollection Products { get; set; }
}

[Table("DataStoreState")]
public class DataStoreState : DataEntity
{
    public DataStoreState()
    {
        Stores = new HashSet();
    }
    public virtual ICollection Stores { get; set; }
}

[Table("DataUserRole")]
public class DataUserRole : DataEntity
{
    public DataUserRole()
    {
        Users = new HashSet();
    }

    public virtual ICollection Users { get; set; }
}

Then all the rest may come:

[Table("Products")]
public class Product : MainEntity
{
    public Product()
    {
        StoreProduct = new HashSet();
    }
    [Column(TypeName = "money")]
    public decimal UnitPrice { get; set; }

    public int UnitsInStock { get; set; }

    public bool Discontinued { get; set; }

    public int CategoryId { get; set; }
    public virtual Category Category { get; set; }
    public virtual ICollection StoreProduct { get; set; }
}

[Table("Store")]
public class Store : MainEntity
{
    public Store()
    {
        StoreProduct = new HashSet();
    }
    public int MaxProducts { get; set; }

    public int StateId { get; set; }
    public virtual DataStoreState DataStoreState { get; set; }

    public virtual ICollection StoreProduct { get; set; }
}

[Table("StoreProduct")]
public class StoreProduct : BaseEntity
{
    public int StoreId { get; set; }
    public int ProductId { get; set; }
    public virtual Store Store { get; set; }
    public virtual Product Product { get; set; }
}

[Table("User")]
public class User : BaseEntity
{
    [Required]
    [StringLength(50)]
    public string UserName { get; set; }

    [Required]
    public string PasswordHash { get; set; }

    [Required]
    [StringLength(50)]
    public string Email { get; set; }

    public int RoleId { get; set; }
    public virtual DataUserRole DataUserRole { get; set; }

}

I like to place Table attribute to all my classes, because it may happen that the class has different name than the corresponding SQL table (like Product – Products).

Required attribute is only need on reference type properties, because value types and structs can have the “?” to mark the class as nullable (DateTime? or int?).

Some words about the relations: let’s look at the User and DataUserRole tables. The SQL relations says that 1 UserRole can have many Users (there can be multiple admins), but 1 User can have only 1 UserRole. For Entity Framework we have to create new properties in these classes. In DataUserRole class we have to create some collection of Users (remember: 1 UserRole can have multiple Users). To achieve this, we need a virtual ICollection property of User type and it must be instantiated in constructor with new HashSet of User type.
On the other side, in User class we have the foreign key property (RoleId – int), but we also have to tell Entity Framework the corresponding class (table), which is DataUserRole with (again) virtual keyword.
All these settings are needed for Entity Framework to set up the Model Creating in our context.

Let’s take a look at our MyContext class, which is inherited from DbContext class of Entity Framework:

public class MyContext : DbContext
{
    public MyContext() : base("name=MyConnectionString")
    {
        Database.SetInitializer(new CreateDatabaseIfNotExists());
        Configuration.LazyLoadingEnabled = false;
        Configuration.ProxyCreationEnabled = false;
    }
}

The constructor will use the MyConnectionString named connection string from the App.config file to initialize the DbContext.
First line will automatically create the database and the tables if they are not exist and the Model Builder is set up correctly.
When LazyLoadingEnabled is set to false, it tells Entity Framework to not load automatically related objects when navigation property is accessed. In other words if I query all the products I don’t want to query automatically the categories because I may need only price now. I want to see the exact category only when I’m trying to access that property. That’s why it’s set to false.
When ProxyCreationEnabled is set to false, it turns off the change tracking. As Microsoft wrote:

When creating instances of POCO entity types, the Entity Framework often creates instances of a dynamically generated derived type that acts as a proxy for the entity. This proxy overrides some virtual properties of the entity to insert hooks for performing actions automatically when the property is accessed. For example, this mechanism is used to support lazy loading of relationships. The techniques shown in this topic apply equally to models created with Code First and the EF Designer.

Now finalize the table creation for our context by overriding the OnModelCreating method. These parameters must follow the class relationships set before!

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity()
    .HasMany(e => e.Products)
        .WithRequired(e => e.Category)
        .HasForeignKey(e => e.CategoryId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity()
    .HasMany(e => e.Stores)
        .WithRequired(e => e.DataStoreState)
        .HasForeignKey(e => e.StateId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity()
    .HasMany(e => e.Users)
        .WithRequired(e => e.DataUserRole)
        .HasForeignKey(e => e.RoleId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity()
    .Property(e => e.UnitPrice)
        .HasPrecision(19, 4);

    modelBuilder.Entity()
    .HasMany(e => e.StoreProduct)
        .WithRequired(e => e.Product)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity()
    .HasMany(e => e.StoreProduct)
        .WithRequired(e => e.Store)
        .WillCascadeOnDelete(false);
}

Of course there are other ways of building the models, not only this one I wrote (you can create configurations one by one and add them to the modelBuilder, etc.)
Because we want to use Generic Repository, let’s create our generic DbSet method in MyContext class:

public DbSet MyDbSet() where T : BaseEntity
{
    return base.Set();
}

This MyDbSet method can be called to every class that’s inherited from BaseEntity (in this tutorial => all our classes)
In DAL project (that contains MyContext class) should contain repositories. If we later create BLL project, that will contain services that would manipulate our models through the repositories. Because of this, generic repository should contain only basic CRUD operations:

• GetAll()
• FindById
• Insert
• Update
• Delete

If we need specific CRUD operation (like find user by email address), that should be implemented in a separate repository that’s inherited from generic repository.
Of course we need a generic interface for our generic repository:

public interface IRepository where T : BaseEntity
{
    IEnumerable GetAll();
    T FindById(int id);
    void Insert(T entity);
    void Update(T entity);
    void Delete(T entity);
}

As you can see, this repository would return any entity by calling the FindById, if it’s inherited from BaseEntity class. And so on the other methods…
The implementation is simple: we need a class that implements this interface, instantiate a new MyContext and do all the functions on this context:

public class Repository : IRepository where T : BaseEntity
{
    protected readonly MyContext Context;

    public Repository(MyContext context)
    {
        Context = context;
    }

    public virtual IEnumerable GetAll()
    {
        return Context.MyDbSet().AsEnumerable();
    }

    public virtual T FindById(int id)
    {
        return Context.MyDbSet().Find(id);
    }

    public virtual void Insert(T entity)
    {
        Context.MyDbSet().Add(entity);
        Context.SaveChanges();
    }

    public virtual void Update(T entity)
    {
        Context.MyDbSet().Attach(entity);
        Context.Entry(entity).State = EntityState.Modified;
        Context.SaveChanges();
    }

    public virtual void Delete(T entity)
    {
        Context.MyDbSet().Remove(entity);
        Context.SaveChanges();
    }
}

The readonly field has protected visiblity, because later if we create a new specific repository inherited from this, we need the same context, of course. Otherwise private is more than enough.

Now if we create a simple console application project in our solution, we can test how all these work. We should create some class level fields, like:

class Program
{
    private static MyContext _context;
    private static IRepository _categoryRepository;
    private static IRepository _productRepository;
    private static IRepository _dataUserRoleRepository;
    private static IRepository _userRepository;

    static void Main(string[] args)
    {
        _context = new MyContext();
        _categoryRepository = new Repository(_context);
        _productRepository = new Repository(_context);
        _dataUserRoleRepository = new Repository(_context);
        _userRepository = new Repository(_context);

        List categories = _categoryRepository.GetAll().ToList();
        List products = _productRepository.GetAll().ToList();
    }
}

Our repository is generic: you always instantiate a repo with new Repository constructor with adding the exact type of it. You can insert new UserRole if you want to:

_dataUserRoleRepository.Insert(new DataUserRole
{
    Name = "admin",
    Active = true
});

Then we want to know its Id, because of creating a new user:

    int roleId = _dataUserRoleRepository.GetAll().FirstOrDefault(x => x.Name.Equals("admin")).Id;

To create a new user, we have to create a password hash for that user with this simple method:

static string CreatePasswordHash(string password)
{
    var data = Encoding.UTF8.GetBytes(password);
    byte[] hash = null;
    using (SHA512 shaM = new SHA512Managed())
    {
        hash = shaM.ComputeHash(data);
    }
    StringBuilder result = new StringBuilder();
    for (int i = 0; i < hash.Length; i++)
    {
        result.Append(hash[i].ToString("X2"));
    }

    return result.ToString();
}

Now we know both the hash and the role id, so let’s create a new user using our generic repository:

_userRepository.Insert(new User
{
    UserName = "attila",
    PasswordHash = CreatePasswordHash("MyPassword123"),
    Email = "admin@crushsoft.eu",
    RoleId = roleId
});

Now, what if we’d like to find a user by email address. It’s a specific property of User class only, so this FindByEmail method shouldn’t be implemented in our generic repository (Products or Categories don’t have email addresses).
TO achieve this, we create a new and specific repository for User entity. The interface can be inherited from IRepository generic interface, so we will be able to access all those basic CRUD operations on our new specific repository:

public interface IUserRepository : IRepository
{
    User FindByEmail(string email);
}

The implementation will use the protected MyContext field:

public class UserRepository : Repository, IUserRepository
{
    public UserRepository(MyContext context) : base(context)
    {
    }

    public User FindByEmail(string email)
    {
        return Context.MyDbSet().FirstOrDefault(x => x.Email.Equals(email));
    }
}

Now you can instantiate a new IUserRepository in the console application project:

IUserRepository _userSpecificRepository = new UserRepository(_context);

and you can try your new FindByEmail method:

User u = _userSpecificRepository.FindByEmail("admin@crushsoft.eu");

If you added my user to the database before, this won’t result null, because a user exists in the database with this email address.

Final tips:

• After all of these you can create your business logic layer in a new BLL project, you can use these repositories there creating new business services.
• You can also create and implement UnitOfWork pattern here in DAL project, so you’ll be able to handle transactions and many more.
• There are no rules carved in stone of creating and implementing all the above: do it as you like and as your business allows you: DTO project must contain only entity classes without referring Entity Framework (no dependency!). DAL project should contain the context specifications, (Generic) Repository pattern and UnitOfWork pattern. Business Logic Layer (BLL) should contain business specific implementations, for example: get list of all products between specific price range.

  public List ListProductsInPriceRange(decimal? minPrice = null, decimal? maxPrice = null)
  {
      return {your logic};
  }
  

• But before creating services it’s highly recommended to implement UnitOfWork pattern.
• When you’re ready with all these projects (DTO, DAL, BLL and/oŕ Service) try to implement some DI (Dependency Injection). This may be important for code-independency. I like the AutoFac modul from NuGet. In few words: DI is to use your logics without new keyword in constructors. Every input parameters in constructors will be some interfaces, so you can change anything behind interfaces, your application will work.

Of course you can download my solution. After downloading and opening the solution file, first you must restore all the NuGet packages in order to work.
Also you must have installed .NET Framework 4.7.1 and Visual Studio 2017.