CODEWITHSUNDEEP
c#.netentity-framework-core
Mustafizur Rohman
Mustafizur Rohman

Reputation: 13

Wrong Query Generated with EF Core 3.0

I am trying to use a simple where clause to retrieve data from a SQL Server. However the generated query is incorrect.This query worked perfectly with EF Core 2.2 but with EF Core 3 it throws an exception.

    public async Task<List<CharacterReplacements>> GetReplacementsAsync(int? replacementSetId)
    {
        var replacementQuery = _context.CharacterReplacements.AsQueryable();

        if (replacementSetId.HasValue)
        {
            replacementQuery = replacementQuery.Where(r => r.CharacterReplacementSetID == replacementSetId.Value); // .AsQueryable();
        }

        var replacementList = await replacementQuery.ToListAsync();

        return replacementList;
    }

[Serializable]
[Table("CharacterReplacementSets", Schema = "SYSTEM")]
public class CharacterReplacementSets
{
    [NavigationPropertyKey]
    [Key]
    public int CharacterReplacementSetID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public ICollection<CharacterReplacements> CharacterReplacements { get; set; }
    public ICollection<FormatField> FormatFields { get; set; }
    public string CreatedBy { get; set; }
    public DateTime CreatedOn { get; set; }
    public string UpdatedBy { get; set; }
    public DateTime? UpdatedOn { get; set; }
    public string DefaultEncoding { get; set; } // Default character set
    public string DefaultCharacter { get; set; }
    public CharacterReplacementSets()
    {
        CharacterReplacements = new List<CharacterReplacements>();
    }
}

[Serializable]
[Table("CharacterReplacements", Schema = "SYSTEM")]
public class CharacterReplacements
{
    [NavigationPropertyKey]
    [Key]
    public int CharacterReplacementID { get; set; }
    public char OriginalCharacter { get; set; }
    public string ReplacementCharacter { get; set; }
    public string CreatedBy { get; set; }
    public DateTime CreatedOn { get; set; }
    public string UpdatedBy { get; set; }
    public DateTime? UpdatedOn { get; set; }
    [ForeignKey("CharacterReplacementSets")]
    public int CharacterReplacementSetID { get; set; }
}

Expected result- Retrieve all CharacterReplacements where the replacementSetId equals the provided replacementSetId.

Actual result- Microsoft.Data.SqlClient.SqlException: 'Invalid column name 'CharacterReplacementSetsCharacterReplacementSetID'.

Could someone kindly help me out with this?

Upvotes: 1

Views: 1117

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

The problem is not the specific query, but the model mapping.

First, the ForeignKey attribute here

[ForeignKey("CharacterReplacementSets")]
public int CharacterReplacementSetID { get; set; }

has no effect. When applied to navigation property, it's supposed to specify the FK property name. And when applied on FK property as here, it's supposed to specify the navigation property name. CharacterReplacements has no navigation property called CharacterReplacementSets, so the attribute is simply ignored. It would be good if EF Core generates runtime error to indicate a mapping problem, but it doesn't.

The attribute has been ignored in EF Core 1.x / 2.x as well. However it worked because the name of the property CharacterReplacementSetID matches the name of the PK of CharacterReplacementSets. This is no more true for EF Core 3.0 due to the following breaking change - The foreign key property convention no longer matches same name as the principal property.

So remove the incorrect and misleading ForeignKey attribute, and configure the FK property by either HasForeignKey fluent API (my preferred):

modelBuilder.Entity<CharacterReplacementSets>()
    .HasMany(e => e.CharacterReplacements)
    .WithOne()
    .HasForeignKey(e => e.CharacterReplacementSetID);

or with ForegnKey attribute on navigation property (or inverse navigation property when there is no navigation property as here):

[ForeignKey("CharacterReplacementSetID")]
public ICollection<CharacterReplacements> CharacterReplacements { get; set; }

Note that you might have similar problem with FormatField and other entities using similar named FKs w/o navigation properties.

Another way to avoid this issue is to use singular entity class names like CharacterReplacementSet, CharacterReplacement etc. because the [entity name] + ID still matches EF Core conventions. And in general singular class names are better/preferable, even just for readability.

Upvotes: 3

Related Questions