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'
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);