Entity Framework core: Relation with two not primary keys

.net-core c# entity-framework-core

Question

I'm working on an old project where I cant make changes to the database, I'm creating a new API using .net core 3.

I have two tables page and Note that have PageId and BookId in common, so that each Note has one Page with the same BookId and PageId

public class Note
{
   public int Id { get; set; }
   public int BookId { get; set; }
   public int PageId { get; set; }
   public string Text { get; set; }
}

public class Page
{
   [Key]
   public int MainId { get; set; } // this is the primary key
   public int BookId { get; set; }
   public int PageId { get; set; }
   public string Content { get; set; }
}

PageId is not the primary key for Page and has duplicate values. I want to get the page by the note id, I'm doing it like this:

 [HttpGet("{id}")]
 public async Task<ActionResult> GetPageByNoteId(int noteId)
 {
        var note = await db.Notes.FindAsync(id);
        var page = await db.Pages.Where(x => x.BookId == note.BookId && x.PageId == note.PageId)
                                 .FirstOrDefaultAsync();

        return Ok(new {note,page});
 }

Is there a way to do this with one database hit?


Update:

The primary key for page is MainId but it should be PageId and BookId together but I cant make changes to the database, If I can make changes to the database I would make the following to the Page table:

  • remove the MainId column

  • Make PageId and BookId the primary key

then I can do this:

      modelBuilder.Entity<Note>(entity =>
       {
                   entity.HasOne(Note => Note.Page)
                         .WithOne(page => page.Note)
                         .HasForeignKey<Note>(note => new { note.PageId, note.BookId});

       });
1
0
4/17/2020 9:45:08 AM

Popular Answer

Maybe this is what you are looking for.

  public class Note
    {
        public int Id { get; set; }
        public int BookId { get; set; }
        public int PageId { get; set; }
        public string Text { get; set; }
        public Page Page { get; set; }
    }

    public class Page
    {
        public int Id { get; set; }
        public int BookId { get; set; }
        public int PageId { get; set; }
        public string Content { get; set; }
        public Note Note { get; set; }
    }

And in your DB Context class.

  protected override void OnModelCreating(ModelBuilder builder)
            {
                base.OnModelCreating(builder);
...
         builder.Entity<Note>()
            .HasOne(r => r.Page)
            .WithOne(x => x.Note);

Now you can do like below.

 var page = (await _context.Notes.Include(x => x.Page)
                                          .FirstOrDefaultAsync(x => x.Id == id)).Page;

This solution is for when you have one to one relations from note to the page. if it is one to may you can change the Pages property to collection.

0
4/17/2020 1:35:11 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow