What should I consider when moving a project using Entity Framework 6 from SQL Server to MySQL or PostgreSQL? [closed]

First, I need to confess this question come mostly from the still expensive licensing model used by Microsoft for SQL Server Standard. Also because Web edition is now only available through Web Hosting provider. For information: it’s 1 license per 2 CPU Core + CAL per user.

From a Microsoft sales contact I spoke with yesterday: between 550 and 800 € (note the incredible difference depending on reseller and I guess volume : 250 € near 50%) per 2 Core + between 280 and 293 € per user.

So if we could easily move our data From SQL Server to any open source engine (MySQL, PostgreSQL or NoSQL like Redis) we do love spend this money in time to learn new skills instead of giving it to Microsoft for a system oversized for our needs. When I write oversized I refer to the other functionality included in Standard Edition instead of Web Edition (SSRS, SSAS, etc. …)

Our software architecture is an Onion Architecture as proposed by Jeffrey Pallermo back in 2008:

Onion Architecture in MVC

As you can see from the picture it was used with MVC in mind. We used it with Web API 2 which share the same kind of philosophy.

We rely on SOLID principles as long as we have time to do it well. So we used dependency injection with per request instance using Ioc Container Autofac. Each controller Action mostly speaks to Core layer Domain services by giving them the correct IEntityRepository implementation resolved when the controller is instantiated during Asp.Net request life cycle. So in theory, we only have dependencies over IEntityRepository interface wich does it’s job as “Contract”:

 public interface IWriteBaseRepository<TEntity> : IReadBaseRepository<TEntity>, IDisposable
    where TEntity : class
    TEntity Add(TEntity entity);

    IEnumerable<TEntity> AddRange(IEnumerable<TEntity> entities);

    TEntity Update(TEntity entity);

    TEntity AddOrUpdate(TEntity entity);

    IEnumerable<TEntity> AddOrUpdate(IEnumerable<TEntity> entities);

    void Delete(TEntity entity);

    void SaveChanges();

    IEnumerable<string> GetPendingChanges(TEntity entity, string identifierPropertyName, Dictionary<string, string> includeProperties = null);

For the repository part we used Code First approach and there absolutely no intelligence in the DB (thanks to Uncle Bob for his clear views). Our Repositories mostly return DbSet as IEnumerable. So You may have guessed we strongly use lazy loading by kind of “Transitivity” in our Domain Services.

So knowing the few I wrote and your past experience on shifting from a DB engine to an other, any feedback would be much appreciate before I find the courage to dive deep in PostgreSQL which remind me not so easy couple of hours on my first try of Oracle back in 2002.


Your primary concerns are:

  1. Finding a way to make your existing IQueryable Linq queries compatible with Postgres.

  2. Working through any vendor differences in the SQL statements and database features you are using.

  3. Finding substitutes for vendor-specific capabilities that you’re using in SQL Server that are not present in Postgres.

For your IQueryable provider, I would have a look at dotConnect for PostGresql.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *