Entity Framework and Repository Pattern conceptual difficulties

Question

I am making an intranet website using ASP.NET MVC and SQL Server 2012. I am making a repository and architecturing with Onion Architecture. My problem is that the company in which I am working, already has several Server DBs in which the tables have no relations between each other. Instead, there are tables to map these relations. For example a table User, and a table Document have a table User_joint_Document to make a relation, containing both IDs (IDDocument and IDUser). Now when I write my generic repository:

class Repository<T> : IRepository<T> where T : class

the problem is the Generic type T makes no sense and I can't affect values in my model using EF queries which is normal, and what would be great would be to have a parent class BaseEntity to have IDs defined for each tables, then I can write:

class Repository<T> : IRepository<T> where T : BaseEntity

And all my table models would inherit from BaseEntity. But that would also mean rewriting the whole DB in a relational manner and mapping every DB POCO manually(correct me if I'm wrong), and I do not have the skillset to do this(there are over 300 tables in the different server DBs and I lack proper knowledge and experience to do this kind of operation).

Is there a way to keep my original DB structure, and still write a Generic Repository? How would one go about doing this?

EDIT To clarify my question because @saeb answered partially to my question. Can I have a generic repo without having a parent class for my DB POCOs? Or do I need it in order to then have only ONE repository to rule them all? For example:

class Repository<T>:IRepository<T> where T : class
{
  private readonly ApplicationContext context;
  private DbSet<T> entities;
  public Repository(PrincipalServerContext context)
  {
        this.context = context;
        entities = context.Set<T>();
  }
  public T Get(long id)
  {
     return entities.SingleOrDefault(s => s.IDUser == id);
     //This does not work, IDUser isn't recognized

  }

Thanks for your help!

The example you've given of `Users` and `Documents` is correct in the database. One user can have many documents so you need a junction table of `User_joint_Document`. That is exactly how a relational database should be structured. — melkisadek, Jun 16 '17 at 10:07
But if you are using Onion Architecture, there is a package that creates repositories automatically for you, you just have to register them in DataOnion. — Mihail Stancescu, Jun 16 '17 at 10:09
@melkisadek Bah! That's how bad I am at working on DBs. I would've thought you would have something like a 1-1 relationship between both tables or something like that? Maybe that rel'ship is an actual table? Really not my strong side. If I make a parent class with ID's, what's does that relation table become? Since it had IDDocument and IDUser, but now I would have ID only from parent class — Flexabust Bergson, Jun 16 '17 at 10:10
I'm assuming there are PK/FK relationships set in the DB? The junction table creates the 1 to 1 relationship from your Many to Many requirement. It's just a structural way to make the Many => 1 to 1 <= Many. The edmx will abstract this away with navigation properties. It shouldn't affect how the repository works with your POCO classes. — melkisadek, Jun 16 '17 at 10:49
@melkisadek Nope there aren't any PK or FK in the original DB but when I reverse engineer to get the POCOs, it does put PKs (no FK). I forgot to precise I work with Code First Approach so no EDMX. — Flexabust Bergson, Jun 16 '17 at 11:19
@MihailStancescu What? This exists? Is this what you are referring to? https://sswdataonion.com — Flexabust Bergson, Jun 16 '17 at 12:03
Yes, I'm using their packages and use the unit of work and repositories. All you have to do is to declare the domain classes and their correct EF configuration. — Mihail Stancescu, Jun 16 '17 at 12:04
@MihailStancescu Amazing, I'll get my head into that then thanks! — Flexabust Bergson, Jun 16 '17 at 12:11
Yeah, check their packages and with a bit of customization you can achieve what you need. — Mihail Stancescu, Jun 16 '17 at 12:12
@MihailStancescu I will go without the package finally because I need to understand really how it works and start getting used to all this syntax which is new to me. Any idea how to do what I want in my post edit? I feel like if I want to have a generic repo I need a Parent class to englobe IDs and then access from anywhere no? — Flexabust Bergson, Jun 16 '17 at 12:56

Saeb Amini · Accepted Answer · 2017-06-16T10:47:24.200

... has several Server DBs in which the tables have no relations between each other ...

But they do have a relationship, a Many-to-Many relationship, which is defined via that third mapping table (whether that's a correctly defined relationship is another topic)

... the problem is the Generic type T makes no sense and I can't affect values in my model using EF queries ...

Why doesn't it and why can't you? considering your table examples, you'd have two entities, User and Document and they'd look like this:

public class User
{
    public int IDUser { get; set; }

    public virtual ICollection<Document> Documents { get; set; }

    ...
}

public class Document
{
    public int IDDocument { get; set; }

    public virtual ICollection<User> Users { get; set; }

    ...
}

And you can use the fluent API in your context's OnModelCreating to set up the relationship via the third table:

public class YourContext: DbContext
{
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasMany<Document>(u => u.Documents)
            .WithMany(d => d.Users)
            .Map(userJointDocument =>
                {
                    userJointDocument.MapLeftKey("IDUser");
                    userJointDocument.MapRightKey("IDDocument");
                    userJointDocument.ToTable("User_joint_Document");
                });
    }

    ...
}

And then you can query Users and Documents in your repository as you would if there was a direct relationship between them. Here are more good sources to learn more about this if you like.

Thanks for that precise answer and good docs. I have done this but I still get problems in my repo, I cannot recognize the IDs. I've edited my question to show what my repo looks like — Flexabust Bergson, Jun 16 '17 at 12:01

score 1 · Answer 2 · answered Jun 19 '17 at 13:33

As far as I can see your problem, there is now way of achieving this without putting at least a base class or an interface to your entities/POCOs

You can play around with expressions for achieving a generic Repository

public interface IEntity<T> where T : class
{
    Expression<Func<T, bool>> GetByIdPredicate(long id);
}        

public partial class User : IEntity<User>
{
   public int UserID { get; set; }

   public Expression<Func<User, bool>> GetByIdPredicate(long id)
   {
      return (User entity) => entity.UserID == id;
   }
}

class Repository<T>:IRepository<T> where T : class, IEntity, new()
{
  private readonly ApplicationContext context;
  private DbSet<T> entities;
  T dummyEntity;

  public Repository(PrincipalServerContext context)
  {
        this.context = context;
        entities = context.Set<T>();
        dummyEntity = new T();
  }
  public T Get(long id)
  {
     return entities.SingleOrDefault(dummyEntity.GetByIdPredicate(id));
  }

There's probably a cleaner way that also gets rid of the dummyEntity field

Oh that's a pretty interesting take on it. I've actually reformulated my question and had another answer here, if ever you're interested, it's all packed in one method using Expression methods: https://stackoverflow.com/questions/44591796/does-a-generic-repository-need-a-base-entity-class-to-be-applied-everywhere/44592318#44592318 — Flexabust Bergson, Jun 19 '17 at 14:01

Entity Framework and Repository Pattern conceptual difficulties

2 Answers2