Advance Ef Core and PostgreSQL in .Net 6
Tags: dotnet, postgres, database, tutorial
Published: 2022-08-18 10:01:08

In this article, we will be exploring advance Ef Core an PostgreSQL with .NET 6. The steps we need need to take to setup, configure and utilise PostgreSQL with .NET 6 WebAPI.

You can watch the full video on YouTube
{% embed https://youtu.be/ApZSICk99RA %}

You can also find the source code on Github

[https://github.com/mohamadlawand087/Net6-EfCore-Postgres-V2](https://github.com/mohamadlawand087/Net6-EfCore-Postgres-V2)

Changing Table Name in the database

```csharp
modelBuilder.Entity<Driver>(entity =>
{
    entity.HasOne(d => d.Team)
        .WithMany(p => p.Drivers)
        .HasForeignKey(d => d.TeamId)
        .OnDelete(DeleteBehavior.Restrict)
        .HasConstraintName("FK_Driver_Team");

    entity.ToTable("F1Drivers");
});

modelBuilder.Entity<Team>().ToTable("F1Teams");
```

Table Comments

```csharp
modelBuilder.Entity<Team>()
        .ToTable("F1Teams")
        .HasComment("This table is used to store F1 teams information");
```

Column Comments

```csharp
modelBuilder.Entity<Team>()
        .ToTable("F1Teams")
        .HasComment("This table is used to store F1 teams information")
        .Property(b => b.Name)
        .HasComment("The Name of the team");
```

Let us see how we can ignore properties from being migrated to the database

```csharp
public class Driver : BaseEntity
{
    public Guid TeamId { get; set; }
    public string Name { get; set; } = "";
    public int RacingNumber { get; set; } 
    public virtual Team Team { get; set; }

    [NotMapped]
    public string NumberAndName { get; set; }
}

public class Team : BaseEntity
{
    public Team()
    {
        Drivers = new HashSet<Driver>();
    }

    public string Name { get; set; } = "";
    public string Year { get; set; } = "";

    [NotMapped]
    public string NameAndYear { get; set; }

    public virtual ICollection<Driver> Drivers { get; set; }
}

// Or with Fluent API
modelBuilder.Entity<Driver>(entity =>
{
    entity.HasOne(d => d.Team)
        .WithMany(p => p.Drivers)
        .HasForeignKey(d => d.TeamId)
        .OnDelete(DeleteBehavior.Restrict)
        .HasConstraintName("FK_Driver_Team");

    entity.ToTable("F1Drivers");
    entity.Ignore(x => x.NumberAndName);
});

modelBuilder.Entity<Team>()
        .ToTable("F1Teams")
        .HasComment("This table is used to store F1 teams information")
        .Ignore(x => x.NameAndYear);
```

Update Column Name

```csharp
// Fluent API
modelBuilder.Entity<Team>()
			.Property(b => b.Id)
        .HasColumnName("team_id");

public class Driver : BaseEntity
{
    public Guid TeamId { get; set; }
    public string Name { get; set; } = "";

    [Column("racing_number")]
    public int RacingNumber { get; set; } 
    public virtual Team Team { get; set; }

    [NotMapped]
    public string NumberAndName { get; set; }
}
```

Column Data type

```csharp
// Data Annotation
public class Driver : BaseEntity
{
    public Guid TeamId { get; set; }

    [Column(TypeName = "varchar(200)")]
    public string Name { get; set; } = "";

    [Column("racing_number")]
    public int RacingNumber { get; set; } 
    public virtual Team Team { get; set; }

    [NotMapped]
    public string NumberAndName { get; set; }
}

// Fluent API
modelBuilder.Entity<Driver>(entity =>
        {
            entity.HasOne(d => d.Team)
                .WithMany(p => p.Drivers)
                .HasForeignKey(d => d.TeamId)
                .OnDelete(DeleteBehavior.Restrict)
                .HasConstraintName("FK_Driver_Team");

            entity.Property(b => b.Name).HasColumnType("varchar(200)");

            entity.ToTable("F1Drivers");
            entity.Ignore(x => x.NumberAndName);
        });
```

Column Order

```csharp
modelBuilder.Entity<Driver>(entity =>
        {
            entity.HasOne(d => d.Team)
                .WithMany(p => p.Drivers)
                .HasForeignKey(d => d.TeamId)
                .OnDelete(DeleteBehavior.Restrict)
                .HasConstraintName("FK_Driver_Team");

            entity.Property(b => b.Name).HasColumnType("varchar(200)");

            entity.ToTable("F1Drivers");
            entity.Ignore(x => x.NumberAndName);

             entity.Property(b => b.Id)
            .HasColumnOrder(0);

            entity.Property(b => b.Name)
            .HasColumnOrder(1);

            entity.Property(b => b.RacingNumber)
            .HasColumnOrder(2);
        });
```