Can't get `Include` to work with EF core , without FK

Question

Can't get `Include` to work with EF core , without FK

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

Question

I have 2 tables in SQL server without a FK :

So each Image has many Comments.

Using this command I've created scaffolded files :

dotnet ef dbcontext scaffold "Server=sff-pc;Database=IMG;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Models -t Images -t comments --context-dir Context -c MyContext -f

Here are the generated files :

 public partial class Images
    {
        public int ImageId { get; set; }
        public Guid ImgGuid { get; set; }
        public string ImgExtension { get; set; }
        public DateTime Datecreated { get; set; }
        public string Origin { get; set; }
        public decimal? Size { get; set; }
        public string Ip { get; set; }
        public int UserId { get; set; }
        public string Description { get; set; }
        public bool? IsActive { get; set; }
        public int? VotesUp { get; set; }
        public int? VotesDown { get; set; }
        public int ImgType { get; set; }
    }

public partial class Comments
{
    public int CommentId { get; set; }
    public int ImageId { get; set; }
    public int UserId1 { get; set; }
    public string CommentTxt { get; set; }
    public DateTime DateCreated { get; set; }
    public bool? IsActive { get; set; }
}

Question:

Using those generated classes and this full context file, How can I get each Image and Include its comments?

What have I tried :

I've tried adding the ForeignKey attribute :

 public partial class Comments
    {
        public int CommentId { get; set; }

        [ForeignKey("Images")]          <--------------Here
        public int ImageId { get; set; }
        public int UserId1 { get; set; }
        public string CommentTxt { get; set; }
        public DateTime DateCreated { get; set; }
        public bool? IsActive { get; set; }
    }

And to run this :

        var context = new MyContext();
        var result = context.Images.Include("Comments"); //exception

         foreach (var a in result)
         {
             Console.WriteLine(a);
         }

Exception :

The property 'Comments' is not a navigation property of entity type 'Images'

Royi Namir

View more on Stack Overflow

Neville Nazerane · Accepted Answer · 1/15/2019 8:51:31 AM

When you use [ForeignKey("Images")] over your fk "Images" is expected to be a navigation property inside your Comments class not the name of your DbSet property in your DbContext. You can read more here and relationships here. In the case of your code based on the definition of your Comments class, you will need to have a property Images.

[ForeignKey("Images")]          
public int ImageId { get; set; }
public Images Images { get; set; } // ForeignKey attribute points to this

While the above shows how you can use [ForeignKey("Images")], I would recommend making the property's name Image instead of Images. Not only will it be a better explanatory name, but it would also make the [ForeignKey("Image")] unnecessary. EF would automatically map Image to Imageid by naming convention.

Based on the way you are using to access comments using context.Images.Include("Comments"), you seem to be trying to access the list of comments under an image. However, if you notice your result variable would be a IQueriable of Images. To achieve this, you will need to add a navigation property into your Images class.

public IEnumerable<Comments> Comments { get; set; }

Since you have already mapped your foreign key, your mapping is implicit between ImageId and Comments. You can, however, use InverseProperty attribute to be safe (check the relationships link for more info). Once this is done you can use the following code:

var result = context.Images.Include(i => i.Comments);

Can't get `Include` to work with EF core , without FK

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related