EF Core retrieves entity with null property from foreign-key to same model

user_4267590 0
Viewed 933

When I query the database to retrieve a record, and the model includes a property which is a foreign key to the same type, that property returns null. What did I do wrong?

I have this model class:

public class Customer
{
    public int ID { get; set; }
    
    [ForeignKey("ParentID")]
    public virtual Customer Parent { get; set; }

    public List<Contact> Contacts { get; set; }

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

with the underlying table:

CREATE TABLE [dbo].[Customer] 
(
    [ID]       INT IDENTITY (1, 1) NOT NULL,
    [ParentID] INT NULL,
    [Name]     NVARCHAR(MAX) NOT NULL,

    CONSTRAINT [PK_Customer] 
        PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Customer_Customer_ParentID] 
        FOREIGN KEY ([ParentID]) REFERENCES [dbo].[Customer] ([ID])
)

which was generated using EF Core code first migrations.

When the CustomersController attempts to:

public async Task<IActionResult> Details(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    // returns the correct customer with null Parent property which causes...
    var customer = await _context.Customer
                                 .FirstOrDefaultAsync(m => m.ID == id); 

    if (customer == null)
    {
        return NotFound();
    }

    var vm = new CustomerViewModel(customer); // ... NRE to be thrown here

    return View(vm);
}

Even though this data exists in the table:

ID  ParentID  Name
--------------------------
5   1         ParentCo
7   5         ChildCo
asp.net-core-3.1 entity-framework-core asp.net-mvc
1 Answers

try this

var result = await _context.Customer.Include(x => x.Parent).FirstOrDefaultAsync(x => x.Id == id);

but if that does not work

var customer = await _context.Customer.FirstOrDefaultAsync(
        .FirstOrDefaultAsync(m => m.ID == id); );

if (customer != null)
{
   var parent = await _context.Customer.FirstOrDefaultAsync(x => x.ParentId == customer.Id);

   if (parent != null)
   {
       customer.Parent = parent;
   }
}
else
{
    return NotFound();
}
user_21222170
Related