Why is SingleOrDefault not returning the whole entity graph

Question

I am trying to get the whole graph of an entity User. The User class is:

Updated

public class User
{
    [Key]
    public int Id { get; set; }

    public string email_address { get; set; }

    [Column("Password Hash")]
    public string PasswordHash { get; set; }

    public string custnmbr { get; set; }        

    public Customer Customer { get; set; }
}

My Customer Class:

public class Customer
{
    [Key]
    public string Custnmbr { get; set; }

    public string Custname { get; set; }        

    public ICollection<User> Users { get; set; }
}

I have this in my DbContext:

public class EntityContext : DbContext
{
    public EntityContext(DbContextOptions<EntityContext> options)
        : base(options)
    {
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Customer> Customers { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasOne<Customer>(u => u.Customer)
            .WithMany(c => c.Users)
            .HasForeignKey(u => u.custnmbr);
    }
}

My UserService:

public class UserService : IUserService
{
    private readonly EntityContext _context;
            
    public UserService(EntityContext context)
    {
        _context = context;
        _context.Users.Include("Customer").Load();
    }

    public IEnumerable<User> GetAll()
    {
        var users = _context.Users.Local.ToList();
        return users;
    }

    public User GetByUsername(string emailAddress)
    {
        var user = _context.Users.Local.SingleOrDefault(x => x.email_address == emailAddress);
        return user;
    }        
}

My UserControler:

[Route("api/Users")]
[ApiController]
public class UsersController : ControllerBase
{
    private readonly IUserService _userService;

    public UsersController(IUserService userService)
    {
        _userService = userService;
    }

    [HttpGet("GetAll")]
    public IActionResult GetAll()
    {
        var users = _userService.GetAll();
        return Ok(users);
    }

    [HttpGet("GetUser")]
    public IActionResult GetUser(string emailAddress)
    {
        var user = _userService.GetByUsername(emailAddress);
        return Ok(user);
    }
}

And GetAll() returns the whole graph for all Users. But GetUser() returns a single User with property Customer null.

How can I get the whole User graph including the property Customer?

Any help would be appreciated.

Microsoft Visual Studio Community 2019 - Version 16.10.3

Microsoft.AspNetCore (5.0.0)

Microsoft.EntityFrameworkCore (5.0.7)

Isn't it because the `NotMapped` attribute is explicitly set on `Customer` property? — Crono, Jul 01 '21 at 12:15
@Crono, I removed `NotMapped` and `Customer` property is still null. — Randy, Jul 01 '21 at 12:21
You need to create a new migration and apply it after removing `[NotMapped]` attribute. — LazZiya, Jul 01 '21 at 12:43
Please update your question to share a [mcve] _without_ `NotMapped`. — mjwills, Jul 01 '21 at 13:30
Most likely "Load" ignores "Include". Idea of Load is to "load" entities of one type, hence you you should Load both Customers and Users separately and only then Include when you doing actual query — Nikita Chayka, Jul 01 '21 at 14:16
@NikitaChayka I added `_context.Customers.Load()` to the UserService Constructor. I even switched to `var user = _context.Users.Include("Customer").SingleOrDefault(x => x.email_address == emailAddress)` in GetUser() and the `Customer` property is still null. — Randy, Jul 01 '21 at 15:25
@Randy, so then are you sure that you have related record in database then? — Nikita Chayka, Jul 01 '21 at 15:46
@NikitaChayka Yes, the `Customer` property shows when I run GetAll() in the exact same UserService. — Randy, Jul 01 '21 at 16:04
I have tried to use `var user = _context.Users.Where(x => x.email_address == emailAddress).Include(x => x.Customer).FirstOrDefault()` in GetUser() like on [entityframeworktutorial](https://www.entityframeworktutorial.net/efcore/querying-in-ef-core.aspx) Eager Loading. — Randy, Jul 01 '21 at 16:17
BTW it's questionable why you always load all users. It'll happen each time the controller is created, i.e. for each request. And `GetUser` only needs to load one user. You may as well execute the db query when it's actually requested. — Gert Arnold, Jul 02 '21 at 12:24
@GertArnold I have used `var user = _context.Users.Where(x => x.email_address == emailAddress).Include(x => x.Customer).FirstOrDefault()` like I said in my previous comment. — Randy, Jul 02 '21 at 15:18
Yes, but I consider the question as reflecting the current status of what you're asking. Comments are many and inconclusive. — Gert Arnold, Jul 02 '21 at 15:21

score 0 · Accepted Answer · answered Jul 02 '21 at 16:49

First, I want to apologize to the StackOverflow community. This was my dumb mistake.

Using this How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?

I found out my own mistake. I needed to add this:

modelBuilder.Entity<Customer>()
            .Property(e => e.Custnmbr)
            .HasConversion(new ValueConverter<string, string>(v => v.TrimEnd(), v => v.TrimEnd()));

to the OnModelCreating() of my EntityContext. I had trailing spaces in some of the Custnmbr properties.

Problem solved.

Why is SingleOrDefault not returning the whole entity graph

1 Answers1