Best practices when modularizing an application with EF and multi-bank

Let's say I want to model an application in .NET using EntityFramework as ORM and use one of its advantages, be "generic" for several RDBMS.

So, for example, I want my application to accept working with Firebird, SQL Server or MySQL. That in the installation of the application the user can select which SGBD he wants and then the settings are made.

But let's also say that I will have SQL commands customized for each database in certain tasks.

Soon, I create my modules with projects Class Library like this:

  • application - containing the business rules, made to be consumed by the layers that request certain tasks from the application;
  • Domain - which contains the classes representing the database tables;
  • from - Ondem has as classes containing the EF request methods for application methods;
  • repository - which will contain my inherited class of DbContext and its properties DbSet<>;

That would be my initial approach.

How would I then be able to have my specific Dal layer for each RDBMS ?
or rather, what are the best practices to be adopted in this scenario?
How to deal with references / dependencies of dlls ?

Author: Tiedt Tech, 2014-05-09

1 answers

I currently use EF in my Windows Forms and Web applications. To use multiple databases, the only differentiation is the connection string.

But I already warn that to make some SGDBs work will have a little headache, but after all ok and installed works perfectly.

My applications follow the pattern:

Database: In this folder I have the ERPDBContext.cs, which has the DbContext and the Dbsets. Example:

public class ERPContext : DbContext
{
    public ERPContext()
        : base("ConexaoERP")
    {
        Database.SetInitializer<ERPContext>(null);
    }

    public DbSet<Usuario> Usuario { get; set; }
    public DbSet<UsuarioAcesso> UsuarioAcesso { get; set; }     

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Usuario>().ToTable("Usuario");
        modelBuilder.Entity<UsuarioAcesso>().ToTable("UsuarioAcesso");
    }
}

If noted I use the Database.SetInitializer( null); not to create the database.

My classes I do in the Model folder, one .cs for each "table" / class. In this model, as I use EF, I make an inherited class to make life easier in development. Example:

using System;
using System.Collections.Generic;
using System.Linq.Dynamic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using FlexGestor.Helpers;
using System.Data.Entity;
using FlexGestor.Interface;

namespace FlexGestor.Models
{
    public class BlogCategoria : IEntidadeBase
    {
        [Key]
        public int BlogCategoriaID { get; set; }
        [Display(Name="Seção")]
        public int BlogSecaoID { get; set; }
        public string Sigla { get; set; }
        [Display(Name = "Descrição")]
        public string Descricao { get; set; }

        /* Campos fixos */
        public int EmpresaID { get; set; }
        public string Fixo { get; set; }
        public string Status { get; set; }
        public string Apagado { get; set; }
        public DateTime? DtApagado { get; set; }
        public int UsuCad { get; set; }
        public DateTime DtCad { get; set; }
        public int UsuAlt { get; set; }
        public DateTime DtAlt { get; set; }
        public int UsuUltAlt { get; set; }
        public DateTime DtUltAlt { get; set; }

        [ForeignKey("UsuCad")]
        public virtual Usuario UsuarioCad { get; set; }
        [ForeignKey("UsuAlt")]
        public virtual Usuario UsuarioAlt { get; set; }
        [ForeignKey("UsuUltAlt")]
        public virtual Usuario UsuarioUltAlt { get; set; }
        [ForeignKey("EmpresaID")]
        public virtual Empresa Empresa { get; set; }

        [ForeignKey("BlogSecaoID")]
        public virtual BlogSecao BlogSecao { get; set; }
    }

    public static class BlogCategoriaExt
    {
        public static IEnumerable<Listagem> Listagem(this DbSet<BlogCategoria> entities, int secaoID)
        {
            return entities
                .Where(u => u.Apagado == "N" && u.BlogSecaoID == secaoID)
                .OrderBy(r => r.Descricao)
                .Select(l => new Listagem { Key = l.BlogCategoriaID, Texto = l.Descricao });
        }

        public static IEnumerable<BlogCategoria> ToListERP(this DbSet<BlogCategoria> entities)
        {
            var usuarioLogado = HttpContext.Current.Session["usuarioLogado"] as UsuarioLogado;
            return ToListERP(entities, usuarioLogado);
        }

        public static IEnumerable<BlogCategoria> ToListERP(this DbSet<BlogCategoria> entities, UsuarioLogado usuarioLogado)
        {
            return ToListERP(entities, usuarioLogado.EmpresaIDLogada.GetValueOrDefault(0));
        }

        public static IEnumerable<BlogCategoria> ToListERP(this DbSet<BlogCategoria> entities, int empresaID)
        {
            return entities.Where(w => w.EmpresaID == empresaID && w.Apagado == "N");
        }

        public static ResultadoListagemPadrao Grid(string orderna, string ordenaTipo, string filtro, int? filtroID, UsuarioLogado usuarioLogado)
        {
            ERPContext db = new ERPContext();
            var resultado = new ResultadoListagemPadrao();
            var dados = db.BlogCategoria.ToListERP(usuarioLogado);
            var where = "";            
            var id = 0;

            if (int.TryParse(filtro, out id))
                where = " CategoriaID == " + id.ToString();

            resultado.TotalRegistros = dados.Count();
            if (filtro != null)
                where = " Descricao.Contains(@0) ";
            resultado.Dados =
                (from a in dados.AsQueryable()
                 select new
                 {
                     CategoriaID = a.BlogCategoriaID,
                     a.Sigla,
                     a.Descricao
                 })
                .Where(where, filtro)
                .OrderBy(orderna + " " + ordenaTipo);
            return resultado;
        }
    }
}

For models, I use interface, so I can make an event to populate the user data.

I am implementing like this.

Update 1:

As a comment of doubts, I edited the response. Then let's go. When I started messing with EF and C#, I didn't have much knowledge. But in all the system I develop I put the fields that are in the IEntidadeBase . There are people who will ask me why structure, simple, with it I go to the registry and see the information of the same quickly.

public interface IEntidadeBase
{
    int EmpresaID { get; set; }
    string Fixo { get; set; }
    string Status { get; set; }
    string Apagado { get; set; }
    DateTime? DtApagado { get; set; }
    int UsuCad { get; set; }
    DateTime DtCad { get; set; }
    int UsuAlt { get; set; }
    DateTime DtAlt { get; set; }
    int UsuUltAlt { get; set; }
    DateTime DtUltAlt { get; set; }
    Usuario UsuarioCad { get; set; }
    Usuario UsuarioAlt { get; set; }
    Usuario UsuarioUltAlt { get; set; }
    Empresa Empresa { get; set; }
}

Then just use the interface in the classes. But what this will help me, simple, with it you can make a method of when it is saved, you call the method and it enters the registration values. But why did I do that? In the version of EF4 had no way to make a generic DbSet, I do not know if the new ones have.

Now let's talk about BlogCateriaExt and this. With this class I can do like this:

db.BlogCateria.ToListERP();

In my case, this is interesting because it follows all system rules. Ah I have other events or rather, other returns. The return listing, serves this very purpose, to return the information in a way that is easier to use in combobox.

In addition, my extended models have the Grid method, which is a default return to mount listing.

And to finish, because all this in one .cs only? Simple, so you don't have to keep walking around too much.

I hope I have settled the doubts, anything gives a shout.

 3
Author: Tiedt Tech, 2014-05-11 03:16:51