CODEWITHSUNDEEP
databaseasp.net-coreentity-framework-coremigrationdbcontext
Dave2022
Dave2022

Reputation: 11

EF Core 8 : get child table count as a property of model

Ok, so back in the day we used the ADO and created entities, but now that Visual Studio Pro 2009 won't work with .NET 8 anymore, it seems to be the target for today's era. Anyways besides that I had a quick question.

Say I create a Blog class:

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Uri SiteUri { get; set; }

    public ICollection<Post> Posts { get; }
}

I then create a Post class:

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateTime PublishedOn { get; set; }
    public bool Archived { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

In the DbContext I include:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasMany(e => e.Posts)
        .WithOne(e => e.Blog)
        .HasForeignKey(e => e.BlogId)
        .HasPrincipalKey(e => e.Id);
}

The question is, what would I have to do to make it so the Blog class has an extra property that showed the count for how many posts matching the BlogId to the Post table?

Basically a property showing how many posts a blog has. Do I need to be focusing on modifying and adding this functionality to the model or the DbContext class? Thanks

Upvotes: 0

Views: 50

Answers (1)

Steve Py
Steve Py

Reputation: 34908

Unfortunately many EF examples out there either use entities inefficiently, or demonstrate the simplest examples on how to do something (like eager load a collection) that it gets interpreted as "this is how you get access to data". Entity classes serve as a data domain representation, while you will also have a view domain or a transfer domain.

So a simple example with Blogs and Posts might read something like:

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string SiteUri { get; set; }
    public DateTime CreatedDateTime { get; set; }

    public virtual ICollection<Post> Posts { get; } = [];
    [NotMapped]
    public int PostCount => Posts.Count();
}

While this will work, there are a couple of problems with the approach. Firstly, the PostCount would not be available unless the Posts collection was eager loaded, or lazy loading was available; And it could be an incomplete/inaccurate count if not eager loaded and the DbContext happened to be tracking some of the posts associated with that blog.

PostCount isn't a data concern. We wouldn't denormalize our Blogs table to include a PostCount then worry about how to keep it in sync with the posts for that blog as they are added & removed. PostCount is a view concern and this is best served by defining a POCO ViewModel class for the domain as the view in question might need. For instance if we want to display a list of blogs and include a count of posts:

public class BlogSummaryViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int PostCount { get; set; }
}

Then when we want to get a list of blogs to populate the view:

var blogs = await _context.Blogs
    .AsNoTracking()
    .OrderByDescending(b => b.CreatedDateTime)
    .Select(b => new BlogSummaryViewModel
    {
        Id = b.Id,
        Name = b.Name,
        PostCount = b.Posts.Count()
    }).Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToListAsync();

We could include other details about posts, like fetching the latest Post date, etc. Projection uses the entity relationships to compose a query to fetch just the data we need from the database. We don't need to worry about eager loading relationships, the use of the navigation properties in the Linq expression lets EF work out what tables will be needed.

This is a very important detail why developers should avoid things like the Generic Repository pattern. Abstracting the EF DbSet interferes with advantages like projection.

As a general rule for building efficient queries, use projection (Select()) for read operations. Reserve fetching entities with tracking queries including eager loading related entities for situations where you want to modify those entities.

Upvotes: 0

Related Questions