EFCore, many-to-many, DB Navigation properties don't work at first
I am trying to map to an existing database. When I use the below code and pull the list of results for a given object, I return the expected results.
However, when I try to add .Include(x => x.Book)
in a simple list query to a table UserBook
, my result set returns empty.
[Table("User")]
public class User
{
[Key]
public Guid UserId { get; set; }
public string UserName{ get; set; }
// Reference
public ICollection<UserBook> UserBooks { get; set; }
}
[Table("Book")]
public class Book
{
[Key]
public Guid BookId { get; set; }
public string BookName{ get; set; }
// Reference
public ICollection<UserBook> UserBooks { get; set; }
}
[Table("UserBook")]
public class UserBook
{
public Guid UserId { get; set; }
public Guid BookId { get; set; }
public int PermissionMask { get; set; }
public bool Deleted { get; set; }
// Ref
public User User { get; set; }
public Book Book { get; set; }
}
I am following the instructions outlined here: http://www.learnentityframeworkcore.com/configuration/many-to-many-relationship-configuration
To better illustrate the problem, here are the queries I'm using:
// Returns 1,000+ rows.
_context.UserBooks.ToList();
// Returns 1 row
_context.UserBooks
.Where(x => x.UserId == "SomeGuid")
.ToList();
// Returns 0 rows
_context.UserBooks
.Include(x => x.Book)
.Where(x => x.UserId == "SomeGuid")
.ToList();
// Returns 0 rows
_context.UserBooks.Include(x => x.Book).ToList();
source to share
I think EF will complain that the UserBook item doesn't have certain keys, not sure how you got it to work. But to make the inclusion work, I believe you need to explicitly specify the composite key. Try adding the DbContext
following to the class :
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<UserBook>(e => e.HasKey(c => new { c.UserId, c.BookId }));
}
source to share