Entity Framework

I ended up going with setting up Entity Framework first.  I found a good tutorial that stepped through all the basics.  I wish I had seen this a year ago when I first started using EF.

https://www.youtube.com/watch?v=qkJ9keBmQWo&feature=youtu.be

I really recommend watching the video for all the details.  I'm going to capture the hints that were helpful to me so I don't forget them.

Setup

1. Create "ASP.NET Core Web Application" Web Application in Visual Studio.

• This will create a Razor Pages application so we have a framework

2. Create a "Class Library (.NET Standard)" project for Data layer

• .NET Standard project will work with .Net Framework, .Net Core, Xamarin, etc.  .Net Core projects won't work with .Net Framework

3. Use NuGet to add "Microsoft.EntityFrameworkCore.SqlServer" to the Data layer

4. Create a constructor that inherits from DbContext.  Ctor should pass in DbContextOptions and call base(options).  Create a DbSet property for each entity.

5.  Set up the front end to use EntityFramework.  Yuck!

• Add reference to "Microsoft.EntityFrameworkCore.SqlServer"
• In StartUp.cs, ConfigureServices()
  
  services.AddDbContext<PeopleContext>(options =>
  {
       options.UseSqlServer(Configuration.GetConnectionString("Default"));
  });
• Make sure the Default connection string is set up in your appsettings.json file
• For Dev, you will most likely need LocalDb.  Go to Tools | Get Tools and Features to install LocalDb.

6.  Create Migrations

• Add a reference to "Microsoft.EntityFrameworkCore.Tools" to run migrations.
• Open Package Manager Console
• Make sure the "Default Project" is you data layer
• Run "Add-Migration Initial DBCreation".  So you start out with no database and you migrate to a database.
• Look at generated migration files to see what entity framework creates

7.  Make changes to tables

• Make a change to entity or create a new entity
• Run "Add-Migration <name of change>"

8.  Create the database

• Run "update-database" to run migrations
• Can look at __EFMigrationsHistory table to see which migrations have been run

Improve Entities

1.  All columns default to nullable.  Use [Required] attribute for required fields/columns.

2. Strings default to nvarchar(max).  Use [MaxLength(#)] to change field size.  EF defaults to nvarchar(max).

3.  If Unicode is not needed (for example, US zip codes).  Use [Column] attribute:
       [Column(TypeName = "varchar(10)")]

The code is usually responsive in development.  When many users start using your app, more and more memory is used.

Testing done with nvarchar4000 and performance

Using EF in your code

1.  Inject your context in constructors of whatever objects need to access the database.

2.  When making changes to the database, be sure to call _db.SaveChanges().

3.  You can include data from other tables by using the .Includes() method

         var people = _db.People.Include(a => a.Addresses).ToList();

    ToList() actually executes the query.  Includes calls result in LEFT JOINS in SQL.  This will give many more records than is returned to you in C#.  This can cause memory issues the bigger your database grows.  So only use Include() clauses when you need that data.
4.  Use Where() clauses to filter data from SQL.  Filter before calling ToList();

Comparison with Dapper

Benefits of EF Core

1.  Faster development speed
2.  You don't have to know SQL
3.  Tools are good

Benefits of Dapper (he prefers Dapper)

1.  Faster in Production
2.  Easier to work with for SQL developer
3.  Knows exactly how to change the query to make more performant
4.  Designed for loose coupling

Other notes:

At about the 1:18 mark, he starts talking about the profiler in SQL Server Management Studio
Should I Use Entity Framework