I am migrating my project from .Net 4.7 to .Net 6, so I have to migrate my Entity Framework too.

I have generate my first migration with dotnet ef migrations add PortedToEFCore. According to Microsoft, when we deal with existing database, we need to remove all the Up() and Down() from the added migration record from dotnet ef migrations add.

So here is my migration record PortedToEFCore:

public partial class PortedToEFCore : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
           
        }
    }

Then I have to modify some relation table name to match my old .Net EF.

This is my model:

public class Account : ObjectMapper
{
    [Key]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn(IsUnique = true)]
    public Guid ID { get; set; } = Guid.NewGuid();

    [Required]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn(IsUnique = true)]
    [StringLength(50)]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn("UserStore", 1, IsUnique = true)]
    public string UserName { get; set; }

    [Required]
    public string Password { get; set; }

    public string Hash { get; set; }

    public ICollection<Role> Roles { get; set; }

    [ForeignKey("AccountProfile")]
    public Guid? AccountProfileId { get; set; }

    public AccountProfile AccountProfile { get; set; }

    [ForeignKey("Store")]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn("UserStore", 2, IsUnique = true)]
    public Guid? StoreId { get; set; }
    public Store Store {get;set;}

    public ICollection<Access> Access { get; set; }
}

public class Access
{
    [Key]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn(IsUnique = true)]
    public Guid ID { get; set; } = Guid.NewGuid();

    public string AccessName { get; set; }

    public ICollection<Account> Accounts { get; set; }
}

public class AccountProfile
{
    [Key]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn(IsUnique = true)]
    public Guid ID { get; set; } = Guid.NewGuid();

    [Required]
    public string Name { get; set; }

    public string Description { get; set; }

    public string Phone { get; set; }

    public string Email { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Province { get; set; }
    public string PostCode { get; set; }
}

public class Role
{
    [Key]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn(IsUnique = true)]
    public Guid ID { get; set; } = Guid.NewGuid();

    [Required]
    [StringLength(50)]
    [Toolbelt.ComponentModel.DataAnnotations.Schema.V5.IndexColumn(IsUnique = true)]
    public string RoleName { get; set; }

    public ICollection<Account> Accounts { get; set; }
}

This code bellow is how I match my relation table name, the default relation table names are AccountRole and AccessAccount so this code is for matching the table name into RoleAccounts and AccountAccesses:

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.BuildIndexesFromAnnotations();
        base.OnModelCreating(mb);

        mb.Entity<Account>()
            .HasMany(left => left.Roles)
            .WithMany(right => right.Accounts)
            .UsingEntity<Dictionary<string, object>>(
            "RoleAccounts",
        j => j
            .HasOne<Role>()
            .WithMany()
            .HasForeignKey("Role_ID"),
        j => j
            .HasOne<Account>()
            .WithMany()
            .HasForeignKey("Account_ID"));

        mb.Entity<Account>()
            .HasMany(left => left.Access)
            .WithMany(right => right.Accounts)
            .UsingEntity<Dictionary<string, object>>(
            "AccountAccesses",
        j => j
            .HasOne<Access>()
            .WithMany()
            .HasForeignKey("Access_ID"),
        j => j
            .HasOne<Account>()
            .WithMany()
            .HasForeignKey("Account_ID"));
    }

It can be compiled well, it runs. Then the problem is when I try to retrieve the record.

Notice here the ADMIN account has NULL AccountProfileId and StoreId. According to my model Account this is valid, but this error comes up everytime I try to access the ADMIN account:

System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'

My example code:

var xxx = await db.Accounts.ToListAsync();

This code throws that error.

But if I create and connect a new AccountProfile and assign StoreId to Admin account. It can be retrieved alright.

What is this happening? My Account model specifies that AccountProfileId and StoreId are optional, why is this not working?

CodePudding user response：

I would guess, that non-nullable AccountProfile and Store cause this problem. Try to make them nullable to test my hypothesis.

That said, why are you describing migration in your question? You don't have a problem with it, do you? I have done similar conversion in a very different manner - but if you already have a model that matches the database, it probably doesn't matter. But your question is extremely confusing (to me at least) as to what exactly is the problem