Birth date validation with LINQ

Asked

Viewed 101 times

1

I am doubtful to conduct a consultation.

I need to list in this query only the offers that are within the age group listed in view in the fields IDADEMINIMA and IDADEMAXIMA passing only the parameter of dtnascimento. Can help?

public IQueryable<VW_PARCEIROSOFERTAS> ConsultaOfertas(string pEmpresa, string pOferta, string dtnascimento)
{
    var query = this.dbSet.AsNoTracking().Where(p => p.EMPRESA == pEmpresa && p.CODOFERTA == pOferta);
    if (!String.IsNullOrEmpty(dtnascimento) || !String.IsNullOrWhiteSpace(dtnascimento))
    {
        query = query.Where(c => Convert.ToDateTime(dtnascimento) <= DateTime.Now.AddYears(Convert.ToInt32(c.IDADEMINIMA))
                              && Convert.ToDateTime(dtnascimento) >= DateTime.Now.AddYears(Convert.ToInt32(c.IDADEMAXIMA)));
    }

    return query;
}

View VW_PARCEIROSOFERTAS:

public partial class VW_PARCEIROSOFERTAS
{
    [Key]
    [Column(Order = 0)]
    [StringLength(2)]
    public string EMPRESA { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(6)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string CODPARCEIRO { get; set; }

    [StringLength(30)]
    public string NOMEPARCEIRO { get; set; }

    [StringLength(30)]
    public string RAZAOSOCIAL { get; set; }

    [StringLength(18)]
    public string CNPJ { get; set; }

    [StringLength(6)]
    public string PRODUTO { get; set; }

    public string CODOFERTA { get; set; }

    [StringLength(30)]
    public string NOMEOFERTA { get; set; }

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

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

    [Column(TypeName = "numeric")]
    public decimal? PRAZOINICIAL { get; set; }

    [Column(TypeName = "numeric")]
    public decimal? PRAZOFINAL { get; set; }

    [Column(TypeName = "numeric")]
    public decimal? VALORINICIAL { get; set; }

    [Column(TypeName = "numeric")]
    public decimal? VALORFINAL { get; set; }

    [Column(TypeName = "numeric")]
    public decimal? VALORPRODUTO { get; set; }
    [Column(TypeName = "numeric")]
    public decimal? COMISSAO { get; set; }

    [Column(TypeName = "numeric")]
    public decimal? IDADEMINIMA { get; set; }

    [Column(TypeName = "numeric")]
    public decimal? IDADEMAXIMA { get; set; }
    public DateTime? VIGENCIAINICIAL { get; set; }
    public DateTime? VIGENCIAFINAL { get; set; }       
}
c# query linq

  • It will give error because it is trying to use Convert.Todatetime() in a Queryable. Same for Convert.Toint32(). Enter the VW_PARCEIROSOFERTAS code and if possible the error that is giving.

  • Hello Gerge, all right? I am including the code, the error that occurs is the one you mentioned. (datetime conversion error and int32).

1 answer

2


First, for you to understand why the error occurs, you cannot use the Convert.ToDateTime() or the Convert.ToInt32() in a IQueryable. This is because the LINQ to Entities does not recognize these methods to convert to an SQL query.

You already receive the date of birth in your method and already have the minimum and maximum age stored in the BD, so you don’t need all this in your query. Calculate age before and then compare with what’s in the comic.

To IDADEMINIMA and IDADEMAXIMA are of the type decimal?, then after calculating the age only convert to this type and you can compare in your query without making any conversion.

I made below a method to calculate age and an example of how to use it by converting the return to decimal:

public static void Main()
{   
    decimal? idadeMax = 50, idadeMin = 18;
    string dtnascimento = "30/12/1988";
    decimal? idade = CalcularIdade(DateTime.ParseExact(dtnascimento, "dd/MM/yyyy", CultureInfo.InvariantCulture));
}

static int CalcularIdade(DateTime dataNascimento) 
{
    int idade = DateTime.Now.Year - dataNascimento.Year;
    if (dataNascimento.Date > DateTime.Now.AddYears(-idade)) 
        idade -= 1;

    return idade;
}

After that you can compare directly in your query by changing this snippet:

query = query.Where(c => Convert.ToDateTime(dtnascimento) <= DateTime.Now.AddYears(Convert.ToInt32(c.IDADEMINIMA))
                              && Convert.ToDateTime(dtnascimento) >= DateTime.Now.AddYears(Convert.ToInt32(c.IDADEMAXIMA)));

For that reason:

query = query.Where(c => c.IDADEMINIMA <= idade && c.IDADEMAXIMA <= idade);

Browser other questions tagged c# query linq

You are not signed in. Login or sign up in order to post.