Pomelo.entityframeworkcore.mysql: Unknown column 'X' in 'field list' after update from 2.2.0 to 3.0.0-rc1.19503.2

Created on 4 Oct 2019  路  6Comments  路  Source: PomeloFoundation/Pomelo.EntityFrameworkCore.MySql

_Originally posted by @Gargano83 in https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/797#issuecomment-538260412_

Hello,
I have been developing a project with Asp.Net Core 2.2 for a long time.
I copied this project and updated to Asp.Net Core 3.
On the new project (with Asp.Net Core 3) I updated Pomelo from version 2.2 to version 3.0.0-rc1.19503.2.
When I start the project in the output of Visual Studio 2019 I get the following error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (28ms) [Parameters=[@__idDocumento_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT d.Id, d.Abstract, d.Attivo, d.CategoriaIdCategoria, d.Corpo, d.IdCategoria, d.IdTemplateDocumento, d.IdTipoDocumento, d.MetaDescription, d.MetaKeywords, d.MetaTitle, d.TemplateDocumentoIdTemplateDocumento, d.TipoDocumentoIdTipoDocumento, d.Titolo, d.Url
      FROM Documenti AS d
      WHERE (d.Id = @__idDocumento_0) AND @__idDocumento_0 IS NOT NULL
      LIMIT 1
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'FFM.Data.FFMDbContext'.
      MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'd.CategoriaIdCategoria' in 'field list'
       ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'd.CategoriaIdCategoria' in 'field list'
         at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
         at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 125
         at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 338
         at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 63
         at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 218
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.QueryingEnumerable1.Enumerator.MoveNext()
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'd.CategoriaIdCategoria' in 'field list'
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'd.CategoriaIdCategoria' in 'field list'
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 125
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 338
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 63
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 218
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.QueryingEnumerable1.Enumerator.MoveNext()

I don't understand why the "CategoriaIdCategoria" column appears in the query since it doesn't exist in the database.
In my DBContext I mapped my "Documents" object like this:
```c#
modelBuilder.Entity().ToTable("Documenti");
modelBuilder.Entity(entity =>
{
entity.HasKey(e => e.IdDocumento);

            entity.HasIndex(i => i.IdTipoDocumento).HasName("IdTipoDocumento");
            entity.HasIndex(i => i.IdTemplateDocumento).HasName("IdTemplateDocumento");
            entity.HasIndex(i => i.IdCategoria).HasName("IdCategoria");
            entity.Property(e => e.IdDocumento).HasColumnName("Id");
            entity.Property(e => e.Titolo).HasColumnType("nvarchar(255)").HasColumnName("Titolo");
            entity.Property(e => e.Url).HasColumnType("nvarchar(255)").HasColumnName("Url");
            entity.Property(e => e.Abstract).HasColumnType("nvarchar(255)").HasColumnName("Abstract");
            entity.Property(e => e.Corpo).HasColumnType("text").HasColumnName("Corpo");
            entity.Property(e => e.Attivo).HasColumnName("Attivo");
            entity.Property(e => e.MetaTitle).HasColumnType("nvarchar(255)").HasColumnName("MetaTitle");
            entity.Property(e => e.MetaDescription).HasColumnType("text").HasColumnName("MetaDescription");
            entity.Property(e => e.MetaKeywords).HasColumnType("nvarchar(255)").HasColumnName("MetaKeywords");

            entity.HasOne(d => d.TipoDocumento).WithMany(p => p.Documenti).OnDelete(DeleteBehavior.Restrict);
            entity.HasOne(d => d.TemplateDocumento).WithMany(p => p.Documenti).OnDelete(DeleteBehavior.Restrict);
            entity.HasOne(d => d.Categoria).WithMany(p => p.Documenti).OnDelete(DeleteBehavior.Restrict);
        });

modelBuilder.Entity().ToTable("Immagini");
modelBuilder.Entity(entity =>
{
entity.HasKey(e => e.IdImmagine);

            entity.HasIndex(i => i.IdDocumento).HasName("IdDocumento");
            entity.Property(e => e.IdImmagine).HasColumnName("Id");
            entity.Property(e => e.Nome).HasColumnType("nvarchar(255)").HasColumnName("Nome");
            entity.Property(e => e.File).HasColumnName("File");
            entity.Property(e => e.Attivo).HasColumnName("Attivo");

            entity.HasOne(d => d.Documento).WithMany(p => p.Immagini).OnDelete(DeleteBehavior.Cascade);
        });

modelBuilder.Entity().ToTable("Allegati");
modelBuilder.Entity(entity =>
{
entity.HasKey(e => e.IdAllegato);

            entity.HasIndex(i => i.IdDocumento).HasName("IdDocumento");
            entity.Property(e => e.IdAllegato).HasColumnName("Id");
            entity.Property(e => e.Nome).HasColumnType("nvarchar(255)").HasColumnName("Nome");
            entity.Property(e => e.File).HasColumnName("File");
            entity.Property(e => e.Attivo).HasColumnName("Attivo");

            entity.HasOne(d => d.Documento).WithMany(p => p.Allegati).OnDelete(DeleteBehavior.Cascade);
        });

```

closed-question type-question
Source
picture lauxjpn
👍1

Most helpful comment

Great. thank you so much @lauxjpn

I explicitly specified the FK and solved it

picture Gargano83 on 4 Oct 2019
👍3

All 6 comments

@Gargano83 Please provide us with either a sample project, or with the following:

  • The model definition (modelBuilder.Entity) for the Categoria class
  • The CREATE TABLE SQL scripts for the Documenti and the Categorias tables
  • The C# code for the Documenti and Categorias model classes (public class Documenti {})
lauxjpn picture lauxjpn on 4 Oct 2019

@lauxjpn below I provide what has been requested:
This is the model definition for the "Categoria" class:
```c#
modelBuilder.Entity().ToTable("Categorie");
modelBuilder.Entity(entity =>
{
entity.HasKey(e => e.IdCategoria);

            entity.Property(e => e.IdCategoria).HasColumnName("Id");
            entity.Property(e => e.Nome).HasColumnType("nvarchar(255)").HasColumnName("Nome");
            entity.Property(e => e.Ordine).HasColumnName("Ordine");
            entity.Property(e => e.VisualizzaMenu).HasColumnName("VisualizzaMenu");
        });

The CREATE TABLE SQL scripts for the Documenti:
```sql
CREATE TABLE documenti (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Titolo varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  Abstract varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  Corpo text,
  Url varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  IdTipoDocumento int(11) NOT NULL DEFAULT '0',
  IdTemplateDocumento int(11) NOT NULL DEFAULT '0',
  Attivo bit(1) NOT NULL DEFAULT b'0',
  MetaTitle varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  MetaKeywords varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  MetaDescription text,
  IdCategoria int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (Id),
  KEY IdTipoDocumento (IdTipoDocumento),
  KEY IdTemplateDocumento (IdTemplateDocumento)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

The CREATE TABLE SQL scripts for the Categorie:

CREATE TABLE categorie (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Nome varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  Ordine int(11) NOT NULL,
  VisualizzaMenu bit(1) NOT NULL,
  PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The C# code for the Documenti model class:
```c#
public class Documenti : PageVM
{
public Documenti()
{
Immagini = new HashSet();
Allegati = new HashSet();
}

    public int IdTipoDocumento { get; set; }
    public int IdCategoria { get; set; }
    public string Titolo { get; set; }
    public string Url { get; set; }
    public string Abstract { get; set; }
    public string Corpo { get; set; }
    public bool Attivo { get; set; }

    public ICollection<Immagini> Immagini { get;set; }
    public ICollection<Allegati> Allegati { get; set; }
    [Display(Name = "Tipo documento")]
    public TipoDocumenti TipoDocumento { get; set; }
    public Categorie Categoria { get; set; }
    [NotMapped]
    public ModelCollection DynamicData { get; set; }
    [NotMapped]
    public Contatti Contatto { get; set; }
}


The C# code for the PageVM model class:
```c#
public class PageVM
    {
        [Display(Name = "Id")]
        public int IdDocumento { get; set; }
        public int IdTemplateDocumento { get; set; }
        public string MetaTitle { get; set; }
        public string MetaKeywords { get; set; }
        public string MetaDescription { get; set; }

        [Display(Name = "Template")]
        public TemplatesDocumenti TemplateDocumento { get; set; }
    }

The C# code for the Categorie model class:
```c#
public class Categorie
{
public Categorie()
{
Documenti = new HashSet();
}

    [Display(Name = "Id")]
    public int IdCategoria { get; set; }
    public string Nome { get; set; }
    public int Ordine { get; set; }
    [Display(Name = "Mostra nel men霉 principale")]
    public bool VisualizzaMenu { get; set; }

    public ICollection<Documenti> Documenti { get; set; }
}

```

However on the 2.2 version of Pomelo I have no problem, these problems are there only with the new version installed.

Gargano83 picture Gargano83 on 4 Oct 2019

I cannot reproduce this issue on the current 3.0.0-wip branch with the following test, which succeeds:
```c#
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.TestUtilities;
using Pomelo.EntityFrameworkCore.MySql.FunctionalTests.TestUtilities;
using Xunit;

namespace Pomelo.EntityFrameworkCore.MySql.FunctionalTests
{
public class ReportedIssuesMySqlTest
: IClassFixture
{
[ConditionalFact]
public virtual void TestIssue851()
{
using var context = CreateContext();

        var documents = context.Set<Documenti>().ToArray();
        Assert.Equal(2, documents.Length);

        var documentsWithCategory = context.Set<Documenti>().Include(d => d.Categoria).ToArray();
        Assert.Equal(2, documentsWithCategory.Count(d => d.Categoria != null));
    }

    public class PageVM
    {
        [Display(Name = "Id")]
        public int IdDocumento { get; set; }
    }

    public class Documenti : PageVM
    {
        public int IdTipoDocumento { get; set; }
        public int IdCategoria { get; set; }

        public Categorie Categoria { get; set; }
    }

    public class Categorie
    {
        [Display(Name = "Id")]
        public int IdCategoria { get; set; }

        public ICollection<Documenti> Documenti { get; set; } = new HashSet<Documenti>();
    }

    public class ReportedIssuesMySqlFixture : SharedStoreFixtureBase<PoolableDbContext>
    {
        protected override void OnModelCreating(
            ModelBuilder modelBuilder,
            DbContext context)
        {
            modelBuilder.Entity<Documenti>(entity =>
            {
                entity.ToTable("Documenti");

                entity.HasKey(e => e.IdDocumento);

                entity.HasIndex(i => i.IdTipoDocumento).HasName("IdTipoDocumento");
                entity.HasIndex(i => i.IdCategoria).HasName("IdCategoria");

                entity.Property(e => e.IdDocumento).HasColumnName("Id");

                entity.HasOne(d => d.Categoria).WithMany(p => p.Documenti).OnDelete(DeleteBehavior.Restrict);
            });

            modelBuilder.Entity<Categorie>(entity =>
            {
                entity.ToTable("Categorie");

                entity.HasKey(e => e.IdCategoria);

                entity.Property(e => e.IdCategoria).HasColumnName("Id");
            });
        }

        protected override void Seed(PoolableDbContext context)
        {
            var categoria = new Categorie();

            context.Set<Categorie>().AddRange(
                categoria
            );

            context.Set<Documenti>().AddRange(
                new Documenti { Categoria = categoria },
                new Documenti { Categoria = categoria }
            );

            context.SaveChanges();
        }

        protected override ITestStoreFactory TestStoreFactory
            => MySqlTestStoreFactory.Instance;

        protected override string StoreName { get; } = "ReportedIssues";
    }

    public ReportedIssuesMySqlTest(ReportedIssuesMySqlFixture fixture)
        => Fixture = fixture;

    protected ReportedIssuesMySqlFixture Fixture { get; }
    protected DbContext CreateContext() => Fixture.CreateContext();
}

}

The following SQL is being generated:
```sql
CREATE TABLE `Categorie` (
    `Id` int NOT NULL AUTO_INCREMENT,
    CONSTRAINT `PK_Categorie` PRIMARY KEY (`Id`)
);

CREATE TABLE `Documenti` (
    `Id` int NOT NULL AUTO_INCREMENT,
    `IdTipoDocumento` int NOT NULL,
    `IdCategoria` int NOT NULL,
    `CategoriaIdCategoria` int NULL,
    CONSTRAINT `PK_Documenti` PRIMARY KEY (`Id`),
    CONSTRAINT `FK_Documenti_Categorie_CategoriaIdCategoria` FOREIGN KEY (`CategoriaIdCategoria`) REFERENCES `Categorie` (`Id`) ON DELETE RESTRICT
);

CREATE INDEX `IX_Documenti_CategoriaIdCategoria` ON `Documenti` (`CategoriaIdCategoria`);

CREATE INDEX `IdCategoria` ON `Documenti` (`IdCategoria`);

CREATE INDEX `IdTipoDocumento` ON `Documenti` (`IdTipoDocumento`);

INSERT INTO `Categorie`
VALUES ();
SELECT `Id`
FROM `Categorie`
WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();

@p0='1' (Nullable = true)
@p1='0'
@p2='0'

INSERT INTO `Documenti` (`CategoriaIdCategoria`, `IdCategoria`, `IdTipoDocumento`)
VALUES (@p0, @p1, @p2);
SELECT `Id`
FROM `Documenti`
WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();

@p0='1' (Nullable = true)
@p1='0'
@p2='0'

INSERT INTO `Documenti` (`CategoriaIdCategoria`, `IdCategoria`, `IdTipoDocumento`)
VALUES (@p0, @p1, @p2);
SELECT `Id`
FROM `Documenti`
WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();

SELECT `d`.`Id`, `d`.`CategoriaIdCategoria`, `d`.`IdCategoria`, `d`.`IdTipoDocumento`
FROM `Documenti` AS `d`

SELECT `d`.`Id`, `d`.`CategoriaIdCategoria`, `d`.`IdCategoria`, `d`.`IdTipoDocumento`, `c`.`Id`
FROM `Documenti` AS `d`
LEFT JOIN `Categorie` AS `c` ON `d`.`CategoriaIdCategoria` = `c`.`Id`

If you think that this is a bug, please provide us with a fully self-contained sample console project, that we can use to replicate this issue.

lauxjpn picture lauxjpn on 4 Oct 2019

The "CategoriaIdCategoria" column reported as an error in your test is a SQL index while in my SQL it is not present (you can see it in the scripts that I sent before).

I can't provide an autonomous console project because I have cloned an existing (rather large) project on which I am trying to migrate to version 3 of Asp.Net Core.

I can't provide a stand-alone console project because I have cloned an existing (rather large) project on which I am trying to migrate to version 3 of Asp.Net Core.

I have the following doubt:
1) the new version of Pomelo that I have installed (version 3.0.0-rc1.19503.2) is not yet complete and definitive

Gargano83 picture Gargano83 on 4 Oct 2019

The CategoriaIdCategoria column is generated by EF Core, because you state that you want a one-to-many relationship, but don't specify which column holds the FK:
```c#
entity.HasOne(d => d.Categoria)
.WithMany(p => p.Documenti)
.OnDelete(DeleteBehavior.Restrict);

If you specify the FK explicitly, the column you expect is being used correctly:
```c#
entity.HasOne(d => d.Categoria)
    .WithMany(p => p.Documenti)
    .HasForeignKey(e => e.IdCategoria) // <-- specify FK property
    .OnDelete(DeleteBehavior.Restrict);

EF Core changed its FK convention in a breaking change for in 3.0.

To fix this, you can either define your own convention, or change your code to explicitly state the FK properties as I did above.

Here is the full test that works as you would expect:
```c#
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.TestUtilities;
using Pomelo.EntityFrameworkCore.MySql.FunctionalTests.TestUtilities;
using Xunit;

namespace Pomelo.EntityFrameworkCore.MySql.FunctionalTests
{
public class ReportedIssuesMySqlTest
: IClassFixture
{
[ConditionalFact]
public virtual void TestIssue851()
{
using var context = CreateContext();

        var documents = context.Set<Documenti>().ToArray();
        Assert.Equal(2, documents.Length);

        var documentsWithCategory = context.Set<Documenti>().Include(d => d.Categoria).ToArray();
        Assert.Equal(2, documentsWithCategory.Count(d => d.Categoria != null));
    }

    public class PageVM
    {
        [Display(Name = "Id")]
        public int IdDocumento { get; set; }
    }

    public class Documenti : PageVM
    {
        public int IdTipoDocumento { get; set; }
        public int IdCategoria { get; set; }

        public Categorie Categoria { get; set; }
    }

    public class Categorie
    {
        [Display(Name = "Id")]
        public int IdCategoria { get; set; }

        public ICollection<Documenti> Documenti { get; set; } = new HashSet<Documenti>();
    }

    public class ReportedIssuesMySqlFixture : SharedStoreFixtureBase<PoolableDbContext>
    {
        protected override void OnModelCreating(
            ModelBuilder modelBuilder,
            DbContext context)
        {
            modelBuilder.Entity<Documenti>(entity =>
            {
                entity.ToTable("Documenti");

                entity.HasKey(e => e.IdDocumento);

                entity.HasIndex(i => i.IdTipoDocumento).HasName("IdTipoDocumento");
                entity.HasIndex(i => i.IdCategoria).HasName("IdCategoria");

                entity.Property(e => e.IdDocumento).HasColumnName("Id");

                entity.HasOne(d => d.Categoria)
                    .WithMany(p => p.Documenti)
                    .HasForeignKey(e => e.IdCategoria)
                    .OnDelete(DeleteBehavior.Restrict);
            });

            modelBuilder.Entity<Categorie>(entity =>
            {
                entity.ToTable("Categorie");

                entity.HasKey(e => e.IdCategoria);

                entity.Property(e => e.IdCategoria).HasColumnName("Id");
            });
        }

        protected override void Seed(PoolableDbContext context)
        {
            var categorie = new Categorie();

            context.Set<Categorie>().AddRange(
                categorie
            );

            context.Set<Documenti>().AddRange(
                new Documenti { Categoria = categorie },
                new Documenti { Categoria = categorie }
            );

            context.SaveChanges();
        }

        protected override ITestStoreFactory TestStoreFactory
            => MySqlTestStoreFactory.Instance;

        protected override string StoreName { get; } = "ReportedIssues";
    }

    public ReportedIssuesMySqlTest(ReportedIssuesMySqlFixture fixture)
        => Fixture = fixture;

    protected ReportedIssuesMySqlFixture Fixture { get; }
    protected DbContext CreateContext() => Fixture.CreateContext();
}

}

It generates the following SQL:
```sql

CREATE TABLE `Categorie` (
    `Id` int NOT NULL AUTO_INCREMENT,
    CONSTRAINT `PK_Categorie` PRIMARY KEY (`Id`)
);

CREATE TABLE `Documenti` (
    `Id` int NOT NULL AUTO_INCREMENT,
    `IdTipoDocumento` int NOT NULL,
    `IdCategoria` int NOT NULL,
    CONSTRAINT `PK_Documenti` PRIMARY KEY (`Id`),
    CONSTRAINT `FK_Documenti_Categorie_IdCategoria` FOREIGN KEY (`IdCategoria`) REFERENCES `Categorie` (`Id`) ON DELETE RESTRICT
);

CREATE INDEX `IdCategoria` ON `Documenti` (`IdCategoria`);

CREATE INDEX `IdTipoDocumento` ON `Documenti` (`IdTipoDocumento`);

INSERT INTO `Categorie`
VALUES ();
SELECT `Id`
FROM `Categorie`
WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();

@p0='1'
@p1='0'

INSERT INTO `Documenti` (`IdCategoria`, `IdTipoDocumento`)
VALUES (@p0, @p1);
SELECT `Id`
FROM `Documenti`
WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();

@p0='1'
@p1='0'

INSERT INTO `Documenti` (`IdCategoria`, `IdTipoDocumento`)
VALUES (@p0, @p1);
SELECT `Id`
FROM `Documenti`
WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();

SELECT `d`.`Id`, `d`.`IdCategoria`, `d`.`IdTipoDocumento`
FROM `Documenti` AS `d`

SELECT `d`.`Id`, `d`.`IdCategoria`, `d`.`IdTipoDocumento`, `c`.`Id`
FROM `Documenti` AS `d`
INNER JOIN `Categorie` AS `c` ON `d`.`IdCategoria` = `c`.`Id`
lauxjpn picture lauxjpn on 4 Oct 2019
👍2

Great. thank you so much @lauxjpn

I explicitly specified the FK and solved it

Gargano83 picture Gargano83 on 4 Oct 2019
👍3
Was this page helpful?
0 / 5 - 0 ratings

Related issues

pjc89 picture pjc89  路  3Comments
cetubig picture cetubig  路  3Comments
aramirezh-dev picture aramirezh-dev  路  3Comments
hainguyenthanh picture hainguyenthanh  路  3Comments
neistow picture neistow  路  4Comments