Cannot insert into or update timestamp(tz) column due to mismatching type

[vaindil]

I'm on 2.1.0-preview1. I'm unable to insert into any table I have that uses timestamp or timestamptz (I tested with both). My .NET type is DateTimeOffset. The relevant part of the log file is below. I don't know how to log any more than what's here, I can't seem to get the actual parameters that are being passed no matter what I try. I can provide any other info that's needed, I'm not sure what you'll need. The problem occurs on both nullable and non-nullable columns.

LOG:  statement: BEGIN
LOG:  statement: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ERROR:  column "first_offline_at" is of type timestamp with time zone but expression is of type text at character 246
HINT:  You will need to rewrite or cast the expression.
STATEMENT:  INSERT INTO "twitch_live_stream" ("twitch_user_id", "first_offline_at", "game_id", "game_name", "profile_image_url", "started_at", "thumbnail_url", "title", "twitch_display_name", "twitch_login", "twitch_stream_id", "viewer_count")
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
LOG:  statement: ROLLBACK
LOG:  statement: DISCARD ALL

[vaindil]

Sorry for the mess of editing above. Downgrading to 2.0.1 fixed the problem for me.

[jholovacs]

I believe this is also related to my problem: it looks like when using the EF identity implementation with npgsql, there's also confusion on the DateTime parsing. Getting messages like:

42804: column "lockout_end" is of type timestamp with time zone but expression is of type text

For DateTime data types, the proper conversion should be timestamp without time zone. For DateTimeOffset data types the proper conversion should be timestamp with time zone.

[jholovacs]

Any motion on this? This is blocking me at this point.

[jholovacs]

Looks like it's related to #358

[roji]

@jholovacs can you please post some code so I can see your issue?

It's probably not related to #358, which is about TimeSpan (which maps to PostgreSQL interval), not DateTimeOffset (which maps to PostgreSQL timestamp with time zone).

[jholovacs]

There's really no code to post, only an error when trying to use EF against Postgres for Identity services. I try to create a new user, and I get this message:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 42804: column "lockout_end" is of type timestamp with time zone but expression is of type text

As I am using migrations, it seems all is not well in the model translation.

[jholovacs]

Perhaps related to #303?

After looking at the debug log, I was able to get the following info out:

Failed executing DbCommand (17ms) [Parameters=[p0='?' (Size = 3000), p1='?', p2='?', p3='?', p4='?' (Size = 256), p5='?', p6='?' (Size = 35), p7='?' (Size = 150), p8='?' (Size = 150), p9='?' (Size = 150), p10='?', p11='?', p12='?', p13='?' (Size = 35), p14='?', p15='?' (Size = 48), p16='?' (Size = 256), p17='?' (Size = 256), p18='?', p19='?', p20='?', p21='?', p22='?', p23='?' (Size = 256)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "asp_net_users" ("about", "access_failed_count", "concurrency_stamp", "created_utc_timestamp", "email", "email_confirmed", "first_name", "image_url_large", "image_url_medium", "image_url_small", "is_site_admin", "last_modified_by", "last_modified_utc_timestamp", "last_name", "lockout_enabled", "lockout_end", "normalized_email", "normalized_user_name", "password_hash", "phone_number", "phone_number_confirmed", "security_stamp", "two_factor_enabled", "user_name")
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23)
      RETURNING "id";

[roji]

@jholovacs I need a minimal code sample in order to investigate, even if it's just a minimal identity services sample. Just a bit of code that triggers the problem.

[jholovacs]

OK, I think I figured out what the problem is: lockout_end is a nullable DateTimeOffset, and that does not seem to be supported.

What I did to get around this is I changed the model builder for the property like so:

            builder.Entity<MyUser>()
                .Property(e => e.LockoutEnd)
                .HasColumnType("VARCHAR")
                .HasMaxLength(50)
                .IsRequired(false)
                .HasConversion(v => v == null ? null : v.Value.UtcDateTime.ToString("o"),
                    c => c == null
                        ? null as DateTimeOffset?
                        : new DateTimeOffset(DateTime.Parse(c, null, DateTimeStyles.RoundtripKind), TimeSpan.Zero));

I don't think this is an ideal solution, but apparently when a nullable DateTimeOffset is inserted into the database, it's sent as a null string, which causes all sorts of conversion issues. By making the column a VARCHAR, and performing an explicit conversion back and forth, the system is able to handle it.

[roji]

@jholovacs, i'm still waiting for a minimal code sample... If the issue is with nullable Datetime offset, can you please write that up and post? It should be working.

[jholovacs]

@roji As mentioned previously, I don't have a code sample. This is boilerplate stuff, built into EF Identity framework. There's no code I wrote to demonstrate this, beyond the standard UserManager<TUser>.CreateAsync(TUser user) and UserManager<TUser>.AddPasswordAsync(TUser user, string password).

I will add that this was working fine until recently, and then it stopped working.

[roji]

Is this still not working with 2.1.0 RTM? If not, any chance I can get you to submit a code sample without ASP.NET, Identity or anything else (just a small console app)? My time is very limited and any help would be appreciated.

[jholovacs]

A lot of changes happened with 2.1.0 recently, so I have to write a bunch of new code. I had originally written code to change PascalCase naming in c# to snake_case naming in postgres, but all the classes for doing so appear to have changed completely (completely broke my implementation) so I have a lot of rework to do before I can try 2.1.0.

On Sun, Jun 10, 2018 at 7:34 AM Shay Rojansky notifications@github.com wrote:

Is this still not working with 2.1.0 RTM? If not, any chance I can get you to submit a code sample without ASP.NET, Identity or anything else (just a small console app)? My time is very limited and any help would be appreciated.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/344#issuecomment-396042083, or mute the thread https://github.com/notifications/unsubscribe-auth/ABr4dDSEqFMDWI35m5Ix3SlHPyWpOFfBks5t7QRLgaJpZM4SvAVH .

[roji]

I'm sorry to hear it (although I'm not aware of any such breaking changes in the EF Core provider - unlike at the ADO level where there indeed are quite a few breaking changes).

The provider has several tests where DateTimeOffset is written and read, and to make extra sure I've just successfully roundtripped some DateTimeOffsets in a test program. My guess is that there's something going on in the application or in the Identity code itself which is triggering your issue, but I need to be able to see the error via a test project or code sample.

Another thought is that there were some date/time mapping changes in Npgsql 4.0.0 which could also have affected 2.1.0 - there's a chance this will just work now.

I'm going to close this for now as I don't have anything to work on, but I'm interested in fixing this if it's a bug in Npgsql and will reopen as soon as more info is provided.

[sarath22]

Hi,

I'm also getting the same issue:

My table:

CREATE TABLE public."TestingDetails" ( "TestID" integer NOT NULL, "TestName" text, "TesterName" text, "TotalCount" integer, "SuccessCount" integer, "FailureCount" integer, "EndDate" timestamp without time zone, "StartDate" timestamp without time zone, CONSTRAINT "TestingDetails_pkey" PRIMARY KEY ("TestID"), CONSTRAINT "TestingDetails_TestID_key" UNIQUE ("TestID") ) WITH ( OIDS=FALSE ); ALTER TABLE public."TestingDetails" OWNER TO postgres;

Code:

string connstring = "Server=localhost;Port=5432;Database=test;User Id=postgres;Password=sync1694"; // Making connection with Npgsql provider NpgsqlConnection conn = new NpgsqlConnection(connstring); conn.Open(); if (conn.State == System.Data.ConnectionState.Open) { string sql = "SELECT * FROM public.\"TestingDetails\" "; // data adapter making request from our connection NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn); DataSet ds = new DataSet(); ds.Reset(); // filling DataSet with result from NpgsqlDataAdapter da.Fill(ds); // since it C# DataSet can handle multiple tables, we will select first int _TestID=0; string _TestName = "test"; string _TesterName = "demo"; DateTime _EndDate = DateTime.Now; DateTime _StartDate = DateTime.Now; int _TotalCount = 10; int _SuccessCount = 5; int _FailureCount = 5; if (ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; _TestID = dt.Rows.Count + 1; } //new NpgsqlDateTime() //dt. //NpgsqlCommand command = new NpgsqlCommand("insert into public.\"TestingDetails\" (TestID, TestName, TesterName, TotalCount, SuccessCount, FailureCount) values(" + _TestID + ",'" + _TestName + "','" + _TesterName + "'," +_TotalCount + "," + _SuccessCount + "," + _FailureCount + ")", conn); NpgsqlCommand command = new NpgsqlCommand("insert into public.\"TestingDetails\" values(:_TestID , :_TestName , :_TesterName , :_TotalCount , :_SuccessCount, :_FailureCount, :_StartDate)", conn); command.Parameters.Add(new NpgsqlParameter("_TestID", _TestID)); command.Parameters.Add(new NpgsqlParameter("_TestName", _TestName)); command.Parameters.Add(new NpgsqlParameter("_TesterName", _TesterName)); command.Parameters.Add(new NpgsqlParameter("_StartDate", _StartDate)); command.Parameters.Add(new NpgsqlParameter("_TotalCount", _TotalCount)); command.Parameters.Add(new NpgsqlParameter("_SuccessCount", _SuccessCount)); command.Parameters.Add(new NpgsqlParameter("_FailureCount", _FailureCount)); command.Parameters.Add(new NpgsqlParameter("_EndDate", _EndDate)); command.ExecuteNonQuery(); }

[roji]

@sarath22 you're describing a different issue. This repo is for Entity Framework Core, while you're just using the ADO.NET provider. Please open a new issue in http://github.com/npgsql/npgsql with the exact exception message and stack trace.

[sarath22]

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/630 - please look