New virtual method QuerySqlGenerator.GenerateSelect

[dmitry-lipetsk]

Firebird DBMS (v3) has some limitation for arguments of "OFFSET" and "FETCH" in "SELECT" statement - it does not support expressions like "CAST(@param AS INTEGER)"

But it (Firebird) has alternative syntax:

SELECT
[FIRST m] [SKIP n]
[DISTINCT | ALL] ...

without these limitations.

I not see in current EFCore an any legal way for generation of SQL with FIRST and SKIP:

efcore/src/EFCore.Relational/Query/QuerySqlGenerator.cs

Lines 174 to 181 in a0bb25f

	_relationalCommandBuilder.Append("SELECT ");
	if (selectExpression.IsDistinct)
	{
	_relationalCommandBuilder.Append("DISTINCT ");
	}
	GenerateTop(selectExpression);

Could you move line with

_relationalCommandBuilder.Append("SELECT "); 

into separated new virtual function GenerateSelect?

I will override this function and append the generation of FIRST, SKIP.

---

Firebird DBMS supports an additional variant for this task - ROWS. Native Firebird provider for EFCore uses this statement in their code.

But usage of "ROWS" requires additional "+1" and uses long.MaxValue for describing unlimited number, so it looks less preferred than "FIRST/SKIP".

---

Of course, I may copy the body of QuerySqlGenerator.VisitSelect into my code (this is my current variant) and resolve this problem, but may be new method "GenerateSelect" will be usefully into another tasks.

Thanks.

[smitpatel]

• We don't want GenerateSelect function. Wrong function and wrong utilization.
• We should investigate if SqlServer has any restrictions around Distinct & Top - mainly can Distinct come after specifying Top. Likewise @dmitry-lipetsk - can First/Skip come after Distinct in projection list?
• We should change the function GenerateTop to allow providers to pre/post write the Limit/Offset.

[smitpatel]

Since this is new API, unlikely to happen in 6.0 (too late in release), so for 6.0 you should override VisitSelect and copy paste the method body. The new API will be available from 7.0

[dmitry-lipetsk]

Since this is new API, unlikely to happen in 6.0 (too late in release), so for 6.0 you should override VisitSelect and copy paste the method body.

Ok. Already done.

Sorry that I not inform about this problem early.

You may close this issue.