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.

Phuong Nguyen

@neunygph

do we need to install both npqsql and the extension ?

Jame

@jamemackson

yes, I believe so.

Phuong Nguyen

@neunygph

yea that's what I have on my machine

Jame

@jamemackson

well, we need to have the visual studio extension for certain UI related things.

Phuong Nguyen

@neunygph

let me try again, I might need to restart VS a few times

thanks Jame

Jame

@jamemackson

it was seeming hopeless on my regular VM but for whatever reason on a fresh install it seems to just work.

¯_(ツ)_/¯

Phuong Nguyen

@neunygph

oh do you know a way to check if a column is null

currently I have to use .GetType().Name != "DBNull"

but the .GetType() is expensive, I think

Jame

@jamemackson

I haven’t gotten that far at all :(

Phuong Nguyen

@neunygph

Jame

@jamemackson

anyone know how to specify that it should use a secure connection for a command like this?

dotnet ef dbcontext scaffold "Host=my_host;Database=my_db;Username=my_user;Password=my_pw" Npgsql.EntityFrameworkCore.PostgreSQL

Dastan S

@daskz

Hi there, I'm using dapper with npgsql.
Multiple connection.Execute(query, params, transaction) for one connection and inside one transaction throws "Transaction is not usable" exception when calling commit() at the end. No exceptions occurs on each execute statement.

When I've debugged code found that transaction's IsCompleted property set to true after first Execute() call.

So I'm not able to multiple Execute() inside one transaction?

sateeshban

@sateeshban

Hi all, I am facing issue Npgsql while using with Dotnet framework 4.6.1, But I am able to connect with 4.7.2 May I know why it is not working with 4.6.1

I am getting the below exception
exception":"FirstDataBank.APIShared.UnivIDLoadException: Cannot instantiate ID \"FDB.RTDRUG+166762\". ---> System.NullReferenceException: Object reference not set to an instance of an object.\n\n at FirstDataBank.DIF4.Core.DataConnectionDIF4.getConnection()\n\n at FirstDataBank.DIF4.Core.DataConnectionDIF4.ExecuteSQL(String SQLName, List`1 FDBParameters, String SearchOperator)\n\n at FirstDataBank.DIF4.Core.RoutedDrug.Factory.GetInstance(UnivID univID)\n\n at FirstDataBank.APIShared.UnivID.GetInstanceX()\n\n --- End of inner exception stack trace ---\n\n at FirstDataBank.APIShared.UnivID.GetInstanceX()\n\n at FirstDataBank.DIF4.Core.RoutedDrug.GetInstanceX(Int32 id)\n\n

Could anyone help me here

Yoh Deadfall

@YohDeadfall

@sateeshban The stack trace you posted says us nothing since it isn't related to Npgsql. Another problem is that no code we can look at.

@daskz Could you post a minimal repro?

bkaid

@bkaid

Any chance of getting a .net 5 preview build published to nuget?

sateeshban

@sateeshban

here is my sample code

var drug = RoutedDrug.GetInstanceX(1);

connection string

@daskz

Shimmy

@weitzhandler

Hi all!
I'm reading npgsql/efcore.pg#981, and am amazed how awesome this is.
First of all, I'd like to thank @roji and the rest of the team for the wonderful job and for the great effort.
I've never used PGS before, but for the project I'm currently working on, I've already made up my mind that this is going to be the DB.
Anyway, it's unclear to me what JSON features are supported and which aren't.

What I want to achieve is a table with dynamic fields, so that they're all stored in extended JSON.
My question is, will it be possible to perform full-text search within JSON?
For example, in a tenant-driven app, I have a Contact entity that can be extended and shaped per tenant and customized with other dynamic fields, which are exposes a collection of POCOs stored as json. Will I be able to search dbContext.Contacts.Where(c.<one of those dynamic json fields>.Title.Contains("search term")?

Shimmy

@weitzhandler

Found answer here.
I'm telling you guys npgs is efin great, can't digest it yet!!!

Shimmy

@weitzhandler

#2903

#2904

Gidmark-wowgroup

@Gidmark-wowgroup

Hello! I'm very new to the whole postgres thing and even newer to ngsql. But how does schemas come into play, don't i need to define a schema somewhere? Like in the connection string?

Shay Rojansky

@roji

@Gidmark-wowgroup No, you don't need to define a schema in the connection string - you can think of schemas as "directories" where you put tables and other objects. The same database can have multiple schemas to organize tables into groups, etc.

You can refer to tables as some_schema.some_table, and use the search_path parameter to manage which schemas get consulted by default (much like path in the filesystem)

Phuong Nguyen

@neunygph

hello

does anyone know a specific exception for "Exception while reading from stream" error ?

i know this is a timeout related exception but I would like to add a specific to catch this specific error

Shay Rojansky

@roji

@neunygph IIRC this should be a SocketException with a specific TimedOut error code, wrapped by an NpgsqlException. So it should be easy to catch that specifically.

Phuong Nguyen

@neunygph

@roji so it should be catch with NpgsqlException ?

I can’t find a wait to catch it efficiently beside checking for the exact exception.Message

Gidmark-wowgroup

@Gidmark-wowgroup

@roji I got two schemas with two different EF applications. If i set ;Search path=xxx in my connection strings i don't get a error saying "42P01: relation \"__EFMigrationsHistory\" does not exist" for psql >=11, but not in 10.7. One of the services start up and create everything fine, but the other one failes with the error stated.

Shay Rojansky

@roji

@neunygph yes, this should be catchable as an NpgsqlException with a SocketException with a specific error code

By default, __EFMigrationsHistory is created in the public schema (unless you change the default schema in your EF model, in OnModelCreating). If you modify the search path, you need to make sure public is in it. Another option is to use an EF Core API in OnModelCreating which tells it to create __EFMigrationsHistory in another schema.

Phuong Nguyen

@neunygph

Ah I see, thanks @roji

Gleb Pospelov

@codefo

Hey 🙌

Does anyone know is it possible to create case insensitive unique index (like CREATE UNIQUE INDEX ix_usernames_value ON usernames (lower(value)) WHERE deleted_at IS NULL; using Fluent API?

Gleb Pospelov

@codefo

Ok, I've found the answer npgsql/efcore.pg#119 :)

Gidmark-wowgroup

@Gidmark-wowgroup

Is there any way of preventing sensitive personal data from being logged on error without stop logging? See test@test.com below:

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "IX_People_Email"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
  Exception data:
    Severity: ERROR
    SqlState: 23505
    MessageText: duplicate key value violates unique constraint "IX_People_Email"
    Detail: Key ("Email")=(test@test.com) already exists.
    SchemaName: sgw_user_dev
    TableName: People
    ConstraintName: IX_People_Email
    File: nbtinsert.c
    Line: 434
    Routine: _bt_check_unique
   --- End of inner exception stack trace ---
   at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationT
oken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Shay Rojansky

@roji

@Gidmark-wowgroup see npgsql/npgsql#2501 and also https://www.postgresql.org/message-id/flat/CAO8wz8QdKRTJUnW--AZ2N-BvJAemS%2BP9%3DSr7zUbkO%2BwmAmA7LQ%40mail.gmail.com

tl;dr no, PostgreSQL outputs this information, and at the moment Npgsql flows it through via PostgresException. For now, you'll need to avoid displaying exceptions to user or logging all of their fields.

(displaying exceptions to users in general is not recommended)

Gidmark-wowgroup

@Gidmark-wowgroup

i guess i need to turn off logging completely else i'll have logs with personal information which can be nasty with GDPR. Thank you though!

Shay Rojansky

@roji

You don't really have to turn off logging: just don't log PostgresException data wholesale...You can log the SqlState, MessageText and stack trace, for instance.

Where communities thrive

People

Repo info

Activity