I'm working on a dotnet core 2.1 application with entity framework core and SQL Server 2017 Express Edition. I created the migration and did the update and everything went smoothly, however, when querying the database I noticed that the size of the fields was not applied according to the migrations.
How can I fix this in my migration?
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Fornecedor",
columns: table => new
{
Id = table.Column<Guid>(nullable: false),
IdSistemaAntigo = table.Column<string>(type: "varchar", maxLength: 32, nullable: false),
Status = table.Column<int>(nullable: false),
DataCadastro = table.Column<DateTime>(nullable: false),
UltimaMovimentacao = table.Column<DateTime>(nullable: false),
TipoPessoa = table.Column<int>(nullable: false),
Nome = table.Column<string>(type: "varchar", maxLength: 100, nullable: false),
Apelido = table.Column<string>(type: "varchar", maxLength: 100, nullable: true),
Sexo = table.Column<int>(nullable: true),
Cnpj = table.Column<string>(type: "varchar", maxLength: 14, nullable: false),
InscricaoEstadual = table.Column<string>(type: "varchar", maxLength: 15, nullable: false),
EstadoEmissorInscricaoEstadual = table.Column<string>(type: "varchar", maxLength: 2, nullable: false),
InscricaoMunicipal = table.Column<string>(type: "varchar", maxLength: 20, nullable: false),
Cpf = table.Column<string>(type: "varchar", maxLength: 11, nullable: false),
Rg = table.Column<string>(type: "varchar", maxLength: 15, nullable: false),
EstadoEmissorRg = table.Column<string>(type: "varchar", maxLength: 2, nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Fornecedor", x => x.Id);
});
migrationBuilder.CreateTable(
name: "FornecedorEmail",
columns: table => new
{
Id = table.Column<Guid>(nullable: false),
IdSistemaAntigo = table.Column<string>(type: "varchar", maxLength: 32, nullable: false),
Status = table.Column<int>(nullable: false),
DataCadastro = table.Column<DateTime>(nullable: false),
UltimaMovimentacao = table.Column<DateTime>(nullable: false),
FornecedorId = table.Column<Guid>(nullable: false),
EnderecoEmail = table.Column<string>(type: "varchar", maxLength: 254, nullable: false),
NomeExibicao = table.Column<string>(type: "varchar", maxLength: 40, nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_FornecedorEmail", x => x.Id);
table.ForeignKey(
name: "FK_FornecedorEmail_Fornecedor_FornecedorId",
column: x => x.FornecedorId,
principalTable: "Fornecedor",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "FornecedorEndereco",
columns: table => new
{
Id = table.Column<Guid>(nullable: false),
IdSistemaAntigo = table.Column<string>(type: "varchar", maxLength: 32, nullable: false),
Status = table.Column<int>(nullable: false),
DataCadastro = table.Column<DateTime>(nullable: false),
UltimaMovimentacao = table.Column<DateTime>(nullable: false),
TipoLogradouro = table.Column<string>(type: "varchar", maxLength: 72, nullable: false),
Logradouro = table.Column<string>(type: "varchar", maxLength: 72, nullable: false),
Numero = table.Column<string>(type: "varchar", maxLength: 6, nullable: false),
Bairro = table.Column<string>(type: "varchar", maxLength: 72, nullable: false),
Complemento = table.Column<string>(type: "varchar", maxLength: 72, nullable: false),
Cidade = table.Column<string>(type: "varchar", maxLength: 72, nullable: false),
Estado = table.Column<string>(type: "varchar", maxLength: 2, nullable: false),
Pais = table.Column<string>(type: "varchar", maxLength: 2, nullable: false),
Cep = table.Column<string>(type: "varchar", maxLength: 8, nullable: false),
CodigoIbge = table.Column<string>(type: "varchar", maxLength: 7, nullable: false),
Identificador = table.Column<string>(nullable: false),
FornecedorId = table.Column<Guid>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_FornecedorEndereco", x => x.Id);
table.ForeignKey(
name: "FK_FornecedorEndereco_Fornecedor_FornecedorId",
column: x => x.FornecedorId,
principalTable: "Fornecedor",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "FornecedorTelefone",
columns: table => new
{
Id = table.Column<Guid>(nullable: false),
IdSistemaAntigo = table.Column<string>(type: "varchar", maxLength: 32, nullable: false),
Status = table.Column<int>(nullable: false),
DataCadastro = table.Column<DateTime>(nullable: false),
UltimaMovimentacao = table.Column<DateTime>(nullable: false),
Ddi = table.Column<string>(type: "varchar", maxLength: 4, nullable: false),
Ddd = table.Column<string>(type: "varchar", maxLength: 4, nullable: false),
Telefone = table.Column<string>(type: "varchar", maxLength: 9, nullable: false),
Ramal = table.Column<string>(type: "varchar", maxLength: 4, nullable: false),
TipoTelefone = table.Column<string>(type: "varchar", maxLength: 1, nullable: false),
Identificador = table.Column<string>(nullable: false),
FornecedorId = table.Column<Guid>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_FornecedorTelefone", x => x.Id);
table.ForeignKey(
name: "FK_FornecedorTelefone_Fornecedor_FornecedorId",
column: x => x.FornecedorId,
principalTable: "Fornecedor",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_FornecedorEmail_FornecedorId",
table: "FornecedorEmail",
column: "FornecedorId");
migrationBuilder.CreateIndex(
name: "IX_FornecedorEndereco_FornecedorId",
table: "FornecedorEndereco",
column: "FornecedorId");
migrationBuilder.CreateIndex(
name: "IX_FornecedorTelefone_FornecedorId",
table: "FornecedorTelefone",
column: "FornecedorId");
}
Migration field size incorrect
This is my migration class
public override void ConfigurarEntidade(EntityTypeBuilder<Fornecedor> builder)
{
builder.ToTable("Fornecedor");
#region Configurações da Entidade
builder.Property(fornecedor => fornecedor.TipoPessoa)
.HasColumnType("varchar")
.HasMaxLength(1)
.IsRequired();
builder.Property(fornecedor => fornecedor.Nome)
.IsRequired()
.HasColumnType("varchar")
.HasMaxLength(Fornecedor.TamanhoNome);
builder.Property(fornecedor => fornecedor.Apelido)
.HasColumnType("varchar")
.HasMaxLength(Fornecedor.TamanhoNome);
builder.Property(fornecedor => fornecedor.Sexo)
.IsRequired(false);
builder.OwnsOne(fornecedor => fornecedor.Cnpj, cnpj =>
{
cnpj.Property(fornecedor => fornecedor.NumeroCnpj)
.HasColumnType("varchar")
.HasColumnName("Cnpj")
.HasMaxLength(Cnpj.TamanhoCnpj)
.IsRequired();
});
builder.OwnsOne(fornecedor => fornecedor.InscricaoEstadual, ie =>
{
ie.Property(fornecedor => fornecedor.NumeroInscricao)
.HasColumnType("varchar")
.HasColumnName("InscricaoEstadual")
.HasMaxLength(InscricaoEstadual.TamanhoInscricaoEstadual)
.IsRequired();
ie.Property(fornecedor => fornecedor.EstadoEmissor)
.HasColumnType("varchar")
.HasColumnName("EstadoEmissorInscricaoEstadual")
.HasMaxLength(InscricaoEstadual.TamanhoEstadoEmissor)
.IsRequired();
});
builder.Property(fornecedor => fornecedor.InscricaoMunicipal)
.HasColumnType("varchar")
.HasColumnName("InscricaoMunicipal")
.HasMaxLength(Fornecedor.TamanhoInscricaoMunicipal)
.IsRequired();
builder.OwnsOne(fornecedor => fornecedor.Cpf, cpf =>
{
cpf.Property(fornecedor => fornecedor.NumeroCpf)
.HasColumnType("varchar")
.HasColumnName("Cpf")
.HasMaxLength(Cpf.TamanhoMaximoCpf)
.IsRequired();
});
builder.OwnsOne(fornecedor => fornecedor.Rg, rg =>
{
rg.Property(fornecedor => fornecedor.NumeroRg)
.HasColumnType("varchar")
.HasColumnName("Rg")
.HasMaxLength(Rg.TamanhoRg)
.IsRequired();
rg.Property(fornecedor => fornecedor.EstadoEmissor)
.HasColumnType("varchar")
.HasColumnName("EstadoEmissorRg")
.HasMaxLength(Rg.TamanhoEstadoEmissor)
.IsRequired();
});
builder.Ignore(fornecedor => fornecedor.DataNascimento);
builder.Ignore(fornecedor => fornecedor.Imagem);
#endregion
#region Relacionamentos
builder
.HasMany(fornecedor => fornecedor.Emails)
.WithOne(email => email.Fornecedor)
.HasForeignKey(email => email.FornecedorId);
builder
.HasMany(fornecedor => fornecedor.Enderecos)
.WithOne(endereco => endereco.Fornecedor)
.HasForeignKey(endereco => endereco.FornecedorId);
builder
.HasMany(fornecedor => fornecedor.Telefones)
.WithOne(telefone => telefone.Fornecedor)
.HasForeignKey(telefone => telefone.FornecedorId);
#endregion
}
And this is my Entity
public class Fornecedor : Entity
{
public static readonly int TamanhoNome = 100;
public static readonly int TamanhoApelido = 20;
public static readonly int TamanhoInscricaoMunicipal = 20;
public static readonly int TamanhoRg = 20;
private string _nome;
private string _nomeFantasia;
public TipoPessoa TipoPessoa { get; private set; }
public string Nome
{
get { return _nome; }
private set { _nome = (value == null ? "" : value.RemoverEspacosDuplos().ToCapitalize().Trim()); }
}
public string Apelido
{
get { return _nomeFantasia; }
private set { _nomeFantasia = (value == null ? "" : value.RemoverEspacosDuplos().ToCapitalize().Trim()); }
}
public Sexo? Sexo { get; private set; }
public Cnpj Cnpj { get; private set; }
public InscricaoEstadual InscricaoEstadual { get; private set; }
public string InscricaoMunicipal { get; private set; }
public Cpf Cpf { get; private set; }
public Rg Rg { get; private set; }
public DateTime? DataNascimento { get; private set; }
public string Imagem { get; private set; }
public Fornecedor() { }
}
The problem is the type: "varchar" argument to all these table.Column<string> calls. When this parameter is supplied, maxLength and some other parameters are ignored by the migration SQL generator. Because the type is supposed to be the actual database type, including the size and other constraints, for instance varchar(100) etc. When it's just varchar, SqlServer DDL treats it as varchar(1).
With that being said, the question is what is causing the inclusion of that parameter. By convention it is not included (but the mapped database type is nvarchar(maxLength)), so it has to be specified in the model mappings.
It could have been done in two ways.
First is by applying [Column(TypeName ="varchar")] data annotation. However, if you do so, you'll get an exception when trying to generate a migration similar to this
Data type 'varchar' is not supported in this form. Either specify the length explicitly in the type name, for example as 'varchar(16)', or remove the data type and use APIs such as HasMaxLength to allow EF choose the data type.
So it shouldn't be this way, but the second way of using .HasColumnType("varchar") fluent API. When you do so, surprisingly the migration is generated without any errors and contains the aforementioned parameter. I find this behavior at least inconsistent - both should throw or both should succeed.
Anyway, the solution is to put the max length inside the type name, e.g.
.HasColumnType("varchar(100)")
or better avoid .HasColumnType where possible. For instance, whether the string is mapped to varchar or nvarchar depends on isUnicode attribute which is true by default. Hence to get the desired varchar(maxLength) database type, replace all
.HasColumnType("varchar")
in your OnModelCreating with
.IsUnicode(false)
and let the existing .HasMaxLenght calls handle the max length. And don't forget to regenerate the migration.