Comments (25)
nit: await using var conn = new NpgsqlConnection(_connectionString);
, but: great repro, thanks - looking
from dapper.
great; that's a workaround, and we'll get the code fixed for next release
from dapper.
This change will almost certainly be reverted; it broke other scenarios, where string
is handled more flexibly. I'll check more options.
from dapper.
@lucax88x the ADO tests in that gist pass, only dapper_should_work_with_json_net seems to fail.
from dapper.
I imagine that what's happening here is that npgsql is doing the deserialize, and giving us something that isn't a string (but rather: a rich object) - and we're expecting it to be a string. This is probably related to Dapper (vanilla) using the untyped data read API in some cases. Dapper.AOT does a better job of preferring typed data, so I imagine that Dapper.AOT would explicitly request a string, and receive a string. Any chance you can try the same query with Dapper AOT enabled? Or provide a full repro?
from dapper.
There's a github link with a full repro on top.
Clone the repo, and run the tests, it will fire up a db with test containers and run the query.
I can try with AOT tomorrow!
from dapper.
See https://aot.dapperlib.dev/gettingstarted
from dapper.
apologies, I overlooked the repro; taking a peek
from dapper.
I'm a little confused... it... works? (I haven't changed anything except the connection string)
and at the command-line:
09:39:49 DapperBug.Tests main 8.0.200 13.796s
➜ dotnet test
Determining projects to restore...
All projects are up-to-date for restore.
DapperBug -> C:\Code\dapper-bug\DapperBug\bin\Debug\net8.0\DapperBug.dll
DapperBug.Tests -> C:\Code\dapper-bug\DapperBug.Tests\bin\Debug\net8.0\DapperBug.Tests.dll
Test run for C:\Code\dapper-bug\DapperBug.Tests\bin\Debug\net8.0\DapperBug.Tests.dll (.NETCoreApp,Version=v8.0)
Microsoft (R) Test Execution Command Line Tool Version 17.9.0 (x64)
Copyright (c) Microsoft Corporation. All rights reserved.
Starting test execution, please wait...
A total of 1 test files matched the specified pattern.
Passed! - Failed: 0, Passed: 1, Skipped: 0, Total: 1, Duration: < 1 ms - DapperBug.Tests.dll (net8.0)
What should I be seeing?
from dapper.
it is possible that this can be fixed with a one-liner in SqlMapper.cs L217:
- [typeof(string)] = DbType.String,
+ [typeof(string)] = new(DbType.String, TypeMapEntryFlags.SetType | TypeMapEntryFlags.UseGetFieldValue),
can check as soon as we can repro
on a per-consumer-project basis, we can check this with
SqlMapper.AddTypeMap(typeof(string), DbType.String, true);
from dapper.
I'm a little confused... it... works? (I haven't changed anything except the connection string)
and at the command-line:
09:39:49 DapperBug.Tests main 8.0.200 13.796s ➜ dotnet test Determining projects to restore... All projects are up-to-date for restore. DapperBug -> C:\Code\dapper-bug\DapperBug\bin\Debug\net8.0\DapperBug.dll DapperBug.Tests -> C:\Code\dapper-bug\DapperBug.Tests\bin\Debug\net8.0\DapperBug.Tests.dll Test run for C:\Code\dapper-bug\DapperBug.Tests\bin\Debug\net8.0\DapperBug.Tests.dll (.NETCoreApp,Version=v8.0) Microsoft (R) Test Execution Command Line Tool Version 17.9.0 (x64) Copyright (c) Microsoft Corporation. All rights reserved. Starting test execution, please wait... A total of 1 test files matched the specified pattern. Passed! - Failed: 0, Passed: 1, Skipped: 0, Total: 1, Duration: < 1 ms - DapperBug.Tests.dll (net8.0)
What should I be seeing?
I'm a little confused... it... works? (I haven't changed anything except the connection string)
and at the command-line:
09:39:49 DapperBug.Tests main 8.0.200 13.796s ➜ dotnet test Determining projects to restore... All projects are up-to-date for restore. DapperBug -> C:\Code\dapper-bug\DapperBug\bin\Debug\net8.0\DapperBug.dll DapperBug.Tests -> C:\Code\dapper-bug\DapperBug.Tests\bin\Debug\net8.0\DapperBug.Tests.dll Test run for C:\Code\dapper-bug\DapperBug.Tests\bin\Debug\net8.0\DapperBug.Tests.dll (.NETCoreApp,Version=v8.0) Microsoft (R) Test Execution Command Line Tool Version 17.9.0 (x64) Copyright (c) Microsoft Corporation. All rights reserved. Starting test execution, please wait... A total of 1 test files matched the specified pattern. Passed! - Failed: 0, Passed: 1, Skipped: 0, Total: 1, Duration: < 1 ms - DapperBug.Tests.dll (net8.0)
What should I be seeing?
Wow.
This is extremely weird, I lost two hours for that.
So, stay with me:
update the git repo
run the compose from the root
docker compose -f docker-compose.yml up
now, you have 2 tests from the solution
-CreateTables
-RunAloneNotTogetherWithCreateTables
- Try to run CreateTables ALONE so you have the structure.
- Now run the RunAloneNotTogetherWithCreateTable ALONE and it should give the Exception above (now reproduced)
But, if for some reason, you try to run both of them together, CreateTables will fail because table is already there (which is OK), but the RunAloneNotTogetherWithCreateTables will be green (WTF?)!
Something must happening with assembly...
Gonna try with your map.
from dapper.
SqlMapper.AddTypeMap(typeof(string), DbType.String, true);
I can confirm it works.
from dapper.
Npgsql.PostgresException : 3D000: database "test" does not exist
so... I don't think it is connecting to the containerized database, but... I think we understand the problem well enough now
from dapper.
Create the db in the container :)
from dapper.
thanks for the quick answer btw.
from dapper.
a couple of subtle behaviour changes with exception messages, but : within tolerance (and in some cases, clearer)
from dapper.
I don't know where the problem is, but I'm noticing the same bug using npgsql 8 and dapper 2.0.151.
I did another repro using a online postgresql.
https://gist.github.com/lucax88x/69c6deb14b80c856271534de5c10e1ad
dapper + json.net
dapper without json.net
npgsql with json.net
ngpsql without json.net
Without the workaround (the SqlMapper one) the one with dapper and json.net fails.
So I think the regression didn't come with 2.1 but rather from 8.0 of npgsql.
from dapper.
from dapper.
https://github.com/DapperLib/Dapper/releases/tag/2.1.37
from dapper.
It looks like we still haven't shipped NuGet packages for this - will fix However: IIRC npgsql now marks this global API as deprecated, preferring a column-specific configuration, so I'm sure they'd say "we already fixed this: the fix is to stop using that API" (apologies if I'm misrepresenting this - I don't do much npgsql)
…
On Thu, 14 Mar 2024, 12:51 Luca Trazzi, @.***> wrote: @mgravell https://github.com/mgravell I don't know where the problem is, but I'm noticing the same bug using npgsql 8 and dapper 2.0.151. I did another repro using a online postgresql. https://gist.github.com/lucax88x/69c6deb14b80c856271534de5c10e1ad dapper + json.net dapper without json.net npgsql with json.net ngpsql without json.net Without the workaround (the SqlMapper one) the one with dapper and json.net fails. So I think the regression didn't come with 2.1 but rather from 8.0 of npgsql. — Reply to this email directly, view it on GitHub <#2049 (comment)> or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAEHMAFKXOWA7YXJ27DRADYYGMMJBFKMF2HI4TJMJ2XIZLTSOBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDUOJ2WLJDOMFWWLLTXMF2GG2C7MFRXI2LWNF2HTAVFOZQWY5LFUVUXG43VMWSG4YLNMWVXI2DSMVQWIX3UPFYGLLDTOVRGUZLDORPXI6LQMWWES43TOVSUG33NNVSW45FGORXXA2LDOOJIFJDUPFYGLKTSMVYG643JORXXE6NFOZQWY5LFU4YTMMJTGM2DLAVEOR4XAZNFNFZXG5LFUV3GC3DVMWVDEMJXGAYDEMJRGUZ2O5DSNFTWOZLSUZRXEZLBORSQ . You are receiving this email because you were mentioned. Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub .
if you check the second repro I'm not using the global one, so they didn't fix, also because the problem is occuring only with dapper
from dapper.
@roji can I summon your wisdom here? Problem is JSON deserialize kicking in when using the untyped read API, but actually wanting a string; there are long reasons (fixed in AOT) why typed read is a PITA. Anything more I can do the detect this problem before it happens?
from dapper.
Hey @mgravell... Is this the repro (from the OP)? That's odd - by default, Npgsql should absolutely return a string for an untyped read of a PG jsonb.
If I I tried to repro this without Dapper (similar to the gist in #2049 (comment)) and couldn't (see below) - is it possible to get an ADO.NET-only code sample that shows what Dapper is doing when stuff fails?
Re NpgsqlConnection.GlobalTypeMapper being obsolete, that shouldn't have anything to do with this; we now recommend using the newer DbDataSource API to create connections, and to configure plugins such as Json.NET there, so:
var builder = new NpgsqlDataSourceBuilder("Host=localhost;Username=test;Password=test");
builder.UseJsonNet();
await using var dataSource = builder.Build();
await using var conn = await dataSource.OpenConnectionAsync();
Attempted ADO-only repro
#pragma warning disable CS0618 // Type or member is obsolete
NpgsqlConnection.GlobalTypeMapper.UseJsonNet();
#pragma warning restore CS0618 // Type or member is obsolete
await using var conn = new NpgsqlConnection("Host=localhost;Username=test;Password=test");
await conn.OpenAsync();
Console.WriteLine("Creating and seeding data");
await conn.ExecuteAsync("""
drop table if exists posts;
create table public.posts
(
postid integer generated by default as identity
constraint "PK_posts"
primary key,
title text not null,
content jsonb not null
);
insert into public.posts (postid, title, content) values (1, 'title', '{"Prop1": "2", "Prop2": 2}');
""");
await using (var command = new NpgsqlCommand("select content from public.posts", conn))
await using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine($"Content typed: {reader.GetFieldValue<string>(0)}");
Console.WriteLine($"Content untyped: {reader.GetValue(0)}");
}
}
// The following indeed throws:
_ = await conn.QueryFirstOrDefaultAsync<string>("select content from public.posts");
from dapper.
@roji this is a simpler repro using both ado and dapper.
https://gist.github.com/lucax88x/69c6deb14b80c856271534de5c10e1ad
from dapper.
crossref #2070
from dapper.
https://github.com/DapperLib/Dapper/releases/tag/2.1.42
from dapper.
Related Issues (20)
- Getting Time column value from DB is not working (2.1.35) HOT 9
- QueryAsync not passing parameters in OdbcConnection and Sybase (SQL Anywhere) HOT 3
- Version 2.1.37 not in NuGet HOT 4
- You can see more details here
- Unable to reuse TempTable created when also sending params into the execute HOT 2
- Multiple enumerations of IEnumerable<SqlDataRecord> passed as TVP HOT 5
- The byte data type is not supported by the IN clause
- [Feature Request] Using InterpolatedStringHandler for templated command, becuase its pretty
- "No mapping exists" error when using a TypeHandler and a collection parameter
- Problem with DateOnly mapping when updating form v2.1.35 to 2.1.44 HOT 5
- DateOnly "off by one" column error HOT 9
- Potential buffer over-read (yielding exception) in Npgsql code when reading arrays with ArrayNullabilityMode.PerInstance HOT 2
- SQLite TypeHandler not called HOT 3
- What's the status of version 2.1.42 and version 2.1.44? HOT 2
- Unable to find Dapper.dll in linux machine. HOT 9
- Silent Exception in QueryFirstOrDefaultAsync<T> / QueryFirstOrDefault (MS SQL) HOT 4
- Add query execution listeners HOT 1
- Dapper Package - Failed to resolve assembly: 'System.Data.Common, Version=0.0.0.0, Culture=neutral, ...' HOT 8
- Pass C# array to UDTF in Snowflake as an array
- Feature Request: Type-safe Mapping
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dapper.