2,155 questions
1
vote
1
answer
53
views
Npgsql EF Core does not map enum to PostgreSQL enum type (sent as integer instead)
I'm trying to map some enums in my model to PostgreSQL custom enum types using EF Core, but no matter what I do, EF Core keeps sending the values as integers instead of using the PostgreSQL enum type.
...
- 1,158
2
votes
2
answers
112
views
EF Core scaffold - unique index with WHERE condition produces a 1:1 mapping instead of 1:n
We are using .NET 10 and the following NuGet packages:
Microsoft.EntityFrameworkCore 10.0.3
Microsoft.EntityFrameworkCore.Design 10.0.3
Npgsql.EntityFrameworkCore.PostgreSQL 10.0.0
We have a simple ...
- 2,360
0
votes
0
answers
116
views
When using lambda expressions with bulk update APIs for EF Core, is the lambda expression evaluated in-memory, or on the DB-side?
I am using the Bulk Update APIs, part of EF Core like the ExecuteUpdateAsync() functions, to perform updates, and I am passing in a lambda for my valueExpression. I am wondering if the evaluation of ...
3
votes
0
answers
87
views
Why is this code leaking Oracle connections?
I've been using Npgsql, which uses the DbDataSource abstraction. It's really nice since commands created by it do not need explicit connections to be opened by them. However, the equivalent code using ...
- 1,256
2
votes
1
answer
128
views
How can I mark parameter for a custom EF Core function to be encoded as column name
I want to use a json function of PostgreSQL, that is not part of NpgsqlJsonDbFunctionsExtensions
[DbFunction("jsonb_extract_path_text", IsBuiltIn = true)]
public static string JsonExtract(...
- 3,746
2
votes
1
answer
153
views
Postgres 23505 duplicate key exception in EF Core AddOrUpdate (race condition on initial insert)
I am encountering a duplicate key value violates unique constraint error in my ASP.NET Core application, and while I understand why it happens, I am unsure about the best practice to handle it using ...
- 45
1
vote
0
answers
85
views
EF Core + Npgsql: "column 'status' is of type application_status but expression is of type text"
I've been struggling for two days with a PostgreSQL enum type issue in Entity Framework Core. Despite all my configuration, EF Core keeps trying to send a string (`text`) to an enum column.
The ...
0
votes
0
answers
109
views
Custom primitive type collection in EF Core (8)
I have a nested entity, defined like this:
entity.OwnsOne(e => e.EncryptedElements, o =>
{
o.ToJson();
o.OwnsMany(x => x.Values, o =>
{
o.Property(x ...
- 3,746
1
vote
1
answer
126
views
C# NET Core LINQ To Entities doubling subquery for sorting
I am working with EF Core 6.0 Web APi.
Npgsql.EntityFrameworkCore.PostgreSQL 6.0.29
PostgreSQL 16.3
I have database (Postgres) with musical entities and lessons. Lessons related to musical entities by ...
0
votes
0
answers
60
views
EF Core 9 Scaffolding NPGSQL - attribute tags vs modelBuilder
When I ran a scaffold command on my NPGSQL db with the typical default format I find on the web:
Scaffold-DbContext <connection-string> OutputDir Models -Force
It created all my model classes ...
- 3,671
1
vote
0
answers
42
views
DB creation fails through the Azure web app with Azure Postgres Flexible Server
I have an Azure Postgres flexible server sitting in a VNet divided into multiple subnets. In a distinct subnet lies an azure app service I am trying to run by publishing it via gitlab. However shortly ...
- 11
0
votes
1
answer
123
views
How to resolve EF Core continuous migration error with npgsql HiLo?
I'm using Hilo with a custom sequence name. When creating migrations, EF Core always creates a migration to drop the sequence with the normal name. Since EF Core 9.0, this produces a runtime exception ...
- 751
0
votes
1
answer
317
views
How do I use TPC to generate a sequence for each inherited entity in EF Core?
I've written a BaseEntity class which defines common fields in my database. It looks something like this:
public abstract class BaseEntity
{
[Key]
public int Id { get; set; }
public ...
- 196
0
votes
0
answers
90
views
ADO.NET failure to acquire connection in an SQL Server to PostgreSQL ETL (DTS)
I'm creating in VisualStudio for Application 2022 an ETL that is migrating data from a MS-SQL Server table to another in PostgreSQL DataBase. I create the ADO.NET Destination connection (server IP, ...
- 1
1
vote
1
answer
130
views
Npgsql TimeoutException during second read command
I attempt to call two functions to retrieve data from a Postgres database like so:
SomeClass.cs
public static IDatabaseProvider DatabaseProvider { get; } = new PgDatabaseProvider();
public async Task ...
1
vote
0
answers
113
views
How does disabling `DatabaseFacade.AutoSavepointsEnabled` risk database corruption?
The code documentation for DatabaseFacade.AutoSavepointsEnabled warns that:
Setting this value to false should only be done with caution since the database could be left in a corrupted state if ...
- 78
0
votes
1
answer
65
views
Should order of columns in index match order of columns in query?
We are using EF Core 8.0 with the Npgsql provider. We have several entities that look similar to this:
public class Foo
{
public Guid TenantId { get; set; }
public Guid FooId { get; set; }
}
...
- 6,738
1
vote
0
answers
63
views
How can I configure DateOnly properties that are mapped to timestamp columns so that queries use appropriate precision?
I have an AccountEntity:
public class AccountEntity
{
public int Id { get; set; }
public DateOnly? EstablishedDate { get; set; }
//...
}
which is being added via this configuration:
...
- 3,583
1
vote
1
answer
143
views
npgsql call jsonb_path_exists on string mapped jsonb
I've got a table with the filed string Data { get; set; } of type jsonb:
modelBuilder.Entity<LayoutEntity>(b =>
{
b.Property(p => p.Data).IsRequired().HasColumnName("data")....
- 2,379
1
vote
1
answer
273
views
How to translate a custom extension method like PgContains to PostgreSQL ILIKE + unaccent in EF Core 9 with Npgsql?
I'm using Entity Framework Core 9 with PostgreSQL via Npgsql, and I frequently write the following query pattern to perform accent-insensitive and case-insensitive searches:
EF.Functions.ILike(EF....
- 1,446
0
votes
0
answers
209
views
How to set a custom migrations history table name using builder.AddNpgsqlDbContext<AppDbContext>() method in .NET Aspire?
I'm building a .NET Aspire-based application using PostgreSQL with Entity Framework Core. I’m registering my DbContext like this as I am using Aspire.Npgsql.EntityFrameworkCore.PostgreSQL package:
...
- 1,082
0
votes
1
answer
154
views
How to get single row of values from database
Reading multiple values from a Postgres database using npgsql and EF Core in an ASP.NET Core MVC view:
var tulem = await ctx.Database
.SqlQuery<(string Klient, int Krediit, int ...
- 28.3k
1
vote
2
answers
71
views
How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
I have prepared a DB fiddle for my questions and also list my SQL code below.
I have created a countries_boundaries table, which I intend to fill with .poly files provided at Geofabrik:
CREATE ...
- 23.4k
0
votes
1
answer
79
views
How to parse logical replication commands
Code
do $$BEGIN
perform pg_create_logical_replication_slot('test', 'pgoutput', false);
END$$ ;
create publication Jalgi_pub for all tables;
select * from pg_logical_slot_peek_binary_changes('test', ...
- 28.3k
0
votes
0
answers
95
views
The entity type is not mapped to a table - ignores ToJson call
I'm trying to follow the npgsql guide to map a class to a JSON column, but I cannot get past the following error:
System.InvalidOperationException: The entity type 'MainCategory' is not mapped to a ...
- 32.3k
0
votes
0
answers
76
views
How to get replication events for all databases
Replication events are displayed by ASP.NET Core 9 MVC controller according using code:
public async Task Index()
{
const string slotName = "all_slot";
await ctx.Database....
- 28.3k
0
votes
1
answer
82
views
Keep same connection during lifetime of dbContext
I have a case where I first want to run a direct query (using .Database.SqlQuery) to try to obtain an advisory lock. Then run another query using linq. When I run the linq query, it seems it uses ...
- 83
1
vote
0
answers
61
views
Npgsql EF Core @> contains operator with a JSONB column modelled as an owned entity (.ToJson)
I have a DB entity with a JSONB column, modelled as an owned entity with .ToJson(), as recommended by the Npgsql EF Core documentation.
modelBuilder.Entity<Notification>()
.OwnsMany<...
- 4,476
0
votes
1
answer
88
views
Execute SQL statement after connection established in EF Core with Npgsql
I'd like to execute a specific SQL command each time after the connection to the database was successful using Entity Framework Core 9 with the Npgsql Entity Framework Core Provider.
More specifically,...
- 154
0
votes
0
answers
98
views
Limitations of database context pooling in .NET
I have a base class called BaseDbContext. I derived two classes from this: WriteDbContext and ReadDbContext. Both contexts have compiled queries as private members in WriteDbContext and ReadDbContext:
...
- 39
3
votes
0
answers
385
views
Enum mapping with Dapper and npgsql (from Postgres enum type)
I have a bunch of enums in my Postgres database, and for now I always parsed the enums manually from string to the effective value inside my .NET code (first map to string and then convert). While ...
- 4,538
1
vote
0
answers
76
views
"42883: procedure usp_delete_announcements(integer) does not exist" while calling stored procedure with out paremeters from .NET Core
I am trying to call a PostgreSQL stored procedure with OUT parameters from my .NET Core 3.1 API to delete a record based on an ID. However, when I attempt to execute the stored procedure with OUT ...
- 11
0
votes
1
answer
141
views
Composite type parameter in PostgreSql using Npgsql,
I'm working with Npgsql and and Dapper I have some tables in PostgreSql with a composite data type:
create type datetimeoffset as
(
"DateTimeUtc" timestamp without time zone,
"...
- 1,760
6
votes
1
answer
969
views
Polymorphism in Entity Framework Core 9 with PostgreSQL and jsonb column
I am currently working on a database table, that shall contain some base properties of the data and one property that is a jsonb column in the database that can contain different objects that are ...
- 539
0
votes
0
answers
79
views
Compare NodaTime-Periods in EF Core
I'm building an application, using .NET (8), EF Core (8) and Postgres (16). I use nodaTime for every date or time related task and NodaTime.Periods are stored in pg as interval.
I configure them ...
- 1,347
0
votes
0
answers
59
views
Multi tenancy support for Single database shared schema
We are using a single database with a shared schema multi-tenancy model, as it is the most practical way to support a large number of tenants.
Is there a recommended pattern or approach in Npgsql to ...
- 1
0
votes
0
answers
80
views
Database tool in Performance Profiler not working when program is pointed to PostgreSQL
My program in C# using Entity Framework Core I can point to a PostgreSQL or SQLite database. When pointed to PostgreSQL the Database tool of Performance Analyzer does not work. There only appear ...
0
votes
0
answers
67
views
Custom boolean predicate sort convention, is it possible?
We're using Hot Chocolate 14. .Net + EF Core 8, and PostgreSQL 13... I'd like to build a custom sort convention... but I haven't found any examples after scouring the internet.
The first example is ...
0
votes
1
answer
202
views
EFCore, NPGSQL and NodaTime throws InvalidCast exception reading timestamptz into Instant after upgrade from .Net 6 to 8
We have an existing Database First (e.g. Scaffolded) .Net 6 project attached to PostgreSQL 13 DB.
The DB has a lot of timestamptz aka TIMESTAMP WITH TIMEZONE fields. So we use NodaTime.
Time(!) came ...
0
votes
1
answer
93
views
web api connection pooling
I am using the following code with npgsql 9.0.2 inside a .net 8 web api to insert records into a table, but I am having some trouble where my database gets completely overloaded with the CPU hovering ...
- 119
1
vote
0
answers
132
views
How to compare two dates with EF Core LINQ and Npgsql?
I am struggling to get documents expiring in less than 90 days:
var query = from doc in context.Documents
where doc.ExpirationDate <= DateOnly.FromDateTime(DateTime.Today.AddDays(90))
...
- 3,473
0
votes
0
answers
36
views
Connect ErikEJ Powertools to Google Cloud Postgres Database
Been fighting with this for some time. Normally got around this running a local copy of my database on my local machine to get around all of the Google/SSL insanity. Burned myself on at least one ...
- 31
1
vote
0
answers
45
views
Npgsql 4.1.14: call stored procedure with nullable types
I have the following PostgreSQL "function1"
function1(par1 Enum_1),
function1(par1 integer),
...
function1(par1 text)
In Npgsql V 3.2.7, the parameters could be created together with the ...
- 27
2
votes
0
answers
108
views
Why would npgsql be throwing two different errors for timing out?
I have an ASP.NET Core Web API using EF Core. I'm trying to implement timeout middleware as shown here:
public async Task InvokeAsync(HttpContext context, RequestDelegate next)
{
int ...
- 21
0
votes
1
answer
144
views
CREATE a stored procedure using plain query text via NpgsqlConnection::ExecuteAsync
I am trying to create a stpored procedure via Npsql from C#
private async Task TestExecute(string cmdQuery)
{
var cnnString = $"Server=localhost;User Id=admin;Password=somepass;...
- 357
0
votes
1
answer
109
views
I see two approaches to npgsql prepares. Trying to understand the differences
I have been exploring how to access Postgres from C#. When using prepared statements ( which I almost always want to do) the website recommends the following:
Create a connection the db ...
- 143
0
votes
1
answer
84
views
How to pass tstzrange to postgres
Passing tstzrange to postgres in EF Core using
DateTime algus = DateTime.Parse("2024-11-25T00:00:00+02:00", CultureInfo.InvariantCulture).ToUniversalTime();
DateTime lopp = DateTime.Parse(&...
- 28.3k
-1
votes
2
answers
182
views
PostgreSQL stored procedure fails with dynamic parameters for date in Dapper
I'm using PostgreSQL with a .NET project, and I'm facing an issue executing a stored procedure through Dapper when using dynamic parameters to pass a DATE parameter.
The stored procedure is defined as ...
0
votes
0
answers
158
views
How to track open database connections with Entity Framework Core 8?
I have an ASP.NET Core 8 MVC application. In production the application throws an error
Npgsql.PostgresException (0x80004005): 08P01: no more connections allowed (max_client_conn)
I am using NPGSQL ...
0
votes
1
answer
204
views
In EF Core is it possible to map a `System.Version` column keeping the type's unique sorting properties?
I'd like to make a Version-backed column in the database and order by it in queries, keeping the ordering consistent with what would be the result if run on the client directly. There are many entity ...
- 966