One to many relation with explicit annotations [ForeignKey]

[juanjoseADN]

Hello,

I'm using .Net 6 MVC with C# 10 and Poco Reverse 3.6 (reverse engineering from SQL Server). I have "UseDataAnnotations"=true and "UseLazyLoading"=true; my Menu model has list of Submenu models and Poco Reverse give me this:

public virtual ICollection<Submenu> Submenus { get; set; } // Submenu.Controlador

but I need explicitly:

[ForeignKey("Controlador")]
public virtual ICollection<Submenu> Submenus { get; set; } // Submenu.Controlador

If I manually add this line "[ForeignKey("Controlador")]" everything works fine but I need Poco Reverse to do this for me. I notice the error when I query the database:

IEnumerable<Menu> lista = await _contexto.Menu.Include("Submenus").ToListAsync();

[juanjoseADN]

I had Menu and Submenu SQL tables with "Controlador" column and it was the problem. Now in Menu table I have "Controlador" column and in Submenu table I have "MenuControlador" column. Using this names I don't need add the line [ForeignKey("Controlador")].

I don't understand why POCO is so strict with the name of a ForeignKey. I would like to use more custom names, for example, ControladorFK in Submenu table. I would like you to explain to me how I can modify this.

Thank you.

[sjh37]

Hi @juanjoseADN It is not strict on the name of the FK. Could you give me some SQL so that I can test this please.

[juanjoseADN]

Of course, I add my actuals tables. IMPORTANT: remember to replace MenuControlador by Controlador in Submenu table if you want to test the configuration of the problem.

CREATE TABLE [dbo].[Menu]
(
    [Controlador] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](50) NOT NULL,
    [NumeroColumnasMostrar] [INT] NOT NULL,
    CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED ([Controlador] ASC)
);
GO

CREATE TABLE [dbo].[Submenu]
(
    [Metodo] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](100) NOT NULL,
    [MenuControlador] [VARCHAR](50) NOT NULL,
    CONSTRAINT [PK_Submenu] PRIMARY KEY CLUSTERED ([Metodo] ASC)
);
GO

ALTER TABLE [dbo].[Submenu] WITH CHECK
ADD CONSTRAINT [Controlador]
    FOREIGN KEY ([MenuControlador])
    REFERENCES [dbo].[Menu] ([Controlador]);
GO

ALTER TABLE [dbo].[Submenu] CHECK CONSTRAINT [Controlador];
GO

If you get it to work in another way, please tell me the solution or send me the project where you do the tests. Thank you.

[sjh37]

using

CREATE TABLE [dbo].[Menu]
(
    [Controlador] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](50) NOT NULL,
    [NumeroColumnasMostrar] [INT] NOT NULL,
    CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED ([Controlador] ASC)
);
GO
CREATE TABLE [dbo].[SubmenuOk]
(
    [Metodo] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](100) NOT NULL,
    [MenuControlador] [VARCHAR](50) NOT NULL,
    CONSTRAINT [PK_SubmenuOK] PRIMARY KEY CLUSTERED ([Metodo] ASC)
);
GO

ALTER TABLE [dbo].[SubmenuOk] WITH CHECK
ADD CONSTRAINT [Controlador]
    FOREIGN KEY ([MenuControlador])
    REFERENCES [dbo].[Menu] ([Controlador]);
GO

ALTER TABLE [dbo].[SubmenuOk] CHECK CONSTRAINT [Controlador];
GO

and running the following C# code

var db = new MyDbContext();
var data = db.SubmenuOks.Include(x => x.Menu).ToList();

executed this sql

SELECT [s].[Metodo],
       [s].[Etiqueta],
       [s].[MenuControlador],
       [m].[Controlador],
       [m].[Etiqueta],
       [m].[NumeroColumnasMostrar]
FROM [dbo].[SubmenuOk] AS [s]
    INNER JOIN [dbo].[Menu] AS [m]
        ON [s].[MenuControlador] = [m].[Controlador];

Which is correct.

WIth your changes you say are bad

CREATE TABLE [dbo].[Menu]
(
    [Controlador] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](50) NOT NULL,
    [NumeroColumnasMostrar] [INT] NOT NULL,
    CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED ([Controlador] ASC)
);
GO
CREATE TABLE [dbo].[SubmenuBroken]
(
    [Metodo] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](100) NOT NULL,
    [Controlador] [VARCHAR](50) NOT NULL,
    CONSTRAINT [PK_SubmenuBroken] PRIMARY KEY CLUSTERED ([Metodo] ASC)
);
GO

ALTER TABLE [dbo].[SubmenuBroken] WITH CHECK
ADD CONSTRAINT [Controlador]
    FOREIGN KEY ([Controlador])
    REFERENCES [dbo].[Menu] ([Controlador]);
GO

ALTER TABLE [dbo].[SubmenuBroken] CHECK CONSTRAINT [Controlador];
GO

Using this C# code

var db = new MyDbContext();
var data = db.SubmenuBrokens.Include(x => x.Menu).ToList();

executed this sql

SELECT [s].[Metodo],
       [s].[Controlador],
       [s].[Etiqueta],
       [m].[Controlador],
       [m].[Etiqueta],
       [m].[NumeroColumnasMostrar]
FROM [dbo].[SubmenuBroken] AS [s]
    INNER JOIN [dbo].[Menu] AS [m]
        ON [s].[Controlador] = [m].[Controlador];

Which is correct too.

Am I doing something wrong to emulate your problem?

[sjh37]

ConsoleApp3.zip Attached is my console application using your SQL, but changing it to version you say is broken.

CREATE TABLE [dbo].[Menu]
(
    [Controlador] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](50) NOT NULL,
    [NumeroColumnasMostrar] [INT] NOT NULL,
    CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED ([Controlador] ASC)
);
GO

CREATE TABLE [dbo].[Submenu]
(
    [Metodo] [VARCHAR](50) NOT NULL,
    [Etiqueta] [VARCHAR](100) NOT NULL,
    [Controlador] [VARCHAR](50) NOT NULL,
    CONSTRAINT [PK_Submenu] PRIMARY KEY CLUSTERED ([Metodo] ASC)
);
GO

ALTER TABLE [dbo].[Submenu] WITH CHECK
ADD CONSTRAINT [Controlador]
    FOREIGN KEY ([Controlador])
    REFERENCES [dbo].[Menu] ([Controlador]);
GO

ALTER TABLE [dbo].[Submenu] CHECK CONSTRAINT [Controlador];
GO

[sjh37]

Please note that the foreign key is not defined as an attribute, but as a fluent configuration. See SubmenuConfiguration.cs and the reference to it in the database context

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.ApplyConfiguration(new MenuConfiguration());
    modelBuilder.ApplyConfiguration(new SubmenuConfiguration());
}

[juanjoseADN]

I'm executing "_contexto.Menu.Include("Submenus").ToListAsync();" so I get first the menus and then I include their submenus. I check you are doing the query in reverse order. ¿Why?

Thank you.

[juanjoseADN]

I have the same OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.ApplyConfiguration(new MenuConfiguracion());
            modelBuilder.ApplyConfiguration(new SubmenuConfiguracion());

[sjh37]

I'm executing "_contexto.Menu.Include("Submenus").ToListAsync();" so I get first the menus and then I include their submenus. I check you are doing the query in reverse order. ¿Why?

This query:

db.Submenus.Include(x => x.Menu).ToList();

=

SELECT [s].[Metodo],
       [s].[Controlador],
       [s].[Etiqueta],
       [m].[Controlador],
       [m].[Etiqueta],
       [m].[NumeroColumnasMostrar]
FROM [dbo].[Submenu] AS [s]
    INNER JOIN [dbo].[Menu] AS [m]
        ON [s].[Controlador] = [m].[Controlador];

Means there must be a submenu, or it will not show the menu as it is using an INNER JOIN.

---

db.Menus.Include(x => x.Submenus).ToList();

=

SELECT [m].[Controlador],
       [m].[Etiqueta],
       [m].[NumeroColumnasMostrar],
       [s].[Metodo],
       [s].[Controlador],
       [s].[Etiqueta]
FROM [dbo].[Menu] AS [m]
    LEFT JOIN [dbo].[Submenu] AS [s]
        ON [m].[Controlador] = [s].[Controlador]
ORDER BY [m].[Controlador];

This query means there can be menus without a sub menu, and is the better way to do it. Notice the LEFT JOIN to the sub menu, meaning it's optional.

---

I don't like magic strings: "_contexto.Menu.Include("Submenus").ToListAsync(); as they can be typed in wrong and only found during run-time. I prefer _contexto.Menus.Include(x => x.Submenus).ToListAsync(); as any errors are cought at compiler-time.

[juanjoseADN]

Ok, the problem is solved. Thanks @sjh37