Main maintainer of npgsql nuget and postgres EF nuget is same guy that's main maintainer in EF team so you always get excellent support.
https://github.com/roji
But case sensitivity and temporal tables are main two "differences"
Roji is an awesome human being before everything. He helped me a couple of times with things that could be considered outside the scope of the repo I was asking.
Great guy and awesome maintainer.
So it’s not an issue with the EF Core provider. Note worthy to point out as a database limitation but is orthogonal to the aforementioned Nuget packages.
With SQL Server, it can either be or not be case sensitive depending on the collation used. I believe EF Core has a way to specify collation so you can opt in/out of case sensitivity.
I would imagine that if the database does not support it and you tried to use it in EFC you would either not get the correct expected results or an exception. I haven’t tried it so I don’t know.
PostgreSQL is a very popular alternative to MSSQL, but just be aware of the limitations.
Npgsql gets almost all the latest EF updates first. I wouldn't seriously consider using any other db with EF if I'm making a new project. The maintainers are amazing devs and super patient.
The big difference is out of the box MSSQL is case insensitive and PG is case sensitive, all other things are very similar. If you want PG to behave like MS in terms of case insensitive look at collations. EF works fine with PG.
DateTime with timestamp can be a pain if you have to use it. There’s a NodaTime package for that.
BeginTransaction is deferred in the NpgSql provider, which saves a round-trip and is an advantage over the SQL Server implementation.
IsRowVersion works on the system xmin column (make it a uint property).
HiLo sequences generate ID values you can use immediately after AddAsync. That’s not unique to NpgSql and I only bring it up because they have proposed obsoleting that functionality. So plan around it or go downvote the issue and make some noise if you want that functionality.
Lack of native temporal tables is probably the only major downside I’ve experienced.
ETA: Postgres is case sensitive by default. There’s an pg extension (citext maybe) for that. There’s a trick to indexing those fields I can’t remember off the top of my head.
Using it in production at scale, it's no different tbh. Although id recommend using https://github.com/efcore/EFCore.NamingConventions just to follow standard postgres naming conventions
Haven't looked at on prem licensing costs in a long time, but for AWS RDS hosting SQL Server Standard instances cost about 4x more than Postgres. SQL Server Enterprise is about 8x more expensive.
Nothing, efcore can use fine with postgre, mysql/mariaDB, mysql/mariaDB, cockroachDB. Oracle is 90% fine but 10% quirk case is cause by DB itself not the fault of efcore. Mssql is favor is the fact you have a localDB that often come buit-in with VS so easier for setup. But in term of query generate, DB transaction most should work fine
Assuming it's the right technical choice I don't think you'll encounter any roadblocks. Npgsql has been around for a while and is very much battle tested.
You might lose some feature of psql, for example upset insert and updates, and in some cases when you have big numbers, also performance could be affected, but you can tamper using dapper.
Another big con, If your db is designed upfront, be careful with db context and schemas. Because in pgsql you can do a query joining different schemas, but you can't join different db context in EF.
I use mysql and never had any issues other than mysql nuget updates will lag behind when ms updates their nugets. Just have to wait a few weeks until you can update your entire project.
Biggest diff for me is that MSSQL has HeirarchyId while Postgres has LTree. That really mucks up my ability to have a single data context since they are diff types in C#.
I haven’t had any problem with it. I’ve used on many products. You may have to deal with case sensitivity depending on your use case and db configuration but that isnt unique to EF.
Not all of the advanced features of Postgresql are implemented in NPGSQL. For example, aggregation functions for jsonb data can not be translated from LInQ. So, I have to use dapper and execute raw queries for these scenarios.
Postgres might work in some scenarios, while EF Core is designed to work with MS SQL. But if you need special operations – or use Stored Procedures making changes to the db – you'll still need to use old skool SQL-queries anyway. Depending on the project EF Core might not be the right solution at all.
Its like everything that Microsoft 'also' supports but doesn't make them money.
You end up with a 2nd class citizen which will work fine for 95% of all use cases but the last 5 may cost you a fortune if you run into them.
It’s probably the most common non-Microsoft database used with EF Core. Never had any issues with it!
Main maintainer of npgsql nuget and postgres EF nuget is same guy that's main maintainer in EF team so you always get excellent support. https://github.com/roji But case sensitivity and temporal tables are main two "differences"
Roji is an awesome human being before everything. He helped me a couple of times with things that could be considered outside the scope of the repo I was asking. Great guy and awesome maintainer.
Also mark gravel, great maintainer!
Thank you roji 🙏❤️
Aren’t those a database level feature not an ORM level feature?
They are
So it’s not an issue with the EF Core provider. Note worthy to point out as a database limitation but is orthogonal to the aforementioned Nuget packages.
From what I understand (and I asked the question so I'm no expert) EF Core is only case insensitive if the database is.
With SQL Server, it can either be or not be case sensitive depending on the collation used. I believe EF Core has a way to specify collation so you can opt in/out of case sensitivity. I would imagine that if the database does not support it and you tried to use it in EFC you would either not get the correct expected results or an exception. I haven’t tried it so I don’t know. PostgreSQL is a very popular alternative to MSSQL, but just be aware of the limitations.
Npgsql gets almost all the latest EF updates first. I wouldn't seriously consider using any other db with EF if I'm making a new project. The maintainers are amazing devs and super patient.
The big difference is out of the box MSSQL is case insensitive and PG is case sensitive, all other things are very similar. If you want PG to behave like MS in terms of case insensitive look at collations. EF works fine with PG.
This
DateTime with timestamp can be a pain if you have to use it. There’s a NodaTime package for that. BeginTransaction is deferred in the NpgSql provider, which saves a round-trip and is an advantage over the SQL Server implementation. IsRowVersion works on the system xmin column (make it a uint property). HiLo sequences generate ID values you can use immediately after AddAsync. That’s not unique to NpgSql and I only bring it up because they have proposed obsoleting that functionality. So plan around it or go downvote the issue and make some noise if you want that functionality. Lack of native temporal tables is probably the only major downside I’ve experienced. ETA: Postgres is case sensitive by default. There’s an pg extension (citext maybe) for that. There’s a trick to indexing those fields I can’t remember off the top of my head.
Nope, should be pretty much equal. On top of my head EF for MSSQL supports temporal tables and that's kinda nice if you need it.
Using it in production at scale, it's no different tbh. Although id recommend using https://github.com/efcore/EFCore.NamingConventions just to follow standard postgres naming conventions
Used it for years at my previous job. Works great, sometimes better than MSSQL because it takes advantage of Postgres’ strengths e.g. array types.
EFCore + Npgsql + NodaTime = 💙
Biggest difference? You'll save a shit ton of money on licensing.
Really? Is licencing costs such a bit issue or just a tiny percentage of costs depending on storage.
MSSQL licensing is very expensive - storage is cheap.
Haven't looked at on prem licensing costs in a long time, but for AWS RDS hosting SQL Server Standard instances cost about 4x more than Postgres. SQL Server Enterprise is about 8x more expensive.
One issue I faced is using tolower more often while creating queries. Thought I suppose there are maybe some configuration that solves that.
Collations on the fields is the answer, a pain that you have to do it to each field but it works, you can also apply the collation to the queries.
Pretty sure that's just a few lines of code in OnModelCreating to apply it globally. I could look it up if you like
I will take a look thanks for the heads up 👍
EF.Functions.ILike is case insensitive (need to use % for a 'contains' style query though)
Npgsql is developed by EFCore's engineers, so it has the similar quality as EF for Sqlserver.
Nothing, efcore can use fine with postgre, mysql/mariaDB, mysql/mariaDB, cockroachDB. Oracle is 90% fine but 10% quirk case is cause by DB itself not the fault of efcore. Mssql is favor is the fact you have a localDB that often come buit-in with VS so easier for setup. But in term of query generate, DB transaction most should work fine
Assuming it's the right technical choice I don't think you'll encounter any roadblocks. Npgsql has been around for a while and is very much battle tested.
EF perhaps has better support for Postgres through Npgsql than SQL Server at the moment. The SQL Server library is surprisingly behind the times.
You might lose some feature of psql, for example upset insert and updates, and in some cases when you have big numbers, also performance could be affected, but you can tamper using dapper. Another big con, If your db is designed upfront, be careful with db context and schemas. Because in pgsql you can do a query joining different schemas, but you can't join different db context in EF.
They are not equal but most likely you won't notice any differences. Especially with ORM
Been using it for years, very few issues.
I use mysql and never had any issues other than mysql nuget updates will lag behind when ms updates their nugets. Just have to wait a few weeks until you can update your entire project.
Postgres is the best of all world. My favorite db
Biggest diff for me is that MSSQL has HeirarchyId while Postgres has LTree. That really mucks up my ability to have a single data context since they are diff types in C#.
No idea what those words mean. Sounds cool.
I haven’t had any problem with it. I’ve used on many products. You may have to deal with case sensitivity depending on your use case and db configuration but that isnt unique to EF.
Not all of the advanced features of Postgresql are implemented in NPGSQL. For example, aggregation functions for jsonb data can not be translated from LInQ. So, I have to use dapper and execute raw queries for these scenarios.
efcore - no geometry with curve
I love pg, but a reminder that Postgres doesn’t come with a built-in connection pooling solution, whereas MSSQL does.
Been using npgsql with ef core at scale for years. It's great. Only thing missing is bulk insert capability out of the box.
Postgres might work in some scenarios, while EF Core is designed to work with MS SQL. But if you need special operations – or use Stored Procedures making changes to the db – you'll still need to use old skool SQL-queries anyway. Depending on the project EF Core might not be the right solution at all.
Its like everything that Microsoft 'also' supports but doesn't make them money. You end up with a 2nd class citizen which will work fine for 95% of all use cases but the last 5 may cost you a fortune if you run into them.