Everything but LeadState is loading without any problems. "LeadState" and "JobBoard" are lookup tables, just mapping an Id to a Name value. I set them both up in the EF entity the same way, like this:
[ForeignKey("LeadState")]
public int LeadStateId { get; set; }
public virtual LeadState LeadState { get; set; }
But JobBoard loads as expected, and LeadState throws an error saying, "Sequence has no elements". I checked out the value of "LeadStateId" when the view model query runs, and it's valid. I'm not sure why this field is giving me trouble. Just to make sure
it wasn't something small such as having 2 "Include" calls at the same level, I tried running the query with only the LeadState Include, and not the JobBoard. Still threw the exception. To get around it, I do this, which works just fine:
The other problem I'm having is updating the model on the form POST. Using the Include calls loads the related entities, and those end up as part of the package when performing the update. Definitely not what I want. I only want to update the Lead, but not
the child entities tied to it. To get around it I do a quick get to retrieve the entity, modify the values with the view model, and then update, rather than just passing in the entity for an update.
It's not how hard you push in life, but who you push, that makes the difference between success and running for your life.
About your first question, I can not confirm the relationship between your models, but if it's all one-on-one，I tried it without problems. Make sure there are no errors in the data in your database.Here is my worked code.
Model:
public class Lead
{
public int Id { get; set; }
public string Name { get; set; }
[ForeignKey("Job")]
public int JobId { get; set; }
public virtual Job Job { get; set; }
[ForeignKey("LeadState")]
public int LeadStateId { get; set; }
public virtual LeadState LeadState { get; set; }
[ForeignKey("JobBoard")]
public int JobBoardId { get; set; }
public virtual JobBoard JobBoard { get; set; }
}
public class Job
{
public int Id { get; set; }
public string Name { get; set; }
[ForeignKey("Source")]
public int SourceId { get; set; }
public virtual Source Source { get; set; }
[ForeignKey("Lead")]
public int LeadId { get; set; }
public virtual Lead Lead { get; set; }
}
public class LeadState
{
public int Id { get; set; }
public string Name { get; set; }
[ForeignKey("Lead")]
public int LeadId { get; set; }
public virtual Lead Lead { get; set; }
}
public class Source
{
public int Id { get; set; }
public string Name { get; set; }
[ForeignKey("Job")]
public int JobId { get; set; }
public virtual Job Job { get; set; }
}
public class JobBoard
{
public int Id { get; set; }
public string Name { get; set; }
[ForeignKey("Lead")]
public int LeadId { get; set; }
public virtual Lead Lead { get; set; }
}
public IActionResult Edit(int? id)
{
if (id == null)
{
return NotFound();
}
var leadDetails = _context.Leads.Include(l => l.Job).ThenInclude(j => j.Source).Include(l => l.LeadState)
.Include(l => l.JobBoard).First(l => l.Id == id);
if (leadDetails == null)
{
return NotFound();
}
return View(leadDetails);
}
Regarding your second question, I think that if your child data has to be passed to the controller and you don't need it, you can define a new lead entity to assign and update in database,just like this.
Member
184 Points
611 Posts
Entity Framework query including related entities
Sep 11, 2019 09:03 PM|WilliamSnell|LINK
I've actually got two problems I'm hoping to get help with. The first is an include in my query that isn't working, and I can't figure out why.
var leadDetails = _context.Lead .Include(l => l.Job) .ThenInclude(j => j.Source) .Include(l => l.LeadState) .Include(l => l.JobBoard) .First(l => l.Id == id);Everything but LeadState is loading without any problems. "LeadState" and "JobBoard" are lookup tables, just mapping an Id to a Name value. I set them both up in the EF entity the same way, like this:
[ForeignKey("LeadState")] public int LeadStateId { get; set; } public virtual LeadState LeadState { get; set; }But JobBoard loads as expected, and LeadState throws an error saying, "Sequence has no elements". I checked out the value of "LeadStateId" when the view model query runs, and it's valid. I'm not sure why this field is giving me trouble. Just to make sure it wasn't something small such as having 2 "Include" calls at the same level, I tried running the query with only the LeadState Include, and not the JobBoard. Still threw the exception. To get around it, I do this, which works just fine:
var leadDetails = _context.Lead .Include(l => l.Job) .ThenInclude(j => j.Source) .Include(l => l.JobBoard) .First(l => l.Id == id); leadDetails.LeadState = _context.LeadStates.FirstOrDefault(s => s.Id == leadDetails.LeadStateId);The other problem I'm having is updating the model on the form POST. Using the Include calls loads the related entities, and those end up as part of the package when performing the update. Definitely not what I want. I only want to update the Lead, but not the child entities tied to it. To get around it I do a quick get to retrieve the entity, modify the values with the view model, and then update, rather than just passing in the entity for an update.
Member
180 Points
88 Posts
Re: Entity Framework query including related entities
Sep 12, 2019 04:51 AM|Lewis Lu|LINK
Hi WilliamSnell ,
About your first question, I can not confirm the relationship between your models, but if it's all one-on-one，I tried it without problems. Make sure there are no errors in the data in your database.Here is my worked code.
Model:
public class Lead { public int Id { get; set; } public string Name { get; set; } [ForeignKey("Job")] public int JobId { get; set; } public virtual Job Job { get; set; } [ForeignKey("LeadState")] public int LeadStateId { get; set; } public virtual LeadState LeadState { get; set; } [ForeignKey("JobBoard")] public int JobBoardId { get; set; } public virtual JobBoard JobBoard { get; set; } } public class Job { public int Id { get; set; } public string Name { get; set; } [ForeignKey("Source")] public int SourceId { get; set; } public virtual Source Source { get; set; } [ForeignKey("Lead")] public int LeadId { get; set; } public virtual Lead Lead { get; set; } } public class LeadState { public int Id { get; set; } public string Name { get; set; } [ForeignKey("Lead")] public int LeadId { get; set; } public virtual Lead Lead { get; set; } } public class Source { public int Id { get; set; } public string Name { get; set; } [ForeignKey("Job")] public int JobId { get; set; } public virtual Job Job { get; set; } } public class JobBoard { public int Id { get; set; } public string Name { get; set; } [ForeignKey("Lead")] public int LeadId { get; set; } public virtual Lead Lead { get; set; } }DbContext:
public class ForumsMVCContext : DbContext { public ForumsMVCContext(DbContextOptions<ForumsMVCContext> options) : base(options) { } public DbSet<Lead> Leads { get; set; } public DbSet<Job> Jobs { get; set; } public DbSet<LeadState> LeadStates { get; set; } public DbSet<JobBoard> JobBoards { get; set; } public DbSet<Source> Sources { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Lead>() .HasOne(sa => sa.Job) .WithOne(s => s.Lead) .HasForeignKey<Job>(b => b.LeadId); modelBuilder.Entity<Lead>() .HasOne(sa => sa.LeadState) .WithOne(s => s.Lead) .HasForeignKey<LeadState>(b => b.LeadId); modelBuilder.Entity<Lead>() .HasOne(sa => sa.JobBoard) .WithOne(s => s.Lead) .HasForeignKey<JobBoard>(b => b.LeadId); modelBuilder.Entity<Job>() .HasOne(sa => sa.Source) .WithOne(s => s.Job) .HasForeignKey<Source>(b => b.JobId); } }Controller:
public IActionResult Edit(int? id) { if (id == null) { return NotFound(); } var leadDetails = _context.Leads.Include(l => l.Job).ThenInclude(j => j.Source).Include(l => l.LeadState) .Include(l => l.JobBoard).First(l => l.Id == id); if (leadDetails == null) { return NotFound(); } return View(leadDetails); }Regarding your second question, I think that if your child data has to be passed to the controller and you don't need it, you can define a new lead entity to assign and update in database,just like this.
Best Regards ,
Lewis Lu