Tag: entity framework

Get Table and Columns Name of mapped entity in Entity Framework Core

- by mahmood 
var context = new BiContext();

var entityType = context.Model.FindEntityType(typeof(InvoiceDetail));

var tableName = entityType.GetTableName();
Console.WriteLine(tableName);
var properties = entityType.GetProperties();

# Property Name in Class
foreach (var p in properties)
{
    Console.WriteLine(p.Name);
}

# Column Name in Table
foreach (var p in properties)
{
    Console.WriteLine(p.GetColumnName());
}

References
https://stackoverflow.com/questions/45667126/how-to-get-table-name-of-mapped-entity-in-entity-framework-core

Indexes in Entity Framework Data Annotations

- by mahmood 
[Index(nameof(Url))]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

Composite index

[Index(nameof(FirstName), nameof(LastName))]
public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Index uniqueness

[Index(nameof(Url), IsUnique = true)]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

Index sort order

[Index(nameof(Url), nameof(Rating), AllDescending = true)]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
}
[Index(nameof(Url), nameof(Rating), IsDescending = new[] { false, true })]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
}

Index name

[Index(nameof(Url), Name = "Index_Url")]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

References
https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations

ASP.NET Core Blazor Server with Entity Framework Core

- by mahmood

appsettings.Development.json

{
  "DetailedErrors": true,
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
    "Microsoft.Hosting.Lifetime": "Information",
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

Database access

using var context = new MyContext();

return await context.MyEntities.ToListAsync();
if (Loading)
{
    return;
}

try
{
    Loading = true;

    ...
}
finally
{
    Loading = false;
}

New DbContext instances

builder.Services.AddDbContextFactory<ContactContext>(opt =>
    opt.UseSqlite($"Data Source={nameof(ContactContext.ContactsDb)}.db"));
private async Task DeleteContactAsync()
{
    using var context = DbFactory.CreateDbContext();
    Filters.Loading = true;

    if (Wrapper is not null && context.Contacts is not null)
    {
        var contact = await context.Contacts
            .FirstAsync(c => c.Id == Wrapper.DeleteRequestId);

        if (contact is not null)
        {
            context.Contacts?.Remove(contact);
            await context.SaveChangesAsync();
        }
    }

    Filters.Loading = false;

    await ReloadAsync();
}

Scope to the component lifetime

@implements IDisposable
@inject IDbContextFactory<ContactContext> DbFactory
public void Dispose()
{
    Context?.Dispose();
}
protected override async Task OnInitializedAsync()
{
    Busy = true;

    try
    {
        Context = DbFactory.CreateDbContext();

        if (Context is not null && Context.Contacts is not null)
        {
            var contact = await Context.Contacts.SingleOrDefaultAsync(c => c.Id == ContactId);

            if (contact is not null)
            {
                Contact = contact;
            }
        }
    }
    finally
    {
        Busy = false;
    }

    await base.OnInitializedAsync();
}

References
https://docs.microsoft.com/en-us/aspnet/core/blazor/blazor-server-ef-core?view=aspnetcore-6.0

Entity Framework Core Database-First using Npgsql

- by mahmood 
dotnet ef dbcontext scaffold "Host=my_host;Database=my_db;Username=my_user;Password=my_pw" Npgsql.EntityFrameworkCore.PostgreSQL

If you have other tables in your database, you may use additional parameters – -Schemas and -Tables – to filter the list of schemas and/or tables that are added to the model. For example, you can use the following command:

dotnet ef dbcontext scaffold "host=server;database=test;user id=postgres;" Devart.Data.PostgreSql.Entity.EFCore -Tables dept,emp

References
https://www.npgsql.org/efcore/index.html
https://www.devart.com/dotconnect/postgresql/docs/EFCore-Database-First-NET-Core.html

Map Table Name and Column Name in Entity Framework

- by mahmood

Table name

[Table("blogs")]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .ToTable("blogs");
}

Table schema

[Table("blogs", Schema = "blogging")]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .ToTable("blogs", schema: "blogging");
}

Rather than specifying the schema for each table, you can also define the default schema at the model level with the fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("blogging");
}

Column name

public class Account
 {
     [Column("MyPasswordHashColumn")]
     public string PasswordHash { get; set; }

 }
public class YourContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Language>(x => x
            .ToTable("MyAccountsTable")
            .Property(entity => entity.PasswordHash)
                .HasColumnName("MyPasswordHashColumn")
        );
    }
}

References
https://docs.microsoft.com/en-us/ef/core/modeling/entity-types?tabs=data-annotations
https://stackoverflow.com/questions/42190909/how-to-specify-entity-framework-core-table-mapping

Reset Entity Framework Migrations and Drop Database

- by mahmood

Remove all files from the migrations folder.

dotnet-ef database drop -f -v
dotnet-ef migrations add Initial
dotnet-ef database update

Do that only if you don’t care about your current persisted data

Without Dropping Database

The Issue: You have mucked up your migrations and you would like to reset it without deleting your existing tables.

The Problem: You can’t reset migrations with existing tables in the database as EF wants to create the tables from scratch.

What to do:

  1. Delete existing migrations from Migrations_History table.
  2. Delete existing migrations from the Migrations Folder.
  3. Run dotnet-ef migrations add Initial. This will create a migration in your Migration folder that includes creating the tables (but it will not run it so it will not error out.)
  4. You now need to create the initial row in the MigrationHistory table so EF has a snapshot of the current state. EF will do this if you apply a migration. However, you can’t apply the migration that you just made as the tables already exist in your database. So go into the Migration and comment out all the code inside the “Up” method.
  5. Now run dotnet-ef database update. It will apply the Migration (while not actually changing the database) and create a snapshot row in MigrationHistory.

You have now reset your migrations and may continue with normal migrations.

References
https://stackoverflow.com/questions/11679385/reset-entity-framework-migrations