EF Core 6.0 navigation property not loading-CodePudding

I'm trying to build a simple ASP.NET Core 6-based web API. I started by following Microsoft's minimal web API tutorial https://docs.microsoft.com/en-us/aspnet/core/tutorials/min-web-api?view=aspnetcore-6.0, which works. Then I added my own data model with a navigation property, using https://docs.microsoft.com/en-us/ef/core/modeling/relationships as a guide, but when I execute a query the property is null.

Here's my model:

public class Author {
  public int Id { get; set; }
  public string Name { get; set; }
  public List<Book> Books { get; set; }
}

public class Book {
  public int Id { get; set; }
  public string Title { get; set; }
  public Author Author { get; set; }
}

My DbContext is as follows:

public class LibraryDb : DbContext {
  public LibraryDb(DbContextOptions<LibraryDb> options) : base(options) {}
  public DbSet<Author> Authors => Set<Author>();
  public DbSet<Book> Books => Set<Book>();
}

And my application:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSqlite<LibraryDb>("Data Source=Library.db");
var app = builder.Build();
app.MapGet("/authors", async (LibraryDb db) => await db.Authors.ToListAsync());
app.MapGet("/books", async (LibraryDb db) => await db.Books.ToListAsync());
app.Run();

After running the "ef migrations add" and "ef database update" commands, the database structure is like so:

CREATE TABLE "Authors" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Authors" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL
)
CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "AuthorId" INTEGER NOT NULL,
    "Title" TEXT NOT NULL,
    CONSTRAINT "FK_Books_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id") ON DELETE CASCADE
)
CREATE INDEX "IX_Books_AuthorId" ON "Books" ("AuthorId")

I've populated the database with a couple of rows (each book has an AuthorId assigned) but here are the results when calling my "/authors" API:

[{"id":1,"name":"Clive Barker","books":null},{"id":2,"name":"Stephen King","books":null}]

and for "/books":

[{"id":1,"title":"Weaveworld","author":null},{"id":2,"title":"The Stand","author":null}]

The "books" and "author" fields are null. The generated SQL statements don't seem to be doing any joins - what am I doing wrong? If I change the routing code to b.Authors.Include(x => x.Books).ToListAsync() as I've seen suggested elsewhere, I get a JsonException that an object cycle was detected.

CodePudding user response：

The Include is the right approach (at least one of - see loading related data documentation). So add back your include:

b.Authors
    .Include(x => x.Books)
    .ToListAsync()

The next issue is caused by EF automatically filling in the reference properties (it is called relationship fix-up) which will introduce cycle which serializer by default does not handle. You can change serializer behaviour with next setting:

builder.Services.Configure<JsonOptions>(options =>
{
    options.SerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
});

Personally I usually prefer to return from endpoints some DTOs not the entities itself, which can allow you more granular control over the return, so mapping your query results into some DTOs also can fix the issue.