Detilium Detilium - 2 months ago 9
C# Question

Entity Framework - Database mapping to get latest entry

Imagine a database with two tables:

Blog
and
Post
. Taken my scenario, the
Post
table has a property called
CreateDate
, which is equal to the date that the
Post
has been created. I have a relation between
Blog
and
Post
, where a
Blog
can contain many
Posts
, and I wish to have a C# model property called
LatestPost
. Here's an example:

public class Blog
{
public int Id { get; set; }
public string Title { get; set; }

public virtual List<Post> Posts { get; set; }
public virtual Post LatestPost { get; set; }
}


I want to be able to get the latest
Post
for this particular
Blog
, sorted by
Post.CreatedDate
. I can see that I can map things as I want to in the
Dbcontext.OnModelCreating()
if I were to override said method. I'm just not quite sure how to make this mapping. Is it possible for me to do this and get the latest
Post
for a
Blog
?

Answer

To do so, your Blog should have foreign key to Posts like:

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int LatestPostId? { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
    public virtual Post LatestPost { get; set; }
}

Then inside OnModelCreating method you can map it like:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasOptional(m => m.LatestPost)
        .WithMany()
        .HasForeignKey(m => m.LatestPostId);
}

But I recommend loading LatestPost when you need it:

Post latestPost = myContext.Posts
    .Where(m => m.BlogId == blogId)
    .OrderByDescending(m => m.CreatedDate)
    .FirstOrDefault();

Additionally you can make LatestPost not mapped and configure it to load data from database:

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int LatestPostId? { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
    public virtual Post LatestPost
    {
        get
        {
            return Posts
                .OrderByDescending(m => m.CreatedDate)
                .FirstOrDefault();
        }
    }
}

Do not forget to Ignore LatestPost in OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Ignore(m => m.LatestPost);
}

Additionally you can ignore this property by adding NotMapped attribute:

public class Blog
{
    ....

    [NotMapped]
    public virtual Post LatestPost
    .....
}
Comments