Entity Framework Core Cookbook

Entity Framework Core Cookbook - Second Edition

By : Ricardo Peres

Buy this Book

Entity Framework Core Cookbook - Second Edition

By: Ricardo Peres

Buy this Book

Overview of this book

Entity Framework is a highly recommended Object Relation Mapping tool used to build complex systems. In order to survive in this growing market, the knowledge of a framework that helps provide easy access to databases, that is, Entity Framework has become a necessity. This book will provide .NET developers with this knowledge and guide them through working efficiently with data using Entity Framework Core. You will start off by learning how to efficiently use Entity Framework in practical situations. You will gain a deep understanding of mapping properties and find out how to handle validation in Entity Framework. The book will then explain how to work with transactions and stored procedures along with improving Entity Framework using query libraries. Moving on, you will learn to improve complex query scenarios and implement transaction and concurrency control. You will then be taught to improve and develop Entity Framework in complex business scenarios. With the concluding chapter on performance and scalability, this book will get you ready to use Entity Framework proficiently.

Entity Framework Core Cookbook - Second Edition

Credits

About the Author

About the Reviewer

www.PacktPub.com

Preface

Free Chapter

Improving Entity Framework in the Real World

Introduction

Improving Entity Framework by using a code-first approach

Unit testing and mocking

Creating databases from code

Creating mock database connections

Implementing the repository pattern

Implementing the unit of work pattern

Mapping Entities

Introduction

Mapping non-public members

Mapping interfaces

Shadow properties

Creating one-to-one maps

Creating one-to-many maps

Creating many-to-many maps

Creating custom conventions

Using sequence key generators

Using GUIDs as keys

Implementing inheritance – Table per Class hierarchy

Validation and Changes

Introduction

Validating simple properties

Validating the whole entity

Validating groups of entities

Intercepting saving changes

Intercepting property changes

Setting the state of an entity

Improving MVC UI with entity framework validation

Inserting, updating, and deleting entities with stored procedures

Updating the database from model changes

Dumping the SQL script for the database creation

Transactions and Concurrency Control

Introduction

Using explicit transactions

Using transactions in custom SQL operations

Implementing optimistic concurrency in SQL Server

Implementing optimistic concurrency in a database-agnostic way

Querying

Introduction

Executing client-side functions in LINQ queries

Mixing SQL with LINQ queries

Getting entities from the local cache

Creating filtered collections

Creating reusable queries

Querying shadow properties

Implementing the query object pattern

Using dynamic LINQ

Advanced Scenarios

Introduction

Generating entities from the database

Implementing multitenancy

Strongly typed bulk operations

Handling soft deletes

Adding logging

Capturing the audit data

Retrieving entity metadata

Improving MVC applications

Hooking infrastructure services

Using other databases

Performance and Scalability

Introduction

Improving the performance of queries

Testing and profiling queries

Using asynchronous operations

Eager loading

Using the cache

Pitfalls

Introduction

GroupBy executes on the client side

Table per class hierarchy requires nullable columns for derived classes

References not eagerly fetched are lost

Date/time operations are not supported

Paging in SQL Server earlier than 2012

Database null semantics

Migrations and contexts with parameterized constructors

Migrations with contexts in different projects

Accessing the service provider too soon

Setting the maximum string length

Mapping discriminator columns

Composite primary keys

Refreshing entities

Cascading entity deletes

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Paging in SQL Server earlier than 2012

Beware if you want to use a SQL Server version earlier than 2012 while doing paging.

Problem

At least two features of Entity Framework Core depend on SQL Server 2012:

Using sequences to generate primary keys
Using OFFSET for pagination

If we are not using SQL Server 2012 or higher, of course, we cannot use these features. The first one is not a problem since we can use IDENTITY columns or manually assigned identifiers, but the second is the default strategy Entity Framework uses for pagination. Type the following LINQ query:

var pagedBlogs = ctx
  .Blogs
  .Skip(4)
  .Take(5)
  .OrderBy(b => b.CreationDate)
  .ToList();

It will produce SQL similar to this:

SELECT [b].[BlogId], [b].[Name], [b].[CreationDate], [b].[Url]
FROM [Blog] AS [b]
ORDER BY [b].[CreationDate]
OFFSET @__b_0 ROWS FETCH NEXT @__b_1 ROWS ONLY

How to solve it…

This syntax with OFFSET… ROWS FETCH NEXT… ROWS ONLY is only valid for versions of SQL Server equal to or higher than 2012. Fortunately, we can tell Entity Framework to use a compatibility mode that will work from SQL Server 2005 upwards. We just need to set the UseRowNumberForPaging configuration setting, probably in the OnConfiguring method of our DbContext-derived class:

protected override void OnConfiguring(
DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(_connectionString, opt =>
    {
        //use ROW_NUMBER instead of OFFSET
        opt.UseRowNumberForPaging();
    });
    base.OnConfiguring(optionsBuilder);
}

After this, the same query will be instead:

SELECT [t].[BlogId], [t].[Name], [t].[CreationDate], [t].[Url]
FROM (
    SELECT [b].[BlogId], [b].[Name], [b].[CreationDate], [b].[Url], ROW_NUMBER() OVER(ORDER BY [b].[CreationDate]) AS [__RowNumber__]
    FROM [Blog] AS [b]
) AS [t]
WHERE ([t].[__RowNumber__] > @__b_0) AND ([t].[__RowNumber__] <= (@__b_0 + @__b_1))

Notice the usage of the ROW_NUMBER function and the nested queries.

Note

You can find a discussion of the two paging techniques in this article: http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx.

Entity Framework Core Cookbook - Second Edition

By : Ricardo Peres

Entity Framework Core Cookbook - Second Edition

By: Ricardo Peres

Overview of this book

Related Content you might be interested in

Current Title:

Entity Framework Core Cookbook - Second Edition

Paging in SQL Server earlier than 2012

Problem

How to solve it…

Note