Foreign Keys, Eager Loading, and DTOs (.NET Core 3.1 / Entity Framework Core)

.net asp.net asp.net-core entity-framework entity-framework-core

Question

Context

I am building a simple .NET Core API and am having some trouble returning the correct data from my Controllers. I foresee having to use DTOs to manage what data is being sent to and from the API, but I am not convinced that I have my EF Core relationships configured properly. What's throwing me for a loop is that the children objects in my models are returning null - I will explain further.

I have two domain models, Player and Match. Each Match has four Players, which I believe will require four foreign keys to be created to the same table. The SQL Database that EF Core generates looks just how I envisioned it looking - I have included a screenshot too for more context. I can create Player objects just fine using the API POST method. However, when I create a Match object, using Player Guids (as seen below), querying the newly created Match object from the database doesn't return the Player object in JSON the way I hoped it would; the MatchController returns the Player Guids, but no player information.

Ultimately, I'd like to consistently display Player data for each of the four players in a Match, but I'm not sure what changes to my models or Fluent API would be needed for me to achieve this. I do plan to using AutoMapper to map the domain model objects to DTO model objects in the future, but this current predicament seems like something I should iron out first. I'd be more than happy to provide more information if there's anything else that would allow for better assistance! Any and all help would be sincerely and greatly appreciated! Thanks in advance!

Note: I don't care about having a collection of Matches in my Player model - I only have that included because I thought that was necessary to build the Foreign Key constraints in Entity Framework Core

Models:

Player.cs (Domain model)

public class Player
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public string Name { get; set; }

    public double Rating { get; set; }

    public DateTime Created { get; set; }
    public DateTime Updated { get; set; }

    public ICollection<Match> MatchesOne { get; set; }
    public ICollection<Match> MatchesTwo { get; set; }
    public ICollection<Match> MatchesThree { get; set; }
    public ICollection<Match> MatchesFour { get; set; }
}

Match.cs (Domain model)

public class Match
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public int TeamOneScore { get; set; }
    [Required]
    public int TeamTwoScore { get; set; }

    public DateTime Created { get; set; }
    public DateTime Updated { get; set; }

    public Guid PlayerOneId { get; set; }
    public Guid PlayerTwoId { get; set; }
    public Guid PlayerThreeId { get; set; }
    public Guid PlayerFourId { get; set; }

    public Player PlayerOne { get; set; }
    public Player PlayerTwo { get; set; }
    public Player PlayerThree { get; set; }
    public Player PlayerFour { get; set; }
}

ApplicationDbContext.cs (Fluent API)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Player>()
        .Property(p => p.Rating)
        .HasDefaultValue(1000);

    modelBuilder.Entity<Player>()
        .Property(p => p.Created)
        .HasDefaultValueSql("GETDATE()");

    modelBuilder.Entity<Player>()
        .Property(p => p.Updated)
        .HasDefaultValueSql("GETDATE()");

    modelBuilder.Entity<Match>()
        .Property(m => m.Created)
        .HasDefaultValueSql("GETDATE()");

    modelBuilder.Entity<Match>()
        .Property(m => m.Updated)
        .HasDefaultValueSql("GETDATE()");

    modelBuilder.Entity<Player>()
        .HasMany(p => p.MatchesOne)
        .WithOne(m => m.PlayerOne)
        .HasForeignKey(m => m.PlayerOneId)
        .OnDelete(DeleteBehavior.NoAction);

    modelBuilder.Entity<Player>()
        .HasMany(p => p.MatchesTwo)
        .WithOne(m => m.PlayerTwo)
        .HasForeignKey(m => m.PlayerTwoId)
        .OnDelete(DeleteBehavior.NoAction);

    modelBuilder.Entity<Player>()
        .HasMany(p => p.MatchesThree)
        .WithOne(m => m.PlayerThree)
        .HasForeignKey(m => m.PlayerThreeId)
        .OnDelete(DeleteBehavior.NoAction);

    modelBuilder.Entity<Player>()
        .HasMany(p => p.MatchesFour)
        .WithOne(m => m.PlayerFour)
        .HasForeignKey(m => m.PlayerFourId)
        .OnDelete(DeleteBehavior.NoAction);
}


Controllers:

PlayerController.cs

[HttpGet]
public async Task<ActionResult<IEnumerable<Player>>> GetPlayers()
{
    return await _context.Players.ToListAsync();
}

[HttpPost]
public async Task<ActionResult<Player>> PostPlayer(Player player)
{
    _context.Players.Add(player);
    await _context.SaveChangesAsync();

    return CreatedAtAction(nameof(GetPlayer), new { id = player.Id }, player);
}

MatchController.cs

[HttpGet]
public async Task<ActionResult<IEnumerable<Match>>> GetMatches()
{
    return await _context.Matches.ToListAsync();
}

[HttpPost]
public async Task<ActionResult<Match>> PostMatch(Match match)
{
    _context.Matches.Add(match);
    await _context.SaveChangesAsync();

    return CreatedAtAction(nameof(GetMatch), new { id = match.Id }, match);
}


HTTP Requests

HTTP POST: PostPlayer (/api/Player) Request:

{
    "Name":"Mike Blart"
}

Response:

{
    "id": "d3c022a2-d347-4a9a-d3ec-08d7b5480646",
    "name": "Mike Blart",
    "rating": 1000,
    "created": "2020-02-19T14:32:48.8033333",
    "updated": "2020-02-19T14:32:48.8033333",
    "matchesOne": null,
    "matchesTwo": null,
    "matchesThree": null,
    "matchesFour": null
}

HTTP POST: PostMatch (/api/Match) Request:

{
    "TeamOneScore":21,
    "TeamTwoScore":13,
    "PlayerOneId":"0589867f-590b-4344-d3e9-08d7b5480646",
    "PlayerTwoId":"0f45247b-1fdb-404a-d3ea-08d7b5480646",
    "PlayerThreeId":"f8b4e13d-0dd0-4ef5-d3eb-08d7b5480646",
    "PlayerFourId":"d3c022a2-d347-4a9a-d3ec-08d7b5480646"
}

Response:

{
    "id": "dfdc2f23-0786-40df-8aa7-08d7b54fd4a1",
    "teamOneScore": 21,
    "teamTwoScore": 13,
    "created": "2020-02-19T15:24:38.7233333",
    "updated": "2020-02-19T15:24:38.7233333",
    "playerOneId": "0589867f-590b-4344-d3e9-08d7b5480646",
    "playerTwoId": "0f45247b-1fdb-404a-d3ea-08d7b5480646",
    "playerThreeId": "f8b4e13d-0dd0-4ef5-d3eb-08d7b5480646",
    "playerFourId": "d3c022a2-d347-4a9a-d3ec-08d7b5480646",
    "playerOne": null,
    "playerTwo": null,
    "playerThree": null,
    "playerFour": null
}


HTTP GET: GetPlayers (/api/Player)

[
    {
        "id": "0589867f-590b-4344-d3e9-08d7b5480646",
        "name": "Merwin Dedrick",
        "rating": 1000,
        "created": "2020-02-19T14:28:44.7966667",
        "updated": "2020-02-19T14:28:44.7966667",
        "matchesOne": null,
        "matchesTwo": null,
        "matchesThree": null,
        "matchesFour": null
    },
    {
        "id": "0f45247b-1fdb-404a-d3ea-08d7b5480646",
        "name": "Omar Rupaz",
        "rating": 1000,
        "created": "2020-02-19T14:30:04.4933333",
        "updated": "2020-02-19T14:30:04.4933333",
        "matchesOne": null,
        "matchesTwo": null,
        "matchesThree": null,
        "matchesFour": null
    },
    {
        "id": "f8b4e13d-0dd0-4ef5-d3eb-08d7b5480646",
        "name": "Aaron Randolph",
        "rating": 1000,
        "created": "2020-02-19T14:32:38.7066667",
        "updated": "2020-02-19T14:32:38.7066667",
        "matchesOne": null,
        "matchesTwo": null,
        "matchesThree": null,
        "matchesFour": null
    },
    {
        "id": "d3c022a2-d347-4a9a-d3ec-08d7b5480646",
        "name": "Mike Blart",
        "rating": 1000,
        "created": "2020-02-19T14:32:48.8033333",
        "updated": "2020-02-19T14:32:48.8033333",
        "matchesOne": null,
        "matchesTwo": null,
        "matchesThree": null,
        "matchesFour": null
    }
]

HTTP GET: GetMatches (/api/Match)

[
    {
        "id": "ce06237b-a137-47bc-e0b7-08d7b5484c68",
        "teamOneScore": 21,
        "teamTwoScore": 13,
        "created": "2020-02-19T14:33:24.1266667",
        "updated": "2020-02-19T14:33:24.1266667",
        "playerOneId": "0589867f-590b-4344-d3e9-08d7b5480646",
        "playerTwoId": "0f45247b-1fdb-404a-d3ea-08d7b5480646",
        "playerThreeId": "f8b4e13d-0dd0-4ef5-d3eb-08d7b5480646",
        "playerFourId": "d3c022a2-d347-4a9a-d3ec-08d7b5480646",
        "playerOne": null,
        "playerTwo": null,
        "playerThree": null,
        "playerFour": null
    }
]
1
0
2/19/2020 3:55:54 PM

Accepted Answer

For you want to show the four players' info when you display one match,you could use Include:

[HttpGet]
public async Task<ActionResult<IEnumerable<Match>>> GetMatches()
{
    return await _context.Matches.ToListAsync();
}

// GET: api/Matches/5
[HttpGet("{id}")]
public async Task<ActionResult<Match>> GetMatch(Guid id)
{
    var match = await _context.Matches
                    .Include(m => m.PlayerOne)
                    .Include(m => m.PlayerTwo)
                    .Include(m => m.PlayerThree)
                    .Include(m => m.PlayerFour)
                    .Where(m => m.Id == id)
                    .FirstOrDefaultAsync();       
    return match;
}

[HttpPost]
public async Task<ActionResult<Match>> PostMatch(Match match)
{
    _context.Matches.Add(match);
    await _context.SaveChangesAsync();

    //change CreatedAtAction to CreatedAtAction...
    return RedirectToAction("GetMatch", new { id = match.Id }); 
}

Result:

{
    "id": "e35bff4e-1d3a-40db-6da7-08d7b5c7defc",
    "teamOneScore": 21,
    "teamTwoScore": 13,
    "created": "2020-02-20T13:43:54.1066667",
    "updated": "2020-02-20T13:43:54.1066667",
    "playerOneId": "6a35cfd0-b55f-4151-a75f-08d7b5c79d60",
    "playerTwoId": "caa71d4a-1fe5-488a-a760-08d7b5c79d60",
    "playerThreeId": "4c057f8c-9a05-4e33-a761-08d7b5c79d60",
    "playerFourId": "4dd619f2-1680-4bb6-a762-08d7b5c79d60",
    "playerOne": {
        "id": "6a35cfd0-b55f-4151-a75f-08d7b5c79d60",
        "name": "Mike Blart",
        "rating": 1000.0,
        "created": "2020-02-20T13:42:05.94",
        "updated": "2020-02-20T13:42:05.94",
        "matchesOne": [],
        "matchesTwo": null,
        "matchesThree": null,
        "matchesFour": null
    },
    "playerTwo": {
        "id": "caa71d4a-1fe5-488a-a760-08d7b5c79d60",
        "name": "aaa",
        "rating": 1000.0,
        "created": "2020-02-20T13:42:24.3866667",
        "updated": "2020-02-20T13:42:24.3866667",
        "matchesOne": null,
        "matchesTwo": [],
        "matchesThree": null,
        "matchesFour": null
    },
    "playerThree": {
        "id": "4c057f8c-9a05-4e33-a761-08d7b5c79d60",
        "name": "vvv",
        "rating": 1000.0,
        "created": "2020-02-20T13:42:28.83",
        "updated": "2020-02-20T13:42:28.83",
        "matchesOne": null,
        "matchesTwo": null,
        "matchesThree": [],
        "matchesFour": null
    },
    "playerFour": {
        "id": "4dd619f2-1680-4bb6-a762-08d7b5c79d60",
        "name": "ccc",
        "rating": 1000.0,
        "created": "2020-02-20T13:42:32.86",
        "updated": "2020-02-20T13:42:32.86",
        "matchesOne": null,
        "matchesTwo": null,
        "matchesThree": null,
        "matchesFour": []
    }
}

Note:be sure to install Microsoft.AspNetCore.Mvc.NewtonsoftJson then use the following code:

services.AddControllers().AddNewtonsoftJson(x =>
              x.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore);

Reference: Loading Related Data

1
2/20/2020 6:28:37 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