FirstOrDefault() Generate a bad select db query on SQL2000

Apr 8, 2009 at 1:28 PM

Hi everyone,

I tried this

    var q = from i in sqldatacontext.LotSerials
                    where (i.RefArticle == value.RefArticle)
                    && (i.DeNo == deno)
                    && (i.LotEpuise == 0)
                    && (i.NoSerie == value.NoSerie)
                    orderby i.QteRestant ascending
                    select i;

  
     var o=q.FirstOrDefault();

Generate this SQL query

SELECT TOP (1) t0.LS_COMPLEMENT, t0.DE_NO, t0.DL_NOIN, t0.DL_NOOUT, t0.LS_FABRICATION, t0.LS_LOTEPUISE, t0.LS_MVTSTOCK, t0.LS_NOSERIE, t0.LS_PEREMPTION, t0.LS_QTE, t0.LS_QTERES, t0.LS_QTERESTANT, t0.AR_REF
FROM F_LOTSERIE AS t0
WHERE ((((t0.AR_REF = @p0) AND (t0.DE_NO = 1)) AND (t0.LS_LOTEPUISE = 0)) AND (t0.LS_NOSERIE = @p1))
ORDER BY t0.LS_QTERESTANT

-- @p0 = [0CCB0002]
-- @p1 = [B10908E]

But it should generate this

SELECT TOP 1  t0.LS_COMPLEMENT, t0.DE_NO, t0.DL_NOIN, t0.DL_NOOUT, t0.LS_FABRICATION, t0.LS_LOTEPUISE, t0.LS_MVTSTOCK, t0.LS_NOSERIE, t0.LS_PEREMPTION, t0.LS_QTE, t0.LS_QTERES, t0.LS_QTERESTANT, t0.AR_REF
FROM F_LOTSERIE AS t0
WHERE ((((t0.AR_REF = @p0) AND (t0.DE_NO = 1)) AND (t0.LS_LOTEPUISE = 0)) AND (t0.LS_NOSERIE = @p1))
ORDER BY t0.LS_QTERESTANT

-- @p0 = [0CCB0002]
-- @p1 = [B10908E]

Fix is in TSqlFormatter.cs

 

 

protected override Expression VisitSelect(SelectExpression select)

 

{

sb.Append(

"SELECT ");

 

 

if (select.IsDistinct)

 

{

sb.Append(

"DISTINCT ");

 

}

 

if (select.Take != null)

 

{

sb.Append(

"TOP ");

 

 

this.Visit(select.Take);

 

sb.Append(

" ");

 

}

 

if (select.Columns.Count > 0)

 

{

 

for (int i = 0, n = select.Columns.Count; i < n; i++)

 

{

 

ColumnDeclaration column = select.Columns[i];

 

 

if (i > 0)

 

{

sb.Append(

", ");

 

}

 

ColumnExpression c = this.VisitValue(column.Expression) as ColumnExpression;

 

 

if (!string.IsNullOrEmpty(column.Name) && (c == null || c.Name != column.Name))

 

{

sb.Append(

" AS ");

 

sb.Append(column.Name);

}

}

}

 

else

 

 

 

 

 

{

sb.Append(

"NULL ");

 

 

if (this.isNested)

 

{

sb.Append(

"AS tmp ");

 

}

}

 

if (select.From != null)

 

{

 

this.AppendNewLine(Indentation.Same);

 

sb.Append(

"FROM ");

 

 

this.VisitSource(select.From);

 

}

 

if (select.Where != null)

 

{

 

this.AppendNewLine(Indentation.Same);

 

sb.Append(

"WHERE ");

 

 

this.VisitPredicate(select.Where);

 

}

 

if (select.GroupBy != null && select.GroupBy.Count > 0)

 

{

 

this.AppendNewLine(Indentation.Same);

 

sb.Append(

"GROUP BY ");

 

 

for (int i = 0, n = select.GroupBy.Count; i < n; i++)

 

{

 

if (i > 0)

 

{

sb.Append(

", ");

 

}

 

this.VisitValue(select.GroupBy[i]);

 

}

}

 

if (select.OrderBy != null && select.OrderBy.Count > 0)

 

{

 

this.AppendNewLine(Indentation.Same);

 

sb.Append(

"ORDER BY ");

 

 

for (int i = 0, n = select.OrderBy.Count; i < n; i++)

 

{

 

OrderExpression exp = select.OrderBy[i];

 

 

if (i > 0)

 

{

sb.Append(

", ");

 

}

 

this.VisitValue(exp.Expression);

 

 

if (exp.OrderType != OrderType.Ascending)

 

{

sb.Append(

" DESC");

 

}

}

}

 

return select;

 

}

 


 

Coordinator
May 7, 2009 at 12:58 AM
The toolkit does not currently support SQL 2000.   This is one of the changes that would need to be made to do so.