npgsql/npgsql

Npgsql is the .NET data provider for Postgresql. It allows any program developed for .NET framework to access a Postgresql database server. It is implemented in 100% C# code. Postgresql versions since 9.0 are officially supported, others may work.

Anthony Acquah

@tonymobster

any guidance is appreciated

Shay Rojansky

@roji

@tonymobster FromSql can only be used to return whole instance of Account Balance - you're trying to use it to read balance instead (which presumably is a single number)

There's no reason to use EF Core here - you can simply get a DbConnection and execute your query via ADO.NET directly, as a DbCommand with ExecuteScalar

The "EF Core way" to do this, would be to query (using LINQ) for all the client's savings accounts, update them in C#, and then call SaveChanges. That would save you from doing any raw SQL yourself, at the cost of worse perf (query + several batched updates instead of a single bulk update). Bulk updates is a feature that's not yet implemented in EF Core.

Anthony Acquah

@tonymobster

oops it was originally

@$"
UPDATE savings_accounts
SET balance = balance - {amount}
WHERE client_code = '{client}'
AND ""number"" = '{account}'
AND balance >= {amount}
RETURNING ""number"" AS ""AccountNumber"", balance AS ""Balance""
")

and AccountBalance was a keyless entity with balance and account number properties

which i only added because I wanted to get the balance returned and I couldn't find a better way to return a scalar from EF

Anthony Acquah

@tonymobster

and I couldn't get the ExecuteSqlInterpolatedAsync and the likes to work with output values

I can't believe I totally forgot about DbCommand with ExecuteScalar _facepalm

I think that's the way to go. Thanks

Deniz Piri

@DenizPiri

Hi everyone. I seem to be having some performance problems with npgsql + ef core. I am connecting to localhost, on Linux, .net core 3.1 and ef core 3.1.
When using pgbench I can get easily up to 30k inserts/sec.
Inserting the same data to same table from .net core, best I get is 6.5k inserts/sec. And I get really really high CPU usage in the .net core app.

Deniz Piri

@DenizPiri

"No Reset On Close" helps a bit. can go up to 7.8k inserts/sec.

Hmm, using Ngpsql directly and dapper's ExecuteAync command, I get 21k inserts/sec. Hmmm. I wonder if we misuse EF Core somehow, or does it really have that much overhead for fast queries.

Anthony Acquah

@tonymobster

seen this? https://exceptionnotfound.net/dapper-vs-entity-framework-core-query-performance-benchmarking-2019/

https://www.mmohsin.com/entity-framework-core-dapper-performance-benchmark/

Deniz Piri

@DenizPiri

I see, in the second link you sent, for inserts, dapper takes 5.4ms and ef core takes 16.2 ms. That is more or less the difference I see as well.

and I assume for Inserts, with prepared queries, Dapper could go even faster.

Deniz Piri

@DenizPiri

Are there any go-to libraries for SQL query generation with Dapper?

Anthony Acquah

@tonymobster

https://sqlkata.com/docs

https://blog.oneunicorn.com/2020/01/12/toquerystring/

Deniz Piri

@DenizPiri

@tonymobster Awesome! Thank you very much! I will look into it!

Alexandre_P

@a540_p_twitter

Good Morning every One ! I would like discuss with you about a issue with NpgsqlCommand in vscode. I have done an error and the exception is not decoded in vscode.

The only information I have, is An exception is occured in an external module. I needed to start vs 2019 to know the complet exception.

Did you know why i have this issue ?

Alexandre_P

@a540_p_twitter

Shay Rojansky

@roji

Do you get the same exception when running from the command-line?

Alexandre_P

@a540_p_twitter

not only through vscode, in VS 2019 all works normaly

Keerthikan Ratnarajah

@Keerthikan

I have an Valid = dapperRow["valid"] as NpgsqlRange<DateTime>?;
which I seem to have issues with from and to is not in UTC?

somehow i can incoropate that?

behroozbc

@behroozbc_gitlab

i have this issue

npgsql/efcore.pg#1370

please help me

developerhakart

@developerhakart

Hey Guys please help me to fix issue related poolsize
The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)

Mohammad Ehsan Tabakhian

@ehsan.tabakhian_gitlab

Hi, String.Contains() is Case inSensitive ? can we relay on it ?

for search purpose

blizzardsolution

@blizzardsolution

hello, could anyone help me with connecting azure postgresql in asp.net core 3.1?
Using this connection string: Server={host}.postgres.database.azure.com;Database={your_database};Port=5432;User Id={user_id};Password={your_password};Ssl Mode=Require;, i tried to publish the web api to azure service, but got a deploy error

C:\Program Files\dotnet\sdk\3.1.202\Sdks\Microsoft.NET.Sdk.Publish\targets\PublishTargets\Microsoft.NET.Sdk.Publish.MSDeploy.targets(140,5): Error : Web deployment task failed. (The value 'Server={host}.postgres.database.azure.com;Database={your_database};Port=5432;User Id={user_id};Password={your_password};Ssl Mode=Require;' is not a valid connection string or an absolute path.  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_INVALID_CONNECTION_STRING.)

The value 'Server={host}.postgres.database.azure.com;Database={your_database};Port=5432;User Id={user_id};Password={your_password};Ssl Mode=Require;' is not a valid connection string or an absolute path.  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_INVALID_CONNECTION_STRING.
Keyword not supported: 'port'.
   at System.Data.SqlClient.SqlConnectionStringBuilder.GetIndex(String keyword)
   at System.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at System.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)
   at Microsoft.Web.Deployment.ConnectionStringMatcher.RemoveExtraSlashFromDataSourceName(String connectionString)
   at Microsoft.Web.Deployment.ConnectionStringMatcher.GetStandardConnectionString(String userConnectionString, Boolean isSqlCE)
   at Microsoft.Web.Deployment.SqlInfo.GetBuilder(String connectionString, String errorMessageFormat)
Publish failed to deploy.

2>Build failed. Check the Output window for more details.

it says that the connection string is invalid or an absolute path, but it doesn't make any sense to me. How can I fix this issue?

Anthony Acquah

@tonymobster

"Keyword not supported: 'port'."

maybe try removing that

Alberto Passalacqua

@AlbertoPa

Note: he asked also in aspnet/Home, and I tried to help. The odd part is that that is the connection string provided by Azure. I am not familiar with Postgres on there so I could not do much to help.

Zonciu Liang

@Zonciu

When I use string property as json type, how to use EF.Functions to make a equal query like .Where(e => e.Customer.Name == "Joe")

blizzardsolution

@blizzardsolution

@tonymobster thanks for your reply! Do you mean by removing port from the connection string?

Anthony Acquah

@tonymobster

yeah

blizzardsolution

@blizzardsolution

ok, will try and get back to you

Anthony Acquah

@tonymobster

on second look it also looks like it's building a sql server connection instead of a postgres one

System.Data.SqlClient is for sql server

i'm not sure it works for postgres as well

blizzardsolution

@blizzardsolution

for postgres, i'm using Azure postgresql

System.Data.SqlClient is for sql server

how can I configure the app to let it know that it's using postgres?

Anthony Acquah

@tonymobster

instructions are here https://docs.microsoft.com/en-us/azure/postgresql/connect-csharp

Where communities thrive

People

Repo info

Activity