I'm using Entity Framework Core using the Database Scaffold.
The project is from a social network, and initially we schematized the tables between user and follower as follows:
Logical Model of the User and Follower tables
The follower_id field stores the ID of the follower who follows that user, then the user_id field stores the field of the user who has that follower. (E.G: The user with ID 1 has the follower with ID 2, The user with ID 2 has the follower with ID 1. This means that they both follow each other)
Field Constraint user_id, Foreign Key
Field Constraint follower_Id, Foreign Key
When I scaffold the database, it generated the following entity (that's right, only a single entity, even with two tables):
namespace Orbit.Domain.Entities;
public partial class User
{
public uint UserId { get; set; }
public string UserName { get; set; } = null!;
public string UserEmail { get; set; } = null!;
public DateOnly UserDateOfBirth { get; set; }
public string UserPassword { get; set; } = null!;
public string? UserDescription { get; set; }
public byte[]? UserImageByteType { get; set; }
public string? UserProfileName { get; set; }
public virtual ICollection<User> Followers { get; set; } = [];
public virtual ICollection<User> Users { get; set; } = [];
}
Analyzing what was passed on to me, it makes sense to generate a single entity.
The property public virtual ICollection<User> Users { get; set; } = []; is a collection that contains the followers of that specific user and public virtual ICollection<User> Users { get; set; } = []; is a collection that contains all the users that this entity follows.
However, when I try to search for these followers and users followed by this user, the Entity Framework does not return any records, even though I have entered records manually and via code in these tables. I tried testing the same database in different applications, I looked at OnModelCreating and the fluent API to see if there were any misconfigurations and in my view nothing wrong.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
_ = modelBuilder
.UseCollation("utf8mb3_general_ci")
.HasCharSet("utf8mb3");
_ = modelBuilder.Entity<User>(entity =>
{
_ = entity.HasKey(e => e.UserId).HasName("PRIMARY");
_ = entity.ToTable("user");
_ = entity.HasIndex(e => e.UserEmail, "user_email_UNIQUE").IsUnique();
_ = entity.HasIndex(e => e.UserId, "user_id_UNIQUE").IsUnique();
_ = entity.HasIndex(e => e.UserName, "user_name_UNIQUE").IsUnique();
_ = entity.Property(e => e.UserId).HasColumnName("user_id");
_ = entity.Property(e => e.UserDateOfBirth).HasColumnName("user_date_of_birth");
_ = entity.Property(e => e.UserDescription)
.HasColumnType("mediumtext")
.HasColumnName("user_description")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserEmail)
.HasMaxLength(200)
.HasColumnName("user_email")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserImageByteType).HasColumnName("user_image_byte_type");
_ = entity.Property(e => e.UserName)
.HasMaxLength(100)
.HasColumnName("user_name")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserPassword)
.HasMaxLength(200)
.HasColumnName("user_password")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserProfileName)
.HasMaxLength(200)
.HasColumnName("user_profile_name")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.HasMany(d => d.Followers).WithMany(p => p.Users)
.UsingEntity<Dictionary<string, object>>(
"Follower",
r => r.HasOne<User>().WithMany()
.HasForeignKey("FollowerId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_1"),
l => l.HasOne<User>().WithMany()
.HasForeignKey("UserId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_2"),
j =>
{
_ = j.HasKey("UserId", "FollowerId")
.HasName("PRIMARY")
.HasAnnotation("MySql:IndexPrefixLength", new[] { 0, 0 });
_ = j.ToTable("follower");
_ = j.HasIndex(new[] { "FollowerId" }, "follower_id");
_ = j.IndexerProperty<uint>("UserId").HasColumnName("user_id");
_ = j.IndexerProperty<uint>("FollowerId").HasColumnName("follower_id");
});
_ = entity.HasMany(d => d.Users).WithMany(p => p.Followers)
.UsingEntity<Dictionary<string, object>>(
"Follower",
r => r.HasOne<User>().WithMany()
.HasForeignKey("UserId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_2"),
l => l.HasOne<User>().WithMany()
.HasForeignKey("FollowerId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_1"),
j =>
{
_ = j.HasKey("UserId", "FollowerId")
.HasName("PRIMARY")
.HasAnnotation("MySql:IndexPrefixLength", new[] { 0, 0 });
_ = j.ToTable("follower");
_ = j.HasIndex(new[] { "FollowerId" }, "follower_id");
_ = j.IndexerProperty<uint>("UserId").HasColumnName("user_id");
_ = j.IndexerProperty<uint>("FollowerId").HasColumnName("follower_id");
});
});
OnModelCreatingPartial(modelBuilder);
}
This is the OnModelCreating of the application and in my view I didn't see anything unusual. I tried to generate again and it remained unchanged. As I said I'm using the Database-First approach, so the Models and DbContext are scaffold generated by Entity Framework Core.
DbContext class:
using Microsoft.EntityFrameworkCore;
using Orbit.Domain.Entities;
namespace Orbit.Infrastructure.Data.Contexts;
public partial class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
public virtual DbSet<User> Users { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
_ = modelBuilder
.UseCollation("utf8mb3_general_ci")
.HasCharSet("utf8mb3");
_ = modelBuilder.Entity<User>(entity =>
{
_ = entity.HasKey(e => e.UserId).HasName("PRIMARY");
_ = entity.ToTable("user");
_ = entity.HasIndex(e => e.UserEmail, "user_email_UNIQUE").IsUnique();
_ = entity.HasIndex(e => e.UserId, "user_id_UNIQUE").IsUnique();
_ = entity.HasIndex(e => e.UserName, "user_name_UNIQUE").IsUnique();
_ = entity.Property(e => e.UserId).HasColumnName("user_id");
_ = entity.Property(e => e.UserDateOfBirth).HasColumnName("user_date_of_birth");
_ = entity.Property(e => e.UserDescription)
.HasColumnType("mediumtext")
.HasColumnName("user_description")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserEmail)
.HasMaxLength(200)
.HasColumnName("user_email")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserImageByteType).HasColumnName("user_image_byte_type");
_ = entity.Property(e => e.UserName)
.HasMaxLength(100)
.HasColumnName("user_name")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserPassword)
.HasMaxLength(200)
.HasColumnName("user_password")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.Property(e => e.UserProfileName)
.HasMaxLength(200)
.HasColumnName("user_profile_name")
.UseCollation("utf8mb4_0900_ai_ci")
.HasCharSet("utf8mb4");
_ = entity.HasMany(d => d.Followers).WithMany(p => p.Users)
.UsingEntity<Dictionary<string, object>>(
"Follower",
r => r.HasOne<User>().WithMany()
.HasForeignKey("FollowerId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_1"),
l => l.HasOne<User>().WithMany()
.HasForeignKey("UserId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_2"),
j =>
{
_ = j.HasKey("UserId", "FollowerId")
.HasName("PRIMARY")
.HasAnnotation("MySql:IndexPrefixLength", new[] { 0, 0 });
_ = j.ToTable("follower");
_ = j.HasIndex(new[] { "FollowerId" }, "follower_id");
_ = j.IndexerProperty<uint>("UserId").HasColumnName("user_id");
_ = j.IndexerProperty<uint>("FollowerId").HasColumnName("follower_id");
});
_ = entity.HasMany(d => d.Users).WithMany(p => p.Followers)
.UsingEntity<Dictionary<string, object>>(
"Follower",
r => r.HasOne<User>().WithMany()
.HasForeignKey("UserId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_2"),
l => l.HasOne<User>().WithMany()
.HasForeignKey("FollowerId")
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("follower_ibfk_1"),
j =>
{
_ = j.HasKey("UserId", "FollowerId")
.HasName("PRIMARY")
.HasAnnotation("MySql:IndexPrefixLength", new[] { 0, 0 });
_ = j.ToTable("follower");
_ = j.HasIndex(new[] { "FollowerId" }, "follower_id");
_ = j.IndexerProperty<uint>("UserId").HasColumnName("user_id");
_ = j.IndexerProperty<uint>("FollowerId").HasColumnName("follower_id");
});
});
OnModelCreatingPartial(modelBuilder);
}
public override int SaveChanges()
{
foreach (Microsoft.EntityFrameworkCore.ChangeTracking.EntityEntry<User> entry in ChangeTracker.Entries<User>())
{
if (entry.State is EntityState.Modified or EntityState.Added)
{
entry.Entity.UserName = entry.Entity.UserName.Trim();
entry.Entity.UserEmail = entry.Entity.UserEmail.Trim();
entry.Entity.UserPassword = entry.Entity.UserPassword.Trim();
entry.Entity.UserDescription = entry.Entity.UserDescription!.Trim();
entry.Entity.UserDescription = entry.Entity.UserProfileName!.Trim();
}
}
// Atualmente os espaços em brancos são limpos em scripts
// após serem enviados, ainda no navegador, porém os
// scripts podem ser alterados manualmente então um fallback
// é configurado para remover espaços em branco logo antes das
// mudanças serem commitados.
return base.SaveChanges();
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
My current database provider is Pomelo.EntityFrameworkCore.MySql
Thanks!
Rows in the User table
Rows in the Follower table
Test performed with the tables under the same conditions as above, with the same records, configurations, etc.:
namespace ConsoleApp1
{
internal class Program
{
static void Main(string[] args)
{
using (var context = new OrbitdatabaseContext())
{
var a = context.Users.First().Followers;
foreach (var item in context.Users.First().Followers)
{
Console.WriteLine(item.UserName); // It didn't even reach that point,
// because the collection doesn't have any row
}
}
}
}
}
Expected result:
samidaniel_reidelas_2015
Test
Test1
C:\Users\devsa\source\repos\ConsoleApp1\ConsoleApp1\bin\Debug\net8.0\ConsoleApp1.exe (process 15212) exited with code 0.
To automatically close the console when debugging stops, enable Tools->Options->Debugging->Automatically close the console when debugging stops.
Press any key to close this window . . .
Current Console result:
C:\Users\devsa\source\repos\ConsoleApp1\ConsoleApp1\bin\Debug\net8.0\ConsoleApp1.exe (process 15212) exited with code 0.
To automatically close the console when debugging stops, enable Tools->Options->Debugging->Automatically close the console when debugging stops.
Press any key to close this window . . .
To retrieve an entity from the database with its related details, EF Core needs to know which details to include; otherwise, it would need to load the entire database. For this purpose, the Include operator is used. Avoid mentioning Lazy Loading here, as it often introduces more problems than it solves.
A simple Include can help:
var followers = context.Users
.Include(x => x.Followers)
.First()
.Followers;
However, if we only need the followers, we can directly query them to improve performance:
var followers = context.Users
.Take(1)
.SelectMany(u => u.Followers);