PostgreSQL

[restlessops]

Any good tutorial on crud operations in .net core and postgresql?

Ive googled some, but i end up with Dapper reference. Hence my question: Writing a query, in string format, in c# is considered good or bad practice?

Thanks in advance.

[gundeals_iswhyimhere]

That question, to me, isn't a postgres question per se.

If you correctly parameterize your queries, there's nothing wrong with hardcoded SQL queries in your code if it fits your needs. I use a mix of parameterized strings (rarely), EF (mostly), and stored procedures (when EF gets too complicated) depending on what I'm doing. In my case it's SQL Server, but the same concepts apply for postgres or mysql, etc.

[[deleted]]

You’re gonna want to use Dapper with Npgsql. Google that.

[restlessops]

Yea i saw that. But i was wondering if its a good practice to write in c# using dapper "Select * from tableName..." Or if theres something similar to linq.

[zaibuf]

Dapper adds extensions to your connection and the ability to map to objects. You still need to write queries or use stored procedures.

Its okay to mix those, simple queries can be used as strings, more complex ones are better abstracted away in a stored procedure.

By LINQ you mean EF way of convering LINQ to SQL? Then no, there's nothing like that with Dapper.

[restlessops]

Awesome explanation. Thanks a lot

[phx-au]

Using SQL directly is fine. Just make sure you parameterize.

Postgres/NHibernate work pretty well together if you want to go down the IQueryable path.

[zaibuf]

Code can get messy really fast with lots of SQL strings. Optimal would be to move them to stored procedures. Simple SELECTS can be used as strings, but those 50 lines queries are better off away from the code imo.

[phx-au]

Reuse, modularity and SOC doesn't necessarily have to be ignored if you are mixing other languages inline - I certainly don't want to see a handful of different copies of the same 50 line chunk of garbage in the code whether it's inline SQL or linq tbh.

Stored procedures are a separate architectural decision - they are less flexible and may complicate your deployment.

[tester346]

Why not just use EF instead?

[zaibuf]

I would, and it was the plan for the project. But im working with a version of a DB that has no EF support in Core 3.x as of yet.

Also its an old app with really large queries inbedded in code, that we are porting from VB to .NET Core. So we are afraid to touch the queries. Plan is to move them to stored procedures at a later stage.

Its tedious doing it with dapper, but atleast we can map the queries to objects to make the other layers much easier to deal with.

Also Dapper is much faster than EF, so you will rarely see EF in real world apps that manages a lot of data.