How to efficiently work with Entity Framework Core?

Question

Let's take a look at simple class examples:

public class Book
{
    [Key]
    public string BookId { get; set; }
    public List<BookPage> Pages { get; set; }
    public string Text { get; set; }
} 

public class BookPage
{
    [Key]
    public string BookPageId { get; set; }
    public PageTitle PageTitle { get; set; }
    public int Number { get; set; }
}

public class PageTitle
{
    [Key]
    public string PageTitleId { get; set; }
    public string Title { get; set; }
}

So, if I want to get all PageTitiles, if I knew only the BookId, I need to write a few includes, like this:

using (var dbContext = new BookContext())
{
    var bookPages = dbContext
    .Book
    .Include(x => x.Pages)
    .ThenInclude(x => x.PageTitle)//.ThenInclude(x => x.Select(y => y.PageTitle)) Shouldn't use in EF Core
    .SingleOrDefault(x => x.BookId == "some example id")
    .Pages
    .Select(x => x.PageTitle);
}

And if i want to get PageTitles connected with other book, I need to rewrite this method again, and nothing changed except the BookId! This is very inefficient way to work with database, in this example I have 3 classes, but if I had hundreds of classes, nested to the very deep level, it would be very slow and uncomfortable to work.

How exactly should I organize working with my database, to avoid many Includes, and redundant queries?

Answer 1

None of the examples given require any Include statements at all. If you are using a select at the end of your query and you are still operating on an IQueryable such as a DbSet, Entity Framework will perform what is known as a 'projection' and will run the query including all of the required fields for you automatically.

For example, your original code:

using (var dbContext = new BookContext())
{
    var bookPages = dbContext
        .Book
        .Include(x => x.Pages)
        .ThenInclude(x => x.PageTitle)//.ThenInclude(x => x.Select(y => y.PageTitle)) Shouldn't use in EF Core
        .SingleOrDefault(x => x.BookId == "some example id")
        .Pages
        .Select(x => x.PageTitle);
}

You can rewrite this like so:

using (var dbContext = new BookContext())
{
    var bookPages = dbContext
        .Book
        .Where(x => x.BookId == "some example id")
        .SelectMany(x => x.Pages.Select(y => y.PageTitle))
        .ToList();
}

Here's what Entity Framework will do to resolve this:

1. We tell Entity Framework that we're going to look at entries from the books table
2. We then tell Entity Framework that we only want the books with a specific ID (which should just be a single record, of course)
3. From there, for each book we tell Entity Framework that we want a list of all of that books pages (again, this will just be one book's pages because of the Where statement)
4. Then we tell Entity Framework that we want just the PageTitle from each Page
5. Finally, we tell Entity Framework to use all of the information we've just provided to generate a query and execute it

The last step is the crucial one if you want to understand how Entity Framework does what it does. In your example when you call SingleOrDefault you are instructing Entity Framework to execute the query, which is why you need the includes. In your example you haven't actually told Entity Framework that you need the pages when you run the query, so you have to manually request them using Include.

In the example I've posted, you can see that by the time you run the query (ToList is what triggers the query execution) Entity Framework knows from your Select expression that it is going to need the pages, and their titles. Even better - this means Entity Framework will not even include unused columns in the SELECT statement that it generates.

I highly recommend investigating projections, they're probably the best way I know of to remove the requirement to continuously manually include stuff.

Answer 2

I would have built the model like this:

    public class Book
    {
        // a property "Id" or ClassName + "Id" is treated as primary key. 
        // No annotation needed.
        public int BookId { get; set; }

        // without [StringLenth(123)] it's created as NVARCHAR(MAX)
        [Required]
        public string Text { get; set; }

        // optionally if you need the pages in the book object:
        // Usually I saw ICollections for this usage.
        // Without lazy loading virtual is probably not necessary.
        public virtual ICollection<BookPage> BookPages { get; set; }
    }

    public class BookPage
    {
        public int BookPageId { get; set; }

        // With the following naming convention EF treats those two property as 
        // on single database column. This automatically corresponds
        // to ICollection<BookPage> BookPages of Books.
        // Required is not neccessary if "BookId" is int. If not required use int?
        // A foreign key relationship is created automatically. 
        // With RC2 also an index is created for all foreign key columns.
        [Required]
        public Book Book { get; set; }
        public int BookId { get; set; }

        [Required]
        public PageTitle PageTitle { get; set; }
        public int PageTitleId { get; set; }

        public int Number { get; set; }
    }

    public class PageTitle
    {
        public int PageTitleId { get; set; }

        // without StringLenth it's created as NVARCHAR(MAX)
        [Required]
        [StringLength(100)]
        public string Title { get; set; }
    }

As you had a collection of BookPage in Book a foreign key is created in BookPage. In my model I have exposed this explicitly in BookPage. And not only with the object Book but also with the key BookId. The created tables are quite the same but now you can access the BookId without using the Book table.

    using (var dbContext = new BookContext())
    {
        var pageTitles = dbContext.BookPages
            .Include(p => p.PageTitle)
            .Where(p => p.BookId == myBookId)
            .Select(p => p.PageTitle);
    }

I would recommend to activate logging or to use the profiler to check which SQL statements are actually executed.

Regarding to the comments of @bilpor: I found out that I did not need many DataAnnotations and almost no fluent API mappings. Primary and foreign keys are created automatically if you use the designated naming conventions. For foreign key relationships I only needed [InverseProperty()] on the collections if I had two foreign key relationships on the same two classes. Currently I only used fluent API mappings for composite primary keys (m:n tables) and to define the discriminator in a TPH structure.

Hint: Currently there are bugs in EF Core which lead to client side evaluation of constraints.

.Where(p => p.BookId == myBookId)  // OK 
.Where(p => p.BookId == myObject.BookId) // client side 
.Where(p => p.BookId == myBookIdList[0]) // client side 

Same is true when you use Contains() and you mix nullable and not nullable data types.

.Where(p => notNullableBookIdList.Contains(p.NullableBookId)) // client side 

Answer 3

Problem 1: I have to add a bunch of Includes each time.

Well, there's not a way around that as you have to explicitly included related data in EF, but you can easily create an extension method to make it cleaner:

public static IQueryable<Book> GetBooksAndPages(this BookContext db)
{
    return db.Book.Include(x => x.Pages);
}

public static IQueryable<Book> GetBooksAndPagesAndTitles(this BookContext db)
{
    return GetBooksAndPages(db).ThenInclude(p => p.PageTitle)

}

Then you can just do:

var bookPages = dbContext
    .GetBooksAndPagesAndTitles()
    .SingleOrDefault(x => x.BookId == "some example id")
    .Pages
    .Select(x => x.PageTitle);

Problem 2: I have to write this query multiple times for different IDs.

Why not just refactor that into a method with a bookId parameter?

public IEnumerable<PageTitle> GetPageTitlesForBook(BookContext dbContext, int bookId)
{
    return dbContext
        .GetBooksAndPagesAndTitles()
        .SingleOrDefault(x => x.BookId == bookId)
        .Pages
        .Select(x => x.PageTitle);
}

Bottom line - if you find yourself writing the same thing many times, that's a perfect opportunity to refactor your code into smaller methods that can be re-used.

Answer 4

I somehow missed this was EF Core (despite the title). Try this instead:

public class BookPage
{
    [Key]
    public string BookPageId { get; set; }
    public int Number { get; set; }
    public PageTitle PageTitle { get; set; }
    public Book Book { get; set; }   // Add FK if desired
}

Now to get all page titles for a book:

// pass the book you want in as a parameter, viewbag, etc.
using (var dbContext = new BookContext())
{
    var bookPages = dbContext.BookPages
        .Include(p => p.Book)
        .Include(p => p.PageTitle)
        .Where(p => p.Book.BookId == myBookId)
        .Select(p => new { 
            Bookid = p.Book.BookId,
            Text = p.Book.Text,
            PageNumber = p.Number,
            PageTitle = p.PageTitle.Title
        });
}