Paging with Entity Framework

[dvoigt]

Hi all,

I'd like to implement paging to limit the number of entities loaded a time. I tried it in an straightforward way like this:

Entities.Products.Skip("it.ListPrice","20").Top("10");

But it throws an System.NotImplementedException. It looks to me the EF provider is not able to generate a proper query. So please can you tell me, how paging can be achieved?

Regards,
Denny

[dvoigt]

Well, I tried to implement paging by bypassing LINQ-to-Entities and directly emitting eSQL, but again the NotImplementedException is thrown (result from eSqlBlast at the end). Is there another way to achieve paging using the entity framework and OraDirect .NET provider?

Thanks in advance for your help!
Denny


Result from eSqlBlast:


select value edition from TestEntities.Editions as edition order by edition.TitleNumber skip 1000 limit 50;

System.Data.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.NotImplementedException: Die Methode oder der Vorgang sind nicht implementiert. bei CoreLab.Common.Entity.m.a(PrimitiveTypeKind A_0) bei CoreLab.Common.Entity.m.h() bei CoreLab.Oracle.Entity.a.a(DbSkipExpression A_0) bei System.Data.Common.CommandTrees.DbSkipExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) bei CoreLab.Common.Entity.m.a(DbExpression A_0, String A_1, TypeUsage A_2, j& A_3) bei CoreLab.Common.Entity.m.a(DbExpression A_0, Boolean A_1) bei CoreLab.Oracle.Entity.a.a(DbLimitExpression A_0) bei System.Data.Common.CommandTrees.DbLimitExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) bei CoreLab.Common.Entity.m.a(DbExpression A_0, String A_1, TypeUsage A_2, j& A_3) bei CoreLab.Common.Entity.m.a(DbProjectExpression A_0) bei System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) bei CoreLab.Common.Entity.m.a(DbExpression A_0, Boolean A_1) bei CoreLab.Common.Entity.m.a(DbQueryCommandTree A_0, List`1& A_1) bei CoreLab.Oracle.Entity.a.a(DbCommandTree A_0, DbConnection A_1) bei CoreLab.Oracle.Entity.OracleEntityProviderServices.b(DbProviderManifest A_0, DbCommandTree A_1) bei System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree) bei System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) --- Ende der internen Ausnahmestapelüberwachung --- bei System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) bei System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) bei System.Data.EntityClient.EntityCommand.CreateCommandDefinition() bei System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition) bei System.Data.EntityClient.EntityCommand.GetCommandDefinition() bei System.Data.EntityClient.EntityCommand.InnerPrepare() bei System.Data.EntityClient.EntityCommand.Prepare() bei Microsoft.Samples.Data.eSqlBlast.XmlVisitor.GetStoreCommandText(EntityCommand command) in D:\dev\tools\eSqlBlast\Core\XmlVisitor.cs:Zeile 261. bei Microsoft.Samples.Data.eSqlBlast.XmlVisitor.VisitCommand(EntityCommand command) in D:\dev\tools\eSqlBlast\Core\XmlVisitor.cs:Zeile 106. bei Microsoft.Samples.Data.eSqlBlast.Session.Execute(String input) in D:\dev\tools\eSqlBlast\Core\Session.cs:Zeile 100.

[danik]

NotImplementedException is thrown (result from eSqlBlast at the end).

Hi,
IMHO Oracle has no support for paging (at least in standard version) and that means that it is not implementable via sql (without slow workarounds as "select from select" or temporary tables)

Danik

[AndreyR]

Hello, Dvoigt.

Try using code like

Code: Select all

Entities.Products.OrderBy(c => c.id).Skip(20).Take(10)

But be aware that in the current build there is a known issue with sorting in the case of using Skip() and Take() simultaneously.
The problem is already fixed, so if possible, wait for the upcoming build.

Regards, Andrey.

[DanielZ]

The new build OraDirect .NET 4.70.38 from yesterday fixed the "System.NotImplementedException". But using Take & Skip still provides unecpedcted (wrong) results. The skipping is always done after sorting by the primary key (id) column and ignores the given orderings.

But Skip & Take are typically used in grids for paging where certain columns are ordered individually - so it is very important that Skip & Take follow the given orderings.

Is there a work-around in the current build for this issue?

Thanks in advance!
Daniel

[AndreyR]

Hello, Daniel.

Thank you for the bug report.
The issue is fixed, look forward for the next build.

Regards, Andrey.

[superboy]

Resuming this thread because i found a related problem.
Implementing pagination with LINQ to Entities works for me, but i'm going over providing some general filtering method to my data access layer.

To do so i'm using ESQL to build dynamic queries. The following works on sql server but gives "ORA-01745: invalid host/bind variable name" on dotConnet for Oracle (oracle 10 with oracle client 11)
The output is taken from ObjectQuery.CommandText, and it's composed calling CreateObjecSet().Where().Skip().Top()

SELECT VALUE it
FROM (
[Entities].[TABLE_NAMEI]
) AS it
ORDER BY
it.TABLE_KEY
SKIP
@skip
LIMIT
@size

Any advice, is it supported? The Oracle error is quite mysterious as usual

[superboy]

I just tried without using objectQuery.Skip( _orderColumns, "@skip", new ObjectParameter("skip", _skip)) but directly objectQuery = objectQuery.Skip(_order, _skip + "") and it works. So i guess the bug is on the parametric version of the method.

[AndreyR]

I have made a test using the following query and succeeded:

Code: Select all

var q1 = db.CreateObjectSet("Depts").Skip("it.Deptno", "@skip", new ObjectParameter("skip", 1)).Top("@limit", new ObjectParameter("limit", 2));

Could you please modify this example or show the exact code that caused the error in your case?