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.

vlad-wallit

@vlad-wallit

Hi! I'm using EF.Core + Npgsql 3.1.4 and got this error recently:
 Npgsql.NpgsqlException (0x80004005): Unknown message code: 47 at Npgsql.Util.PGUtil.ValidateBackendMessageCode(BackendMessageCode code) at Npgsql.NpgsqlConnector.<>cDisplayClass160_0.<<DoReadMessage>gReadMessageLong|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Npgsql.NpgsqlConnector.<>cDisplayClass160_0.<<DoReadMessage>gReadMessageLong|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)  at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken)  at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken)  at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.MoveNextAsync() at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsyncTSource  at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsyncTSource at A

After this error is thrown other queries just hang when awaited.
Please help me how to fix it? Thanks!

Dan Beaulieu

@dbeaulieu

I'm looking at the source and see an enum called PostgresExpressionType.AtTimeZone and am wondering if this can be used. For example i have a type.

public class Example
{
    public DateTimeOffset SomeDate {get; set;}
    public string IanaTimeZone {get; set;}
}

i'd like to write a query as follows
_context.Example.Where(x=> x.SomeDate.AtTimeZone(x.IanaTimeZone).DayOfWeek == DayOfWeek.Monday

Skeferstat

@Skeferstat

Hi, I have a question about the EF Core and npgsql. I have a table with 3 columns: Id, Type and Data. Type is a string, Data a jsonb string. Depends on the Type I want cast the data (from column Data) into a specific object. How I can accomplished this ?
_

1 reply

Farzad Niknam

@farzad.niknam_gitlab

I have a question about migration code first by using npsql version 4.1.5. When i try migrate i get error : "he Entity Framework provider type 'Npgsql.NpgsqlServices, Npgsql.EntityFramework' registered in the application config file for the ADO.NET provider with invariant name 'Npgsql' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information"

Farzad Niknam

@farzad.niknam_gitlab

I have red many article about the issue but non of them could be helpful to solve my problem. I use dapper contribe in my project and my data layer is code first. When i use npgsql 2.2.7 it works fine but when updated npgsql to latest version i got the error that i mentioned. Could you help me please what is the correct way to use this package.

Amit Hansda

@amitHansda

Hi Guys,
Got a question. I have a column having type as text[], while storing data I am facing no problem. but while querying its not returning anything. e.g.

var query = dbContext.Users.Where(x=>x.Aliases.Contains("somevalue")).Select(a=>a);

Alberto Passalacqua

@AlbertoPa

hmm is the syntax of that query as you have it in the code?

Amit Hansda

@amitHansda

@AlbertoPa yes, correct

Alberto Passalacqua

@AlbertoPa

ok, just asking in case you typed it here...

shouldn't it be x => x ....

Amit Hansda

@amitHansda

@AlbertoPa Sorry typo there..Corrected it. As I mentioned nothing getting returned here. If I exclude Where clause I see the value in query result

Alberto Passalacqua

@AlbertoPa

so you want all users where the alias contains that string :-?

Amit Hansda

@amitHansda

@AlbertoPa Yes, and I am using EF Core

AlbertoPa @AlbertoPa tries

Alberto Passalacqua

@AlbertoPa

What about:

var usersWithAliasSomeValue = dbContext.Users.Where(u => u.Aliases.Contains("somevalue")).ToList();

I assume Aliases is a list of strings?

Amit Hansda

@amitHansda

What about:

var usersWithAliasSomeValue = dbContext.Users.Where(u => u.Aliases.Contains("somevalue")).ToList();

Nope, does not work. Am I missing any using statement specific to npgsql?

Alberto Passalacqua

@AlbertoPa

if you miss a using statement, it would not compile

does not work as in? empty result?

Amit Hansda

@amitHansda

Yes. No result

[]

Alberto Passalacqua

@AlbertoPa

and Aliases is a list of strings?

Amit Hansda

@amitHansda

@AlbertoPa Yes, Aliases is a list of string, The table get successfully created in database with Aliases column type text[]


        public class User{
            public int Id { get; set; }
            public string Username{get;set;}
            public List<string> Aliases {get;set;}
        }

Alberto Passalacqua

@AlbertoPa

Take a look at the suggested queries: https://www.npgsql.org/efcore/mapping/array.html

Amit Hansda

@amitHansda

@AlbertoPa Basically trying the simple thing this document mentions https://www.npgsql.org/efcore/mapping/array.html

Alberto Passalacqua

@AlbertoPa

ah ok, sorry for linking then :-)

hmm

Amit Hansda

@amitHansda

@AlbertoPa As the document suggest the equivalent query works on pgsql query tool

select * from "Users" Where 'somevalue' = ANY("Aliases")

Alberto Passalacqua

@AlbertoPa

yep

I guess I would test if it can find one user with a given alias to see what goes wrong

Alberto Passalacqua

@AlbertoPa

(bypassing where)

Amit Hansda

@amitHansda

Hi Guys,
Got a question. I have a column having type as text[], while storing data I am facing no problem. but while querying its not returning anything. e.g.
var query = dbContext.Users.Where(x=>x.Aliases.Contains("somevalue")).Select(a=>a);

Solved!! Upgraded to 5.0.0-rc1

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 5.0.0-rc1

Thanks @AlbertoPa for your time :)

Alberto Passalacqua

@AlbertoPa

ohhh

you really don't need that "select" :P

you want a list, so a ToList(); is probably a better idea

Amit Hansda

@amitHansda

@AlbertoPa Yeah

Filip Makowski

@fmakowski

Hey! I have a question regarding JsonDocument.GetProperty(string) method, which doesn't seem to exist even though it is used in the API reference

unfortunately using RootElement leads to The LINQ expression could not be translated

of course I can solve that by adding AsEnumerable() before where, but that kind of defeats the purpose

using JsonElement instead of JsonDocument as a property type for a column doesn't solve that either

what am I doing wrong? any advice?

Filip Makowski

@fmakowski

obviously I can make POCO out of those JsonDocuments, but I have to admit, I think this is quite limiting

Filip Makowski

@fmakowski

removing reference to Newtonsoft.Json is not helping either

Emill

@Emill

@roji do you have some quick start guide to set up the dev environment?

I get this:

1>C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin\Microsoft.Common.CurrentVersion.targets(1175,5): error MSB3644: The reference assemblies for .NETFramework,Version=v5.0 were not found. To resolve this, install the Developer Pack (SDK/Targeting Pack) for this framework version or retarget your application. You can download .NET Framework Developer Packs at https://aka.ms/msbuild/developerpacks

but I'm not sure which one to download

Emill

@Emill

nevermind got it working by installing VS 2019 Preview 3

Shay Rojansky

@roji

@fmakowski yeah, as discussed in npgsql/doc#58 are incorrect - GetProperty is a method on JsonElement, so you need to call JsonDocument.RootElement. But you're saying you have issues using JsonElement too?

Where communities thrive

People

Repo info

Activity