Insertion bug

[redbaty]

The issue

Can't insert properly on Firebird 2.5

Steps to reproduce

Create a project, add the entity framework dependencies and try to insert multiple objects into a table ( Not tested single ones )

Error details

It doesn't create an error but it doesn't insert into the database and the SQL output shown on the Output window is not right

Further technical details

Firebird version: 2.5 EntityFrameworkCore.FirebirdSql version: 2.0.11

[ralmsdeveloper]

Hello @redbaty , Could you show us something? any code?

To try to make a repro.

[redbaty]

@ralmsdeveloper Hey sorry, I created this just when I was leaving to go home. I'll create one now

[redbaty]

@ralmsdeveloper Oh and I've just noted that you can't use .EnsureCreated() too, should I create another Issue, to keep track of?

[ralmsdeveloper]

Yes, thanks for this!

[ralmsdeveloper]

For me it's working EnsureCreated()

[redbaty]

@ralmsdeveloper hmm strange, here it gives an error saying that the token BY is unknown, I'll post the stack trace in a bit

[redbaty]

@ralmsdeveloper Try to reproduce it using this repo: https://github.com/redbaty/FirebirdTest

Let's see if you can reproduce it first, if so then I'll create the issue.

Oh and here's the stack trace:

{FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 32
BY ---> Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 32
BY
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteNonQuery() in C:\Users\Jiri\Documents\devel\NETProvider\working\Provider\src\FirebirdSql.Data.FirebirdClient\FirebirdClient\FbCommand.cs:line 478
   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at EntityFrameworkCore.FirebirdSql.Storage.Internal.FirebirdRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()
   at FirebirdTest.Program.Main(String[] args) in C:\Users\Marcos\source\repos\FirebirdTest\FirebirdTest\Program.cs:line 10}

[ralmsdeveloper]

Make um DownGrade , and test please!

[redbaty]

@ralmsdeveloper You mean the package or firebird itself?

[ralmsdeveloper]

I will solve some things now, but tomorrow, I will reproduce with your code!

[redbaty]

@ralmsdeveloper All right, Boa Noite!

[ralmsdeveloper]

Rsss, Boa noite conterrâneo!

[ralmsdeveloper]

Hi redbaty,

Thanks for the Issue, please upgrade to 2.0.11.1

[redbaty]

@ralmsdeveloper Hey the ensure created works 🎉 I've just updated the same repository with this issue's repro. (The insertion bug)

[ralmsdeveloper]

Change this: public string Url {get; set; }

Per:

[StringLength (100)]
public string Url {get; set; }

There are limitations to internal use of the "BLOCK" command for varchar (8191)

So when you do not tell the maximum size it creates fields to type varchar (8191)

👍

[redbaty]

@ralmsdeveloper Hmm, it is still not saving, did it work on your computer?

[redbaty]

@ralmsdeveloper Oh wow, that was dumb, I was not adding the toAdd list to the context. It worked thank you so much! :tada:

[redbaty]

@ralmsdeveloper Hey just a suggestion though, could you throw an exception when the attribute is not there? Cause then it'll make it easier for others to debug this.

[ralmsdeveloper]

Hello @Redbaty, But this is the expected treatment! That's right, you just did not pass anything on the EFCore crawler, so by calling SaveChanges (), it will record what's in the crawler!

You can use the int ret = .... SaveChanges (); if the return is greater than 0 recorded!

[redbaty]

@ralmsdeveloper Could we add it to the scaffolding process then? Or is it this limitation you were talking about?

[ralmsdeveloper]

Not limitation, only you were doing this:

 for (int i = 0; i < 100; i++)
{
     toAdd.Add(new Blog{Url = Guid.NewGuid().ToString()});
 }
 x.SaveChanges();

instead:

 for (int i = 0; i < 100; i++)
{
     toAdd.Add(new Blog{Url = Guid.NewGuid().ToString()});
 }
 x.Blogs.AddRange(toAdd);  // <<<<---------
 x.SaveChanges();

Since there was no information for the crawler then SaveChanges did nothing!

[redbaty]

@ralmsdeveloper I meant these: [StringLength (100)]

[ralmsdeveloper]

Sorry, I did not see where you referenced this. But you should do this with Annotation or Fluent API, in OnModelCreating()

Was this class generated by Scaffolding?

[redbaty]

From the test project no, but a colleague at work was having some trouble with this, we tried the -d argument when scaffolding but it didn't help, the length attribute was missing

[redbaty]

@ralmsdeveloper Actually it does, my bad, but now there's a mistake on the insert statement:

INSERT INTO "PRODUTOS" ("CODIGOPRODUTO", "APELIDOPRODUTO", "AREAPRODUTO", "BALANCA", "CEST", "CHKREGISTRO", "CODGRADE", "CODIGOANP", "CODIGOANTERIOR", "CODIGOEMP", "CODIGOINSTRUCAO", "CODNBMSH", "CODORIGINAL", "CODSETOR", "CONTROLAGRADE", "DATACADASTRO", "DESCFISCAL", "DESCORCAMENTO", "DESCRICPRODUTO", "DIASVAL", "FABRICANTEPRODUTO", "FAMILIA", "GRUPOPRODUTO", "IMAGEMPRODUTO", "IMPDATAEMB", "IMPDATAVAL", "LVDESCRICAO", "LVRESUMO", "LVTAG", "MARCAPRODUTO", "MEMOPRODUTO", "NIVEL10PRODUTO", "NIVEL1PRODUTO", "NIVEL2PRODUTO", "NIVEL3PRODUTO", "NIVEL4PRODUTO", "NIVEL5PRODUTO", "NIVEL6PRODUTO", "NIVEL7PRODUTO", "NIVEL8PRODUTO", "NIVEL9PRODUTO", "PERCLOJAONLINE", "PRODUTOLOJAONLINE", "REFERENCIAPRODUTO")
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, :p24, :p25, :p26, :p27, :p28, :p29, :p30, :p31, :p32, :p33, :p34, :p35, :p36, :p37, :p38, :p39, :p40, :p41, :p42, :p43)END;

There's a unnecessary END; at the end.

Ps: this Database is very old and we're moving it to a new platform, that's why there are those horrible column names

[ralmsdeveloper]

Open a issue for this, but I believe this is not the full SQL output!

[redbaty]

@ralmsdeveloper There is the parameters below on the output window, but the error is only occurring at that "END;" we've ran it without it and it worked fine

[redbaty]

I'll reopen this issue since this also falls into the "Insertion bug" category

[redbaty]

@ralmsdeveloper Hey if you're up to it you can create a slack or something like that so we can debug it faster

[ralmsdeveloper]

I'll check that later. Thanks!

[redbaty]

@ralmsdeveloper Thank YOU! 😄

[redbaty]

@ralmsdeveloper Some progress, I found out that it needs a ; after the insert statement, now the problem is the affectedrows var on the execute block, it expected 1 but got actually 0, is it actually increasing? Cause the block generated is:

EXECUTE BLOCK ( 
p0  INTEGER=@p0,p1  VARCHAR(200)=@p1,p2  INTEGER=@p2,p3  VARCHAR(1)=@p3,p4  VARCHAR(7)=@p4,p5  VARCHAR(32)=@p5,p6  INTEGER=@p6,p7  VARCHAR(9)=@p7,p8  INTEGER=@p8,p9  INTEGER=@p9,p10  VARCHAR(9)=@p10,p11  VARCHAR(20)=@p11,p12  INTEGER=@p12,p13  VARCHAR(1)=@p13,p14  TIMESTAMP=@p14,p15  VARCHAR(30)=@p15,p16  VARCHAR(200)=@p16,p17  VARCHAR(200)=@p17,p18  INTEGER=@p18,p19  INTEGER=@p19,p20  INTEGER=@p20,p21  INTEGER=@p21,p22  BLOB SUB_TYPE BINARY=@p22,p23  VARCHAR(1)=@p23,p24  VARCHAR(1)=@p24,p25  BLOB SUB_TYPE BINARY=@p25,p26  VARCHAR(400)=@p26,p27  VARCHAR(400)=@p27,p28  INTEGER=@p28,p29  VARCHAR(1000)=@p29,p30  INTEGER=@p30,p31  INTEGER=@p31,p32  INTEGER=@p32,p33  INTEGER=@p33,p34  INTEGER=@p34,p35  INTEGER=@p35,p36  INTEGER=@p36,p37  INTEGER=@p37,p38  INTEGER=@p38,p39  INTEGER=@p39,p40  FLOAT=@p40,p41  VARCHAR(1)=@p41,p42  VARCHAR(200)=@p42) 
RETURNS (AffectedRows BIGINT) AS BEGIN
AffectedRows=0;

INSERT INTO "PRODUTOS" ("CODIGOPRODUTO", "APELIDOPRODUTO", "AREAPRODUTO", "BALANCA", "CEST", "CHKREGISTRO", "CODGRADE", "CODIGOANP", "CODIGOANTERIOR", "CODIGOEMP", "CODNBMSH", "CODORIGINAL", "CODSETOR", "CONTROLAGRADE", "DATACADASTRO", "DESCFISCAL", "DESCORCAMENTO", "DESCRICPRODUTO", "DIASVAL", "FABRICANTEPRODUTO", "FAMILIA", "GRUPOPRODUTO", "IMAGEMPRODUTO", "IMPDATAEMB", "IMPDATAVAL", "LVDESCRICAO", "LVRESUMO", "LVTAG", "MARCAPRODUTO", "MEMOPRODUTO", "NIVEL10PRODUTO", "NIVEL1PRODUTO", "NIVEL2PRODUTO", "NIVEL3PRODUTO", "NIVEL4PRODUTO", "NIVEL5PRODUTO", "NIVEL6PRODUTO", "NIVEL7PRODUTO", "NIVEL8PRODUTO", "NIVEL9PRODUTO", "PERCLOJAONLINE", "PRODUTOLOJAONLINE", "REFERENCIAPRODUTO")
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, :p24, :p25, :p26, :p27, :p28, :p29, :p30, :p31, :p32, :p33, :p34, :p35, :p36, :p37, :p38, :p39, :p40, :p41, :p42);
END;

[redbaty]

@ralmsdeveloper Okay, got it working. I've added a suspend; before the end; statement and it worked, but it threw a IndexOutOfRange exception: Something related about a column name, but the data got inserted so the problem is on the C# side.

Exception

Microsoft.EntityFrameworkCore.DbUpdateException
  HResult=0x80131500
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=EntityFrameworkCore.FirebirdSql
  StackTrace:
   at EntityFrameworkCore.FirebirdSql.Update.Internal.FbModificationCommandBatch.Consume(RelationalDataReader relationalReader) in C:\Users\Desenv09\Documents\GitHub\EntityFrameworkCore.FirebirdSQL\EFCore.FirebirdSql\Update\Internal\FbModificationCommandBatch.cs:line 343
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

Inner Exception 1:
IndexOutOfRangeException: Could not find specified column in results.

[redbaty]

@ralmsdeveloper Hey, can you come over my gitter ? Cause this seems easy to fix, I just need to discuss somethings

[redbaty]

This issue seems to have been fixed in the 2.0.11.3 update!