Error in search with like

Question

I have a table in a Firebird database

CREATE TABLE CIDADE (
CID_CD              SMALLINT NOT NULL,
CID_DS              CHAR(20) NOT NULL,
CID_UF              CHAR(2) NOT NULL,
CID_DISTANCIA_SEDE  SMALLINT NOT NULL,
CID_CD_ALTERNATIVO  INTEGER NOT NULL,
CID_DT_LK           DATE NOT NULL);

I'm doing a query using LINQ with table columns and I'm getting the error in the CHAR type columns:

arithmetic exception, numeric overflow, or string truncation string right truncation

Identifying the problem, I realized that the size of type String fields are beyond the limits of the field. I do not understand this, because I mapped the correct length constraints on these fields.

Follows my code:

public IQueryable<Cidade> Pesquisar(Cidade cidade)
{
    string uf = cidade.UF; // "SP" for example
    var query = pctxContexto.Cidade.Where(c=> c.UF.Contains(uf));
    return query;
}

Mapping:

// Table Mapping
ToTable("CIDADE");

//Chave primária
HasKey(t => new { t.Codigo });

//Propriedades
Property(t => t.Codigo).HasColumnName("CID_CD");

Property(t => t.Descricao)
    .IsRequired()
    .HasMaxLength(20)
    .HasColumnType("Char")
    .HasColumnName("CID_DS");

Property(t => t.UF)
    .IsRequired()
    .HasMaxLength(2)
    .HasColumnType("Char")
    .HasColumnName("CID_UF");

Property(t => t.DistanciaSede)
    .IsRequired()
    .HasColumnType("Smallint")
    .HasColumnName("CID_DISTANCIA_SEDE");

Property(t => t.Codigo_Alternativo)
    .IsRequired()
    .HasColumnType("Int")
    .HasColumnName("CID_CD_ALTERNATIVO");

How can I solve this problem? Why is it occurring?

maybe you should change the column type for your char fields from varchar to char since that is what they actually are. I am not sure if it is the problem, but I would say it is a pretty good guess. — pquest, Jan 08 '16 at 17:11

score 4 · Answer 1 · answered Jan 09 '16 at 07:42

If I am not mistaken, the .Where(c=> c.UF.Contains(uf) is translated to WHERE c.UF LIKE ? and the parameter has value "%SP%", which is 4 characters.

The problem is that Firebird describes and only accepts parameters with lengths up to the declared length of the field, which is 2 characters. So using a value with length 4 will fail with a string truncation error. For assignment that would be acceptable, but for comparison (and especially for LIKE or SIMILAR TO) this is highly annoying.

The normal workaround would be to cast the parameter to a longer type, but I am not sure if there is a way to do that when using entity framework. The only other workaround is to define columns longer than is strictly necessary for the data.

The related Firebird improvement tickets are:

score 0 · Answer 2 · edited May 23 '17 at 11:59

This is most probably an issue related to encoding. Try the following mapping:

Property(t => t.UF)
    .IsRequired()
    .IsUnicode(false)            // <--- 
    .HasMaxLength(2)
    .HasColumnType("Varchar")
    .HasColumnName("CID_UF");

Another option (found in this answer) is to use "AnsiString" custom type:

Property(t => t.UF)
    .IsRequired()
    .CustomType("AnsiString")    // <--- 
    .HasMaxLength(2)
    .HasColumnType("Varchar")
    .HasColumnName("CID_UF");

Error in search with like

2 Answers2