EF CORE Data Annotations and JOINS

Question

I'm trying to query an old database (which I cant change).

Each table has a RecordId which is a unique id. The tables are related by their Account Number fields.

class tblOneToOne
{
[ForeignKey("Accountno")]
public string Accountno {get; set;}
public string Recordid {get; set; }
public string fields {get; set; }
}

class tblOneToMany
{
[ForeignKey("Accountno")]
public string Accountno {get; set;}
public string Recordid {get; set; }
public string Ref {get; set; }
}

class tblContact
{
[ForeignKey("Accountno")]
public string Accountno {get; set;}
[key]
public string Recordid {get; set; }
public string Company {get; set; }
public string Contact {get; set; }

public tblOneToOne tblOneToOne {get; set; }

public tblOneToMany tblOneToMany {get; set; }
}

I've tried using Fluent Api to specify the foreign key

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<tblContact>().ToTable("Contact")
              .HasMany(c => c.tblOneToMany)
              .WithOne(cs => cs.tblContact)
              .HasForeignKey(c => c.Accountno);

}

I then query a record that does have multiple tblOneToMany records, but no results are returned.

var c = _context.tblContact
                .Include("tblOneToMany")
                .FirstOrDefaultAsync(c => c.Accountno == accountno);

  return c;

What should I be doing differently to get the correct results returned?

Any help would be appreciated.

Answer 1

I finally managed to resolve my issue using Fluent API.

I removed the Foreignkey data annotations from my classes and used the following Fluent Api to specify the ForeignKeys on the tables.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<tblContact>(entity =>
  {
    entity.HasKey(c => c.Accountno);

    entity.HasOne(c => c.tblOneToOne)
          .WithOne(c => c.tblContact)
          .HasForeignKey<tblOneToOne>(c => c.Accountno);

    entity.HasMany(c => c.tblOneToMany)
          .WithOne(c => c.tblContact)
          .HasForeignKey(cs => cs.Accountno);

  });
}