invalid FK field name

[mastras23]

for entities

 [Index("Name", IsUnique = true)]
    public class Revision
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int RevisionId { get; set; }
        public virtual ICollection<ScrapRegister> ScrapRegisters { get; set; } = [];
        public virtual ICollection<Component> Components { get; set; } = [];
        public virtual ICollection<AssemblyComponent> AssemblyComponents { get; set; } = [];
   ...}

    public class ScrapRegister
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [ForeignKey("Revision")]
        public int RevisionId { get; set; } = 1;
        public virtual Revision Revision { get; set; }
  ...}

    [PrimaryKey(nameof(ComponentId), nameof(RevisionId))]
    public class Component
    {
        [StringLength(32)]
        [MaxLength(32)]
        [Unicode(false)]
        public string ComponentId { get; set; } 

        [ForeignKey("Revision")]
        public int RevisionId { get; set; } = 1;
        public virtual Revision Revision { get; set; }
     ...}

this query

                var qry = _context
                    .Revisions
                    .Select(r => new RevisionDTO
                    {
                        RevisionId = r.RevisionId,
                        ScrapsInRevision = r.ScrapRegisters.**Count**(),
                        AssemblyComponentsInRevision = r.AssemblyComponents.Count(),
                        ComponentsInRevision = r.Components.Count(),
                        ...
                    });
                
                List<RevisionDTO> r = await qry
                    .OrderBy(r => r.RevisionId)
                    .ToListAsync(ct)
                    .ConfigureAwait(false);

generates reference to no existent field RevisionId1

SELECT [r].[RevisionId], (
    SELECT COUNT(*)
    FROM [Scrap].[ScrapRegisters] AS [s]
    WHERE [r].[RevisionId] = [s].[**RevisionId1**]) AS [ScrapsInRevision], (
    SELECT COUNT(*)
    FROM [Scrap].[AssemblyComponents] AS [a]
    WHERE [r].[RevisionId] = [a].[RevisionId]) AS [AssemblyComponentsInRevision], (
    SELECT COUNT(*)
    FROM [Scrap].[Components] AS [c]
    WHERE [r].[RevisionId] = [c].[RevisionId]) AS [ComponentsInRevision], [r].[Closed], [r].[Created], [r].[CreatorId], [r].[Description], [r].[Disabled], [r].[Name], 0 AS [RowStatus]
FROM [Scrap].[Revisions] AS [r]

	<PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.2" />
	<PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.10" />
	<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.10">
	<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.10" />

[maumar]

I'm unable to reproduce this. Here is the full listing I used:

using var ctx = new MyContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();


var qry = ctx
    .Revisions
    .Select(r => new RevisionDTO
    {
        RevisionId = r.RevisionId,
        ScrapsInRevision = r.ScrapRegisters.Count(),
        AssemblyComponentsInRevision = r.AssemblyComponents.Count(),
        ComponentsInRevision = r.Components.Count(),
    });

List<RevisionDTO> r = await qry
    .OrderBy(r => r.RevisionId)
    .ToListAsync()
    .ConfigureAwait(false);

public class MyContext : DbContext
{
    public DbSet<Revision> Revisions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true")
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging();
    }
}


public class RevisionDTO
{
    public int RevisionId { get; set; }

    public int ScrapsInRevision { get; set; }
    public int AssemblyComponentsInRevision { get; set; }
    public int ComponentsInRevision { get; set; }
}

[Index("Name", IsUnique = true)]
public class Revision
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int RevisionId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<ScrapRegister> ScrapRegisters { get; set; } = [];
    public virtual ICollection<Component> Components { get; set; } = [];
    public virtual ICollection<AssemblyComponent> AssemblyComponents { get; set; } = [];
}

public class ScrapRegister
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [ForeignKey("Revision")]
    public int RevisionId { get; set; } = 1;
    public virtual Revision Revision { get; set; }
}

[PrimaryKey(nameof(ComponentId), nameof(RevisionId))]
public class Component
{
    [StringLength(32)]
    [MaxLength(32)]
    [Unicode(false)]
    public string ComponentId { get; set; }

    [ForeignKey("Revision")]
    public int RevisionId { get; set; } = 1;
    public virtual Revision Revision { get; set; }
}

public class AssemblyComponent
{
    public int Id { get; set; }
}

This generates the following sql:

SELECT [r].[RevisionId], (
    SELECT COUNT(*)
    FROM [ScrapRegister] AS [s]
    WHERE [r].[RevisionId] = [s].[RevisionId]) AS [ScrapsInRevision], (
    SELECT COUNT(*)
    FROM [AssemblyComponent] AS [a]
    WHERE [r].[RevisionId] = [a].[RevisionId]) AS [AssemblyComponentsInRevision], (
    SELECT COUNT(*)
    FROM [Component] AS [c]
    WHERE [r].[RevisionId] = [c].[RevisionId]) AS [ComponentsInRevision]
FROM [Revisions] AS [r]
ORDER BY [r].[RevisionId]

@mastras23 please modify the code above so that it reproduces the issue.

[mastras23]

Hi, Ok, I will , I'll just say that in my code just adding this single navigation only public class Revision { public virtual ICollection<ScrapRegister> ScrapRegisters { get; set; } = []; caused the problem of additional FK The foreign key property 'ScrapRegister.RevisionId1' was created in shadow state because a conflicting property with the simple name 'RevisionId' exists in the entity type, but is either not mapped, is already used for another relationship, or is incompatible with the associated primary key type. in relations with other classes this problem did not occur. R. From: Maurycy Markowski ***@***.***> Sent: Monday, November 4, 2024 10:29 AM To: dotnet/efcore ***@***.***> Cc: mastras23 ***@***.***>; Mention ***@***.***> Subject: Re: [dotnet/efcore] invalid FK field name (Issue #35034) I'm unable to reproduce this. Here is the full listing I used: using var ctx = new MyContext(); await ctx.Database.EnsureDeletedAsync(); await ctx.Database.EnsureCreatedAsync(); var qry = ctx .Revisions .Select(r => new RevisionDTO { RevisionId = r.RevisionId, ScrapsInRevision = r.ScrapRegisters.Count(), AssemblyComponentsInRevision = r.AssemblyComponents.Count(), ComponentsInRevision = r.Components.Count(), }); List<RevisionDTO> r = await qry .OrderBy(r => r.RevisionId) .ToListAsync() .ConfigureAwait(false); public class MyContext : DbContext { public DbSet<Revision> Revisions { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true") .LogTo(Console.WriteLine, LogLevel.Information) .EnableSensitiveDataLogging(); } } public class RevisionDTO { public int RevisionId { get; set; } public int ScrapsInRevision { get; set; } public int AssemblyComponentsInRevision { get; set; } public int ComponentsInRevision { get; set; } } [Index("Name", IsUnique = true)] public class Revision { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int RevisionId { get; set; } public string Name { get; set; } public virtual ICollection<ScrapRegister> ScrapRegisters { get; set; } = []; public virtual ICollection<Component> Components { get; set; } = []; public virtual ICollection<AssemblyComponent> AssemblyComponents { get; set; } = []; } public class ScrapRegister { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } [ForeignKey("Revision")] public int RevisionId { get; set; } = 1; public virtual Revision Revision { get; set; } } [PrimaryKey(nameof(ComponentId), nameof(RevisionId))] public class Component { [StringLength(32)] [MaxLength(32)] [Unicode(false)] public string ComponentId { get; set; } [ForeignKey("Revision")] public int RevisionId { get; set; } = 1; public virtual Revision Revision { get; set; } } public class AssemblyComponent { public int Id { get; set; } } This generates the following sql: SELECT [r].[RevisionId], ( SELECT COUNT(*) FROM [ScrapRegister] AS [s] WHERE [r].[RevisionId] = [s].[RevisionId]) AS [ScrapsInRevision], ( SELECT COUNT(*) FROM [AssemblyComponent] AS [a] WHERE [r].[RevisionId] = [a].[RevisionId]) AS [AssemblyComponentsInRevision], ( SELECT COUNT(*) FROM [Component] AS [c] WHERE [r].[RevisionId] = [c].[RevisionId]) AS [ComponentsInRevision] FROM [Revisions] AS [r] ORDER BY [r].[RevisionId] @mastras23 <https://github.com/mastras23> please modify the code above so that it reproduces the issue. — Reply to this email directly, view it on GitHub <#35034 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AVIABY6BBS6EOALCSB2W3YTZ64V4HAVCNFSM6AAAAABRDBGMVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJUGIYDANRUGY> . You are receiving this because you were mentioned. <https://github.com/notifications/beacon/AVIABY4MMFTTA26HGODVYV3Z64V4HA5CNFSM6AAAAABRDBGMVGWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTUSJAQUM.gif> Message ID: ***@***.*** ***@***.***> >

[roji]

@mastras23 please clean up your comment and code just above - we need to be able to read what you're posting. Refer to this guide for writing markdown on github.

[mastras23]

I don't know what factors cause it to show up in my project but not in your code, I used a workaround, maybe someone else will have a more clear situation,