Bug #69923 ERROR IN WHERE CLAUSE
Submitted: 4 Aug 2013 13:26 Modified: 7 Aug 2013 11:12
Reporter: Rafael Serafim Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6,7,4 OS:Microsoft Windows
Assigned to: Uma Hoysala CPU Architecture:Any

[4 Aug 2013 13:26] Rafael Serafim
Description:
ERROR: Unknown column 'Extent1.numeroContratoAntigo' in 'where clause'

{SELECT
`Project1`.`Id`, 
`Project1`.`numeroContratoAntigo`, 
`Project1`.`nomeMoeda`, 
`Project1`.`dataEmissao`, 
`Project1`.`valorContrato`, 
`Project1`.`sinalRecebido`, 
`Project1`.`Observacao`, 
`Project1`.`ativo`, 
`Project1`.`valorContratoDesc`, 
`Project1`.`ValorContratoAcresc`, 
`Project1`.`status`, 
`Project1`.`CorretorId`, 
`Project1`.`dataStatusAlterado`, 
`Project1`.`DTMOV`, 
`Project1`.`LOTECANCELADO`, 
`Project1`.`Filho1`, 
`Project1`.`Filho2`, 
`Project1`.`Plano`, 
`Project1`.`DTCANC`, 
`Project1`.`DTATUA`, 
`Project1`.`TPCOBRA`, 
`Project1`.`UsuarioId`, 
`Project1`.`statusAntigo`
FROM `Contrato` AS `Project1`
 WHERE (`Project1`.`status` = @p__linq__0) AND ((LOCATE(@p__linq__1, `Extent1`.`numeroContratoAntigo`)) > 0)
 ORDER BY 
`Project1`.`dataEmissao` DESC}

This error started appearing after I migrated from version 6.6.5 to 6.7.4. In version 6.6.5 this code worked perfectly.

var contracts = from c in model.ContratoSet.Where (filtroFinal) c.dataEmissao orderby descending select c;

filtroFinal is dynamically created using the Utility class:

Expression <Func <Contrato, bool>> filtroFinal = Utility.And (conditions [0], conditions [1]);

CODE CLASS UTILITY

public static class Utility
    {

        public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
        {

            // build parameter map (from parameters of second to parameters of first)

            var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

            // replace parameters in the second lambda expression with parameters from the first

            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // apply composition of lambda expression bodies to parameters from the first expression 

            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);

        }

        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {

            return first.Compose(second, Expression.And);

        }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {

            return first.Compose(second, Expression.Or);

        }

    }

stack trace
   em System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   em System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   em System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   em System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   em System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
   em System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
   em System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   em System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   em NovoSistemaSolario.Cadastro.contrato.formContratoCentral.listarContrato() na C:\Rafael\Projetos\NovoSistemaSolario\NovoSistemaSolario\Cadastro\contrato\formContratoCentral.cs:linha 114

How to repeat:
CREATE A TABLE WITH 2 FIELDS, AND RIDE A QUERY USING THE UTILITY CLASS.
[4 Aug 2013 13:35] Rafael Serafim
Expression<Func<Contrato, bool>> condition[0] = c => c.status == status;

Expression<Func<Contrato, bool>> conditions[1]= c => c.numeroContratoAntigo == numeroContratoAntigo || c.Id == idContrato;
[7 Aug 2013 11:12] Uma Hoysala
Duplicate of Bug #69751 , see below snippet:

//snippet
[22 Jul 3:24] Fernando Gonzalez Sanchez

Verified, thanks for the bug report; and yes, this appeared after fix for http://bugs.mysql.com/bug.php?id=65723.

The bug is only reproducible in Code First (not in Database First).

A fix will appear in Connector/NET versions 6.5.7, 6.6.6, 6.7.5 & 6.8.0.
snippet//