# Introduction of Entity Framework Core [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Introduction of Entity Framework Core EF Core (Entity Framework Core) is an ORM (Object Relational Mapper) developed by Microsoft to simplify the database operations for .NET developers. Entity Framework Core is a lightweight, extensible, and open-source software like all the previous versions of the Entity Framework. It is a cross-platform software, making it easily to work on different operating systems like Windows, Mac OS, and Linux OS. Some advantage of EF Core include: * Entity Framework Core execute create, read, update, and delete operation automatically without the need to write all the SQL queries manually every time. * Entity Framework Core works on the principle of ORM, which allows it to drop the need for writing all the access code that the developers usually had to spend so much time writing. ### Entity Framework Core Approaches EF Core supports two development approaches: Code First [Database First](https://www.learnentityframeworkcore.com/walkthroughs/existing-database) #### Code First Approach The code-first approach allows you to create your model through [Data Annotations](https://www.learnentityframeworkcore.com/configuration/data-annotation-attributes) and [Fluent API](https://www.learnentityframeworkcore.com/configuration/fluent-api) It also allow you to specify some [Migrations](https://www.learnentityframeworkcore.com/migrations) and execute command like [Add-Migration](https://www.learnentityframeworkcore.com/migrations/add-migration) #### Database First Approach For the database first approach, EF Core creates the required classes by using the available database or database tables, it creates these classes using the EF Core commands. But, the drawback of the database first approach is that this method can be applied to only limited numbers of classes as the EF Core does not support visual designer or wizard. ### Features If you are familiar with Entity Framework 6, the EF Core consists of all the features from EF 6, Some of the basic features that are included in the Entity Framework Core are: * [DbContext](https://github.com/tutorialpedia/learnentityframeworkcore5.com/blob/master/dbcontext/README.md) * [DbSet](https://github.com/tutorialpedia/learnentityframeworkcore5.com/blob/master/dbset/README.md) * Data Model * Querying using Linq-to-Entities * [Change Tracking](https://www.learnentityframeworkcore.com/dbcontext/change-tracker) * SaveChanges * [Migrations](https://www.learnentityframeworkcore.com/migrations) * Easy relationship configuration * In-memory provider for testing * Support for IoC (Inversion of Control) * Unique constraints * Shadow properties * Alternate keys * Global query filter * Field mapping * DbContext pooling * Better patterns for handling disconnected entity graphs Besides these features, Entity Framework Core has been recently updated to have the support for the following features as well. ### LINQ Overhaul LINQ allows developers to write an unlimited number of different .NET queries of their choices. It helps in having rich type information that offers IntelliSense and compile-time type checking, but the real challenge is to handle these combinations for LINQ providers. * The newer EF Core 8 allows LINQ providers to translate more numbers of queries into SQL, giving the user more efficient queries in SQL and letting in-efficient queries remain undetected. * It also allows developers to create a single SQL statement per LINQ query. Although it can have further improvements which will bring more performance upgrades in the future. ### Cosmos Database Support For developers who are familiar with Entity Framework, Cosmos DB support enables them to target the Azure Cosmos DB as an Application database. This allows the .NET developers to have access to features like global distribution, always-on availability, elastic scalability, and low latency. ### Lazy Loading Lazy loading allows the EF Core to take the required data without writing extra queries for the same. * To apply this, it uses the proxies but since proxy logic isn’t the core feature of the EF Core, it keeps the data in its own package of the project. * With lazy loading enabled on the context, any virtual-navigation property is overridden under the covers by the proxy at run time, the developer has to manually change the declaration to virtual. ### Reverse Engineering of Database Views Those query type which represents data are readable from the database, but they cannot be changed or updated, these queries have been renamed to key-less entity types. EF Core now automatically creates key-less entity types when reverse engineering of database views. Entity Framework Core is continuously getting new and better firmware updates which are adding more useful features for developers, some of the upcoming features for the Future are: * Ability to ignore parts of a model in migrations. * Property bag entities tracked as two separate issues. ### Example of The Entity Framework Core In a typical situation to read, write, update, and delete from the database table, the developers must write different code to generate the SQL operations. When the data is read from the database, in order to map the data to the relevant classes, the developers must generate another custom code to map the data to their respective classes. All these actions must be performed for each individual project making it complicated and time-consuming for the developers. With the help of an Object Relation Mapper like the EF Core, all these tasks can be done automatically making it simple and time saver for all the .NET Developers. EF Core sits between application code and database and it eliminates the need for the custom data access code that usually had to be written when proper ORM solutions were absent. **Example:** In case we have to develop an application to manage the employees in a company, we will have different classes like employees, departments, positions, etc. These classes will be called as domain classes. To arrange all these classes in a systematic and structured order, any developer would have to invest a lot of time and effort to create suitable codes for the following situation. In this case, an ORM like the EF Core comes really handy as it does all the work automatically, resulting in saving lots of resources. ### Bulk Extensions In some other situations where you need optimal performance and save thousands of entities, you need to use a third-party library named [Entity Framework Extensions](https://entityframework-extensions.net/). They offer [EFCore BulkExtensions](https://entityframework-extensions.net/bulk-extensions) that allow you to be way faster but also use way less memory: | Versus | BulkInsert | SaveChanges | | ----------- | :--------: | :---------: | | Memory | 400 MB | 1800 MB | | Performance | 10s | 58s | ### Conclusion Entity Framework Core is very useful yet agile software which sits between different category of classes and the databases or database tables to automatically configure the SQL statements needed to restructure and manage the given databases. It simplifies the work of the developers by developing the data on its own, which allows the user to do their work more efficiently and effectively. Since its a cross-platform software, any .NET developer can have the benefits of the Entity Framework Core. With the on-going development happening to the Entity Framework Core, developers can expect useful and amazing features making their way to future firmware releases. ### References * [Learn EF Core](https://www.learnentityframeworkcore.com/) * [EFE EFCore BulkExtensions](https://entityframework-extensions.net/bulk-extensions) # What's New in EF Core 5 [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## What's New in EF Core 5 EF Core 5.0 is currently in development, and here is the list of all the interesting changes introduced so far in each preview. ### Preview 1 #### Simple logging The simple logging feature adds functionality similar to `Database.Log` in EF6 by providing a simple way to get logs from EF Core without the need to configure any kind of external logging framework. #### Simple way to get generated SQL EF Core 5.0 introduces the `ToQueryString` extension method, which will return the SQL that EF Core will generate when executing a LINQ query. #### Use a C# attribute to indicate that an entity has no key An entity type can now be configured as having no key using the new `KeylessAttribute`. ```csharp [Keyless] public class Address { public string Street { get; set; } public string City { get; set; } public int Zip { get; set; } } ``` #### Connection or connection string can be changed on initialized DbContext * It is now easier to create a `DbContext` instance without any connection or connection string. * The connection or connection string can now be mutated on the context instance. * This feature allows the same context instance to dynamically connect to different databases. #### Change-tracking proxies * EF Core can now generate runtime proxies that automatically implement `INotifyPropertyChanging` and `INotifyPropertyChanged`. * These then report value changes on entity properties directly to EF Core, avoiding the need to scan for changes. * However, proxies come with their own set of limitations, so they are not for everyone. #### Enhanced debug views * Debug views are an easy way to look at the internals of EF Core when debugging issues. A debug view for the Model was implemented some time ago. For EF Core 5.0, we have made the model view easier to read and added a new debug view for tracked entities in the state manager. #### Improved handling of database null semantics * Relational databases typically treat NULL as an unknown value and therefore not equal to any other NULL. * While C# treats null as a defined value, which compares equal to any other null. * EF Core by default translates queries so that they use C# null semantics. EF Core 5.0 greatly improves the efficiency of these translations. #### Indexer properties EF Core 5.0 supports the mapping of C# indexer properties. These properties allow entities to act as property bags where columns are mapped to named properties in the bag. #### Generation of check constraints for enum mappings EF Core 5.0 migrations can now generate `CHECK` constraints for `enum` property mappings. ```sql MyEnumColumn VARCHAR(10) NOT NULL CHECK (MyEnumColumn IN ('Useful', 'Useless', 'Unknown')) ``` #### IsRelational A new `IsRelational` method has been added in addition to the existing `IsSqlServer`, `IsSqlite` and `IsInMemory`. This method can be used to test if the DbContext is using any relational database provider. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { if (Database.IsRelational()) { // Do relational-specific model configuration. } } ``` #### Cosmos optimistic concurrency with ETags The Azure Cosmos DB database provider now supports optimistic concurrency using `ETags`. Use the model builder in `OnModelCreating` to configure an `ETag`. ```csharp builder.Entity().Property(c => c.ETag).IsEtagConcurrency(); ``` The `SaveChanges` will then throw a `DbUpdateConcurrencyException` on a concurrency conflict, which can be handled to implement retries, etc. #### Query translations for more DateTime constructs Queries containing new `DateTime` construction is now translated. Also, the following SQL Server functions are now mapped: * DateDiffWeek * DateFromParts For example: ```csharp var count = context.Orders.Count(c => date > EF.Functions.DateFromParts(DateTime.Now.Year, 12, 25)); ``` #### Query translations for more byte array constructs Queries using `Contains`, `Length`, `SequenceEqual`, etc. on `byte[]` properties are now translated to SQL. #### Query translation for Reverse Queries using `Reverse` are now translated. ```csharp context.Employees.OrderBy(e => e.EmployeeID).Reverse() ``` #### Query translation for bitwise operators Queries using bit-wise operators are now translated in more cases. ```csharp context.Orders.Where(o => ~o.OrderID == negatedId) ``` #### Query translation for strings on Cosmos Queries that use the string methods such as, `Contains`, `StartsWith`, and `EndsWith` are now translated when using the Azure Cosmos DB provider. ### Preview 2 #### Use a C# attribute to specify a property backing field A C# attribute can now be used to specify the backing field for a property. This attribute allows EF Core to still write to and read from the backing field as would normally happen, even when the backing field cannot be found automatically. ```csharp public class Blog { private string _mainTitle; public int Id { get; set; } [BackingField(nameof(_mainTitle))] public string Title { get => _mainTitle; set => _mainTitle = value; } } ``` #### Complete discriminator mapping EF Core uses a discriminator column for TPH mapping of an inheritance hierarchy. * Some performance enhancements are possible as long as EF Core knows all possible values for the discriminator. * EF Core 5.0 now implements these enhancements. For example, previous versions of EF Core would always generate this SQL for a query returning all types in a hierarchy. ```sql SELECT [a].[Id], [a].[Discriminator], [a].[Name] FROM [Animal] AS [a] WHERE [a].[Discriminator] IN (N'Animal', N'Cat', N'Dog', N'Human') ``` EF Core 5.0 will now generate the following when a complete discriminator mapping is configured ```sql SELECT [a].[Id], [a].[Discriminator], [a].[Name] FROM [Animal] AS [a] ``` It will be the default behavior starting with preview 3. #### Performance improvements in Microsoft.Data.Sqlite The following two performances improvements are made for SQLite: * Retrieving binary and string data with `GetBytes`, `GetChars`, and `GetTextReader` is now more efficient by making use of **SqliteBlob** and **streams**. * The initialization of `SqliteConnection` is now lazy. These improvements are in the ADO.NET `Microsoft.Data.Sqlite` provider and hence also improve performance outside of EF Core. ### Preview 3 #### Filtered Include The `Include` method now supports filtering of the entities included. ```csharp var blogs = context.Blogs .Include(e => e.Posts.Where(p => p.Title.Contains("Cheese"))) .ToList(); ``` This query will return blogs together with each associated post, but only when the post title contains "Cheese". Skip and Take can also be used to reduce the number of included entities. ```csharp var blogs = context.Blogs .Include(e => e.Posts.OrderByDescending(post => post.Title).Take(5))) .ToList(); ``` This query will return blogs with at most five posts included on each blog. #### New ModelBuilder API for navigation properties Navigation properties are primarily configured when defining relationships. However, the new `Navigation` method can be used in cases where navigation properties need an additional configuration. For example, to set a backing field for the navigation when the field would not be found by convention. ```csharp modelBuilder.Entity().Navigation(e => e.Posts).HasField("_myposts"); ``` Note that the `Navigation` API does not replace relationship configuration. Instead, it allows additional configuration of navigation properties in already discovered or defined relationships. #### New command-line parameters for namespaces and connection strings Migrations and scaffolding now allow namespaces to be specified on the command line. For example, to reverse engineer a database putting the context and model classes in different namespaces. ```bash dotnet ef dbcontext scaffold "connection string" Microsoft.EntityFrameworkCore.SqlServer --context-namespace "My.Context" --namespace "My.Model" ``` Also, a connection string can now be passed to the `database-update` command. ```bash dotnet ef database update --connection "connection string" ``` Equivalent parameters have also been added to the **PowerShell** commands used in the VS Package Manager Console. #### EnableDetailedErrors has returned For performance reasons, EF doesn't do additional null-checks when reading values from the database. This can result in exceptions that are hard to root-cause when an unexpected null is encountered. Using `EnableDetailedErrors` will add extra null checking to queries such that, for a small performance overhead, these errors are easier to trace back to a root cause. ```csharp protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .EnableDetailedErrors() .EnableSensitiveDataLogging() // Often also useful with EnableDetailedErrors .UseSqlServer(Your.SqlServerConnectionString); ``` #### Cosmos partition keys The partition key to use for a given query can now be specified in the query. ```csharp await context.Set() .WithPartitionKey(myPartitionKey) .FirstAsync(); ``` #### Support for the SQL Server DATALENGTH function This can be accessed using the new `EF.Functions.DataLength` method. ```csharp var count = context.Orders.Count(c => 100 < EF.Functions.DataLength(c.OrderDate)); ``` ### Preview 4 #### Configure database precision/scale in model Precision and scale for a property can now be specified using the model builder. ```csharp modelBuilder .Entity() .Property(b => b.Numeric) .HasPrecision(16, 4); ``` Precision and scale can still be set via the full database type, such as "decimal(16,4)". #### Specify SQL Server index fill factor The fill factor can now be specified when creating an index on SQL Server. For example. ```csharp modelBuilder .Entity() .HasIndex(e => e.Name) .HasFillFactor(90); ``` ### Preview 5 #### Database collations The default collation for a database can now be specified in the EF model. It will flow through to generated migrations to set the collation when the database is created. ```csharp modelBuilder.UseCollation("German_PhoneBook_CI_AS"); ``` When you create migrations then it generates the following to create the database on SQL Server. ```sql CREATE DATABASE [Test] COLLATE German_PhoneBook_CI_AS; ``` You can also specify the collation to use for specific database columns. ```csharp modelBuilder .Entity() .Property(e => e.Name) .UseCollation("German_PhoneBook_CI_AS"); ``` For those not using migrations, collations are now reverse-engineered from the database when scaffolding a `DbContext`. Finally, the `EF.Functions.Collate()` allows for ad-hoc queries using different collations. ```csharp context.Users.Single(e => EF.Functions.Collate(e.Name, "French_CI_AS") == "Jean-Michel Jarre"); ``` This will generate the following query for SQL Server. ```sql SELECT TOP(2) [u].[Id], [u].[Name] FROM [Users] AS [u] WHERE [u].[Name] COLLATE French_CI_AS = N'Jean-Michel Jarre' ``` The ad-hoc collations should be used with care as they can negatively impact database performance. #### Flow arguments into IDesignTimeDbContextFactory Arguments now flow from the command line into the `CreateDbContext` method of `IDesignTimeDbContextFactory`. For example, to indicate this is a dev build, a custom argument (e.g. `dev`) can be passed on the command line. ```bash dotnet ef migrations add two --verbose --dev ``` This argument will then flow into the factory, where it can be used to control how the context is created and initialized. ```csharp public class MyDbContextFactory : IDesignTimeDbContextFactory { public SomeDbContext CreateDbContext(string[] args) => new SomeDbContext(args.Contains("--dev")); } ``` #### No-tracking queries with identity resolution No-tracking queries can now be configured to perform identity resolution. For example, the following query will create a new Blog instance for each Post, even if each Blog has the same primary key. ```csharp context.Posts.AsNoTracking().Include(e => e.Blog).ToList(); ``` However, at the expense of usually being slightly slower and always using more memory, this query can be changed to ensure only a single Blog instance is created. ```csharp context.Posts.AsNoTracking().PerformIdentityResolution().Include(e => e.Blog).ToList(); ``` It is only useful for no-tracking queries since all tracking queries already exhibit this behavior. Also, following the API review, the `PerformIdentityResolution` the syntax will be changed. #### Stored Computed columns Most databases allow computed column values to be stored after computation. * The computed column is calculated only once on the update, instead of each time its value is retrieved it takes up disk space. * This also allows the column to be indexed for some databases. EF Core 5.0 allows computed columns to be configured as stored. ```csharp modelBuilder .Entity() .Property(e => e.SomethingComputed) .HasComputedColumnSql("my sql", stored: true); ``` **SQLite computed columns** EF Core now supports computed columns in SQLite databases. ### Preview 6 #### Split queries for related collections Starting with EF Core 3.0, EF Core always generates a single SQL query for each LINQ query. * It ensures consistency of the data returned within the constraints of the transaction mode in use. * However, it can become very slow when the query uses `Include` or a projection to bring back multiple related collections. EF Core 5.0 now allows a single LINQ query including related collections to be split into multiple SQL queries. * It can significantly improve performance but can result in inconsistency in the results returned if the data changes between the two queries. * The serializable or snapshot transactions can be used to mitigate this and achieve consistency with split queries, but that may bring other performance costs and behavioral differences. **Split queries with Include** For example, consider a query that pulls in two levels of related collections using `Include`method. ```csharp var artists = context.Artists .Include(e => e.Albums).ThenInclude(e => e.Tags) .ToList(); ``` By default, EF Core will generate the following SQL when using the SQLite provider. ```sql SELECT "a"."Id", "a"."Name", "t0"."Id", "t0"."ArtistId", "t0"."Title", "t0"."Id0", "t0"."AlbumId", "t0"."Name" FROM "Artists" AS "a" LEFT JOIN ( SELECT "a0"."Id", "a0"."ArtistId", "a0"."Title", "t"."Id" AS "Id0", "t"."AlbumId", "t"."Name" FROM "Album" AS "a0" LEFT JOIN "Tag" AS "t" ON "a0"."Id" = "t"."AlbumId" ) AS "t0" ON "a"."Id" = "t0"."ArtistId" ORDER BY "a"."Id", "t0"."Id", "t0"."Id0" ``` The new `AsSplitQuery` API can be used to change this behavior. ```csharp var artists = context.Artists .AsSplitQuery() .Include(e => e.Albums).ThenInclude(e => e.Tags) .ToList(); ``` The `AsSplitQuery` is available for all relational database providers and can be used anywhere in the query, just like `AsNoTracking`. EF Core will now generate the following three SQL queries. ```sql SELECT "a"."Id", "a"."Name" FROM "Artists" AS "a" ORDER BY "a"."Id" SELECT "a0"."Id", "a0"."ArtistId", "a0"."Title", "a"."Id" FROM "Artists" AS "a" INNER JOIN "Album" AS "a0" ON "a"."Id" = "a0"."ArtistId" ORDER BY "a"."Id", "a0"."Id" SELECT "t"."Id", "t"."AlbumId", "t"."Name", "a"."Id", "a0"."Id" FROM "Artists" AS "a" INNER JOIN "Album" AS "a0" ON "a"."Id" = "a0"."ArtistId" INNER JOIN "Tag" AS "t" ON "a0"."Id" = "t"."AlbumId" ORDER BY "a"."Id", "a0"."Id" ``` All operations on the query root are supported including `OrderBy`, `Skip`, `Take`, `Join`, `FirstOrDefault` and similar single result selecting operations. The filtered Includes with `OrderBy`, `Skip`, `Take` are not supported in preview 6, but are available in the daily builds and will be included in preview 7. **Split queries with collection projections** The `AsSplitQuery` method can also be used when collections are loaded in projections. ```csharp context.Artists .AsSplitQuery() .Select(e => new { Artist = e, Albums = e.Albums, }).ToList(); ``` The above LINQ query generates the following two SQL queries when using the SQLite provider ```csharp SELECT "a"."Id", "a"."Name" FROM "Artists" AS "a" ORDER BY "a"."Id" SELECT "a0"."Id", "a0"."ArtistId", "a0"."Title", "a"."Id" FROM "Artists" AS "a" INNER JOIN "Album" AS "a0" ON "a"."Id" = "a0"."ArtistId" ORDER BY "a"."Id" ``` Only materialization of the collection is supported. Any composition after `e.Albums` in the above case won't result in a split query. #### IndexAttribute The new `IndexAttribute` can be placed on an entity type to specify an index for a single column. ```csharp [Index(nameof(FullName), IsUnique = true)] public class User { public int Id { get; set; } [MaxLength(128)] public string FullName { get; set; } } ``` For SQL Server, Migrations will then generate the following SQL. ```sql CREATE UNIQUE INDEX [IX_Users_FullName] ON [Users] ([FullName]) WHERE [FullName] IS NOT NULL; ``` IndexAttribute can also be used to specify an index spanning multiple columns. ```csharp [Index(nameof(FirstName), nameof(LastName), IsUnique = true)] public class User { public int Id { get; set; } [MaxLength(64)] public string FirstName { get; set; } [MaxLength(64)] public string LastName { get; set; } } ``` For SQL Server, the result is as shown below. ```sql CREATE UNIQUE INDEX [IX_Users_FirstName_LastName] ON [Users] ([FirstName], [LastName]) WHERE [FirstName] IS NOT NULL AND [LastName] IS NOT NULL; ``` #### Improved query translation exceptions We are continuing to improve the exception messages generated when query translation fails. For example, this query uses the `IsSigned`unmapped property. ```csharp var artists = context.Artists.Where(e => e.IsSigned).ToList(); ``` EF Core will throw the following exception indicating that translation failed because `IsSigned` is not mapped. ```csharp Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet() .Where(a => a.IsSigned)' could not be translated. Additional information: Translation of member 'IsSigned' on entity type 'Artist' failed. Possibly the specified member is not mapped. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See for more information. ``` Similarly, better exception messages are now generated when attempting to translate string comparisons with culture-dependent semantics. For example, the following query attempts to use `StringComparison.CurrentCulture`. ```csharp var artists = context.Artists .Where(e => e.Name.Equals("The Unicorns", StringComparison.CurrentCulture)) .ToList(); ``` EF Core will now throw the following exception. ```csharp Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet() .Where(a => a.Name.Equals( value: "The Unicorns", comparisonType: CurrentCulture))' could not be translated. Additional information: Translation of 'string.Equals' method which takes 'StringComparison' argument is not supported. See for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See for more information. ``` #### Specify transaction ID EF Core exposes a transaction ID for the correlation of transactions across calls. * This ID is typically set by EF Core when a transaction is started. * If the application starts the transaction instead, then this feature allows the application to explicitly set the transaction ID so it is correlated correctly everywhere it is used. ```csharp using (context.Database.UseTransaction(myTransaction, myId)) { ... } ``` #### IPAddress mapping The standard .NET `IPAddress` class is now automatically mapped to a string column for databases that do not already have native support. For example, consider mapping this entity type. ```csharp public class Host { public int Id { get; set; } public IPAddress Address { get; set; } } ``` On SQL Server, the migration will create the following table. ```sql CREATE TABLE [Host] ( [Id] int NOT NULL, [Address] nvarchar(45) NULL, CONSTRAINT [PK_Host] PRIMARY KEY ([Id])); ``` Entities can then be added in the normal way. ```csharp context.AddRange( new Host { Address = IPAddress.Parse("127.0.0.1")}, new Host { Address = IPAddress.Parse("0000:0000:0000:0000:0000:0000:0000:0001")}); ``` And the resulting SQL will insert the normalized IPv4 or IPv6 address. ```sql Executed DbCommand (14ms) [Parameters=[@p0='1', @p1='127.0.0.1' (Size = 45), @p2='2', @p3='::1' (Size = 45)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; INSERT INTO [Host] ([Id], [Address]) VALUES (@p0, @p1), (@p2, @p3); ``` #### Exclude OnConfiguring when scaffolding When a `DbContext` is scaffolded from an existing database, EF Core by default creates an `OnConfiguring` overload with a connection string so that the context is immediately usable. However, this is not useful if you already have a partial class with `OnConfiguring`, or if you are configuring the context some other way. To address this, the scaffolding commands can now be instructed to omit the generation of `OnConfiguring`. ```bash dotnet ef dbcontext scaffold "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Chinook" Microsoft.EntityFrameworkCore.SqlServer --no-onconfiguring ``` Or in the Package Manager Console. ```csharp Scaffold-DbContext 'Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Chinook' Microsoft.EntityFrameworkCore.SqlServer -NoOnConfiguring ``` It is recommended to use [a named connection string and secure storage like User Secrets](https://docs.microsoft.com/en-us/ef/core/managing-schemas/scaffolding#configuration-and-user-secrets). #### Translations for FirstOrDefault on strings The `FirstOrDefault` and similar operators for characters in strings are now translated in a LINQ query. ```csharp context.Customers.Where(c => c.ContactName.FirstOrDefault() == 'A').ToList(); ``` It will be translated to the following SQL when using SQL Server. ```sql SELECT [c].[Id], [c].[ContactName] FROM [Customer] AS [c] WHERE SUBSTRING([c].[ContactName], 1, 1) = N'A' ``` #### Simplify case blocks EF Core now generates better queries with CASE blocks. Let's consider the following LINQ query. ```csharp context.Weapons .OrderBy(w => w.Name.CompareTo("Marcus' Lancer") == 0) .ThenBy(w => w.Id) ``` Previously, the above LINQ would be translated to the following query on SQL Server. ```sql SELECT [w].[Id], [w].[AmmunitionType], [w].[IsAutomatic], [w].[Name], [w].[OwnerFullName], [w].[SynergyWithId] FROM [Weapons] AS [w] ORDER BY CASE WHEN (CASE WHEN [w].[Name] = N'Marcus'' Lancer' THEN 0 WHEN [w].[Name] > N'Marcus'' Lancer' THEN 1 WHEN [w].[Name] < N'Marcus'' Lancer' THEN -1 END = 0) AND CASE WHEN [w].[Name] = N'Marcus'' Lancer' THEN 0 WHEN [w].[Name] > N'Marcus'' Lancer' THEN 1 WHEN [w].[Name] < N'Marcus'' Lancer' THEN -1 END IS NOT NULL THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, [w].[Id]"); ``` But it is now translated to the following query. ```sql SELECT [w].[Id], [w].[AmmunitionType], [w].[IsAutomatic], [w].[Name], [w].[OwnerFullName], [w].[SynergyWithId] FROM [Weapons] AS [w] ORDER BY CASE WHEN ([w].[Name] = N'Marcus'' Lancer') AND [w].[Name] IS NOT NULL THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, [w].[Id]"); ``` ### Preview 7 #### DbContextFactory EF Core 5.0 introduces `AddDbContextFactory` and `AddPooledDbContextFactory` to register a factory for creating `DbContext` instances in the application's dependency injection container. ```csharp services.AddDbContextFactory(b => b.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test")); ``` Application services such as ASP.NET Core controllers can then depend on `IDbContextFactory` in the service constructor. ```csharp public class MyController { private readonly IDbContextFactory _contextFactory; public MyController(IDbContextFactory contextFactory) { _contextFactory = contextFactory; } } ``` `DbContext` instances can then be created and used as needed. ```csharp public void DoSomeThing() { using (var context = _contextFactory.CreateDbContext()) { // ... } } ``` The `DbContext` instances created in this way are *not* managed by the application's service provider and therefore must be disposed of by the application. * This decoupling is very useful for Blazor applications, where using `IDbContextFactory` is recommended, but may also be useful in other scenarios. * DbContext instances can be pooled by calling `AddPooledDbContextFactory`. * This pooling works the same way as for `AddDbContextPool`, and also has the same limitations. #### Reset DbContext state EF Core 5.0 introduces `ChangeTracker.Clear()` which clears the `DbContext` of all tracked entities. * This should usually not be needed when using the best practice of creating a new, short-lived context instance for each unit-of-work. * However, if there is a need to reset the state of a `DbContext` instance, then using the new `Clear()` method is more performant and robust than mass-detaching all entities. #### New pattern for store-generated defaults EF Core allows an explicit value to be set for a column that may also have default value constraints. * EF Core uses the CLR default of type property type as a sentinel for this; if the value is not the CLR default, then it is inserted, otherwise, the database default is used. * This creates problems for types where the CLR default is not a good sentinel--most notably, `bool` properties. EF Core 5.0 now allows the backing field to be nullable for cases like this. ```csharp public class Blog { private bool? _isValid; public bool IsValid { get => _isValid ?? false; set => _isValid = value; } } ``` The backing field is nullable, but the publicly exposed property is not. * It allows the sentinel value to be `null` without impacting the public surface of the entity type. * In this case, if the `IsValid` is never set, then the database default will be used since the backing field remains null. * If either `true` or `false` are set, then this value is saved explicitly to the database. #### Cosmos partition keys EF Core allows the Cosmos partition key is included in the EF model. ```csharp modelBuilder.Entity().HasPartitionKey(b => b.AlternateKey) ``` Starting with preview 7, the partition key is included in the entity type's PK and is used to improved performance in some queries. #### Cosmos configuration EF Core 5.0 improves the configuration of Cosmos and Cosmos connections. * Previously, EF Core required the end-point and key to be specified explicitly when connecting to a Cosmos database. * EF Core 5.0 allows the use of a connection string instead. * In addition, EF Core 5.0 allows the WebProxy instance to be explicitly set. ```csharp protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseCosmos("my-cosmos-connection-string", "MyDb", cosmosOptionsBuilder => { cosmosOptionsBuilder.WebProxy(myProxyInstance); }); ``` Many other timeout values, limits, etc. can now also be configured. ```csharp protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseCosmos("my-cosmos-connection-string", "MyDb", cosmosOptionsBuilder => { cosmosOptionsBuilder.LimitToEndpoint(); cosmosOptionsBuilder.RequestTimeout(requestTimeout); cosmosOptionsBuilder.OpenTcpConnectionTimeout(timeout); cosmosOptionsBuilder.IdleTcpConnectionTimeout(timeout); cosmosOptionsBuilder.GatewayModeMaxConnectionLimit(connectionLimit); cosmosOptionsBuilder.MaxTcpConnectionsPerEndpoint(connectionLimit); cosmosOptionsBuilder.MaxRequestsPerTcpConnection(requestLimit); }); ``` Finally, the default connection mode is now `ConnectionMode.Gateway`, which is generally more compatible. #### Scaffold-DbContext now singularizes Previously when scaffolding a DbContext from an existing database, EF Core will create entity type names that match the table names in the database. For example, tables `People` and `Addresses` resulted in entity types named `People` and `Addresses`. In previous releases, this behavior was configurable through the registration of a pluralization service. Now in EF Core 5.0, the [Humanizer](https://www.nuget.org/packages/Humanizer.Core/) package is used as a default pluralization service. This means tables `People` and `Addresses` will now be reverse engineered to entity types named `Person` and `Address`. #### Savepoints EF Core now supports [savepoints](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql#remarks) for greater control over transactions that execute multiple operations. Savepoints can be manually created, released, and rolled back. ```csharp context.Database.CreateSavepoint("MySavePoint"); ``` In addition, EF Core will now roll back to the last savepoint when executing `SaveChanges` fails. This allows SaveChanges to be re-tried without re-trying the entire transaction. ### Preview 8 #### Table-per-type (TPT) mapping By default, EF Core maps an inheritance hierarchy of .NET types to a single database table. This is known as table-per-hierarchy (TPH) mapping. EF Core 5.0 also allows mapping each .NET type in an inheritance hierarchy to a different database table; known as table-per-type (TPT) mapping. For example, consider this model with a mapped hierarchy. ```csharp public class Animal { public int Id { get; set; } public string Species { get; set; } } public class Pet : Animal { public string Name { get; set; } } public class Cat : Pet { public string EducationLevel { get; set; } } public class Dog : Pet { public string FavoriteToy { get; set; } } ``` By default, EF Core will map this to a single table. ```sql CREATE TABLE [Animals] ( [Id] int NOT NULL IDENTITY, [Species] nvarchar(max) NULL, [Discriminator] nvarchar(max) NOT NULL, [Name] nvarchar(max) NULL, [EdcuationLevel] nvarchar(max) NULL, [FavoriteToy] nvarchar(max) NULL, CONSTRAINT [PK_Animals] PRIMARY KEY ([Id]) ); ``` However, mapping each entity type to a different table will instead result in one table per type. ```sql CREATE TABLE [Animals] ( [Id] int NOT NULL IDENTITY, [Species] nvarchar(max) NULL, CONSTRAINT [PK_Animals] PRIMARY KEY ([Id]) ); CREATE TABLE [Pets] ( [Id] int NOT NULL, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Pets] PRIMARY KEY ([Id]), CONSTRAINT [FK_Pets_Animals_Id] FOREIGN KEY ([Id]) REFERENCES [Animals] ([Id]) ON DELETE NO ACTION ); CREATE TABLE [Cats] ( [Id] int NOT NULL, [EdcuationLevel] nvarchar(max) NULL, CONSTRAINT [PK_Cats] PRIMARY KEY ([Id]), CONSTRAINT [FK_Cats_Animals_Id] FOREIGN KEY ([Id]) REFERENCES [Animals] ([Id]) ON DELETE NO ACTION, CONSTRAINT [FK_Cats_Pets_Id] FOREIGN KEY ([Id]) REFERENCES [Pets] ([Id]) ON DELETE NO ACTION ); CREATE TABLE [Dogs] ( [Id] int NOT NULL, [FavoriteToy] nvarchar(max) NULL, CONSTRAINT [PK_Dogs] PRIMARY KEY ([Id]), CONSTRAINT [FK_Dogs_Animals_Id] FOREIGN KEY ([Id]) REFERENCES [Animals] ([Id]) ON DELETE NO ACTION, CONSTRAINT [FK_Dogs_Pets_Id] FOREIGN KEY ([Id]) REFERENCES [Pets] ([Id]) ON DELETE NO ACTION ); ``` The creation of the foreign key constraints shown above was added after branching the code for preview 8. Entity types can be mapped to different tables using mapping attributes. ```csharp [Table("Animals")] public class Animal { public int Id { get; set; } public string Species { get; set; } } [Table("Pets")] public class Pet : Animal { public string Name { get; set; } } [Table("Cats")] public class Cat : Pet { public string EdcuationLevel { get; set; } } [Table("Dogs")] public class Dog : Pet { public string FavoriteToy { get; set; } } ``` Or using `ModelBuilder` configuration. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity().ToTable("Animals"); modelBuilder.Entity().ToTable("Pets"); modelBuilder.Entity().ToTable("Cats"); modelBuilder.Entity().ToTable("Dogs"); } ``` #### Migrations: Rebuild SQLite tables Compared to other databases SQLite is relatively limited in its schema manipulation capabilities. For example, dropping a column from an existing table requires that the entire table be dropped and re-created. EF Core 5.0 Migrations now supports automatic rebuilding of the table for schema changes that require it. For example, imagine we have a `Unicorns` table created for a `Unicorn` entity type. ```csharp public class Unicorn { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } } ``` It will translate to the following SQL. ```sql CREATE TABLE "Unicorns" ( "Id" INTEGER NOT NULL CONSTRAINT "PK_Unicorns" PRIMARY KEY AUTOINCREMENT, "Name" TEXT NULL, "Age" INTEGER NOT NULL ); ``` We then learn that storing the age of a unicorn is considered very rude, so let's remove that property, add a new migration, and update the database. This update will fail when using EF Core 3.1 because the column cannot be dropped. In EF Core 5.0, Migrations will instead rebuild the table. ```sql CREATE TABLE "ef_temp_Unicorns" ( "Id" INTEGER NOT NULL CONSTRAINT "PK_Unicorns" PRIMARY KEY AUTOINCREMENT, "Name" TEXT NULL ); INSERT INTO "ef_temp_Unicorns" ("Id", "Name") SELECT "Id", "Name" FROM Unicorns; PRAGMA foreign_keys = 0; DROP TABLE "Unicorns"; ALTER TABLE "ef_temp_Unicorns" RENAME TO "Unicorns"; PRAGMA foreign_keys = 1; ``` * A temporary table is created with the desired schema for the new table * Data is copied from the current table into the temporary table * Foreign key enforcement is switched off * The current table is dropped * The temporary table is renamed to be the new table #### Table-valued functions EF Core 5.0 includes first-class support for mapping .NET methods to table-valued functions (TVFs). These functions can then be used in LINQ queries where additional composition on the results of the function will also be translated to SQL. For example, consider this TVF defined in a SQL Server database. ```sql CREATE FUNCTION GetReports(@employeeId int) RETURNS @reports TABLE ( Name nvarchar(50) NOT NULL, IsDeveloper bit NOT NULL ) AS BEGIN WITH cteEmployees AS ( SELECT Id, Name, ManagerId, IsDeveloper FROM Employees WHERE Id = @employeeId UNION ALL SELECT e.Id, e.Name, e.ManagerId, e.IsDeveloper FROM Employees e INNER JOIN cteEmployees cteEmp ON cteEmp.Id = e.ManagerId ) INSERT INTO @reports SELECT Name, IsDeveloper FROM cteEmployees WHERE Id != @employeeId RETURN END ``` The EF Core model requires two entity types to use this TVF: * An `Employee` type that maps to the Employees table in the normal way * A `Report` type that matches the shape returned by the TVF ```csharp public class Employee { public int Id { get; set; } public string Name { get; set; } public bool IsDeveloper { get; set; } public int? ManagerId { get; set; } public virtual Employee Manager { get; set; } } ``` ``` public class Report { public string Name { get; set; } public bool IsDeveloper { get; set; } } ``` These types must be included in the EF Core model. ```csharp modelBuilder.Entity(); modelBuilder.Entity(typeof(Report)).HasNoKey(); ``` As you can see that the `Report` has no primary key and so must be configured as such. Finally, a .NET method must be mapped to the TVF in the database. This method can be defined on the DbContext using the new `FromExpression` method. ```csharp public IQueryable GetReports(int managerId) => FromExpression(() => GetReports(managerId)); ``` This method uses a parameter and return type that matches the TVF defined above. The method is then added to the EF Core model in OnModelCreating. ```csharp modelBuilder.HasDbFunction(() => GetReports(default)); ``` Using a lambda here is an easy way to pass the `MethodInfo` to EF Core. The arguments passed to the method are ignored. We can now write queries that call `GetReports` and compose over the results. ```csharp from e in context.Employees from rc in context.GetReports(e.Id) where rc.IsDeveloper == true select new { ManagerName = e.Name, EmployeeName = rc.Name, }) ``` On SQL Server, it translates to the following SQL ```sql SELECT [e].[Name] AS [ManagerName], [g].[Name] AS [EmployeeName] FROM [Employees] AS [e] CROSS APPLY [dbo].[GetReports]([e].[Id]) AS [g] WHERE [g].[IsDeveloper] = CAST(1 AS bit) ``` The SQL is rooted in the `Employees` table, calls `GetReports`, and then adds an additional `WHERE` clause on the results of the function. #### Flexible query/update mapping EF Core 5.0 allows mapping the same entity type to different database objects. These objects may be tables, views, or functions. For example, an entity type can be mapped to both a database view and a database table. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder .Entity() .ToTable("Blogs") .ToView("BlogsView"); } ``` By default, EF Core will then query from the view and send updates to the table. For example, executing the following code. ```csharp var blog = context.Set().Single(e => e.Name == "One Unicorn"); blog.Name = "1unicorn2"; context.SaveChanges(); ``` Results in a query against the view, and then an update to the table. ```sql SELECT TOP(2) [b].[Id], [b].[Name], [b].[Url] FROM [BlogsView] AS [b] WHERE [b].[Name] = N'One Unicorn' SET NOCOUNT ON; UPDATE [Blogs] SET [Name] = @p0 WHERE [Id] = @p1; SELECT @@ROWCOUNT; ``` #### Context-wide split-query configuration The split queries can now be configured as the default for any query executed by the DbContext. This configuration is only available for relational providers, and so must be specified as part of the `UseProvider` configuration. ```csharp protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseSqlServer( Your.SqlServerConnectionString, b => b.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)); ``` #### PhysicalAddress mapping The standard .NET [PhysicalAddress class](https://docs.microsoft.com/en-us/dotnet/api/system.net.networkinformation.physicaladdress) is now automatically mapped to a string column for databases that do not already have native support. For more information, see the examples for `IPAddress` above. ### RC1 #### Many-to-many EF Core 5.0 supports many-to-many relationships without explicitly mapping the join table. For example, consider these entity types. ```csharp public class Post { public int Id { get; set; } public string Name { get; set; } public ICollection Tags { get; set; } } public class Tag { public int Id { get; set; } public string Text { get; set; } public ICollection Posts { get; set; } } ``` As you can see that the `Post` class contains a collection of `Tags`, and `Tag` class contains a collection of `Posts`. EF Core 5.0 recognizes this as a many-to-many relationship by convention. This means no code is required in `OnModelCreating`. ```csharp public class BlogContext : DbContext { public DbSet Posts { get; set; } public DbSet Tags { get; set; } } ``` When Migrations or `EnsureCreated` are used to create the database, EF Core will automatically create the join table. On SQL Server, it will translate to this model. ```sql CREATE TABLE [Posts] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Posts] PRIMARY KEY ([Id]) ); CREATE TABLE [Tag] ( [Id] int NOT NULL IDENTITY, [Text] nvarchar(max) NULL, CONSTRAINT [PK_Tag] PRIMARY KEY ([Id]) ); CREATE TABLE [PostTag] ( [PostsId] int NOT NULL, [TagsId] int NOT NULL, CONSTRAINT [PK_PostTag] PRIMARY KEY ([PostsId], [TagsId]), CONSTRAINT [FK_PostTag_Posts_PostsId] FOREIGN KEY ([PostsId]) REFERENCES [Posts] ([Id]) ON DELETE CASCADE, CONSTRAINT [FK_PostTag_Tag_TagsId] FOREIGN KEY ([TagsId]) REFERENCES [Tag] ([Id]) ON DELETE CASCADE ); CREATE INDEX [IX_PostTag_TagsId] ON [PostTag] ([TagsId]); ``` Creating and associating `Tag` and `Post` entities will result in join table updates happening automatically. ```csharp var beginnerTag = new Tag {Text = "Beginner"}; var advancedTag = new Tag {Text = "Advanced"}; var efCoreTag = new Tag {Text = "EF Core"}; context.AddRange( new Post {Name = "EF Core 101", Tags = new List {beginnerTag, efCoreTag}}, new Post {Name = "Writing an EF database provider", Tags = new List {advancedTag, efCoreTag}}, new Post {Name = "Savepoints in EF Core", Tags = new List {beginnerTag, efCoreTag}}); context.SaveChanges(); ``` After inserting the `Posts` and `Tags`, EF will then automatically create rows in the join table. For example, on SQL Server. ```sql SET NOCOUNT ON; INSERT INTO [PostTag] ([PostsId], [TagsId]) VALUES (@p6, @p7), (@p8, @p9), (@p10, @p11), (@p12, @p13), (@p14, @p15), (@p16, @p17); ``` For queries, `Include` and other query operations work just like for any other relationship. ```csharp foreach (var post in context.Posts.Include(e => e.Tags)) { Console.Write($"Post \"{post.Name}\" has tags"); foreach (var tag in post.Tags) { Console.Write($" '{tag.Text}'"); } } ``` The SQL generated uses the join table automatically to bring back all related `Tags`. ```sql SELECT [p].[Id], [p].[Name], [t0].[PostsId], [t0].[TagsId], [t0].[Id], [t0].[Text] FROM [Posts] AS [p] LEFT JOIN ( SELECT [p0].[PostsId], [p0].[TagsId], [t].[Id], [t].[Text] FROM [PostTag] AS [p0] INNER JOIN [Tag] AS [t] ON [p0].[TagsId] = [t].[Id] ) AS [t0] ON [p].[Id] = [t0].[PostsId] ORDER BY [p].[Id], [t0].[PostsId], [t0].[TagsId], [t0].[Id] ``` Unlike EF6, EF Core allows full customization of the join table. For example, the code below configures a many-to-many relationship that also has navigations to the join entity, and in which the join entity contains a payload property. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder .Entity() .HasMany(p => p.Tags) .WithMany(p => p.Posts) .UsingEntity( j => j .HasOne(pt => pt.Tag) .WithMany() .HasForeignKey(pt => pt.TagId), j => j .HasOne(pt => pt.Post) .WithMany() .HasForeignKey(pt => pt.PostId), j => { j.Property(pt => pt.PublicationDate).HasDefaultValueSql("CURRENT_TIMESTAMP"); j.HasKey(t => new { t.PostId, t.TagId }); }); } ``` The support for scaffolding many-to-many relationships from the database is not yet added. #### Map entity types to queries Entity types are commonly mapped to tables or views such that EF Core will pull back the contents of the table or view when querying for that type. * EF Core 5.0 allows an entity type to be mapped to a **defining query**. * This was partially supported in previous versions, but is much improved and has different syntax in EF Core 5.0 For example, consider two tables; one with modern posts; the other with legacy posts. The modern posts table has some additional columns, but for our application we want both modern and legacy posts to be combined and mapped to an entity type with all necessary properties. ```csharp public class Post { public int Id { get; set; } public string Name { get; set; } public string Category { get; set; } public int BlogId { get; set; } public Blog Blog { get; set; } } ``` In EF Core 5.0, `ToSqlQuery` can be used to map this entity type to a query that pulls and combines rows from both tables. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity().ToSqlQuery( @"SELECT Id, Name, Category, BlogId FROM posts UNION ALL SELECT Id, Name, ""Legacy"", BlogId from legacy_posts"); } ``` The `legacy_posts` table does not have a `Category` column, so we instead synthesize a default value for all legacy posts. This entity type can then be used in the normal way for LINQ queries as shown below. ```csharp var posts = context.Posts.Where(e => e.Blog.Name.Contains("Unicorn")).ToList(); ``` Generates the following SQL on SQLite. ```sql SELECT "p"."Id", "p"."BlogId", "p"."Category", "p"."Name" FROM ( SELECT Id, Name, Category, BlogId FROM posts UNION ALL SELECT Id, Name, "Legacy", BlogId from legacy_posts ) AS "p" INNER JOIN "Blogs" AS "b" ON "p"."BlogId" = "b"."Id" WHERE ('Unicorn' = '') OR (instr("b"."Name", 'Unicorn') > 0) ``` The query configured for the entity type is used as a starting for composing the full LINQ query. #### Event counters The .NET event counters are a way to efficiently expose performance metrics from an application. EF Core 5.0 includes event counters under the `Microsoft.EntityFrameworkCore` category. ```csharp dotnet counters monitor Microsoft.EntityFrameworkCore -p 49496 ``` This tells dotnet counters to start collecting EF Core events for process 49496. This generates output like this in the console. ```csharp [Microsoft.EntityFrameworkCore] Active DbContexts 1 Execution Strategy Operation Failures (Count / 1 sec) 0 Execution Strategy Operation Failures (Total) 0 Optimistic Concurrency Failures (Count / 1 sec) 0 Optimistic Concurrency Failures (Total) 0 Queries (Count / 1 sec) 1,755 Queries (Total) 98,402 Query Cache Hit Rate (%) 100 SaveChanges (Count / 1 sec) 0 SaveChanges (Total) 1 ``` #### Property bags EF Core 5.0 allows the same CLR type to be mapped to multiple different entity types. Such types are known as shared-type entity types. This feature combined with indexer properties (included in preview 1) allows property bags to be used as the entity type. For example, the `DbContext` below configures the BCL type `Dictionary` as a shared-type entity type for both products and categories. ```csharp public class ProductsContext : DbContext { public DbSet> Products => Set>("Product"); public DbSet> Categories => Set>("Category"); protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.SharedTypeEntity>("Category", b => { b.IndexerProperty("Description"); b.IndexerProperty("Id"); b.IndexerProperty("Name").IsRequired(); }); modelBuilder.SharedTypeEntity>("Product", b => { b.IndexerProperty("Id"); b.IndexerProperty("Name").IsRequired(); b.IndexerProperty("Description"); b.IndexerProperty("Price"); b.IndexerProperty("CategoryId"); b.HasOne("Category", null).WithMany(); }); } } ``` Dictionary objects ("property bags") can now be added to the context as entity instances and saved. ```csharp var beverages = new Dictionary { ["Name"] = "Beverages", ["Description"] = "Stuff to sip on" }; context.Categories.Add(beverages); context.SaveChanges(); ``` These entities can then be queried and updated in the normal way. ```csharp var foods = context.Categories.Single(e => e["Name"] == "Foods"); var marmite = context.Products.Single(e => e["Name"] == "Marmite"); marmite["CategoryId"] = foods["Id"]; marmite["Description"] = "Yummy when spread _thinly_ on buttered Toast!"; context.SaveChanges(); ``` #### SaveChanges interception and events EF Core 5.0 introduces both .NET events and an EF Core interceptor triggered when `SaveChanges` is called. The events are simple to use as shown below. ```csharp context.SavingChanges += (sender, args) => { Console.WriteLine($"Saving changes for {((DbContext)sender).Database.GetConnectionString()}"); }; context.SavedChanges += (sender, args) => { Console.WriteLine($"Saved {args.EntitiesSavedCount} changes for {((DbContext)sender).Database.GetConnectionString()}"); }; ``` * The event `sender` is the `DbContext` instance. * The `args` for the `SavedChanges` event contains the number of entities saved to the database. The interceptor is defined by `ISaveChangesInterceptor`, but it is often convenient to inherit from `SaveChangesInterceptor` to avoid implementing every method. ```csharp public class MySaveChangesInterceptor : SaveChangesInterceptor { public override InterceptionResult SavingChanges( DbContextEventData eventData, InterceptionResult result) { Console.WriteLine($"Saving changes for {eventData.Context.Database.GetConnectionString()}"); return result; } public override ValueTask> SavingChangesAsync( DbContextEventData eventData, InterceptionResult result, CancellationToken cancellationToken = new CancellationToken()) { Console.WriteLine($"Saving changes asynchronously for {eventData.Context.Database.GetConnectionString()}"); return new ValueTask>(result); } } ``` * The interceptor has both sync and async methods. This can be useful if you need to perform async I/O, such as writing to an audit server. * The interceptor allows `SaveChanges` being skipped using the `InterceptionResult` mechanism common to all interceptors. The downside of interceptors is that they must be registered on the DbContext when it is being constructed. ```csharp protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .AddInterceptors(new MySaveChangesInterceptor()) .UseSqlite("Data Source = test.db"); ``` In contrast, the events can be registered on the `DbContext` instance at any time. #### Exclude tables from migrations It is sometimes useful to have a single entity type mapped in multiple DbContexts. This is especially true when using [bounded contexts](https://www.martinfowler.com/bliki/BoundedContext.html), for which it is common to have a different DbContext type for each bounded context. For example, a `User` type may be needed by both an authorization context and a reporting context. If a change is made to the `User` type, then migrations for both DbContexts will attempt to update the database. To prevent this, the model for one of the contexts can be configured to exclude the table from its migrations. In the code below, the `AuthorizationContext` will generate migrations for changes to the `Users` table, but the `ReportingContext` will not, preventing the migrations from clashing. ```csharp public class AuthorizationContext : DbContext { public DbSet Users { get; set; } } public class ReportingContext : DbContext { public DbSet Users { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity().ToTable("Users", t => t.ExcludeFromMigrations()); } } ``` #### Required 1:1 dependents In EF Core 3.1, the dependent end of a one-to-one relationship was always considered optional. This was most apparent when using owned entities. For example, consider the following model. ```csharp public class Person { public int Id { get; set; } public string Name { get; set; } public Address HomeAddress { get; set; } public Address WorkAddress { get; set; } } public class Address { public string Line1 { get; set; } public string Line2 { get; set; } public string City { get; set; } public string Region { get; set; } public string Country { get; set; } public string Postcode { get; set; } } ``` Here is the configuration for the above model. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(b => { b.OwnsOne(e => e.HomeAddress, b => { b.Property(e => e.Line1).IsRequired(); b.Property(e => e.City).IsRequired(); b.Property(e => e.Region).IsRequired(); b.Property(e => e.Postcode).IsRequired(); }); b.OwnsOne(e => e.WorkAddress); }); } ``` Migrations will create the following table for SQLite. ```sql CREATE TABLE "People" ( "Id" INTEGER NOT NULL CONSTRAINT "PK_People" PRIMARY KEY AUTOINCREMENT, "Name" TEXT NULL, "HomeAddress_Line1" TEXT NULL, "HomeAddress_Line2" TEXT NULL, "HomeAddress_City" TEXT NULL, "HomeAddress_Region" TEXT NULL, "HomeAddress_Country" TEXT NULL, "HomeAddress_Postcode" TEXT NULL, "WorkAddress_Line1" TEXT NULL, "WorkAddress_Line2" TEXT NULL, "WorkAddress_City" TEXT NULL, "WorkAddress_Region" TEXT NULL, "WorkAddress_Country" TEXT NULL, "WorkAddress_Postcode" TEXT NULL ); ``` As you can see that all the columns are nullable, even though some of the `HomeAddress` properties have been configured as required. Also, when querying for a `Person`, if all the columns for either the home or work address are null, then EF Core will leave the `HomeAddress` and/or `WorkAddress` properties as null, rather than setting an empty instance of `Address`. In EF Core 5.0, the `HomeAddress` navigation can now be configured as a required dependency. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(b => { b.OwnsOne(e => e.HomeAddress, b => { b.Property(e => e.Line1).IsRequired(); b.Property(e => e.City).IsRequired(); b.Property(e => e.Region).IsRequired(); b.Property(e => e.Postcode).IsRequired(); }); b.Navigation(e => e.HomeAddress).IsRequired(); b.OwnsOne(e => e.WorkAddress); }); } ``` The table created by Migrations will now include non-nullable columns for the required properties of the required dependent. ```sql CREATE TABLE "People" ( "Id" INTEGER NOT NULL CONSTRAINT "PK_People" PRIMARY KEY AUTOINCREMENT, "Name" TEXT NULL, "HomeAddress_Line1" TEXT NOT NULL, "HomeAddress_Line2" TEXT NULL, "HomeAddress_City" TEXT NOT NULL, "HomeAddress_Region" TEXT NOT NULL, "HomeAddress_Country" TEXT NULL, "HomeAddress_Postcode" TEXT NOT NULL, "WorkAddress_Line1" TEXT NULL, "WorkAddress_Line2" TEXT NULL, "WorkAddress_City" TEXT NULL, "WorkAddress_Region" TEXT NULL, "WorkAddress_Country" TEXT NULL, "WorkAddress_Postcode" TEXT NULL ); ``` EF Core will now throw an exception if an attempt is made to save an owner that has a null required dependent. In this example, EF Core will throw when attempting to save a `Person` with a null `HomeAddress`. Finally, EF Core will still create an instance of a required dependent even when all the columns for the required dependent have null values. #### Options for migration generation EF Core 5.0 introduces greater control over the generation of migrations for different purposes. This includes the ability to: * Know if the migration is being generated for a script or for immediate execution * Know if an idempotent script is being generated * Know if the script should exclude transaction statements (See *Migrations scripts with transactions* below.) This behavior is specified by the `MigrationsSqlGenerationOptions` enum, which can now be passed to `IMigrator.GenerateScript`. It also included the better generation of idempotent scripts with calls to `EXEC` on SQL Server when needed. It also enables similar improvements to the scripts generated by other database providers, including PostgreSQL. #### Migrations scripts with transactions SQL scripts generated from migrations now contain statements to begin and commit transactions as appropriate for the migration. For example, the migration script below was generated from two migrations. Notice that each migration is now applied inside a transaction. ```sql BEGIN TRANSACTION; GO CREATE TABLE [Groups] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Groups] PRIMARY KEY ([Id]) ); GO CREATE TABLE [Members] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, [GroupId] int NULL, CONSTRAINT [PK_Members] PRIMARY KEY ([Id]), CONSTRAINT [FK_Members_Groups_GroupId] FOREIGN KEY ([GroupId]) REFERENCES [Groups] ([Id]) ON DELETE NO ACTION ); GO CREATE INDEX [IX_Members_GroupId] ON [Members] ([GroupId]); GO INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20200910194835_One', N'6.0.0-alpha.1.20460.2'); GO COMMIT; GO BEGIN TRANSACTION; GO EXEC sp_rename N'[Groups].[Name]', N'GroupName', N'COLUMN'; GO INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20200910195234_Two', N'6.0.0-alpha.1.20460.2'); GO COMMIT; ``` As mentioned in the previous section, this use of transactions can be disabled if transactions need to be handled differently. #### See pending migrations The `dotnet ef migrations list` command now shows which migrations have not yet been applied to the database. ```csharp ajcvickers@avickers420u:~/AllTogetherNow/Daily$ dotnet ef migrations list Build started... Build succeeded. 20200910201647_One 20200910201708_Two 20200910202050_Three (Pending) ajcvickers@avickers420u:~/AllTogetherNow/Daily$ ``` There is now a `Get-Migration` command for the **Package Manager Console** with the same functionality. #### ModelBuilder API for value comparers EF Core properties for custom mutable types [require a value comparer](https://docs.microsoft.com/en-us/ef/core/modeling/value-comparers) for property changes to be detected correctly. This can now be specified as part of configuring the value conversion for the type. ```csharp modelBuilder .Entity() .Property(e => e.MyProperty) .HasConversion( v => JsonSerializer.Serialize(v, null), v => JsonSerializer.Deserialize>(v, null), new ValueComparer>( (c1, c2) => c1.SequenceEqual(c2), c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())), c => c.ToList())); ``` #### EntityEntry TryGetValue methods A `TryGetValue` method has been added to `EntityEntry.CurrentValues` and `EntityEntry.OriginalValues`. This allows the value of a property to be requested without first checking if the property is mapped in the EF model. ```csharp if (entry.CurrentValues.TryGetValue(propertyName, out var value)) { Console.WriteLine(value); } ``` #### Default max batch size for SQL Server Starting with EF Core 5.0, the default maximum batch size for `SaveChanges` on SQL Server is now 42. As is well known, this is also the answer to the Ultimate Question of Life, the Universe, and Everything. However, this is probably a coincidence, since the value was obtained through [analysis of batching performance](https://github.com/dotnet/efcore/issues/9270). We do not believe that we have discovered a form of the Ultimate Question, although it does seem somewhat plausible that the Earth was created to understand why SQL Server works the way it does. #### Default environment to Development The EF Core command-line tools now automatically configure the `ASPNETCORE_ENVIRONMENT` *and* `DOTNET_ENVIRONMENT` environment variables to "Development". This brings the experience when using the generic host in line with the experience for ASP.NET Core during development. #### Better migrations column ordering The columns for unmapped base classes are now ordered after other columns for mapped entity types. Note this only impacts newly created tables. The column order for existing tables remains unchanged. #### Query improvements EF Core 5.0 RC1 contains some additional query translation improvements: * Translation of `is` on Cosmos. * User-mapped functions can now be annotated to control null propagation. * Support for translation of GroupBy with conditional aggregates. * Translation of Distinct operator over group element before aggregate. #### Model building for fields Finally, for RC1, EF Core now allows the use of the lambda methods in the `ModelBuilder` for fields as well as properties. For example, if you are averse to properties for some reason and decide to use public fields, then these fields can now be mapped using the lambda builders. ```csharp public class Post { public int Id; public string Name; public string Category; public int BlogId; public Blog Blog; } public class Blog { public int Id; public string Name; public ICollection Posts; } ``` The configuaration are as follows. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(b => { b.Property(e => e.Id); b.Property(e => e.Name); }); modelBuilder.Entity(b => { b.Property(e => e.Id); b.Property(e => e.Name); b.Property(e => e.Category); b.Property(e => e.BlogId); b.HasOne(e => e.Blog).WithMany(e => e.Posts); }); } ``` While this is now possible, we are certainly not recommending that you do this. Also, note that this does not add any additional field mapping capabilities to EF Core, it only allows the lambda methods to be used instead of always requiring the string methods. This is seldom useful since fields are rarely public. # Simple Logging [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Simple Logging Simple Logging is the equivalent of `Database.Log` in EF6. It provides a simple way to get logs from EF Core without the need to configure any kind of external logging framework. EF Core replaces `Database.Log` with a `LogTo` method called on `DbContextOptionsBuilder` in either `AddDbContext` or `OnConfiguring`. ```csharp protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.LogTo(Console.WriteLine); ``` There are multiple overloads available which can be used in different use cases. * Set the minimum log level * Example: `.LogTo(Console.WriteLine, LogLevel.Information)` * Filter for only specific events: * Example: `.LogTo(Console.WriteLine, new[] {CoreEventId.ContextInitialized, RelationalEventId.CommandExecuted})` * Filter for all events in specific categories: * Example: `.LogTo(Console.WriteLine, new[] {DbLoggerCategory.Database.Name}, LogLevel.Information)` * Use a custom filter over event and level: * Example: `.LogTo(Console.WriteLine, (id, level) => id == RelationalEventId.CommandExecuting)` Output format can be minimally configured (API is in flux) but the default output looks something like: ```bash warn: 12/5/2019 09:57:47.574 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development. dbug: 12/5/2019 09:57:47.581 CoreEventId.ShadowPropertyCreated[10600] (Microsoft.EntityFrameworkCore.Model.Validation) The property 'BlogId' on entity type 'Post' was created in shadow state because there are no eligible CLR members with a matching name. info: 12/5/2019 09:57:47.618 CoreEventId.ContextInitialized[10403] (Microsoft.EntityFrameworkCore.Infrastructure) Entity Framework Core 5.0.0-dev initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled dbug: 12/5/2019 09:57:47.644 CoreEventId.ValueGenerated[10808] (Microsoft.EntityFrameworkCore.ChangeTracking) 'BloggingContext' generated temporary value '-2147482647' for the 'Id' property of new 'Blog' entity. ... ``` # Filtered Included [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Filtered Included The Include method now supports filtering of the entities included. When applying Include to load related data, you can apply certain enumerable operations on the included collection navigation, which allows for filtering and sorting of the results. The supported operations are `Where`, `OrderBy`, `OrderByDescending`, `ThenBy`, `ThenByDescending`, `Skip`, and `Take` which should be applied on the collection navigation in the lambda passed to the `Include` method, as shown in the below example. ```csharp var blogs = context.Blogs .Include(e => e.Posts.Where(p => p.Title.Contains("Cheese"))) .ToList(); ``` The above query will return blogs together with each associated post, but only when the post title contains "Cheese". You can also use `Skip` and `Take` methods to reduce the number of included entities. ```csharp var blogs = context.Blogs .Include(e => e.Posts.OrderByDescending(post => post.Title).Take(5))) .ToList(); ``` This query will return blogs with at most five posts included on each blog. # Backing Fields [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Backing Fields Backing fields allow EF to read and/or write to a field rather than a property. This can be useful when encapsulation in the class is being used to restrict the use of and/or enhance the semantics around access to the data by application code, but the value should be read from and/or written to the database without using those restrictions/enhancements. ### Basic configuration By convention, the following fields will be discovered as backing fields for a given property (listed in precedence order). * `_` * `_` * `m_` * `m_` In the following sample, the `Url` property is configured to have `_url` as its backing field. ```csharp class MyContext : DbContext { public DbSet Blogs { get; set; } } public class Blog { private string _url; public int BlogId { get; set; } public string Url { get { return _url; } set { _url = value; } } } ``` The backing fields are only discovered for properties that are included in the model. You can also configure backing fields by using a Data Annotation (available in EFCore 5.0) or the Fluent API, e.g. if the field name doesn't correspond to the above conventions: #### Data Annotations ```csharp class MyContext : DbContext { public DbSet Blogs { get; set; } } public class Blog { private string _validatedUrl; public int BlogId { get; set; } [BackingField(nameof(_validatedUrl))] public string Url { get { return _validatedUrl; } } public void SetUrl(string url) { // put your validation code here _validatedUrl = url; } } ``` #### Fluent API ```csharp class MyContext : DbContext { public DbSet Blogs { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property(b => b.Url) .HasField("_validatedUrl"); } } public class Blog { private string _validatedUrl; public int BlogId { get; set; } public string Url { get { return _validatedUrl; } } public void SetUrl(string url) { using (var client = new HttpClient()) { var response = client.GetAsync(url).Result; response.EnsureSuccessStatusCode(); } _validatedUrl = url; } } ``` ### Field and property access By default, EF will always read and write to the backing field. It will assume that one has been properly configured and will never use the property. However, EF also supports other access patterns. For example, the following sample instructs EF to write to the backing field only while materializing and to use the property in all other cases. ```csharp class MyContext : DbContext { public DbSet Blogs { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property(b => b.Url) .HasField("_validatedUrl") .UsePropertyAccessMode(PropertyAccessMode.PreferFieldDuringConstruction); } } public class Blog { private string _validatedUrl; public int BlogId { get; set; } public string Url { get { return _validatedUrl; } } public void SetUrl(string url) { using (var client = new HttpClient()) { var response = client.GetAsync(url).Result; response.EnsureSuccessStatusCode(); } _validatedUrl = url; } } ``` ### Field-only properties You can also create a conceptual property in your model that does not have a corresponding CLR property in the entity class but instead uses a field to store the data in the entity. This is different from Shadow Properties, where the data is stored in the change tracker, rather than in the entity's CLR type. Field-only properties are commonly used when the entity class uses methods instead of properties to get/set values, or in cases where fields shouldn't be exposed at all in the domain model (e.g. primary keys). You can configure a field-only property by providing a name in the `Property(...)` API. ```csharp class MyContext : DbContext { public DbSet Blogs { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property("_validatedUrl"); } } public class Blog { private string _validatedUrl; public int BlogId { get; set; } public string GetUrl() { return _validatedUrl; } public void SetUrl(string url) { using (var client = new HttpClient()) { var response = client.GetAsync(url).Result; response.EnsureSuccessStatusCode(); } _validatedUrl = url; } } ``` EF will attempt to find a CLR property with the given name, or a field if a property isn't found. If neither a property nor a field is found, a shadow property will be set up instead. You may need to refer to a field-only property from LINQ queries, but such fields are typically private. You can use `EF.Property(...)` method in a LINQ query to refer to the field. ```csharp var blogs = db.blogs.OrderBy(b => EF.Property(b, "_validatedUrl")); ``` # Keyless Entity Types [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Keyless Entity Types In addition to regular entity types, an EF Core model can contain *keyless entity types*, which can be used to carry out database queries against data that doesn't contain key values. ### Defining Keyless entity types Keyless entity types can be defined using either the Data Annotation or the Fluent API. #### Data Annotations ```csharp class BlogsContext : DbContext { public DbSet BlogPostCounts { get; set; } } [Keyless] public class BlogPostsCount { public string BlogName { get; set; } public int PostCount { get; set; } } ``` #### Fluent API ```csharp class BlogsContext : DbContext { public DbSet BlogPostCounts { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .HasNoKey(); } } public class BlogPostsCount { public string BlogName { get; set; } public int PostCount { get; set; } } ``` #### Keyless entity types characteristics Keyless entity types support many of the same mapping capabilities as regular entity types, like inheritance mapping and navigation properties. On relational stores, they can configure the target database objects and columns via fluent API methods or data annotations. However, they are different from regular entity types, such as: * It cannot have a key defined. * Are never tracked for changes in the *DbContext* and therefore are never inserted, updated or deleted on the database. * Are never discovered by convention. * Only support a subset of navigation mapping capabilities, specifically: * They may never act as the principal end of a relationship. * They may not have navigations to owned entities * They can only contain reference navigation properties pointing to regular entities. * Entities cannot contain navigation properties to keyless entity types. * Need to be configured with a `[Keyless]` data annotation or a `.HasNoKey()` method call. * May be mapped to a *defining query*. A defining query is a query declared in the model that acts as a data source for a keyless entity type. #### Usage scenarios Some of the main usage scenarios for keyless entity types are: * Serving as the return type for raw SQL queries. * Mapping to database views that do not contain a primary key. * Mapping to tables that do not have a primary key defined. * Mapping to queries defined in the model. #### Mapping to database objects Mapping a keyless entity type to a database object is achieved using the `ToTable` or `ToView` fluent API. From the perspective of EF Core, the database object specified in this method is a *view*, meaning that it is treated as a read-only query source and cannot be the target of the update, insert or delete operations. However, this does not mean that the database object is actually required to be a database view. It can alternatively be a database table that will be treated as read-only. Conversely, for regular entity types, EF Core assumes that a database object specified in the `ToTable` method can be treated as a *table*, meaning that it can be used as a query source but also targeted by the update, delete and insert operations. In fact, you can specify the name of a database view in `ToTable` and everything should work fine as long as the view is configured to be updatable on the database. > \[!NOTE] `ToView` assumes that the object already exists in the database and it won't be created by migrations. #### Example The following example shows how to use keyless entity types to query a database view. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder .Entity(eb => { eb.HasNoKey(); eb.ToView("View_BlogPostCounts"); eb.Property(v => v.BlogName).HasColumnName("Name"); }); } ``` # Configure Precision and Scale [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Configure Precision and Scale In EF Core 5.0, you can configure the precision and scale using Fluent API. It tells the database provider how much storage is needed for a given column. It only applies to data types where the provider allows the precision and scale to vary - usually just `decimal` and `DateTime`. * For `decimal` properties, precision defines the maximum number of digits needed to express any value the column will contain, and scale defines the maximum number of decimal places needed. * For `DateTime` properties, precision defines the maximum number of digits needed to express fractions of seconds, and scale is not used. In the following example, configuring the `Score` property to have precision 14 and scale 2 will cause a column of type `decimal(14,2)` to be created on SQL Server, and configuring the `LastUpdated` property to have precision 3 will cause a column of type `datetime2(3)`. ```csharp class MyContext : DbContext { public DbSet Blogs { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property(b => b.Score) .HasPrecision(14, 2); modelBuilder.Entity() .Property(b => b.LastUpdated) .HasPrecision(3); } } public class Blog { public int BlogId { get; set; } public decimal Score { get; set; } public DateTime LastUpdated { get; set; } } ``` > Entity Framework does not do any validation of precision or scale before passing data to the provider. It is up to the provider or data store to validate as appropriate. For example, when targeting SQL Server, a column of data type `datetime` does not allow the precision to be set, whereas a `datetime2` one can have precision between 0 and 7 inclusive. # Translation of Contains on byte arrays [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Translation of Contains on byte arrays Queries using Contains on byte\[] properties are now translated to SQL. ```csharp var blogs = context.Blogs.Where(e => e.Picture.Contains((byte)127)).ToList(); ``` Translates to the following on SQL Server: ```sql info: 12/5/2019 11:42:42.022 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [b].[Id], [b].[Picture], [b].[Title] FROM [Blogs] AS [b] WHERE CHARINDEX(0x7F, [b].[Picture]) > 0 ``` # Many-to-many Relationship [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Many-to-many Relationship Earlier in Entity Framework, the many-to-many relationship was classified as two one-to-many relationships. To make it work the developer must create a joining entity class. Now in Entity Framework Core 5.0, it will have full support for many-to-many relations without explicitly mapping the join table. * The navigation properties skip the join table and directly point to the other entity. * It will result in writing cleaner queries and simplify the use of the query result. Let's consider the following model. ```csharp public class Movie { public int MovieId { get; set; } public string Name{ get; set; } public Actor Actor { get; set; } public List Genres { get; set; } } public class Genre { public int GenreId { get; set; } public string GenreName { get; set; } public List Movies{ get; set; } } ``` As you can see that `Movie` class contains a collection of `Genres`, and `Genre` class contains a collection of `Movies`. EF Core 5.0 recognizes this as a many-to-many relationship by convention and there is no need for configuration in `OnModelCreating`. ```csharp public class MyEntityContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder opBuilder) { opBuilder.UseSqlServer("Data Source=(localdb)\\ProjectsV13;Initial Catalog=MyContextDB;"); } public DbSet Movies { get; set; } public DbSet Genres { get; set; } } ``` When you create migration or call the `EnsureCreated`method, it will create the following tables including the join table. ```sql CREATE TABLE [dbo].[Movies] ( [MovieId] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (MAX) NULL, CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED ([MovieId] ASC) ); CREATE TABLE [dbo].[Genres] ( [GenreId] INT IDENTITY (1, 1) NOT NULL, [GenreName] NVARCHAR (MAX) NULL, CONSTRAINT [PK_Genres] PRIMARY KEY CLUSTERED ([GenreId] ASC) ); CREATE TABLE [dbo].[GenreMovie] ( [GenresGenreId] INT NOT NULL, [MoviesMovieId] INT NOT NULL, CONSTRAINT [PK_GenreMovie] PRIMARY KEY CLUSTERED ([GenresGenreId] ASC, [MoviesMovieId] ASC), CONSTRAINT [FK_GenreMovie_Genres_GenresGenreId] FOREIGN KEY ([GenresGenreId]) REFERENCES [dbo].[Genres] ([GenreId]) ON DELETE CASCADE, CONSTRAINT [FK_GenreMovie_Movies_MoviesMovieId] FOREIGN KEY ([MoviesMovieId]) REFERENCES [dbo].[Movies] ([MovieId]) ON DELETE CASCADE ); ``` Let's insert some movies and genres. ```sql using (var context = new MyEntityContext()) { context.Database.EnsureCreated(); var comedy = new Genre() { GenreName = "Comedy" }; var action = new Genre() { GenreName = "Action" }; var horror = new Genre() { GenreName = "Horror" }; var scifi = new Genre() { GenreName = "Sci-fi" }; context.AddRange( new Movie() { Name = "Avengers", Genres = new List() { action, scifi } }, new Movie() { Name = "Satanic Panic", Genres = new List() { comedy, horror } }); context.SaveChanges(); } ``` EF will then automatically create rows in the join table. ![](/files/-MfYG8akUinwjLkdVC9A) ### References * [EF Core Many to Many](https://www.learnentityframeworkcore.com/conventions/many-to-many-relationship) # Table-per-type (TPT) mapping [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Table-per-type (TPT) mapping Table-per-type inheritance uses a separate table in the database to maintain data for non-inherited properties and key properties for each type in the inheritance hierarchy. * Table per Type is about representing inheritance relationships as relational foreign key associations. * Every class and subclass including abstract classes has its own table. * The table for subclasses contains columns only for each non-inherited property along with a primary key that is also a foreign key of the base class table. By default, EF Core maps an inheritance hierarchy of .NET types to a single database table. This is known as table-per-hierarchy (TPH) mapping. EF Core 5.0 also allows mapping each .NET type in an inheritance hierarchy to a different database table; known as table-per-type (TPT) mapping. Let's consider the following simple model with a mapped hierarchy. ```csharp public class Person { public int Id { get; set; } public string FullName { get; set; } } public class Student : Person { public DateTime EnrollmentDate { get; set; } } public class Teacher : Person { public DateTime HireDate { get; set; } } ``` Here is the context class implementation without any additional configuration. ```csharp public class EntityContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder opBuilder) { opBuilder.UseSqlServer("Data Source=(localdb)\\ProjectsV13;Initial Catalog=PeopleContextDb;"); } public DbSet People { get; set; } public DbSet Students { get; set; } public DbSet Teachers { get; set; } } ``` By default, EF Core will map this to a single table. ```sql CREATE TABLE [dbo].[People] ( [Id] INT IDENTITY (1, 1) NOT NULL, [FullName] NVARCHAR (MAX) NULL, [Discriminator] NVARCHAR (MAX) NOT NULL, [EnrollmentDate] DATETIME2 (7) NULL, [HireDate] DATETIME2 (7) NULL, CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED ([Id] ASC) ); ``` In the TPT mapping pattern, all the types are mapped to individual tables. Properties that belong solely to a base type or derived type are stored in a table that maps to that type. Entity types can be mapped to different tables using mapping attributes. ```csharp [Table("People")] public class Person { public int Id { get; set; } public string FullName { get; set; } } [Table("Students")] public class Student : Person { public DateTime EnrollmentDate { get; set; } } [Table("Teachers")] public class Teacher : Person { public DateTime HireDate { get; set; } } ``` However, mapping each entity type to a different table will instead result in one table per type. ```sql CREATE TABLE [dbo].[People] ( [Id] INT IDENTITY (1, 1) NOT NULL, [FullName] NVARCHAR (MAX) NULL, CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED ([Id] ASC) ); CREATE TABLE [dbo].[Students] ( [Id] INT NOT NULL, [EnrollmentDate] DATETIME2 (7) NOT NULL, CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_Students_People_Id] FOREIGN KEY ([Id]) REFERENCES [dbo].[People] ([Id]) ); CREATE TABLE [dbo].[Teachers] ( [Id] INT NOT NULL, [HireDate] DATETIME2 (7) NOT NULL, CONSTRAINT [PK_Teachers] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_Teachers_People_Id] FOREIGN KEY ([Id]) REFERENCES [dbo].[People] ([Id]) ); ``` Tables that map to derived types also store a foreign key that joins the derived table with the base table. ### Reference * [EF Core - TPT](https://www.learnentityframeworkcore.com/inheritance/table-per-type) # Required one-to-one Dependents [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Required one-to-one Dependents EF Core allows you to model entity types that can only ever appear on navigation properties of other entity types. These are called *owned entity types*. The entity containing an owned entity type is its *owner*. * In EF Core 3.1, the dependent end of a one-to-one relationship was always considered optional. * This was most apparent when using owned entities. Let's consider the following model. ```sql public class Customer { public int Id { get; set; } public string Name { get; set; } public Address HomeAddress { get; set; } public Address BillingAddress { get; set; } } public class Address { public string Street { get; set; } public string City { get; set; } public string State { get; set; } public string Country { get; set; } } ``` Here is the implementation of the context class which contains the configuration. ```sql public class EntityContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder opBuilder) { opBuilder.UseSqlServer("Data Source=(localdb)\\ProjectsV13;Initial Catalog=PeopleContextDb1;"); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(b => { b.OwnsOne(e => e.HomeAddress); b.OwnsOne(e => e.BillingAddress, b => { b.Property(e => e.Street).IsRequired(); b.Property(e => e.City).IsRequired(); b.Property(e => e.State).IsRequired(); }); }); } public DbSet Customers { get; set; } } ``` When migrations or `EnsureCreated` are used to create the database. On SQL Server, it will translate to the following SQL. ```sql CREATE TABLE [dbo].[Customers] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (MAX) NULL, [HomeAddress_Street] NVARCHAR (MAX) NULL, [HomeAddress_City] NVARCHAR (MAX) NULL, [HomeAddress_State] NVARCHAR (MAX) NULL, [HomeAddress_Country] NVARCHAR (MAX) NULL, [BillingAddress_Street] NVARCHAR (MAX) NULL, [BillingAddress_City] NVARCHAR (MAX) NULL, [BillingAddress_State] NVARCHAR (MAX) NULL, [BillingAddress_Country] NVARCHAR (MAX) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([Id] ASC) ); ``` As you can see all the columns are nullable, even though some of the `BillingAddress` properties have been configured as required. * When you query for a `Customer`, and all the columns for any of the addresses are `null`. * EF Core will leave all the properties of that address `null`, instead of setting an empty instance of address. In EF Core 5.0, the `BillingAddress` navigation can now be configured as a required dependent. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(b => { b.OwnsOne(e => e.HomeAddress); b.OwnsOne(e => e.BillingAddress, b => { b.Property(e => e.Street).IsRequired(); b.Property(e => e.City).IsRequired(); b.Property(e => e.State).IsRequired(); }); b.Navigation(e => e.BillingAddress).IsRequired(); }); } ``` Now when you create migration or call the `EnsureCreated` method, you will see that it will now include non-nullable columns for the required properties of the required dependent. ```sql CREATE TABLE [dbo].[Customers] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (MAX) NULL, [HomeAddress_Street] NVARCHAR (MAX) NULL, [HomeAddress_City] NVARCHAR (MAX) NULL, [HomeAddress_State] NVARCHAR (MAX) NULL, [HomeAddress_Country] NVARCHAR (MAX) NULL, [BillingAddress_Street] NVARCHAR (MAX) NOT NULL, [BillingAddress_City] NVARCHAR (MAX) NOT NULL, [BillingAddress_State] NVARCHAR (MAX) NOT NULL, [BillingAddress_Country] NVARCHAR (MAX) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([Id] ASC) ); ``` EF Core will now throw an exception if an attempt is made to save a customer with a null`BillingAddress`. # Support for Fields using Lambda [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Support for Fields using Lambda EF Core 5 allows you to use the lambda methods in the ModelBuilder for fields as well as properties. Let's suppose you don't want to use properties for some reason and decide to use public fields. ```csharp public class Book { public int Id; public string Title; public string Category; public int AuthorId; public Author Author; } public class Author { public int Id; public string Name; public ICollection Books; } ``` In EF Core 5, you can map these fields using the lambda builders as shown below. ```csharp public class EntityContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder opBuilder) { opBuilder.UseSqlServer("Data Source=(localdb)\\ProjectsV13;Initial Catalog=BookContextDb;"); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(b => { b.Property(e => e.Id); b.Property(e => e.Name); }); modelBuilder.Entity(b => { b.Property(e => e.Id); b.Property(e => e.Title); b.Property(e => e.Category); b.Property(e => e.AuthorId); b.HasOne(e => e.Author).WithMany(e => e.Books); }); } public DbSet Blogs { get; set; } public DbSet Posgs { get; set; } } ``` It will create the following tables on the SQL Server. ```sql CREATE TABLE [dbo].[Authors] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (MAX) NULL, CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED ([Id] ASC) ); CREATE TABLE [dbo].[Books] ( [Id] INT IDENTITY (1, 1) NOT NULL, [AuthorId] INT NOT NULL, [Category] NVARCHAR (MAX) NULL, [Title] NVARCHAR (MAX) NULL, CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_Books_Authors_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [dbo].[Authors] ([Id]) ON DELETE CASCADE ); ``` Before EF Core 5, if you try to map these fields using the model builder, It will throw the following exception. ```csharp System.ArgumentException: 'The expression 'e => e.Id' is not a valid property expression. The expression should represent a simple property access: 't => t.MyProperty'. (Parameter 'propertyAccessExpression')' ``` # Drop Column from SQLite Database [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Drop Column from SQLite Database SQLite is relatively limited in its schema manipulation capabilities as compared to other databases. For example, dropping a column from an existing table requires that the entire table be dropped and re-created. In EF Core 5.0, migrations now support the automatic rebuilding of the table for schema changes that require it. Let's suppose we have a simple model that contains a `Book` entity as shown below. ```csharp public class Book { public int Id { get; set; } public string Title { get; set; } public string Category { get; set; } } public class EntityContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder opBuilder) { opBuilder.UseSqlite("Data Source=D:\\Blogging.db"); } public DbSet Books { get; set; } } ``` To create the database, let's run the following migration command in **Package Manager Console**. ```bash PM> Add-Migration Init ``` You will see that migration creates the following script. ```csharp public partial class Init : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.CreateTable( name: "Books", columns: table => new { Id = table.Column(nullable: false) .Annotation("Sqlite:Autoincrement", true), Title = table.Column(nullable: true), Category = table.Column(nullable: true) }, constraints: table => { table.PrimaryKey("PK_Books", x => x.Id); }); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.DropTable( name: "Books"); ``` To apply the above script to the database, run the following command to update the database. ```bash PM> Update-Database ``` You will see that the database is created that contains a `Books` table. Now we want to remove the `Category` property so let's remove that property from the `Book` class as shown below. ```csharp public class Book { public int Id { get; set; } public string Title { get; set; } } ``` Add a new migration using the following migration command. ```csharp PM> Add-Migration RemoveCategory ``` It will create the following script to update the database. ```csharp public partial class RemoveCategory : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.DropColumn( name: "Category", table: "Books"); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.AddColumn( name: "Category", table: "Books", type: "TEXT", nullable: true); } } ``` To update the database with the above script, run the following. ```bash PM> Update-Database ``` Before EF Core 5.0, this update will fail, because the column cannot be dropped. ![](/files/-MfYG8jRfPm9zh8TMlM8) In EF Core 5.0, you will see that migrations will instead rebuild the table successfully. ![](/files/dSxLzpvBxWzGHOOHwuIx) #### How it Works * A temporary table is created with the desired schema for the new table. * Data is copied from the current table into the temporary table. * Foreign key enforcement is switched off. * The current table is dropped * The temporary table is renamed to be the new table. # Index Attribute [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Index Attribute Entity Framework 6 provides the `Index` attribute to create an index on a particular column in the database. ```csharp public class Book { public int Id { get; set; } [Index] public string Title { get; set; } public string Category { get; set; } public int AuthorId { get; set; } public Author Author { get; set; } } ``` But in EF Core, indexes cannot be created using data annotations. You have to use the Fluent API to specify an index on a column as shown below. ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .HasIndex(b => b.Title); } ``` Now in EF Core, the new `Index` attribute can be placed on an entity type to specify an index for one or more columns. ```csharp [Index(nameof(Title), IsUnique = true)] public class Book { public int Id { get; set; } public string Title { get; set; } public string Category { get; set; } public int AuthorId { get; set; } public Author Author { get; set; } } ``` Now when you add a migration, you will see that the index is created for the `Title` column. ```sql CREATE TABLE [dbo].[Books] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Title] NVARCHAR (450) NULL, [Category] NVARCHAR (MAX) NULL, [AuthorId] INT NOT NULL, CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_Books_Authors_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [dbo].[Authors] ([Id]) ON DELETE CASCADE ); GO CREATE NONCLUSTERED INDEX [IX_Books_AuthorId] ON [dbo].[Books]([AuthorId] ASC); GO CREATE UNIQUE NONCLUSTERED INDEX [IX_Books_Title] ON [dbo].[Books]([Title] ASC) WHERE ([Title] IS NOT NULL); ``` You can also use the `Index` attribute to specify an index spanning multiple columns. ```csharp [Index(nameof(FirstName), nameof(LastName), IsUnique = true)] public class Author { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public ICollection Books { get; set; } } ``` For SQL Server, Migrations will then generate the following SQL. ```sql CREATE TABLE [dbo].[Authors] ( [Id] INT IDENTITY (1, 1) NOT NULL, [FirstName] NVARCHAR (450) NULL, [LastName] NVARCHAR (450) NULL, CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED ([Id] ASC) ); GO CREATE UNIQUE NONCLUSTERED INDEX [IX_Authors_FirstName_LastName] ON [dbo].[Authors]([FirstName] ASC, [LastName] ASC) WHERE ([FirstName] IS NOT NULL AND [LastName] IS NOT NULL); ``` # BulkExtensions in EF Core In EF Core, if you want to improve your CRUD performance, you need to call [BulkExtensions](https://entityframework-extensions.net/bulk-extensions) from the library made by [ZZZ Projects](https://zzzprojects.com/). They provide all common bulk extensions required when working with EF Core: * [Bulk SaveChanges](https://entityframework-extensions.net/bulk-savechanges) * [Bulk Insert](https://entityframework-extensions.net/bulk-insert) * [Bulk Update](https://entityframework-extensions.net/bulk-update) * [Bulk Delete](https://entityframework-extensions.net/bulk-delete) * [Bulk Merge](https://entityframework-extensions.net/bulk-merge) * [Bulk Synchronize](https://entityframework-extensions.net/bulk-synchronize) * [Bulk Read](https://entityframework-extensions.net/bulk-read) * [Where Bulk Contains](https://entityframework-extensions.net/where-bulk-contains) * [Where Bulk Not Contains](https://entityframework-extensions.net/where-bulk-not-contains) And even more features through their free library [Entity Framework Plus](https://entityframework-plus.net/). ## How to create a Bulk Operations? Without options, it's pretty simple. Instead of tracking your entities, you simply pass it to one of their methods, such as Bulk Insert: ```csharp context.BulkInsert(customers); ``` If you need an option like including all your entities graph, you need to create a lambda expression such as: ```csharp context.BulkInsert(customers, options => { options.IncludeGraph = true}); ``` ## Why using Bulk Operations in EF Core? The main reason people need to use Bulk Operations in EF Core is to improve their performance when importing thousand of entities. In addition to saving data, you also reduce your memory usage. When processing a lot of entities, using Bulk Extensions instead of `SaveChanges` can be 5 times faster and use 20% of the memory. ## References * [EFCore BulkExtensions](https://entityframework-extensions.net/bulk-extensions) * [EF Core BulkExtensions Download](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) # Connection Strings: Entity Framework Core [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Connection Strings: Entity Framework Core In Entity Framework Core, there could be multiple numbers of databases that needed to be connected or if any database provider needs to connect with the database. * The connection string is used to establish a connection between the database and database providers. * The connection string could have sensitive data from the database, which is required to be protected and can be done by using the Secret Manager tool. * The connection string is needed to be configured based on the environment, such as testing, production, and Development. ### Configuring Connection Strings in EF Core After creating the connection string between database and database providers, we need to make it available to the `DbContext` for processing the data for the application. There are a few numbers of methods for configuring the connection strings for `DbContext`. #### OnConfiguring Method On `DbContext`their data needs to be updated regularly for providing better results, to maintain this procedure the data needs to be overridden onto `DbContext`. * To override the data every time on `DbContext` using the connection string, the `OnConfiguring`method is used to achieve the overridden of the data. * The only downside of the `OnConfiguring`method is that if it is used on the connection string, it will override all other configurations for that database. ```csharp protected override void OnConfiguring(DbContextOptionsBuilder opBuilder) { opBuilder.UseSqlServer("server=.;database=myEFCoreDB;trusted_connection=true;"); } ``` #### Configuring Connection String as Service In .NET Core Applications using Entity Framework Core, the connection string can be configured using the `AddDbContext`extension method which can be used in the `Startup` class using the `IServiceCollection`. In Entity Framework Core, connection string can also be configured to `DbContext` using **ASP.NET Core MVC** applications and **.NET Core Console** application. ```csharp public void ConfigureServices(IServiceCollection servicescol) { servicescol.AddDbContext(options => { options.UseSqlServer("server=.;database=myEFCoreDb;trusted_connection=true;")); }); } ``` **ASP.NET Core MVC Application** Earlier in ASP.NET, the connection string was stored on the web.config file, but now ASP.NET core can extract and read connection strings from different locations such as `appsettings.json`, command-line arguments, and the environment variable, etc. ASP.NET Core uses the **Model-View-Architecture** (MVC) Pattern, this model separates the application into three main groups, model, view and controller. These groups work together to provide the required results from the model. In any of the MVC Applications using the Entity Framework Core, the `DbContext` is injected using dependency injection in the `ConfigureServices` method. Configure Services method comes in startup class, which means that **connection strings are also required in the Startup Class**. To read from the Startup Class, an `IConfiguration` object is required, which can be injected from Dependency injection. To inject into a `Startup` class, the developers can use a constructor or a **`GetConnectionString`** method. ```csharp public class Startup { public Startup(IConfiguration configuration) { Configuration = configuration; } public IConfiguration Configuration { get; } public void ConfigureServices(IServiceCollection services) { services.AddDbContext(options => options.UseSqlServer( Configuration.GetConnectionString("EFCoreDBContext"))); services.AddIdentity() .AddEntityFrameworkStores() .AddDefaultTokenProviders(); services.AddRazorPages(); } } ``` After this, the connection string is needed to be passed onto the `DbContext`. For this to happen a separate context class \*\*\*\*needs to be created derived from the `DbContext` itself. ```csharp public partial class EFCoreDBContext : DbContext { public EFCoreDBContext () { } public EFCoreDBContext(DbContextOptions options) : base(options) { } public virtual DbSet Actors{ get; set; } public virtual DbSet Movies{ get; set; } public virtual DbSet Biographies{ get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { //warning You can move this code to protect potentially senstive information //in connection string. optionsBuilder.UseSqlServer("Data Source= .;Initial Catalog=EFCoreDB;User ID=test;Password=test123"); } } } ``` The startup service is availed again for using the `ConfigureService` method to register the DbContext for dependency injection. The Entity Framework Core provides the `AddDbContext` extension method, which can be used to register our context class. The `DbContext` options are configured using the `DbContextOptionBuilder`, the SQL server is configured as the database provider through `UseSQLServer` method. **Console App(.Net Core)** In .NET Core application, dependency injection is set up manually unlike the ASP.NET core, which use to do this automatically. * The `appsettings.json` file is not created in .NET Core, hence it has to be created ourselves. * To read connection string in the .NET Core console application, the developer has to initialize `IConfigurations`. To do that, the following packages are required to be installed If you miss these packages install them now. * Microsoft.Extensions.Configuration * Microsoft.Extensions.Configuration.FileExtensions * Microsoft.Extensions.Configuration.Json After successful installation, connection strings can be read using the relevant codes. ```csharp class Program { static void Main(string[] args) { var newbuilder = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json"); IConfiguration iconfig = new ConfigurationBuilder() .AddJsonFile("appsettings.json", true, true) .Build(); Console.WriteLine($" Hello { iconfig["fullname"] } !"); } } ``` To further pass the connection string `DbContext` create a context class. For the reference of context class see the above example in **ASP.NET Core MVC** application. This is how connection string is provided to `DbContext` using **ASP.NET Core** **and Console app (.NET Core)**. # Entity Framework Core Model [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Entity Framework Core Model In Entity Framework, the model is prepared according to the requirement of the user. It depends upon the number of classes and categories that will be embedded into the database. * To perform various CRUD operations on the applications, model can also be configured manually, and it can be further modified to suit the required database. * Creating a model in Entity Framework Core has become easy for developers. * To design a model, it can either be done by coding it manually or by using the previous database model and tweaking it as per the requirement. * But with latest updates in the Entity Framework Core, there is a slight change in the approach for generating a model from an existing database. * Earlier the developers used the Database-First approach but now in the Entity Framework Core, the Code-First approach is used to generate Model using the existing database. ### Code-First Approach to Generate Model using Existing Database #### Command Line Interface For creating a model using the existing database, the developer can use the **Command Line Interface (CLI)** tools, these tools help in generating SQL statements for model based on the existing database. Using the CLI method, the developers can create as well as apply migrations and generate code for the model based on the existing model database. Another advantage of using the CLI approach is that the commands generated using this can be used for .NET core projects as well. Before starting, don’t forget to create a folder. ```bash mkdir EFCoreExample ``` Once the folder is created, navigate to this folder ```bash cd EFCoreExample ``` Now, create another project ```bash dotnet new console ``` After doing all this, add these Entity framework Core Tool and Packages, if you don’t have them. ```bash dotnet add package Microsoft.EntityFrameworkCore.SqlServer dotnet add package Microsoft.EntityFrameworkCore.Design ``` In the above two packages, the first one is the EF-Core provider for SQL Server, The second package have the EF-Core commands, without these packages it is not possible to execute the SQL Server statements, You use the Db Scaffold command to generate the model. The command has two required arguments - a connection string and a provider. ```bash dotnet ef dbcontext scaffold "Server=.\;Database=DemoEFCore;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Model ``` The `DbContext` class will take the name of the database plus context, You can override this using the -c or --context option e.g. ```bash dotnet ef dbcontext scaffold "Server=.\;Database=DemoEFCore;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Model -c "EFCoreContext" ``` Model Configuration ```bash dotnet ef dbcontext scaffold "Server=.\;Database=DemoEFCore;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -d ``` Updating the model ```bash dotnet ef dbcontext scaffold "Server=.\;Database=DemoEFCore;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -force ``` #### Visual Studio Another great approach for generating the model using the existing database is through Visual Studio. * In Visual Studio the developer can use the Package Manager Console (PMC) to create the required model using the existing database. * Using PMC the user can create migrations, apply migrations and generate the relevant code for the model based on the existing model. * To keep the new changes in the database in sync with the generated model in Entity Framework Core, we use migrations. * With Migrations, changes will get updated within the model and the application will get in sync with the model resulting in better performance. ```bash PM> Scaffold-DbContext "Server=.\;Database=DemoEFCore;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Model -Context "EFCoreContext" -DataAnnotations ``` ### Shadow Properties for Generating Model in EF Core Shadow properties are introduced with the release of the EF Core, they are not present in .NET entity class but rather situated or defined in the entity type of the Entity Framework Core model. * Shadow properties are dependent on the **Change Tracker** as their value and state can be completely maintained using Change Tracker. * So, whenever the user wants to change or need the values of the shadow properties, they will use the Change Tracker API. * To configure the shadow properties the developers can use the **Fluent API** which will enable them to tweak their values and state. * Most places where shadow properties are preferred are for the use of **foreign key properties**, to represent the relationship between two entities in the Database. * To represent shadow property into the database in a relationship between two entities when no foreign key is found, EF Core will use the **Convention** method. Below example shows the `LastUpdated` shadow property which was configured on the Contact entity: ```csharp public class SampleContext : DbContext { public DbSet Accounts{ get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property("LastUpdated"); } } public class Account { public int AccountId { get; set; } public string FName { get; set; } public string LName { get; set; } public string EmailID { get; set; } } ``` Now, the below code shows a `Version` of shadow property which is added to the actor entity in the `OnModelCreating` method, and then it is configured to be a part in concurrency management: ```csharp public class SampleContext : DbContext { public DbSet Actors { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property("Version") .IsRowVersion(); } } public class Actor { public int ActorId { get; set; } public string FName { get; set; } public string LName { get; set; } public ICollection Movies{ get; set; } } ``` **Setting the value of shadow properties:** To access shadow property, you can use the `DbContext.Entry` property and then set the value through the `CurrentValue` property: ```csharp var context = new SampleContext(); var account = new Account{ FName = "John", LName = "Cena" }; context.Add(account); context.Entry(account).Property("LastUpdated").CurrentValue = DateTime.UtcNow; context.SaveChanges(); ``` Another way to set the values is the ChangeTracker API by its `Entries()` method. This method will provide more logical way to a `LastUpdated` value by overriding the `SaveChanges` method: ```csharp public class SampleContext: DbContext { protected override void OnModelBuilding(ModelBuider modelBuilder) { modelBuilder.Entity() .Property("LastUpdated"); } public override int SaveChanges() { ChangeTracker.DetectChanges(); foreach (var enty in ChangeTracker.Entries()) { if(enty.State == EntityState.Added || enty.State == EntityState.Modified) { enty.Property("LastUpdated").CurrentValue = DateTime.UtcNow; } } return base.SaveChanges(); } public DbSet Accounts{ get; set; } } public class Account { public int AccountId { get; set; } public string FName { get; set; } public string LName { get; set; } public string EmailID { get; set; } } ``` **Querying with shadow properties:** The shadow properties can be linked to LINQ queries by the static Property method in the EF utility class: ```csharp var accounts = context.Accounts .OrderBy(account=> EF.Property(account, "LastUpdated")); ``` You can use the C# 6 using static directive: ```csharp using static Microsoft.EntityFrameworkCore.EF; using static System.Console; ... var accounts = context.Accounts .OrderBy(account=> Property(account, "LastUpdated")); ``` ### References * [EF Core Model](https://www.learnentityframeworkcore.com/model) # DbContext [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## DbContext The `DbContext` is simply the way for the developers to incorporate Entity Framework based data to the application. * It allows you to make database connections inside an application model and allows the developer to link the model properties to the database table using a connection string. * It is the base class to manage all types of database operations, such as establishing a connection with the database, query the database and end the connection. * In the older version of Entity Framework, there was an `ObjectContext` class, but now in the latest version, we have `DbContext` class. The `DbContext` in Entity Framework Core consist of the following features and responsibilities: * Database Management * Database Connections * Entity Set * Querying * Change Tracking * Persisting Data * Caching * Model Binding * Materialization * Configuration * Validation All these features are present to perform a dedicated task on the Entity Framework Core. #### Database Management In Entity Framework Core, the `DbContext` allows the user to manage the complete database. It allows the user to create, delete or check for the existing database connections inside the given project. #### Database Connections The `DbContext` also allows the user to check, establish and close the connections between the databases as per the requirement of the project. #### Entity Set The `DbContext` represents all the entities in a project, it also manages these entities throughout their lifetime. It also applies CRUD operations on all the entity types, such as `Add`, `Attach` or `Remove`. #### Querying The `DbContext` also converts the LINQ queries to the SQL queries using the querying method. #### Change Tracking In Entity Framework, the `DbContext` includes the Change Tracker API which tracks all the changes when entities are added, updated or deleted. The state of the entity can be changed manually or automatically depending on the user. #### Persisting Data Since `DbContext` performs all the CRUD related operations, It persists all the changes made to the database. #### Caching The `DbContext` keeps all the changes made to the entities throughout their lifetime in the form of first-level cache files. #### Model Binding The `DbContext` also performs model binding operations in which it automatically read classes and code-based configuration to build an in-memory model, metadata and database. #### Materialization In materialization, the `DbContext` converts the queries from the database tables into entities. #### Configuration The `DbContext` configures the behavior of the context of the databases. #### Validations The `DbContext` checks the validity of the data and performs automatic validation of the data. **DbContext Query** Using `DbContext` in Entity Framework Core, there are three types of query operations which can be performed, they are: * Adding a new entity. * Changing or modifying the properties of the current entity. * Deleting or removing the existing Entity. ### Adding new Entity To add a new entity using `DbContext`, the developer must prepare the relevant code related to the database. This code is very straightforward and new entities can be added by using these four key methods: #### Add\ ```csharp // with type parameter var Actor = new Actor{ FirstName = "Dwayne", LastName = "Johnson" }; context.Add(actor); context.SaveChanges(); // without type parameter var Actor = new Actor{ FirstName = "Dwayne", LastName = "Johnson" }; context.Add(actor); context.SaveChanges(); ``` #### Add(object entity) ```csharp object actor = new Actor{ FirstName = "Dwayne", LastName = "Johnson" }; context.Add(actor); context.SaveChanges(); ``` #### AddRange(IEnumerable\ entities) ```csharp var context = new SampleContext(); var actor = new Actor() { FirstName = "Dwayne", LastName = "Johnson", Movies = new List() { new Movie { Title = "Baywatch"}, new Movie { Title = "Rampage" }, new Movie { Title = "SkyScraper" } } }; context.Add(actor); context.SaveChanges(); ``` #### AddRange(params object \[] entities) ```csharp var actor = new Actor{ FirstName = "Dwayne", LastName = "Johnson" }; var baywatch = new Movie { Title = "Baywatch", Actor= actor}; var rampage = new Movie { Title = "Rampage", Actor= actor}; var skyscraper = new Movie { Title = "SkyScraper", Actor= actor}; context.Add(actor); context.SaveChanges(); ``` To add multiple records the developer must use the AddRange entity method. **Sample 1:** ```csharp var context = new SampleContext(); var actor = new Actor() { FirstName = "Dwayne", LastName = "Johnson", Movies = new List() { new Movie { Title = "Baywatch"}, new Movie { Title = "Rampage" }, new Movie { Title = "SkyScraper" } } }; context.AddRange(Movies); context.SaveChanges(); ``` **Sample 2:** ```csharp var context = new SampleContext(); var actor = new Actor{ FirstName = "Dwayne", LastName = "Johnson" }; var movie = new Movie { Title = "Baywatch", Actor= actor}; context.AddRange(actor, movie); context.SaveChanges(); ``` The `SaveChanges` method insert all entities to the database. ### Changing/Modifying Entity To change or modify an entity, the DbContext should track the current modifications of the entities, so whenever there will be a change in the entity, the DbContext will register it as Modified, and the change tracker API will record the current modifications and will also keep the previous changes. There are various method for Modifying Entities, which are: #### Disconnected Scenario. ```csharp var actor = context.Actors.First(a => a.ActorId == 1); actor.FirstName = "Dwayne"; context.SaveChanges(); ``` #### Setting Entitystate. ```csharp public void Save(Actor actor) { context.Entry(actor).State = EntityState.Modified; context.SaveChanges(); } ``` #### DbContext Update. ```csharp public void Save(Actor actor) { context.Update(actor); context.SaveChanges(); } ``` #### Attach. ```csharp var context = new TestContext(); var actor = new Actor() { ActorId = 1, FirstName = "Dwayne", LastName = "Johnson" }; actor.Movies.Add(new Movie {MovieId = 1, Title = "Baywatch" }); context.Attach(actor); context.Entry(actor).Property("FirstName").IsModified = true; context.SaveChanges(); ``` #### TrackGraph. **Sample 1:** ```csharp var actor = new Actor() { ActorId = 1, FirstName = "Dwayne", LastName = "Johnson" }; actor.Movies.Add(new Movie { ActorId = 1, MovieId = 1, Title = "Baywatch", Isbn = "0123" }); actor.Movies.Add(new Movie { ActorId = 1, MovieId = 2, Title = "Rampage", Isbn = "0123" }); actor.Movies.Add(new Movie { ActorId = 1, MovieId = 3, Title = "SkyScraper", Isbn = "0123" }); var context = new TestContext(); context.ChangeTracker.TrackGraph(actor, e => { if((e.Entry.Entity as Actor) != null) { e.Entry.State = EntityState.Unchanged; } else { e.Entry.State = EntityState.Modified; } }); context.SaveChanges(); ``` **Sample 2:** ```csharp var actor = new Actor() { ActorId = 1, FirstName = "Dwayne", LastName = "Johnson" }; actor.Movies.Add(new Movie { MovieId = 1, Title = "Baywatch", Isbn = "0123" }); actor.Movies.Add(new Movie { MovieId = 2, Title = "Rampage", Isbn = "0123" }); actor.Movies.Add(new Movie { MovieId = 3, Title = "SkyScraper", Isbn = "0123" }); var context = new TestContext(); context.ChangeTracker.TrackGraph(actor, e => { e.Entry.State = EntityState.Unchanged; //starts tracking if((e.Entry.Entity as Movie) != null) { context.Entry(e.Entry.Entity as Movie).Property("Isbn").IsModified = true; } }); ``` ### Deleting/Removing Entity To delete an existing entity, the current entity should be continuously tracked by the `DbContext` in order to check its state and change it to Deleted. To apply this approach, we use `DbContext.Remove` method, after applying this code, the `DbContext` usually executes two SQL Statements. The first one to retrieve the entity from the database and the other one to delete it permanently from the Database. To remove entities there are usually two properties: #### Setting Entity State. ```csharp var context = new SampleContext(); var actor = new Actor { ActorId = 1 }; context.Entry(actor).State = EntityState.Deleted; context.SaveChanges(); ``` #### Related Data. ```csharp var context = new SampleContext(); var actor = context.Actors.Single(a => a.ActorId == 1); var movies = context.Movies.Where(b => EF.Property(b, "ActorId") == 1); foreach (var movie in movies) { actor.Movies.Remove(movie); } context.Remove(actor); context.SaveChanges(); ``` ### References * [EF Core DbContext](https://www.learnentityframeworkcore.com/dbcontext) # DbSet [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## DbSet In Entity Framework Core, the `DbSet` represents the set of entities. In a database, a group of similar entities is called an Entity Set. The `DbSet` enables the user to perform various operations like add, remove, update, etc. on the entity set. Each entity type shows some `DbSet` properties to participate in CRUD operations. In the working model, the `DbContext` represents the `DbSet` property of all the entities, and it keeps the collection of entities in memory. ### Operations of DbSet The `DbSet` is responsible for performing all the basic CRUD (Create, Read, Update and Delete) operations on each of the Entity. The `DbSet` operations are used to change any property of the entity in the EF Core. The most essential methods of the `DbSet` are: * Querying Data * Adding Data * Modifying Data * Deleting Data #### Querying of Data In Entity Framework, querying a data is performed using `DbSet` and the queries are specified using LINQ. * To write LINQ queries, the .NET developers can use query syntax or method syntax. * The query syntax is similar to the SQL and EF Core provider is responsible to check if the query is converted to SQL or not so that the database can perform the execution of the code. * The method syntax is a chained method in which some of the queries are like the SQL query, but most of them are not. In Entity Framework Core, there are various methods for querying the different type of data such as: * Retrieving Single Object * Retrieving Multiple objects * Filtering and Ordering * Grouping * Returning Non-Entity types * Include Related Data * NoTracking Queries **Retrieving Single object** To retrieve a single entity from a query, you can use `First`, `FirstOrdefault`, `Single`, `SingleOrDefault` and `Find` methods. * In the `First` and `FirstOrDefault` criteria, there is only one entity returned from a large group of entities. * Whereas in `Single` and `SingleOrDefault` one record entity is returned but that entity should be relevant to the required field otherwise it will be discarded. * The `Find` method is familiar to the users of the older version of Entity Framework that use to support the `DbSet` API. * The `Find` method requires a Key-value parameter in the entity to determine the required entity otherwise it gives null as the result. ```csharp var actor = context.Actors.First(); var actor = context.Actors.Where(a => a.ActorId == 1).Single(); var actor = context.Actors.Single(a => a.ActorId == 1); var actor = context.Actors.Find(1); ``` **Retrieving Multiple Objects** The queries that are retrieving the data which is compatible with multiple entities are executed otherwise those queries are ignored. Data is iterated when the required query is executed in `foreach` loop, `ToList`, `Sum` or `count`. The query is not executed until the `foreach` loop is utilized. **Sample 1:** ```csharp var movies = context.Movies; // define query foreach(var movie in movies) // query executed and data obtained from database { ... } ``` **Sample 2:** ```csharp var movies = context.Movies.ToList(); // define query and force execution ``` **Filtering and Ordering** Filtering is the process of picking up the correct entity using the where method, which has become the principle of the filtering process. **Sample 1:** ```csharp var movies = context.Movies.Where(p => p.CategoryId == 1); // where method ``` **Sample 2:** ```csharp // lambda as expression that returns boolean var movies = context.Movies.Where(p => p.CategoryId == 1 && p.Ratings< 5); ``` **Sample 3:** ```csharp var movies = context.Movies.OrderBy(p => p.MovieName); var categories = context.Categories.OrderBy(c => c.CategoryName) .ThenOrderBy(c => c.CategoryId); ``` **Grouping** It uses the `GroupBy` method which is used to group all the entity results according to their categories. ```csharp var groups = context.Movies.GroupBy(p => p.CategoryId); var groups = context.Movies .GroupBy(p => new {Director = p.DirectorId, Country = p.CountryId}); ``` **Returning Non-Entity types** When the developer wants to keep the main data but needs to return the sub-data then Non-Entity types `DbSet` can be used. The returned data can be stated as a non-entity type or the anonymous type, to perform this operation, query types can also be used as an alternate method. ```csharp public class MovieHeader { public int MovieId { get; set; } public string MovieName { get; set; } } ``` ```csharp List headers = context.Movies.Select(p => new MovieHeader() { MovieId = p.MovieId, MovieName = p.MovieName }).ToList(); ``` **Include Related Data** Just as the name suggests the `Include` property is used to include the related entity type into the database. **Sample 1:** ```csharp var actors = context.Actors.Include(a => a.Movies).ToList(); ``` **Sample 2:** ```csharp var actors = context.Actors .Include(a => a.Producer) .Include(a => a.Movies) .ToList(); ``` **Sample 3:** ```csharp var actors = context.Actors .Include(a => a.Movies) .ThenInclude(b => b.Producer) .ToList(); ``` **No-Tracking Queries** To improve the performance of the application the no-tracking queries are used in the form `AsNoTracking` method. This will inform the `DbContext` that the entity is read-only and does not need to be tracked by the context, this will eventually decrease the load on the system, hence improving the performance. ```csharp var movies = context.Movies.AsNoTracking().ToList(); using (var context = new SampleContext()) { context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; var movies = context.Movies.ToList(); var actors = context.Actors.ToList(); … } ``` #### Add Data To add a new entity to the database, the `Add` method is used and then after tracking the entity by `DbContext` the state of the entity is changed to Added. To add multiple records into the database, the `AddRange` method is used, it is like the `Add` method but in `AddRange` method, all the records are saved in just a single step instead of adding them all individually. To add an entity using the `DbSet` there are 3 key methods: **Add (Entity)** ```csharp // with type parameter var actor = new Actor{ FirstName = "John", LastName = "Dwayne" }; context.Actors.Add(actor); // without type parameter var actor = new Actor{ FirstName = "John", LastName = "Dwayne" }; context.Actors.Add(actor); ``` **AddRange \** ```csharp var context = new SampleContext(); var actor = new Actor() { FirstName = "John", LastName = "Dwayne" }; var movies = new List() { new Movie { Title = "Baywatch", Actor = actor}, new Movie { Title = "Rampage", Actor = actor }, new Movie { Title = "Skyscraper", Actor = actor } } }; context.Movies.AddRange(movies); Context.SaveChanges(); ``` **AddRange \[Entity]** ```csharp var actor = new Actor { FirstName = "John", LastName = "Dwayne" }; var baywatch = new Movie { Title = "Baywatch", Actor = actor }; var rampage = new Movie { Title = "Rampage", Actor = actor }; var skyscraper = new Movie { Title = "Skyscraper", Actor = actor }; context.Movies.AddRange(actor, baywatch, rampage, skyscraper); context.SaveChanges(); ``` #### Modify Data To modify the data in `DbSet`, first it checks whether the data which is to be modified is being tracked or not. After the Change tracker detects a change in the state of the entity, it issues a SQL statement that updates the properties that were changed. ```csharp var actor = context.Actors.Find(1); actor.FirstName = "John R"; context.SaveChanges(); ``` **Disconnected Scenario in Modifying Data** If the application such as ASP.NET is disconnected from the `DbContext`, then the modifications must be updated using another method. To update the state of the entity in disconnected form, the `DbSet.Update` method is used, so whenever the application is loaded, it changes the state of the entity as Updated. This method is newly introduced in Entity Framework Core. **DbSet Update** The `Update` method is using `DbSet` class and provides different methods to work with an individual or multiple entities. ```csharp public void Save(Actor actor) { context.Actors.Update(actor); context.SaveChanges(); } ``` #### Delete Data Deleting the data is similar to modifying the data using `DbSet`. For deleting any data, it will first check whether the data which is to be deleted is being tracked by the context or not. To determine the state of the entity `DbSet` uses `DbSet.Remove` method which sets the state of the entity as `Deleted`, after this when the changes are saved, the `DELETE` statement is generated and executed by the Database. **Sample 1:** ```csharp context.Actors.Remove(context.Actors.Find(1)); context.SaveChanges(); ``` **Sample 2:** ```csharp var context = new SampleContext(); var actor = new Actor { ActorId = 1 }; context.Actors.Remove(actor); context.SaveChanges(); ``` **Related Data in Deleting the in DbSet** If the entity that user wants to delete has a related data then the approach that you take will depend on how the relationship has been configured. If the relation is a fully defined relationship, then the resulting output will be either deleted or set to null. In other cases, EF Core has also introduced a Shadow property to represent the foreign key, this method is a little longer as it requires four action calls from the database. ```csharp var context = new SampleContext(); var actor = context.Actors.Find(1); var movies = context.Movies.Where(b => EF.Property(b, "ActorId") == 1); foreach (var movie in movies) { actor.Movies.Remove(movie); } context.Actors.Remove(actor); context.SaveChanges(); ``` ### References * [EF Core DbSet](https://www.learnentityframeworkcore.com/dbset) # Relationship in EF-Core [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](/files/-MhuW50Bxq3CSispWCuT)](https://entityframework-extensions.net/) ## Relationship in EF Core In Entity Framework Core, relationship means how two or more entities related to each other in the database. * In a database, there can be multiple entities that relate to each other in terms of their entity classes or entity types. * But if any entity does not have any relation with other entities then the user can use the **foreign key entity** at place of that entity. * When the user creates a relationship between two entities the primary entity becomes the **principal entity** while the other entity is termed as a **dependent entity**. * The principal entity is the main entity in the relationship and the dependent entity holds the foreign key that refers to the principal entity’s primary key when the application is initiated. One of the advantages of having **relational data** is that it stops the multiple entries of similar data, which in turn puts less weight on the database in-turn improving the performance. **For** **example:** Consider a company keeps all the details of its employees including their home address, contact details and more and due to some reason, the position of that employee is shifted to another department. In this case, instead of re-entering all the details, the company could just relate that data as the principal entity of that employee and just modify its position. In Entity Framework Core, there is various form of relationships such as: * One-to-One Relationship * One-to-Many Relationship * Many-to-Many Relationship * OnDelete Method ### One-to-One Relationship In a one-to-one relationship, the entities in one database table can relate to the entities of another database table. Only one row of entities in a table can relate to another row of the different tables to make it less complicated. So, the first database table will be termed as the principal entity, and the other table will be represented as the dependent entity. The updated Entity Framework Core also supports table splitting feature which enables it to use a single database table to showcase one-to-one relationships for both the entities and eliminates the need for having two separate database tables. To configure a one-to-one relationship the developers can either use the Convention approach or they can even use the Fluent API approach. ![](/files/-MfYG3JTpTmQBkeiHNQH) This is example of one-to-one relationship; each Actor can have only one biography. ```csharp public class Actors { public int ActorId { get; set; } public string FName { get; set; } public string LName { get; set; } public ActorBiographies Biography { get; set; } } public class ActorBiographies { public int ActorBiographyId { get; set; } public string Biography { get; set; } public DateTime DOB { get; set; } public string POB { get; set; } public string Nationality { get; set; } public int ActorID { get; set; } public Actors Actor { get; set; } } ``` ### One-to-Many Relationship In the one-to-many relationship, each row of the principal entity table can relate with the multiple rows of the dependent database table. ![](/files/-MfYG3JaZxP3oZyklClM) To establish a one-to-many relationships there are two basic ways: #### Using Convention Approach ```csharp public class Actor { public int ActorID { get; set; } public string FName { get; set; } public string LName { get; set; } public List Movies { get; set; } } public class Movie { public int MovieID { get; set; } public string Name{ get; set; } } ``` #### Inverse Navigation Property ```csharp public class Actor { public int ActorID { get; set; } public string FName { get; set; } public string LName { get; set; } public List Movies { get; set; } } public class Movie { public int MovieID { get; set; } public string Name { get; set; } public Actor Actor { get; set; } } ``` #### Fully Defined Relationship ```csharp public class Actor { public int ActorID { get; set; } public string FName { get; set; } public string LName { get; set; } public List Movies { get; set; } } public class Movie { public int MovieID { get; set; } public string Name { get; set; } public int ActorID { get; set; } public Actor Actor { get; set; } } ``` **Optional Relationships** ```csharp public class Actor { public int ActorID { get; set; } public string FName { get; set; } public string LName { get; set; } public List Movies { get; set; } } public class Movie { public int MovieID { get; set; } public string Name { get; set; } public int? ActorID { get; set; } public Actor Actor { get; set; } } ``` #### Using the Fluent API Approach #### Has/With pattern ```csharp protected override void OnModelCreating(Modelbuilder modelBuilder) { modelBuilder.Entity() .HasMany(c => c.Students) .WithOne(e => e.College); } protected override void OnModelCreating(Modelbuilder modelBuilder) { modelBuilder.Entity() .HasOne(e => e.College) .WithMany(c => c.Students); } ``` #### Required relationship ```csharp protected override void OnModelCreating(Modelbuilder modelBuilder) { modelBuilder.Entity() .HasMany(c => c.Students) .WithOne(e => e.College). .IsRequired(); } ``` #### Cascading Referential Integrity Constraints ```csharp protected override void OnModelCreating(Modelbuilder modelBuilder) { modelBuilder.Entity() .HasMany(c => c.Students) .WithOne(e => e.College). .OnDelete(DeleteBehavior.SetNull); } protected override void OnModelCreating(Modelbuilder modelBuilder) { modelBuilder.Entity() .HasMany(c => c.Students) .WithOne(e => e.College). .OnDelete(DeleteBehavior.Delete); } ``` ### Many-to-Many Relationship This type of relationship can also be classified as two one-to-many relationships in Entity Framework Core. To make it work the developer must create a joining entity class. Earlier in Entity Framework, the application uses to create the joining entity automatically, but in the Entity Framework Core, this method must be applied manually. Unlike the one-to-many relationship, the conventional approach cannot be applied. Data Annotation approach can be applied and to make it work, the developer must create a join table for the application, but in this, we can’t create a primary key. So, fluent API seems to be the best option for using a many-to-many relationship in Entity Framework Core. ```csharp public class Movie { public int MovieId { get; set; } public string Name{ get; set; } public Actor Actor { get; set; } public List Genres { get; set; } } public class Genre { public int GenreId { get; set; } public string GenreName { get; set; } public List Movies{ get; set; } } ``` However, for the future upcoming updates like the EF Core, Microsoft is considering to kick off the join table entity feature from many-to-many relationships. If the join table is removed, it will become easy for the developers to interact with the queries directly, without the need of creating a join table. They are making the Entity Framework more agile than before. ### OnDelete Method This method adds the ability to apply delete actions between relational entities and delete the entities which are not being used by the application. Usually, this method is applied to the end of the above-mentioned relationship methods, so the `OnDelete` method either deletes entities or restricts other commands from deleting the entities. The `OnDelete` Method can use the following values in the Entity Framework Core: * Restrict. * SetNull. * ClientSetNull. * Cascade. ### References * [EF Core Relationships](https://www.learnentityframeworkcore.com/relationships) * [EF Core One to One Relationship](https://www.learnentityframeworkcore.com/conventions/one-to-one-relationship) * [EF Core One to Many Relationship](https://www.learnentityframeworkcore.com/conventions/one-to-many-relationship) * [EF Core One to Many Relationship](https://www.learnentityframeworkcore.com/conventions/many-to-many-relationship) # Lazy Loading in EF Core [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Lazy Loading in EF Core Lazy Loading was introduced in Entity Framework Core with EF Core 2.1 to allow better optimizations, performance, and working of the software. * Lazy Loading is a method of loading and processing only the required data to run the application, the data which is not required at that moment stays untouched. * It allows the system to perform better and faster and it has become an essential part of the Entity Framework core. ### Procedures to enable Lazy Loading in EF-Core To enable Lazy Loading in Entity Framework core, there are 2 methods which can be applied. #### With Proxy Package The First method is by installing the **Proxy Package** provided by Microsoft. All the developer has to do is install `Microsoft.EntityFrameworkCore.Proxies` package which will add all the required proxies needed to run Lazy Loading. After installing the package, the system will ask the developer to allow the installed proxies to access the databases and enable lazy loading. ```csharp public void ConfigureServices(IServiceCollection services) { services.AddDbContext(b => b.UseLazyLoadingProxies() .UseSqlServer(ConnectionString)); } ``` ```csharp public class Actor { public int ActorId { get; set; } public string FullName { get; set; } public virtual List Movies{ get; set; } } public class Movie { public int MovieId { get; set; } public string Title { get; set; } public virtual Actor Actor{ get; set; } } ``` #### Without Proxy Package The second method of enabling Lazy Loading in Entity Framework Core is using the `ILazyLoader` interface. * The `ILazyLoader` interface represents a component that is responsible for loading navigation properties if they haven't already been loaded. * It can be embedded directly into the principle entity of the database. * The `ILazyLoader` can be found in `Microsoft.EntityFrameworkCore.Abstraction` Package. ```csharp public class Actor { private List _movies; public Actor() { } private Actor(ILazyLoader lazyLoader) { LazyLoader = lazyLoader; } private ILazyLoader LazyLoader { get; set; } public int ActorId { get; set; } public string FullName { get; set; } public List Movies { get => LazyLoader.Load(this, ref _movies); set => _movies = value; } } public class Movie { private Actor _actor; public Movie() { } private Movie(ILazyLoader lazyLoader) { LazyLoader = lazyLoader; } private ILazyLoader LazyLoader { get; set; } public int MovieId { get; set; } public string Title { get; set; } public Actor Actor { get => LazyLoader.Load(this, ref _actor); set => _actor = value; } } ``` ### Is Lazy Loading Useful? Lazy loading is helpful once the association between entities is a one-to-many relationship and you're certain that associated entities aren't going to be utilized immediately. It helps in functionality reducing application startup time, less memory utilization, and reduce the load on DBMS due to a small amount of query load on the server. What can be concluded from Lazy Loading is that this feature is useful in some scenarios, otherwise, it could distract the users and developers if they forget to disable the feature. ### References * [EF Core - Lazy Loading](https://www.learnentityframeworkcore.com/lazy-loading) # Migrations in EF-Core [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Migrations in EF-Core In Entity Framework Core, when there is a change in the model, the database tables are also needed to be updated to keep everything in sync for the proper working of the application. * To update or generate the change in the ongoing model, the **Migration** method is used, it allows the developer to update the data without losing it. * Also, the migration method can be used to update and generate change in the Database tables, based on the Model. * Migrations make it easy for the developer to change and update the data of the application, otherwise, all the codes had to be re-written manually. In Entity Framework Core, migration method can help in multiple numbers of tasks, such as: * [Creating Migration](https://www.learnentityframeworkcore.com/migrations/add-migration) * [Update or create Database](https://www.learnentityframeworkcore.com/migrations/update-database) * Modify current migration code * [Remove Migration from Model](https://www.learnentityframeworkcore.com/migrations/remove-migration) * Revert the effect of Migration * Create SQL Scripts * Apply Migration at Runtime All these tasks can be applied with the help of the Migration method. ### Creating a Migration To add migration to the model, the developers can use the `Add-Migration` command. **Command Line - CLI** `dotnet ef migrations add ` **Package Manager console** `add-migration ` When you create a migration, the framework checks if there is any change from the previous migration if one exists and generates a file containing a class inheriting from `Microsoft.EntityFrameworkCore.Migrations.Migration` featuring an Up and a Down method. If it finds any change in the model then EF Core updates the model as well as the Database according to the current changes that are required for running the application. ### Update Database After updating the model, migration does not automatically update the database, for updating the database, we need to use an additional command. To update the database in order to keep it in sync with the current model, `Update-Database` command is used to bring the database in sync with the current migration updates. **Command Line - CLI** `dotnet ef database update` **Package Manager console** `Update-Database` ### Modify Current Migration code The Entity Framework Core allows the developer to modify the migration code in order to make everything work in sync with each other. This feature comes into play every time the developer needs to make changes to the model and the database tables and make everything up to date. To modify the migration code simply add or modify the code with the required changes for the model. **Command Line - CLI** `dotnet ef migrations add AddActor` **Package Manager console** `Add-Migration AddActor` Rewrite the database schema in the following way: ```csharp migrationBuilder.AddColumn( name: "FullName", table: "Actor", nullable: true); migrationBuilder.Sql( @"UPDATE Actor SET FullName = FName + ' ' + LName;"); migrationBuilder.DropColumn( name: "FName", table: "Actor"); migrationBuilder.DropColumn( name: "LName", table: "Actor"); ``` ### Removing Migration from EF-Core Model It is easy to remove migration from your Entity Framework model, sometimes adding a migration can cause issues in the application. In that case, removing that migration helps a lot, the developer can easily remove that migration and add the relevant migration later when required. To simply remove the last migration, the `Remove-Migration` command can be used. **Command Line - CLI** `dotnet ef migrations remove` **Package Manager console** `Remove-Migration` ### Revert Migration Effect After applying the migration to the database, if the developer wants to revert the effect of the changes made, then they can easily revert the model or the database to its previous form by passing the name of a target migration to the update command. **Command Line - CLI** `dotnet ef database update LastGoodMigration` **Package Manager console** `Update-Database LastGoodMigration` ### Create SQL Scripts Sometimes if the migration process is not working for your model due to some fault or incompatibility from the database provider, then creating custom SQL scripts can help in resolving the issue, the good thing is that the migration process in EF-Core allows the developers to create custom SQL scripts to be embedded in the Model. **Command Line - CLI** `dotnet ef migrations script` **Package Manager console** `Script-Migration` ### Apply Migration at Runtime Different applications prefer different modes in EF-Core, some prefer start-up migration and some prefer runtime migration, this is based on the type of application. To apply migration during runtime, developers can simply use the `Migrate()` method and achieve the migration process during Runtime. ```csharp EFCoreDbContext.Database.Migrate(); ``` ### Additional Features of Migration in EF – Core #### Targeting Multiple Database Providers Simultaneously Entity Framework Core allows the migrations to be database friendly, it allows migration settings from one Model to be embedded in other similar types of models. But there can be situations where a migration which is working on some model might not work on another, in this situation, the developer can set these types of migrations as ignored. #### Custom Migration Timeout In some cases, the migration might not work due to application taking more time than the default setting for which the migration was configured. 1. The developer can set customized active time for the migrations so that they can properly sync with the application. 2. To set the migration timeout, the developer must set the required timeout through the `DbContext` \*\*\*\*Level, but it comes with a trade-off. 3. If you set timeout using `DbContext`, then this setting will be applied to all the operations that are coming under the `DbContext` Level. ### Summary This completes everything about the migrations in Microsoft Entity Framework Core, over-all migration is a very useful feature and it helps lots of developers while using EF Core. ### References * [EF Core Add Migration](https://www.learnentityframeworkcore.com/migrations/add-migration) * [EF Core Update Database](https://www.learnentityframeworkcore.com/migrations/update-database) * [EF Core Remove Migration](https://www.learnentityframeworkcore.com/migrations/remove-migration) * [EF Core Migration Files](https://www.learnentityframeworkcore.com/migrations/migration-files) # Handling Concurrency in EF-Core [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Handling Concurrency in EF-Core Concurrency means the conflicts happening on the data due to access to multiple users, trying to modify the same data at the same time. Concurrency Control or Management refers to techniques or methods to maintain the consistency of the data when more than one user is accessing it for different purposes. Concurrency Management helps in obtaining safety, optimization, consistency and preventing the Data. ### Optimistic Concurrency By default, Entity Framework core offers Optimistic Concurrency control, in this case, it will consider the data that is saved most recently, and before committing, each transaction verifies that no other transaction has modified the data it has read. So, If multiple users are working on the same database, the data from the last user will be taken into consideration, this way all users can work simultaneously on the same database and the Last user can save the final data. ### Pessimistic Concurrency In the Pessimistic concurrency method, the system locks the complete data which is being modified concurrently by multiple users. Due to this, that data stays unchanged and the modifications can be applied later when there is no concurrency is happening. However, the Entity Framework Core does not support pessimistic concurrency, as when the internet connection is weak or disconnected the data cannot be managed properly which will affect the working of the database. #### Detecting Conflicts in EF-Core Concurrency For detecting Concurrency in EF Core, there are two methods available to perform concurrency conflict detection in an optimistic concurrency method. One is to configure the Entities as concurrency tokens and the other one is adding row version property in the entity classes. **Using Concurrency tokens** Consider there are multiple users in a database and all of them are working concurrently, So when the EF Core detects data using the `ConcurrencyCheck`attribute, it performs a comparison of the values of that entity. If the values of the entity match then the operation is performed successfully, but if these values of the same entity differ from each other than that means there are concurrency conflicts in that entity due to multiple concurrent users. **Data Annotation** ```csharp public class Actor { public int ActorId { get; set; } [ConcurrencyCheck] public string LName { get; set; } public string FName { get; set; } } ``` **Fluent API** ```csharp protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property(p => p.LName) .IsConcurrencyToken(); } ``` **Using RowVersion Property** In the `RowVersion` Property, a new column is added to the database table and it stores the version stamp of the data. A new Row version value is added each time a user updates the data. * If two users are working on the same database, and the first user updates the data and leave, then the Second user updates the data and leave. * Then EF Core will compare both the updated Row version properties and if the values match, the operation is performed successfully. * Otherwise, if both the Row version values differ, the operation gives a `DbUpdateConcurrencyException`. **Data Annotation** ```csharp public class Movie { public int MovieId { get; set; } public string Title{ get; set; } [Timestamp] public byte[] Timestamp { get; set; } } ``` **Fluent API** ```csharp class MyContext : DbContext { public DbSet Movies { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .Property(p => p.Timestamp) .IsRowVersion(); } } public class Blog { public int MovieId { get; set; } public string Title{ get; set; } public byte[] Timestamp { get; set; } } ``` **Resolving the Data Concurrency Conflicts** To resolve the concurrency conflicts in Entity Framework Core, the system traces 3 main values to determine where the problem is coming from: * **Current values:** The present values that were last updated into the database by the user. * **Original Values**: the value that was present in the database initially, before concurrency occurred. * **Database Value**: The values that are currently stored in the database. To determine where the problem was occurring from inside the database table, the following code can be applied, and the problem will be shown up-front. ```csharp using (var DBcontext = new EFCoreContext()) { // Get the actor from database and change its contact number var actor = DBcontext.Actors.Single(p => p.ActorID == 1); actor.ContactNumber = "222-222-2222"; // Change name of the actor in the database to simulate a concurrency conflict DBcontext.Database.ExecuteSqlRaw( "UPDATE dbo.Actor SET FName = 'John' WHERE ActorId = 1"); var savedData = false; while (!savedData) { try { // Save the changes to the database DBcontext.SaveChanges(); savedData = true; } catch (DbUpdateConcurrencyException ex) { foreach (var item in ex.Entries) { if (item.Entity is Actor) { var currentValues = entry.CurrentValues; var dbValues = entry.GetDatabaseValues(); foreach (var prop in currentValues.Properties) { var currentValue = currentValues[prop ]; var dbValue = dbValues[prop ]; } // Refresh the original values to bypass next concurrency check item.OriginalValues.SetValues(dbValues); } else { throw new NotSupportedException( "Don’t know handling of concurrency conflict " + item.Metadata.Name); } } } } } ``` ### References * [EF Core Concurrency](https://entityframework-extensions.net/concurrency) # Raw SQL Queries in EF-Core [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Raw SQL Queries in EF-Core In Entity Framework Core, if your LINQ Query is not able to execute the application properly, then there is an option for embedding custom raw SQL queries into the database according to the requirement of the application. If the LINQ queries generated automatically by the system are not suitable to the application, then the Raw SQL queries can help in executing the command. ### Using FromSqlRaw Method instead of FromSql With the release of EF Core 3, Microsoft removed the `FromSql` Method and replaced it with the `FromSqlRaw` Method. They made this change to make it easy for developers to call the required action. In the earlier versions of EF Core, the `FromSql` method made it confusing for the system to accidentally trigger the raw string method when the developer wanted to call the interpolated string method or vice-versa, which give unsuccessful results. Now with the release of EF Core 3.0, a parameterized query can be developed using the `FromSqlRaw` method. ### Parameterized Query In Entity Framework Core, there is also support for parameterized queries, which means the developer can pre-compile queries into the applications. * This method helps in preventing the SQL injection attack on the application, by pre-compiling the SQL Queries. * So that when the statement is about to be executed the developer can add the suitable parameters for it to be successful. * Usually, in parameterized queries, placeholders are used in place of parameters and these placeholders are replaced by the parameter values at the time of execution. ```csharp // Format string var actor = db.Actors .FromRawSql("SELECT * From Actors Where ActorID = {0}", id).FirstOrDefault(); // String interpolation var actor = db.Actors .FromRawSql($"SELECT * From Actors Where ActorID = {Id}").FirstOrDefault(); ``` You can also explicitly create DbParameter objects for the provider. The 1st example shows parameter construction for SQLite, and the 2nd example for SQL Server: ```csharp var param1 = new SqliteParameter("@Id", id); var actor = db.Actors .FromRawSql($"SELECT * From Actors Where ActorId = @Id", param1) .FirstOrDefault(); var param1= new SqlParameter("@Id", id); var actor = db.Actors .FromRawSql($"SELECT * From Actors Where ActorId = @Id", param1) .FirstOrDefault(); ``` ### Composing Over Raw SQL In Entity Framework Core, it is possible to compose over raw SQL queries using the LINQ operators. Due to which EF Core will treat SQL statements as a sub-query and will put this data up in the database. But in composing over raw SQL with LINQ operator, the statement can only be executed if the available raw SQL query is composable. Otherwise, it will reject the composing request, resulting in a failed execution of the application. ```csharp var searchTerm = "Horror"; var Movies = context.Movies .FromSqlInterpolated($"SELECT * FROM dbo.MovieCategories({searchTerm})") .Where(b => b.Rating > 4) .OrderByDescending(b => b.Rating) .ToList(); ``` Including related data ```csharp var searchTerm = "Horror"; var Movies = context.Movies .FromSqlInterpolated($"SELECT * FROM dbo.MovieCategories({searchTerm})") .Include(b => b.directors) .ToList(); ``` ### Change Tracking (SQL Server) In Entity Framework Core, the SQL Server supports a very essential feature called Change Tracking, This feature enables the SQL servers to change, update or modify the values in the SQL queries according to the need. Change Tracking is a very useful feature of SQL Server as it allows us to modify the current data, otherwise, everything has to be initiated from start. ```csharp var searchTerm = "Horror"; var Movies = context.Movies .FromSqlInterpolated($"SELECT * FROM dbo.MovieCategories({searchTerm})") .AsNoTracking() .ToList(); ``` ### Limitations of Using Raw SQL 1. All the SQL queries must return the queries of the same type, otherwise, the program would fail to run. 2. The SQL queries must return all the columns in the table, also the returned column names should match the names that are mapped into the database. 3. The SQL queries cannot use the join queries to get the previous data, they should use the Include method instead. ### References * [EF Core - Raw SQL Query](https://www.learnentityframeworkcore.com/raw-sql) * [EF Core - FromSql](https://www.learnentityframeworkcore.com/raw-sql/from-sql) # Database Providers [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Database Providers Entity Framework Core can access many different databases through plug-in libraries called database providers. * Database providers can extend EF Core to enable functionality unique to specific databases. * Some concepts are common to most databases, and are included in the primary EF Core components. * Such concepts include expressing queries in LINQ, transactions, and tracking changes to objects once they are loaded from the database. * Some concepts are specific to a particular provider. For example, the SQL Server provider allows you to [configure memory-optimized tables](https://docs.microsoft.com/en-us/ef/core/providers/sql-server/memory-optimized-table) (a feature specific to SQL Server). The following providers are supported in Entity Framework Core 5. | Name | NuGet Package | | -------------------------------------------- | ----------------------------------------------------------------------------------------------------------------- | | [SQL Server](/database-providers/sql-server) | [Microsoft.EntityFrameworkCore.SqlServer](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer) | | [SQLite](/database-providers/sqlite) | [Microsoft.EntityFrameworkCore.Sqlite](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Sqlite) | | [InMemory](/database-providers/inmemory) | [Microsoft.EntityFrameworkCore.InMemory](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.InMemory) | | [Cosmos](/database-providers/cosmos) | [Microsoft.EntityFrameworkCore.Cosmos](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Cosmos) | | [PostgreSQL](/database-providers/postgresql) | [Npgsql.EntityFrameworkCore.PostgreSQL](https://www.nuget.org/packages/Npgsql.EntityFrameworkCore.PostgreSQL) | ### References * [EF Core - Database Providers](https://www.learnentityframeworkcore.com/database-providers) # SQL Server [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## SQL Server ### SQL Server Provider Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence, and analytic applications in corporate IT environments. * It is the default database provider which is available when you install [Entity Framework Extensions](https://entityframework-extensions.net/download) * It allows Entity Framework Core to be used with Microsoft SQL Server (including SQL Azure). #### Install Entity Framework Core Let's create a new application using the **Console App (.NET Core)** template, and install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/). [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` You can also install it by right-clicking on your project in **Solution Explorer** and select **Manage Nuget Packages...** ![](/files/-MfYG2GrYrgR8XxJkZvW) Search for [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) and install the latest version by pressing the install button. **I**t doesn't have additional logic that won't apply to all scenarios. For example, EF Core will need to know what database or datastore you plan on working with and who those providers are in individual packages. #### Register EF Core Provider For SQL Server LocalDB, which is installed with Visual Studio, we need to install [Microsoft.EntityFrameworkCore.SqlServer](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer ``` Now, you are ready to start your application. #### Create Data Model Model is a collection of classes to interact with the database. * A model stores data that is retrieved according to the Controller's commands and displayed in the View. * It can also be used to manipulate the data to implement the business logic. To create a data model for our application, we will start with the following two entities. ```csharp public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } public List Books { get; set; } } public class Book { public int BookId { get; set; } public string Title { get; set; } public Author Author { get; set; } } ``` There's a one-to-many relationship between `Author` and `Book` entities. In other words, an author can write any number of books, and a book can be written by only one author. #### Create Database Context The database context class provides the main functionality to coordinate Entity Framework with a given data model. * You create this class by deriving from the `System.Data.Entity.DbContext` class. * In your code, you specify which entities are included in the data model. * You can also customize certain Entity Framework behaviors. So, let's add a new `BookStore` class which will inherit the `DbContext` class. ```csharp public class BookStore : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=BookStoreDb;"); } public DbSet Authors { get; set; } public DbSet Books { get; set; } } ``` In EF Core, the `DbContext` has a virtual method called `OnConfiguring` which will get called internally by EF Core. * It will pass in an `optionsBuilder` instance which can be used to configure options for the `DbContext`. * The `optionsBuilder` has `UseSqlServer` method which expects a connection string as a parameter. Now, we are done with the required classes and database creation. Let's add some authors and book records to the database and then retrieve them. ```csharp using (var context = new BookStore()) { context.Database.EnsureCreated(); var authors = new List { new Author { FirstName ="Carson", LastName ="Alexander", BirthDate = DateTime.Parse("1985-09-01"), Books = new List() { new Book { Title = "Introduction to Machine Learning"}, new Book { Title = "Advanced Topics on Machine Learning"}, new Book { Title = "Introduction to Computing"} } }, new Author { FirstName ="Meredith", LastName ="Alonso", BirthDate = DateTime.Parse("1970-09-01"), Books = new List() { new Book { Title = "Introduction to Microeconomics"} } }, new Author { FirstName ="Arturo", LastName ="Anand", BirthDate = DateTime.Parse("1963-09-01"), Books = new List() { new Book { Title = "Calculus I"}, new Book { Title = "Calculus II"} } } }; context.Authors.AddRange(authors); context.SaveChanges(); } using (var context = new BookStore()) { var list = context.Authors .Include(a => a.Books) .ToList(); foreach (var author in list) { Console.WriteLine(author.FirstName + " " + author.LastName); foreach (var book in author.Books) { Console.WriteLine("\t" + book.Title); } } } ``` If you run the application, you will see that authors and books are successfully inserted into the database. ### References * [EF Core - SQL Server Provider](https://www.learnentityframeworkcore.com/database-providers#ef-core-sql-server-provider) # SQLite [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## SQLite ### SQLite Provider SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. * It is the most widely deployed SQL database engine, and the source code for SQLite is in the public domain. * It is a database, which does not need to be configured in your system like other databases. #### Install Entity Framework Core Let's create a new application using the **Console App (.NET Core)** template and install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/). [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` You can also install it by right-clicking on your project in **Solution Explorer** and select **Manage Nuget Packages...** ![](/files/-MfYG2GrYrgR8XxJkZvW) Search for [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) and install the latest version by pressing the install button. **I**t doesn't have additional logic that won't apply to all scenarios. For example, EF Core will need to know what database or datastore you plan on working with and who those providers are in individual packages. #### Register EF Core Provider For SQLite, we need to install [Microsoft.EntityFrameworkCore.Sqlite](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Sqlite) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.Sqlite ``` Now, you are ready to start your application. #### Create Data Model Model is a collection of classes to interact with the database. * A model stores data that is retrieved according to the Controller's commands and displayed in the View. * It can also be used to manipulate the data to implement the business logic. To create a data model for our application, we will start with the following two entities. ```csharp public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } public List Books { get; set; } } public class Book { public int BookId { get; set; } public string Title { get; set; } public Author Author { get; set; } } ``` There's a one-to-many relationship between `Author` and `Book` entities. In other words, an author can write any number of books, and a book can be written by only one author. #### Create Database Context The database context class provides the main functionality to coordinate Entity Framework with a given data model. * You create this class by deriving from the `System.Data.Entity.DbContext` class. * In your code, you specify which entities are included in the data model. * You can also customize certain Entity Framework behaviors. So, let's add a new `BookStore` class which will inherit the `DbContext` class. ```csharp public class BookStore : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlite(@"Data Source=D:\BookStoreContext.db;"); } public DbSet Authors { get; set; } public DbSet Books { get; set; } } ``` In EF Core, the `DbContext` has a virtual method called `OnConfiguring` which will get called internally by EF Core. * It will pass in an `optionsBuilder` instance which can be used to configure options for the `DbContext`. * The `optionsBuilder` has `UseSqlite` method which expects a connection string as a parameter. Now, we are done with the required classes and database creation, let's add some authors and book records to the database and then retrieve them. ```csharp using (var context = new BookStore()) { context.Database.EnsureCreated(); var authors = new List { new Author { FirstName ="Carson", LastName ="Alexander", BirthDate = DateTime.Parse("1985-09-01"), Books = new List() { new Book { Title = "Introduction to Machine Learning"}, new Book { Title = "Advanced Topics on Machine Learning"}, new Book { Title = "Introduction to Computing"} } }, new Author { FirstName ="Meredith", LastName ="Alonso", BirthDate = DateTime.Parse("1970-09-01"), Books = new List() { new Book { Title = "Introduction to Microeconomics"} } }, new Author { FirstName ="Arturo", LastName ="Anand", BirthDate = DateTime.Parse("1963-09-01"), Books = new List() { new Book { Title = "Calculus I"}, new Book { Title = "Calculus II"} } } }; context.Authors.AddRange(authors); context.SaveChanges(); } using (var context = new BookStore()) { var list = context.Authors .Include(a => a.Books) .ToList(); foreach (var author in list) { Console.WriteLine(author.FirstName + " " + author.LastName); foreach (var book in author.Books) { Console.WriteLine("\t" + book.Title); } } } ``` If you run the application, you will see that authors and books are successfully inserted into the database. ### References * [EF Core SQLite Provider](https://www.learnentityframeworkcore.com/database-providers#ef-core-sqlite-provider) # InMemory [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## InMemory ### InMemory Provider InMemory is designed to be a general-purpose database for testing and is not designed to mimic a relational database. * InMemory will allow you to save data that would violate referential integrity constraints in a relational database. * If you use DefaultValueSql(string) for a property in your model, this is a relational database API and will not affect when running against InMemory. * Concurrency via Timestamp/row version (\[Timestamp] or IsRowVersion) is not supported. * No `DbUpdateConcurrencyException` will be thrown if an update is done using an old concurrency token. #### Install Entity Framework Core Let's create a new application using the **Console App (.NET Core)** template and install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/). [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` You can also install it by right-clicking on your project in **Solution Explorer** and select **Manage Nuget Packages...** ![](/files/-MfYG2GrYrgR8XxJkZvW) Search for [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) and install the latest version by pressing the install button. **I**t doesn't have additional logic that won't apply to all scenarios. For example, EF Core will need to know what database or datastore you plan on working with and who those providers are in individual packages. #### Register EF Core Provider For InMemory, we need to install [Microsoft.EntityFrameworkCore.InMemory](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.InMemory) and will get all the packages required for EF Core. ```csharp PM> Install-Package Microsoft.EntityFrameworkCore.InMemory ``` Now, you are ready to start your application. #### Create Data Model Model is a collection of classes to interact with the database. * A model stores data that is retrieved according to the Controller's commands and displayed in the View. * It can also be used to manipulate the data to implement the business logic. To create a data model for our application, we will start with the following two entities. ```csharp public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } public List Books { get; set; } } public class Book { public int BookId { get; set; } public string Title { get; set; } public Author Author { get; set; } } ``` There's a one-to-many relationship between `Author` and `Book` entities. In other words, an author can write any number of books, and a book can be written by only one author. #### Create Database Context The database context class provides the main functionality to coordinate Entity Framework with a given data model. * You create this class by deriving from the `System.Data.Entity.DbContext` class. * In your code, you specify which entities are included in the data model. * You can also customize certain Entity Framework behaviors. So, let's add a new `BookStore` class which will inherit the `DbContext` class. ```csharp public class BookStore : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlite(@"Data Source=D:\BookStoreContext.db;"); } public DbSet Authors { get; set; } public DbSet Books { get; set; } } ``` In EF Core, the `DbContext` has a virtual method called `OnConfiguring` which will get called internally by EF Core. * It will pass in an `optionsBuilder` instance which can be used to configure options for the `DbContext`. * The `optionsBuilder` has `UseInMemoryDatabase` method which expects a connection string as a parameter. Now, we are done with the required classes and database creation, let's add some authors and book records to the database and then retrieve them. ```csharp using (var context = new BookStore()) { context.Database.EnsureCreated(); var authors = new List { new Author { FirstName ="Carson", LastName ="Alexander", BirthDate = DateTime.Parse("1985-09-01"), Books = new List() { new Book { Title = "Introduction to Machine Learning"}, new Book { Title = "Advanced Topics on Machine Learning"}, new Book { Title = "Introduction to Computing"} } }, new Author { FirstName ="Meredith", LastName ="Alonso", BirthDate = DateTime.Parse("1970-09-01"), Books = new List() { new Book { Title = "Introduction to Microeconomics"} } }, new Author { FirstName ="Arturo", LastName ="Anand", BirthDate = DateTime.Parse("1963-09-01"), Books = new List() { new Book { Title = "Calculus I"}, new Book { Title = "Calculus II"} } } }; context.Authors.AddRange(authors); context.SaveChanges(); } using (var context = new BookStore()) { var list = context.Authors .Include(a => a.Books) .ToList(); foreach (var author in list) { Console.WriteLine(author.FirstName + " " + author.LastName); foreach (var book in author.Books) { Console.WriteLine("\t" + book.Title); } } } ``` ### References * [EF Core InMemory Provider](https://www.learnentityframeworkcore.com/database-providers#ef-core-inmemory-provider) If you run the application, you will see that authors and books are successfully inserted into the database. # Cosmos [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Cosmos ### Cosmos Provider Azure Cosmos DB is a fully managed NoSQL database for modern app development. Single-digit millisecond response times, and automatic and instant scalability, guarantee the speed at any scale. * Business continuity is assured with [SLA-backed](https://azure.microsoft.com/support/legal/sla/cosmos-db) availability and enterprise-grade security. * App development is faster and more productive thanks to turnkey multi-region data distribution anywhere in the world, open-source APIs, and SDKs for popular languages. This database provider allows Entity Framework Core to be used with Azure Cosmos DB. The provider is maintained as part of the [Entity Framework Core Project](https://github.com/dotnet/efcore). It is strongly recommended to familiarize yourself with the [Azure Cosmos DB documentation](https://docs.microsoft.com/en-us/azure/cosmos-db/introduction) before reading this section. #### Install Entity Framework Core Let's create a new application using the **Console App (.NET Core)** template and install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/). [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package, and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` You can also install it by right-clicking on your project in **Solution Explorer** and select **Manage Nuget Packages...** ![](/files/-MfYG2GrYrgR8XxJkZvW) Search for [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) and install the latest version by pressing the install button. **I**t doesn't have additional logic that won't apply to all scenarios. For example, EF Core will need to know what database or datastore you plan on working with and who those providers are in individual packages. #### Register EF Core Provider For SQL Server LocalDB, which is installed with Visual Studio, we need to install [Microsoft.EntityFrameworkCore.Cosmos](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Cosmos/) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.Cosmos ``` Now, you are ready to start your application. #### Create Data Model Model is a collection of classes to interact with the database. * A model stores data that is retrieved according to the Controller's commands and displayed in the View. * It can also be used to manipulate the data to implement the business logic. To create a data model for our application, we will start with the following two entities. ```csharp public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } public List Books { get; set; } } public class Book { public int BookId { get; set; } public string Title { get; set; } public Author Author { get; set; } } ``` There's a one-to-many relationship between `Author` and `Book` entities. In other words, an author can write any number of books, and a book can be written by only one author. #### Create Database Context The database context class provides the main functionality to coordinate Entity Framework with a given data model. * You create this class by deriving from the `System.Data.Entity.DbContext` class. * In your code, you specify which entities are included in the data model. * You can also customize certain Entity Framework behaviors. So, let's add a new `BookStore` class, which will inherit the `DbContext` class. ```csharp public class BookStore : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseCosmos( "https://localhost:8081", "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", databaseName: "BookStoreDb"); } public DbSet Authors { get; set; } public DbSet Books { get; set; } } ``` In EF Core, the `DbContext` has a virtual method called `OnConfiguring` which will get called internally by EF Core. * It will pass in an `optionsBuilder` instance which can be used to configure options for the `DbContext`. * The `optionsBuilder` has `UseSqlServer` method, which expects a connection string as a parameter. Now, we are done with the required classes and database creation, let's add some authors and book records to the database and then retrieve them. ```csharp using (var context = new BookStore()) { context.Database.EnsureCreated(); var authors = new List { new Author { FirstName ="Carson", LastName ="Alexander", BirthDate = DateTime.Parse("1985-09-01"), Books = new List() { new Book { Title = "Introduction to Machine Learning"}, new Book { Title = "Advanced Topics on Machine Learning"}, new Book { Title = "Introduction to Computing"} } }, new Author { FirstName ="Meredith", LastName ="Alonso", BirthDate = DateTime.Parse("1970-09-01"), Books = new List() { new Book { Title = "Introduction to Microeconomics"} } }, new Author { FirstName ="Arturo", LastName ="Anand", BirthDate = DateTime.Parse("1963-09-01"), Books = new List() { new Book { Title = "Calculus I"}, new Book { Title = "Calculus II"} } } }; context.Authors.AddRange(authors); context.SaveChanges(); } using (var context = new BookStore()) { var list = context.Authors .Include(a => a.Books) .ToList(); foreach (var author in list) { Console.WriteLine(author.FirstName + " " + author.LastName); foreach (var book in author.Books) { Console.WriteLine("\t" + book.Title); } } } ``` ### References * [EF Core - Cosmos Provider](https://www.learnentityframeworkcore.com/database-providers#ef-core-azure-cosmos-db-provider) If you run the application, you will see that authors and books are successfully inserted into the database. # PostgreSQL [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## PostgreSQL ### PostgreSQL Provider PostgreSQL is a general-purpose and object-relational database management system, the most advanced open-source database system. * PostgreSQL has been proven to be highly scalable both in the sheer quantity of data it can manage and the number of concurrent users it can accommodate. * It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc. * PostgreSQL requires very minimum maintained efforts because of its stability. #### Install Entity Framework Core Let's create a new application using the **Console App (.NET Core)** template and install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/). [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package, and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` You can also install it by right-clicking on your project in **Solution Explorer** and select **Manage Nuget Packages...** ![](/files/-MfYG2GrYrgR8XxJkZvW) Search for [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) and install the latest version by pressing the install button. **I**t doesn't have additional logic that won't apply to all scenarios. For example, EF Core will need to know what database or datastore you plan on working with and who those providers are in individual packages. #### Register EF Core Provider For PostgreSQL, we need to install [Npgsql.EntityFrameworkCore.PostgreSQL](https://www.nuget.org/packages/Npgsql.EntityFrameworkCore.PostgreSQL) and will get all the packages required for EF Core. ```bash PM> Install-Package Npgsql.EntityFrameworkCore.PostgreSQL ``` Now, you are ready to start your application. #### Create Data Model Model is a collection of classes to interact with the database. * A model stores data that is retrieved according to the Controller's commands and displayed in the View. * It can also be used to manipulate the data to implement the business logic. To create a data model for our application, we will start with the following two entities. ```csharp public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } public List Books { get; set; } } public class Book { public int BookId { get; set; } public string Title { get; set; } public Author Author { get; set; } } ``` There's a one-to-many relationship between `Author` and `Book` entities. In other words, an author can write any number of books, and a book can be written by only one author. #### Create Database Context The database context class provides the main functionality to coordinate Entity Framework with a given data model. * You create this class by deriving from the `System.Data.Entity.DbContext` class. * In your code, you specify which entities are included in the data model. * You can also customize certain Entity Framework behaviors. So, let's add a new `BookStore` class which will inherit the `DbContext` class. ```csharp public class BookStore : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseNpgsql("host=localhost;user id=postgres;password=mw;database=postgres;Pooling=false;Timeout=300;CommandTimeout=300;"); } public DbSet Authors { get; set; } public DbSet Books { get; set; } } ``` In EF Core, the `DbContext` has a virtual method called `OnConfiguring` which will get called internally by EF Core. * It will pass in an `optionsBuilder` instance which can be used to configure options for the `DbContext`. * The `optionsBuilder` has `UseNpgsql` method which expects a connection string as a parameter. Now, we are done with the required classes and database creation, let's add some authors and book records to the database and then retrieve them. ```csharp using (var context = new BookStore()) { context.Database.EnsureCreated(); var authors = new List { new Author { FirstName ="Carson", LastName ="Alexander", BirthDate = DateTime.Parse("1985-09-01"), Books = new List() { new Book { Title = "Introduction to Machine Learning"}, new Book { Title = "Advanced Topics on Machine Learning"}, new Book { Title = "Introduction to Computing"} } }, new Author { FirstName ="Meredith", LastName ="Alonso", BirthDate = DateTime.Parse("1970-09-01"), Books = new List() { new Book { Title = "Introduction to Microeconomics"} } }, new Author { FirstName ="Arturo", LastName ="Anand", BirthDate = DateTime.Parse("1963-09-01"), Books = new List() { new Book { Title = "Calculus I"}, new Book { Title = "Calculus II"} } } }; context.Authors.AddRange(authors); context.SaveChanges(); } using (var context = new BookStore()) { var list = context.Authors .Include(a => a.Books) .ToList(); foreach (var author in list) { Console.WriteLine(author.FirstName + " " + author.LastName); foreach (var book in author.Books) { Console.WriteLine("\t" + book.Title); } } } ``` If you run the application, you will see that authors and books are successfully inserted into the database. ### References * [EF Core PostgreSQL Provider](https://www.learnentityframeworkcore.com/database-providers#ef-core-postgresql-provider) # Project Types [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Project Types In Visual Studio, projects are the containers that developers use to organize source code files and other resources that appear in **Solution Explorer**. * Typically, projects are files, for example, a .csproj file for a Visual C# project, that store references to source code files and resources like bitmap files. * Projects let you organize, build, debug, and deploy source code, references to Web services and databases, and other resources. * Visual Studio includes several project types for languages such as C#, Visual Basic, etc. Visual Studio comes with many project templates to create the necessary code and files to start developing applications. The following are the most commonly used project types. | Project Type | Description | | ----------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | [**Console**](/project-types/console)\*\*\*\* | A **Console application** is a program designed to be used via a text-only computer interface, such as a text terminal, the command line interface, etc. | | \*\*\*\*[**MVC**](/project-types/mvc)\*\*\*\* | The Model-View-Controller (**MVC**) is an architectural pattern that separates an **application** into three main logical components: the model, the view, and the controller. Each of these components is built to handle specific development aspects of an **application**. | | \*\*\*\*[**WinForm**](/project-types/winform)\*\*\*\* | A **Windows Forms application** is an event-driven **application** supported by Microsoft's .NET Framework. Unlike a batch program, it spends most of its time simply waiting for the user to do something, such as fill in a text box or click a button. | | \*\*\*\*[**Xamarin**](/project-types/xamarin)\*\*\*\* | **Xamarin**.**Forms** is an open-source mobile UI framework from Microsoft for building iOS, Android, & Windows **apps** with .NET from a single shared codebase. | | \*\*\*\*[**Blazor**](/project-types/blazor)\*\*\*\* | **Blazor** is a free and open-source web framework that enables developers to create web apps using C# and HTML. | # Console [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Console A **Console application** is a program designed to be used via a text-only computer interface, such as a text terminal, the command line interface, etc. * A user typically interacts with a console application using only a keyboard and display screen, as opposed to GUI applications, which normally require the use of a mouse or other pointing device. * Many console applications such as command-line interpreters are command-line tools, but numerous text-based user interface (TUI) programs also exist. #### Create a Console App To start, we will create a Console application project. The project type comes with all the template files you will need before adding anything. Let's open Visual Studio 2019. If you haven't already installed Visual Studio, go to the [Visual Studio downloads](https://visualstudio.microsoft.com/downloads) page to install it for free. ![](/files/-MfYG8eAf06w_b9hxqSf) On the start window, choose **Create a new project**. ![](/files/-MfYG8eBU5nhWZ4xpACK) On the **Create a new project** window, enter or type *console* in the search box. Next, choose **C#** from the Language list, and then choose **Windows** from the Platform list. Select the **Console App (.NET Core)** template, and then choose **Next**. ![](/files/-MfYG8eC8auQwqo1ua3E) In the **Configure your new project** window, type or enter ***EFCore5InConsoleApp*** in the **Project name** box and click on the **Create** button. ![](/files/-MfYG8eDQrZQp4xhFXam) Visual Studio opens your new project and includes the default "Hello World" code in your project. #### Install Entity Framework Core To use Entity Framework Core we need to install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) library. [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` For SQL Server LocalDB, which is installed with Visual Studio, we need to install [Microsoft.EntityFrameworkCore.SqlServer](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer ``` #### Create a Data Model and Database Context To create a data model for our application, we will start with the following two entities. ```csharp public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } public List Books { get; set; } } public class Book { public int BookId { get; set; } public string Title { get; set; } public Author Author { get; set; } } ``` The database context class provides the main functionality to coordinate Entity Framework with a given data model. So, let's add a new `BookStore` class which will inherit the `DbContext` class. ```csharp public class BookStore : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=BookStoreDb;"); } public DbSet Authors { get; set; } public DbSet Books { get; set; } } ``` Now, we are done with the required classes and database creation, let's add some authors and book records to the database and then retrieve them as shown below. ```csharp static void Main(string[] args) { using (var context = new BookStore()) { context.Database.EnsureCreated(); var authors = new List { new Author { FirstName ="Carson", LastName ="Alexander", BirthDate = DateTime.Parse("1985-09-01"), Books = new List() { new Book { Title = "Introduction to Machine Learning"}, new Book { Title = "Advanced Topics on Machine Learning"}, new Book { Title = "Introduction to Computing"} } }, new Author { FirstName ="Meredith", LastName ="Alonso", BirthDate = DateTime.Parse("1970-09-01"), Books = new List() { new Book { Title = "Introduction to Microeconomics"} } }, new Author { FirstName ="Arturo", LastName ="Anand", BirthDate = DateTime.Parse("1963-09-01"), Books = new List() { new Book { Title = "Calculus I"}, new Book { Title = "Calculus II"} } } }; context.Authors.AddRange(authors); context.SaveChanges(); } using (var context = new BookStore()) { var list = context.Authors .Include(a => a.Books) .ToList(); foreach (var author in list) { Console.WriteLine(author.FirstName + " " + author.LastName); foreach (var book in author.Books) { Console.WriteLine("\t" + book.Title); } } } } ``` If you run the application, you will see that authors and books are successfully inserted into the database and also print on the console window. ![](/files/-MfYG8eEgSbfwJ7bAFPl) ### References * [EF Core Console Application](https://www.learnentityframeworkcore.com/walkthroughs/console-application) # MVC [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## MVC #### What is MVC? The Model-View-Controller (**MVC**) is an architectural pattern that separates an **application** into three main logical components: the model, the view, and the controller. Each of these components is built to handle specific development aspects of an **application**. MVC stands for Model, View, and Controller. MVC separates the application into three components * **Model:** Responsible for maintaining application data and business logic. * **View:** The user interface of the application, which displays the data. * **Controller:** Handles users' requests and renders appropriate view with model data. #### Create MVC App To start, we will create an ASP.NET Core web application project. The project type comes with all template files to create a web application before adding anything. Let's open Visual Studio 2019, if you haven't already installed Visual Studio, go to the [Visual Studio downloads](https://visualstudio.microsoft.com/downloads) page to install it for free. ![](/files/-MfYHjZ_3HRSL8mIPLKR) On the start window, choose **Create a new project**. ![](/files/-MfYG1KVuoxm3N9X2uJr) On the **Create a new project** window, enter or type *asp.net* in the search box. Next, choose **C#** from the Language list, and then choose **Windows** from the Platform list. Select the **ASP.NET Core Web Application** template, and then choose **Next**. ![](/files/-MfYG1KWJ4wieuyzivcp) In the **Configure your new project** window, type or enter ***EFCore5InMvcApp*** in the **Project name** box and click on the **Create** button. ![](/files/-MfYG1KXenhAmH5okdnt) In the **Create a new ASP.NET Core web application** window, verify that **ASP.NET Core 5.0** appears in the top drop-down menu. Then, choose **ASP.NET Core Web App (Model-View-Controller)**, including example ASP.NET Core MVC Views and Controllers, and then click on the **Create** button. ![](/files/-MfYG1KYv3MqFhtCbAeR) Visual Studio opens your new project and includes the default code files in your project as shown in the **Solution Explorer**. #### Install Entity Framework Core To use Entity Framework Core we need to install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) library. [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` For SQL Server LocalDB, which is installed with Visual Studio, we need to install [Microsoft.EntityFrameworkCore.SqlServer](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer ``` We also need to install the following NuGet package. ```csharp PM> Install-Package Microsoft.VisualStudio.Web.CodeGeneration.Design ``` #### Create a Data Model and Database Context In **Solution Explorer**, right-click on the ***Models*** folder and choose **Add > Class**. Enter a class file name **Author.cs** and add the following code. ```csharp using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InMvcApp.Models { public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } public virtual ICollection Books { get; set; } } } ``` Now let's add another entity class `Book` and replace the following code. ```csharp using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InMvcApp.Models { public class Book { public int Id { get; set; } public string Title { get; set; } public int AuthorId { get; set; } public Author Author { get; set; } } } ``` So let's create a folder in your project by right-clicking on your project in Solution Explorer and click **Add > New Folder**. Name the folder **DAL** (Data Access Layer). In that folder, create a new class file named **BookStore.cs**, and replace the following code. ```csharp using EFCore5InMvcApp.Models; using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InMvcApp.DAL { public class BookStore : DbContext { public BookStore(DbContextOptions options) : base(options) { } public DbSet Authors { get; set; } public DbSet Books { get; set; } } } ``` #### Register Context Class To register `BookStore` as a service, open `Startup.cs`, and call the `AddDnContext` in the `ConfigureServices` method. ```csharp // This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddDbContext(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); services.AddControllersWithViews(); } ``` The name of the connection string is passed into the context by calling a method on a `DbContextOptionsBuilder` object. #### Setup Connection String For local development, the ASP.NET Core configuration system reads the connection string from the ***appsettings.json*** file. So let's add the connection to that file as shown below. ```javascript { "ConnectionStrings": { "DefaultConnection": "Data Source=(localdb)\\ProjectsV13;Initial Catalog=BookStoreDb;Trusted_Connection=True;MultipleActiveResultSets=true" }, "Logging": { "LogLevel": { "Default": "Information", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Information" } }, "AllowedHosts": "*" } ``` The above connection string specifies that the Entity Framework will use a `LocalDB` database named `BookStoreDb`. #### Initialize Database The Entity Framework will create an empty database for you. So we need to write a method that's called after the database is created to populate it with test data. In the DAL folder, add a new class `BookStoreInitializer` and replace the following code. ```csharp using EFCore5InMvcApp.Models; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InMvcApp.DAL { public class BookStoreInitializer { public static void Initialize(BookStore context) { context.Database.EnsureCreated(); // Look for any authors. if (context.Authors.Any()) { return; // DB has been seeded } var authors = new List { new Author { FirstName="Carson", LastName="Alexander", BirthDate = DateTime.Parse("1985-09-01")}, new Author { FirstName="Meredith", LastName="Alonso", BirthDate = DateTime.Parse("1970-09-01")}, new Author { FirstName="Arturo", LastName="Anand", BirthDate = DateTime.Parse("1963-09-01")}, new Author { FirstName="Gytis", LastName="Barzdukas", BirthDate = DateTime.Parse("1988-09-01")}, new Author { FirstName="Yan", LastName="Li", BirthDate = DateTime.Parse("2000-09-01")}, }; authors.ForEach(a => context.Authors.Add(a)); context.SaveChanges(); var books = new List { new Book { Title = "Introduction to Machine Learning", AuthorId = 1 }, new Book { Title = "Advanced Topics in Machine Learning", AuthorId = 1 }, new Book { Title = "Introduction to Computing", AuthorId = 1 }, new Book { Title = "Introduction to Microeconomics", AuthorId = 2 }, new Book { Title = "Calculus I", AuthorId = 3 }, new Book { Title = "Calculus II", AuthorId = 3 }, new Book { Title = "Trigonometry Basics", AuthorId = 4 }, new Book { Title = "Special Topics in Trigonometry", AuthorId = 4 }, new Book { Title = "Advanced Topics in Mathematics", AuthorId = 4 }, new Book { Title = "Introduction to AI", AuthorId = 4 }, }; books.ForEach(b => context.Books.Add(b)); context.SaveChanges(); } } } ``` * The above code creates a database when needed and loads test data into the new database. * It also checks if there are any authors in the database, and if not, it assumes the database is new and needs to be seeded with test data. In `Program.cs` file, replace the following code in the `Main` method. ```csharp using EFCore5InMvcApp.DAL; using Microsoft.AspNetCore.Hosting; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using Microsoft.Extensions.Logging; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InMvcApp { public class Program { public static void Main(string[] args) { var host = CreateHostBuilder(args).Build(); using (var scope = host.Services.CreateScope()) { var services = scope.ServiceProvider; try { var context = services.GetRequiredService(); BookStoreInitializer.Initialize(context); } catch (Exception ex) { var logger = services.GetRequiredService>(); logger.LogError(ex, "An error occurred while seeding the database."); } } host.Run(); } public static IHostBuilder CreateHostBuilder(string[] args) => Host.CreateDefaultBuilder(args) .ConfigureWebHostDefaults(webBuilder => { webBuilder.UseStartup(); }); } } ``` On application startup, the `Main` method does the following operations. * Get a database context instance from the dependency injection container. * Call the seed method, passing to it the context. * Dispose of the context when the seed method is done. #### Create Controller and Views MVC controllers are responsible for responding to requests made against your website. Each browser request is mapped to a particular controller. To create a controller, right-click the **Controllers** folder in Solution Explorer, and select **Add > Controller...** ![](/files/-MfYG1KZ9B0j6KsCOhXo) It will open the **Add Scaffold** dialog box. ![](/files/-MfYG1K_Rwu38TN96CfO) Select **MVC Controller with views, using Entity Framework**, and then click the **Add** button. ![](/files/-MfYG1Ka0eMxELhps640) In the **Add MVC Controller with views, using Entity Framework** dialog box, select **Author (EFCore5InMvcApp.Models)** from the **Model class** and **BookStore (EFCore5InMvcApp.DAL)** from the **Data context class** dropdown. Enter **AuthorController** (not AuthorsController) as a **Controller name** and click the **Add** button. ![](/files/-MfYG1Kb1xtsbQb8YB1t) The scaffolder creates an `AuthorController.cs` file and a set of views (`.cshtml` files) that work with the controller. #### Setup Menu Options Open ***Views\Shared\\\_Layout.cshtml***, and add a menu entry for **Authors** after the **Home** menu option as shown below. ```csharp
``` Press Ctrl+F5 to run the project, click the **Authors** tab to see the test data. ![](/files/-MfYG1KguIbYqXrjmbO0) ### References * [EF Core MVC Application](https://www.learnentityframeworkcore.com/walkthroughs/aspnetcore-application) # WinForm [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## WinForm A **Windows Forms application** is an event-driven **application** supported by Microsoft's .NET Framework. Unlike a batch program, it spends most of its time simply waiting for the user to do something, such as fill in a text box or click a button. * Windows Forms provides access to native Windows User Interface Common Controls by wrapping the existent Windows API in managed code. * With the help of Windows Forms, the .NET Framework provides a more comprehensive abstraction above the Win32 API than Visual Basic or MFC did. #### Create a WinForm App The project type comes with all the template files you will need before adding anything. Let's open Visual Studio 2019, if you haven't already installed Visual Studio, go to the [Visual Studio downloads](https://visualstudio.microsoft.com/downloads) page to install it for free. ![](/files/0tVU3Ow1uoCnjemY4JSP) On the start window, choose **Create a new project**. ![](/files/-MfYG6Hhd0wtQ8y1tDSg) On the **Create a new project** window, enter or type *windows forms* in the search box. Next, choose **C#** from the Language list, and then choose **Windows** from the Platform list. Select the **Windows Forms App (.NET)** template, and then choose **Next**. ![](/files/-MfYG6Hiv7aYrkZlu1pt) In the **Configure your new project** window, type or enter ***EFCore5InWinFormApp*** in the **Project name** box and click on the **Create** button, and you will see Visual Studio opens your new project. #### Install Entity Framework Core To use Entity Framework Core we need to install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) library. [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package, and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` For SQL Server LocalDB, which is installed with Visual Studio, we need to install [Microsoft.EntityFrameworkCore.SqlServer](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer ``` #### Create a Data Model and Database Context In **Solution Explorer**, right-click on your project and choose **Add > Class**. Enter a class file name **Author.cs** and add the following code. ```csharp using System; using System.Collections.Generic; using System.Text; namespace EFCore5InWinFormApp { public class Author { public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime BirthDate { get; set; } } } ``` So let's create a new class file named **BookStore.cs**, and replace the following code. ```csharp using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Text; namespace EFCore5InWinFormApp { public class AuthorContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=AuthorDb;"); } public DbSet Authors { get; set; } } } ``` We need to add **DataGridView** to display the data from the database, so let's select the **DataGridView** from the Toolbox and drag it to the Form1 as shown below. ![](/files/-MfYG6Hn7WG0-sE8B8XF) Now in the `Form1_Load` method, we will add some authors' data to the database first, and then we will retrieve the data and display it on the **DataGridView** as shown below. ```csharp private void Form1_Load(object sender, EventArgs e) { using (var context = new AuthorContext()) { context.Database.EnsureCreated(); var authors = new List { new Author { FirstName="Carson", LastName="Alexander", BirthDate = DateTime.Parse("1985-09-01")}, new Author { FirstName="Meredith", LastName="Alonso", BirthDate = DateTime.Parse("1970-09-01")}, new Author { FirstName="Arturo", LastName="Anand", BirthDate = DateTime.Parse("1963-09-01")}, new Author { FirstName="Gytis", LastName="Barzdukas", BirthDate = DateTime.Parse("1988-09-01")}, new Author { FirstName="Yan", LastName="Li", BirthDate = DateTime.Parse("2000-09-01")}, }; context.Authors.AddRange(authors); context.SaveChanges(); dataGridView1.DataSource = authors; } } ``` If you run the application, you will see that authors are successfully inserted into the database and display on the **DataGridView**. ![](/files/-MfYG6HoElcuMA-opWtT) # Xamarin [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Xamarin **Xamarin**.**Forms** is an open-source mobile UI framework from Microsoft for building iOS, Android, & Windows **apps** with .NET from a single shared codebase. * Xamarin.Forms is a feature of Xamarin, the popular mobile development framework that extends the .NET developer platform with tools and libraries for building mobile apps. * Use Xamarin.Forms built-in pages, layouts, and controls to build and design mobile apps from a single API that is highly extensible. Subclass any control to customize their behavior or define your own controls, layouts, pages, and cells to make your app pixel perfect. #### Create a Xamarin App To start, we will create a Xamarin project. The project type comes with all template files to create Xamarin application before adding anything. Let's open Visual Studio 2019, if you haven't already installed Visual Studio, go to the [Visual Studio downloads](https://visualstudio.microsoft.com/downloads) page to install it for free. ![](/files/-MfYG6i4uKE-uMwjwADJ) On the start window, choose **Create a new project**. ![](/files/-MfYG6i5RQzavcosr9IN) On the **Create a new project** window, enter or type *xamarin.forms* in the search box. Next, choose **C#** from the Language list. Select the **Mobile App (Xamarin.Forms)** template, and then choose **Next**. ![](/files/-MfYG6i6Bw62rGGDH1Aw) In the **Configure your new project** window, type or enter ***EFCore5InXamarinApp*** in the **Project name** box and click on the **Create** button. ![](/files/-MfYG6i7SXpYRrxJl3Fw) On the **New Mobile App** page, select the **Flyout** option, check the **Andriod** checkbox, and click on the **Create** button. ![](/files/-MfYG6i8g4lHQIKE5WKd) Visual Studio opens your new project and includes the default code files in your project as shown in the **Solution Explorer**. #### Install Entity Framework Core To use Entity Framework Core we need to install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) library. [I](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/)t is available as a nuget package and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` For SQLite, we need to install [Microsoft.EntityFrameworkCore.Sqlite](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Sqlite) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.Sqlite ``` #### Create a Data Model and Database Context In **Solution Explorer**, right-click on the ***Models*** folder and choose **Add > Class**. Enter a class file name **Author.cs** and add the following code. ```csharp using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Text; namespace EFCore5InXamarinApp.Models { public class Author { [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public string Id { get; set; } public string Name { get; set; } public string Address { get; set; } } } ``` To add a context class, right-click on the ***Services*** folder in **Solution Explorer**, and choose **Add > Class**. Enter a class file name **AuthorContext.cs** and add the following code. ```csharp using EFCore5InXamarinApp.Models; using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Text; namespace EFCore5InXamarinApp.Services { class AuthorContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { var path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); optionsBuilder.UseSqlite($"Data Source={path}/AuthorContext.db;"); } public DbSet Authors { get; set; } } } ``` Add another class to the ***Services*** folder and name it **AuthorDataStore.cs** and add the following code. ```csharp using EFCore5InXamarinApp.Models; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace EFCore5InXamarinApp.Services { public class AuthorDataStore : IDataStore { AuthorContext _context; public AuthorDataStore() { _context = new AuthorContext(); } public async Task AddItemAsync(Author author) { _context.Authors.Add(author); _context.SaveChanges(); return await Task.FromResult(true); } public async Task UpdateItemAsync(Author author) { var oldAuthor = _context.Authors.Where((Author arg) => arg.Id == author.Id).FirstOrDefault(); _context.Authors.Remove(oldAuthor); _context.Authors.Add(author); _context.SaveChanges(); return await Task.FromResult(true); } public async Task DeleteItemAsync(string id) { var oldAuthor = _context.Authors.Where((Author arg) => arg.Id == id).FirstOrDefault(); _context.Authors.Remove(oldAuthor); _context.SaveChanges(); return await Task.FromResult(true); } public async Task GetItemAsync(string id) { return await Task.FromResult(_context.Authors.FirstOrDefault(s => s.Id == id)); } public async Task> GetItemsAsync(bool forceRefresh = false) { return await Task.FromResult(_context.Authors); } } } ``` It implements the `IDataStore` interface and contains all the required database operations. To get the implementation of `IDataStore` add the following code the `BaseViewModel`. ```csharp public IDataStore AuthDataStore => DependencyService.Get>(); ``` #### Add Views and ViewModels **Create New Author View and ViewModel** To create a new author view, right-click on the **Views** folder and select **Add > New Item...** ![](/files/-MfYG6iAPGC1U58JAitZ) Select the **Content Page** template, enter `NewAuthorPage.xaml` in the **Name** field and click on the **Add** button. Replace the following code in `NewAuthorPage.xaml` file. ```csharp ``` Now let's add a view model class for this page by adding a new class in the **ViewModels** folder, name it `NewAuthorViewModel.cs` and replace the following code. ```csharp using EFCore5InXamarinApp.Models; using System; using System.Collections.Generic; using System.Text; using System.Windows.Input; using Xamarin.Forms; namespace EFCore5InXamarinApp.ViewModels { public class NewAuthorViewModel : BaseViewModel { private string name; private string address; public NewAuthorViewModel() { SaveCommand = new Command(OnSave, ValidateSave); CancelCommand = new Command(OnCancel); this.PropertyChanged += (_, __) => SaveCommand.ChangeCanExecute(); } private bool ValidateSave() { return !String.IsNullOrWhiteSpace(name) && !String.IsNullOrWhiteSpace(address); } public string Name { get => name; set => SetProperty(ref name, value); } public string Address { get => address; set => SetProperty(ref address, value); } public Command SaveCommand { get; } public Command CancelCommand { get; } private async void OnCancel() { // This will pop the current page off the navigation stack await Shell.Current.GoToAsync(".."); } private async void OnSave() { Author newAuthor = new Author() { Id = Guid.NewGuid().ToString(), Name = Name, Address = Address }; await AuthDataStore.AddItemAsync(newAuthor); // This will pop the current page off the navigation stack await Shell.Current.GoToAsync(".."); } } } ``` Update the `NewAuthorPage.xaml.cs` to bind the view model with a view. ```csharp using EFCore5InXamarinApp.Models; using EFCore5InXamarinApp.ViewModels; using System; using System.Collections.Generic; using System.ComponentModel; using Xamarin.Forms; using Xamarin.Forms.Xaml; namespace EFCore5InXamarinApp.Views { public partial class NewAuthorPage : ContentPage { public Author Author { get; set; } public NewAuthorPage() { InitializeComponent(); BindingContext = new NewAuthorViewModel(); } } } ``` **Create Author Detail View and ViewModel** To create an author detail view, right-click on the **Views** folder and select **Add > New Item...** ![](/files/-MfYG6iEG1nkGhqADDGy) Select the **Content Page** template, enter `AuthorDetailPage.xaml` in the **Name** field and click on the **Add** button. Replace the following code in `AuthorDetailPage.xaml` file. ```csharp ``` Now let's add a view model class for this page by adding a new class in the **ViewModels** folder, name it `AuthorDetailViewModel.cs` and replace the following code. ```csharp using EFCore5InXamarinApp.Models; using EFCore5InXamarinApp.Services; using System; using System.Diagnostics; using System.Threading.Tasks; using Xamarin.Forms; namespace EFCore5InXamarinApp.ViewModels { [QueryProperty(nameof(AuthorId), nameof(AuthorId))] public class AuthorDetailViewModel : BaseViewModel { private string authorId; private string name; private string address; public string Id { get; set; } public string Name { get => name; set => SetProperty(ref name, value); } public string Address { get => address; set => SetProperty(ref address, value); } public string AuthorId { get { return authorId; } set { authorId = value; LoadAuthorId(value); } } public async void LoadAuthorId(string authorId) { try { var author = await AuthDataStore.GetItemAsync(authorId); Id = author.Id; Name = author.Name; Address = author.Address; } catch (Exception) { Debug.WriteLine("Failed to Load Author"); } } } } ``` Update the `AuthorDetailPage.xaml.cs` to bind the view model with a view. ```csharp using EFCore5InXamarinApp.ViewModels; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Xamarin.Forms; using Xamarin.Forms.Xaml; namespace EFCore5InXamarinApp.Views { [XamlCompilation(XamlCompilationOptions.Compile)] public partial class AuthorDetailPage : ContentPage { public AuthorDetailPage() { InitializeComponent(); BindingContext = new AuthorDetailViewModel(); } } } ``` **Create Authors View and ViewModel** To display all the authors from the database, right-click on the **Views** folder and select **Add > New Item...** ![](/files/-MfYG6iM_m-cK4qVoIs3) Select the **Content Page** template, enter `AuthorsPage.xaml` in the **Name** field and click on the **Add** button. Replace the following code in `AuthorsPage.xaml` file. ```markup ``` Now let's add a view model class for this page by adding a new class in the **ViewModels** folder, name it `AuthorsViewModel.cs` and replace the following code. ```csharp using EFCore5InXamarinApp.Models; using EFCore5InXamarinApp.Views; using System; using System.Collections.Generic; using System.Collections.ObjectModel; using System.Diagnostics; using System.Text; using System.Threading.Tasks; using Xamarin.Forms; namespace EFCore5InXamarinApp.ViewModels { public class AuthorsViewModel : BaseViewModel { private Author _selectedAuthor; public ObservableCollection Authors { get; } public Command LoadAuthorsCommand { get; } public Command AddAuthorCommand { get; } public Command AuthorTapped { get; } public AuthorsViewModel() { Title = "Authors"; Authors = new ObservableCollection(); LoadAuthorsCommand = new Command(async () => await ExecuteLoadAuthorCommand()); AuthorTapped = new Command(OnAuthorSelected); AddAuthorCommand = new Command(OnAddAuthor); } async Task ExecuteLoadAuthorCommand() { IsBusy = true; try { Authors.Clear(); var authors = await AuthDataStore.GetItemsAsync(true); foreach (var author in authors) { Authors.Add(author); } } catch (Exception ex) { Debug.WriteLine(ex); } finally { IsBusy = false; } } public void OnAppearing() { IsBusy = true; SelectedAuthor = null; } public Author SelectedAuthor { get => _selectedAuthor; set { SetProperty(ref _selectedAuthor, value); OnAuthorSelected(value); } } private async void OnAddAuthor(object obj) { await Shell.Current.GoToAsync(nameof(NewAuthorPage)); } async void OnAuthorSelected(Author author) { if (author == null) return; // This will push the AuthorDetailPage onto the navigation stack await Shell.Current.GoToAsync($"{nameof(AuthorDetailPage)}?{nameof(AuthorDetailViewModel.AuthorId)}={author.Id}"); } } } ``` Update the `AuthorsPage.xaml.cs` to bind the view model with a view. ```csharp using EFCore5InXamarinApp.ViewModels; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Xamarin.Forms; using Xamarin.Forms.Xaml; namespace EFCore5InXamarinApp.Views { [XamlCompilation(XamlCompilationOptions.Compile)] public partial class AuthorsPage : ContentPage { AuthorsViewModel _viewModel; public AuthorsPage() { InitializeComponent(); BindingContext = _viewModel = new AuthorsViewModel(); } protected override void OnAppearing() { base.OnAppearing(); _viewModel.OnAppearing(); } } } ``` Let's update the `App.xaml.cs` to create and initialize the database with test data. ```csharp using EFCore5InXamarinApp.Models; using EFCore5InXamarinApp.Services; using EFCore5InXamarinApp.Views; using System; using System.Linq; using Xamarin.Forms; using Xamarin.Forms.Xaml; namespace EFCore5InXamarinApp { public partial class App : Application { public App() { InitializeComponent(); using (var context = new AuthorContext()) { context.Database.EnsureDeleted(); context.Database.EnsureCreated(); context.Authors.Add(new Author() { Name = "Karl Jablonski", Address = "Skagen 21, Stavanger, Norway" }); context.Authors.Add(new Author() { Name = "Matti Karttunen", Address = "Keskuskatu 45, Helsinki, Finland" }); context.Authors.Add(new Author() { Name = "Tom Erichsen", Address = "305 - 14th Ave. S. Suite 3B, Seattle, USA" }); context.SaveChanges(); var list = context.Authors.ToList(); } DependencyService.Register(); MainPage = new AppShell(); } protected override void OnStart() { } protected override void OnSleep() { } protected override void OnResume() { } } } ``` To register the route for the author pages, replace the following code in `AppShell.xaml.cs` ```csharp using EFCore5InXamarinApp.ViewModels; using EFCore5InXamarinApp.Views; using System; using System.Collections.Generic; using Xamarin.Forms; namespace EFCore5InXamarinApp { public partial class AppShell : Xamarin.Forms.Shell { public AppShell() { InitializeComponent(); Routing.RegisterRoute(nameof(AuthorDetailPage), typeof(AuthorDetailPage)); Routing.RegisterRoute(nameof(NewAuthorPage), typeof(NewAuthorPage)); } private async void OnMenuItemClicked(object sender, EventArgs e) { await Shell.Current.GoToAsync("//LoginPage"); } } } ``` Now we also need to add the options to the menu in `AppShell.xaml` file to navigate to the Authors page. ```csharp ``` Let's run your application and click on the Authors menu option. ![](/files/-MfYG6iQvhZgPZK1VwQs) To add a new author tap the **ADD** button which is on the top right corner, it will navigate to the **New Author Page**. ![](/files/-MfYG6iR4hDZqJAmbPno) Enter **Name** and **Address** and click on the **SAVE** button and you will see a new author is added. ![](/files/-MfYG6iSCWFxmRsvTZEP) To view the detail, tap on an author and it will navigate to the detail page. ![](/files/-MfYG6iTlSDtL2QWDKw1) # Blazor [**Improve EF Core performance with EF Extensions**](https://entityframework-extensions.net/) [![](https://zzzprojects.github.io/images/logo/entityframework-extensions-pub.jpg)](https://entityframework-extensions.net/) ## Blazor **Blazor** is a free and open-source web framework that enables developers to create web apps using C# and HTML. Blazor is a framework for building interactive client-side web UI with [.NET](https://docs.microsoft.com/en-us/dotnet/standard/tour). * Create rich interactive UIs using [C#](https://docs.microsoft.com/en-us/dotnet/csharp/) instead of [JavaScript](https://www.javascript.com/). * Share server-side and client-side app logic written in .NET. * Render the UI as HTML and CSS for wide browser support, including mobile browsers. * Integrate with modern hosting platforms, such as [Docker](https://docs.microsoft.com/en-us/dotnet/standard/microservices-architecture/container-docker-introduction/index). #### Create Blazor App To start, we will create an ASP.NET Core hosted Blazor App project. The project type comes with all template files to create a Blazor application before adding anything. Let's open Visual Studio 2019. If you haven't already installed Visual Studio, go to the [Visual Studio downloads](https://visualstudio.microsoft.com/downloads) page to install it for free. ![](/files/-MfYG6KBKtM046DLsGFz) On the start window, choose **Create a new project**. ![](/files/-MfYG6KCJJJoH8tzZWJR) On the **Create a new project** window, enter or type *blazor* in the search box. Next, choose **C#** from the Language list. Select the **Blazor App** template, and then choose **Next**. ![](/files/-MfYG6KDNfJq3591VFnh) In the **Configure your new project** window, type or enter ***EFCore5InBlazorApp*** in the **Project name** box and click on the **Create** button. ![](/files/-MfYG6KEfJ7y1fmtIvrm) Now select the **Blazor WebAssembly App** project template and select the **ASP.NET Core hosted** check box. Click on the **Create** button to create an ASP.NET Core-hosted application. ![](/files/-MfYG6KFULWrI9ZiC68v) Visual Studio opens your new project and includes the default code files in your project as shown in the **Solution Explorer**. You will see that 3 projects are created inside this solution. * **EFCore5InBlazorApp.Client**: It has the client-side code and contains the pages that will be rendered on the browser. * **EFCore5InBlazorApp.Server**: It has the server-side code, such as DB related operations and the web API. * **EFCore5InBlazorApp.Shared**: It contains the shared code that can be accessed by both client and server. #### Install Entity Framework Core To use Entity Framework Core we need to install [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) library in the **EFCore5InBlazorApp.Server** project. [It](https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/) is available as a nuget package, and you can install it using **Nuget Package Manager**. In the **Package Manager Console** window, enter the following command. ```bash PM> Install-Package Microsoft.EntityFrameworkCore ``` For SQL Server LocalDB, which is installed with Visual Studio, we need to install [Microsoft.EntityFrameworkCore.SqlServer](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer) and will get all the packages required for EF Core. ```bash PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer ``` We also need to install the following NuGet package. ```csharp PM> Install-Package Microsoft.VisualStudio.Web.CodeGeneration.Design ``` #### Create a Data Model and Database Context To add a data model to the application, right-click on **EFCore5InBlazorApp.Shared** project and then select **Add > New Folder** and name the folder **Models**. Right-click on the **Models** folder and select **Add > Class...,** enter a class file name **Author.cs,** and add the following code. ```csharp using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace EFCore5InBlazorApp.Shared.Models { public class Author { public string Id { get; set; } public string Name { get; set; } public string Gender { get; set; } public string Address { get; set; } } } ``` So let's create a folder in your project by right-clicking on **EFCore5InBlazorApp.Server** project in **Solution Explorer** and click **Add > New Folder**. Name the folder **DAL** (Data Access Layer). In that folder, create a new class file named **AuthorContext.cs**, and replace the following code. ```csharp using EFCore5InBlazorApp.Shared.Models; using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InBlazorApp.Server.DAL { public class AuthorContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Data Source=(localdb)\\ProjectsV13;Initial Catalog=AuthorDb;Trusted_Connection=True;MultipleActiveResultSets=true"); } public DbSet Authors { get; set; } } } ``` #### Register Context Class To register `AuthoContext` as a service, open `Startup.cs` from **EFCore5InBlazorApp.Server** project, and call the `AddDnContext` in the `ConfigureServices` method. ```csharp // This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddDbContext(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); services.AddControllersWithViews(); } ``` The name of the connection string is passed into the context by calling a method on a `DbContextOptionsBuilder` object. #### Setup Connection String For local development, the ASP.NET Core configuration system reads the connection string from the ***appsettings.json*** file. So let's add the connection to that file as shown below. ```javascript { "ConnectionStrings": { "DefaultConnection": "Data Source=(localdb)\\ProjectsV13;Initial Catalog=AuthorDb;Trusted_Connection=True;MultipleActiveResultSets=true" }, "Logging": { "LogLevel": { "Default": "Information", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Information" } }, "AllowedHosts": "*" } ``` The above connection string specifies that the Entity Framework will use a `LocalDB` database named `AuthorDb`. #### Initialize Database The Entity Framework will create an empty database for you. So we need to write a method that's called after the database is created to populate it with test data. In the **DAL** folder of **EFCore5InBlazorApp.Server** project, add a new class `AuthorContextInitializer` and replace the following code. ```csharp using EFCore5InBlazorApp.Shared.Models; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InBlazorApp.Server.DAL { public class AuthorContextInitializer { public static void Initialize(AuthorContext context) { context.Database.EnsureCreated(); // Look for any authors. if (context.Authors.Any()) { return; // DB has been seeded } var authors = new List { new Author { Name="Carson Alexander", Gender="Male", Address = "Skagen 21, Stavanger, Norway"}, new Author { Name="Meredith Alonso", Gender="Female", Address = "Keskuskatu 45, Helsinki, Finland"}, new Author { Name="Arturo Anand", Gender="Male", Address = "305 - 14th Ave. S. Suite 3B, Seattle, USA"}, }; authors.ForEach(a => context.Authors.Add(a)); context.SaveChanges(); } } } ``` * The above code creates a database when needed and loads test data into the new database. * It also checks if there are any authors in the database, and if not, it assumes the database is new and needs to be seeded with test data. In `Program.cs` file of **EFCore5InBlazorApp.Server** project, replace the following code in the `Main` method. ```csharp using EFCore5InBlazorApp.Server.DAL; using Microsoft.AspNetCore.Hosting; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using Microsoft.Extensions.Logging; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InBlazorApp.Server { public class Program { public static void Main(string[] args) { var host = CreateHostBuilder(args).Build(); using (var scope = host.Services.CreateScope()) { var services = scope.ServiceProvider; try { var context = services.GetRequiredService(); AuthorContextInitializer.Initialize(context); } catch (Exception ex) { var logger = services.GetRequiredService>(); logger.LogError(ex, "An error occurred while seeding the database."); } } host.Run(); } public static IHostBuilder CreateHostBuilder(string[] args) => Host.CreateDefaultBuilder(args) .ConfigureWebHostDefaults(webBuilder => { webBuilder.UseStartup(); }); } } ``` #### Create Web API Controller To create a controller, right-click the **Controllers** folder in **EFCore5InBlazorApp.Server**, and select **Add > Controller...** and it will open the **Add Scaffold** dialog box. ![](/files/-MfYG6KKEH_STz4X2naH) Select **API Controller - Empty**, and then click the **Add** button. ![](/files/-MfYG6KLJLrjso7ojwQH) Select **API Controller - Empty**, enter **AuthorController** (not AuthorsController) as a **Controller name** and click the **Add** button. It will create an empty controller, let's add the following code which contains all the basic CRUD operations. ```csharp using EFCore5InBlazorApp.Server.DAL; using EFCore5InBlazorApp.Shared.Models; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EFCore5InBlazorApp.Server.Controllers { [Route("api/[controller]")] [ApiController] public class AuthorController : ControllerBase { AuthorContext context = new AuthorContext(); [HttpGet] [Route("Index")] public IEnumerable Index() { return context.Authors.ToList(); } [HttpPost] [Route("Create")] public void Create([FromBody] Author author) { if (ModelState.IsValid) { context.Authors.Add(author); context.SaveChanges(); } } [HttpGet] [Route("Details/{id}")] public Author Details(int id) { Author author = context.Authors.Find(id); return author; } [HttpPut] [Route("Edit")] public void Edit([FromBody] Author author) { if (ModelState.IsValid) { context.Entry(author).State = EntityState.Modified; context.SaveChanges(); } } [HttpDelete] [Route("Delete/{id}")] public void Delete(int id) { Author author = context.Authors.Find(id); context.Authors.Remove(author); context.SaveChanges(); } } } ``` #### Add Views To display all the authors, we need to create a view by right-clicking on the **Pages** folder in **EFCore5InBlazorApp.Client** project and select **Add > Razor Component...** ![](/files/-MfYG6KMGRbateTBSA7v) Select **Razor Component**, enter **GetAuthors.razor** as a **Name,** and click the **Add** button. It will create an empty view and then add the following code. ```csharp @page "/getauthors" @using EFCore5InBlazorApp.Shared.Models @inject HttpClient Http

Authors

This component demonstrates fetching Author data from the server.

Create New

@if (authors == null) {

Loading...

} else { @foreach (var author in authors) { }
Name Gender Address
@author.Name @author.Gender @author.Address Edit | Delete
} @code { Author[] authors; protected override async Task OnInitializedAsync() { authors = await Http.GetFromJsonAsync("/api/Author/Index"); } } ``` Now let's add one more page that will use to add a new author, call it **AddAuthor.razor,** and add the following code. ```csharp @page "/addauthor" @using EFCore5InBlazorApp.Shared.Models @inject HttpClient Http @inject NavigationManager urlNavigationManager

Create Author

 @code { Author author = new Author(); protected async Task CreateAuthor() { await Http.PostAsJsonAsync("/api/Author/Create", author); urlNavigationManager.NavigateTo("/getauthors"); } void Cancel() { urlNavigationManager.NavigateTo("/getauthors"); } } ``` Let's run your application and click on the Authors menu option. ![](/files/-MfYG6KNQ-Yuv4nXJoDL) To add a new author click on the **Create New** link and it will open the **Create Author** Page. ![](/files/-MfYG6KOW5KVTd_seBIV) Enter **Name, Gender,** and **Address** and click on the **Save** button and you will see a new author is added. ![](/files/-MfYG6KPMnhz38R3kFiD)