It tries to use a column name that doesn't exists, but fluent API is correctly configure

[ComptonAlvaro]

I have an employee that can have several contacts forms, telephones and emails. For that, he has two collections, one for telephones and another for emails.

I show first the relevant code, but this is the zip with the sql server database and the code to can test it. temp.zip

Entities:

public class Employee
{
    public long Id { get; set; }
    public string UserName { get; set; }

    public ObservableCollection<ContactForm> Telephones { get; set; } = new ObservableCollection<ContactForm>();
    public ObservableCollection<ContactForm> Emails { get; set; } = new ObservableCollection<ContactForm>();
}


public class ContactForm
{
    public long Id { get; set; }
    public string Contact { get; set; }
}

Configuration with fluent API

public class EmployeeConfiguration : IEntityTypeConfiguration<Employee>
{
    public void Configure(EntityTypeBuilder<Employee> paramEmployeefiguracion)
    {
        paramEmployeefiguracion.ToTable("Employees");


        paramEmployeefiguracion.HasKey(o => o.Id);
        paramEmployeefiguracion.Property<long>(o => o.Id)
            .HasColumnName("IDEmployee")
            .HasColumnType("bigint")
            .IsRequired();

        paramEmployeefiguracion.Property<string>(o => o.UserName)
            .HasColumnName("UserName")
            .HasColumnType("varchar(500)")
            .IsRequired();



        paramEmployeefiguracion
            .HasMany(x => x.Telephones)
            .WithOne()
            .HasForeignKey("IDEmployee");


        paramEmployeefiguracion
            .HasMany(x => x.Emails)
            .WithOne()
            .HasForeignKey("IDEmployee");
    }
}


public class ContactsFormsConfiguration : IEntityTypeConfiguration<ContactForm>
{
    public void Configure(EntityTypeBuilder<ContactForm> paramContactFormConfiguration)
    {
        paramContactFormConfiguration.ToTable("ContactsForms");


        paramContactFormConfiguration.HasKey(o => o.Id);
        paramContactFormConfiguration.Property<long>(o => o.Id)
            .HasColumnName("IDContactForm")
            .HasColumnType("bigint")
            .IsRequired();

        //SHADOW PROPERTY
        paramContactFormConfiguration.Property<long>("IDContactFormType")
            .HasColumnName("IDContactFormType")
            .HasColumnType("bigint")
            .IsRequired();

        paramContactFormConfiguration.Property<string>(o => o.Contact)
            .HasColumnName("ContactForm")
            .HasColumnType("varchar(200)");
    }
}

This is the query:

    public async Task<Employee?> GetEmployeeWithTelephonesAndEmails(long paramIdEmployee)
    {
        return await _context.Employees
                .AsNoTracking()
                .Include(x => x.Telephones.Where(y => EF.Property<long>(y, "IDContactFormType") == 2))
                .Include(x => x.Emails.Where(y => EF.Property<long>(y, "IDContactFormType") == 3))
                .FirstOrDefaultAsync(x => x.Id == paramIdEmployee);
    }

The problem is that if I use the two collections of the employee, telephones and emails and I configure both collections in the configuration in fluent API, I get an exception that says the column name EmployeeId is not valid.

If in the employee i comment one of the collections, emails for example, and I comment the configuration in fluent API and I comment the include of the emails in query, it works.

Is it not possible to have two collections that map to the same table? If it is possible. How could I configure this example?

Thanks.

[ajcvickers]

I get an exception that says the column name EmployeeId is not valid.

I am not able to reproduce this. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[ComptonAlvaro]

@ajcvickers In the first lines I have attached a zip file called temp.zip that has the source code of a project that reproduces the problem and a .bak file with the tables for Sql Server. I guess with this it should be enough to can reproduce the problem, but if you need anything else, tell me.

Thanks so much.

[ajcvickers]

@ComptonAlvaro Yes. I used your code, but did not see any exceptions thrown.

[ComptonAlvaro]

In my case i get an exception.

This is the main window:

When in the UI I click the button "Get employee with contacts forms" and I set a break point catch of the method of the button, the exception is thrown:

And in the UI, in the textbox in the button of the window, I see the general message:

The odd thing that I get the error twice, although I run the query once.

[roji]

@ComptonAlvaro we still need a minimal, runnable code sample. There's nothing really we can learn from screenshots.

[ComptonAlvaro]

@roji The intention of the screenshots it is to tell how I get the exception with the source code that I have attached in the first post. In the zip, there is the source code and the .bak with the sql server that the sample uses.

[roji]

@ComptonAlvaro I haven't tried it, but @ajcvickers says above that your provided source doesn't cause the exception to be thrown... Can you please double check that from a clean slate, using your code indeed throws your exception?

[ComptonAlvaro]

@roji yes, I understand that he had problems to reproduce the issue with the code that I attached. It is why I add the screenshots, to explain how to reproduce it.

If he can't reproduce it with the sample that I attached, I don't know how to provide another sample that it could work for him because I don't konw the reason why the exception is not thrown when he runs it.

[ajcvickers]

@ComptonAlvaro I didn't run your code directly because I saw it had a U.I. I ripped out the model and query and ran it in a console app. I may not have run the right thing doing that--I will look again.

[ComptonAlvaro]

@ajcvickers I use a WPF application with UI because I thought it was more easy to run. But if you prefer a test proyect or console application, I can modify the sample.

Thanks so much.

[ajcvickers]

Console is always best.

[ComptonAlvaro]

I have replaced the first sample with a new one in which I added a new project, a console application, that show the message of the exception. This console application is set as startup project, but I still keep the original project in WPF.

[ajcvickers]

@ComptonAlvaro This code attempts to use the same foreign key for two different relationships:

paramEmployeefiguracion
    .HasMany<ContactForm>(x => x.Telephones)
    .WithOne()
    .HasForeignKey("IDEmployee");


paramEmployeefiguracion
    .HasMany<ContactForm>(x => x.Emails)
    .WithOne()
    .HasForeignKey("IDEmployee");

EF Core does not support this.