Paging with Entity Framework

Paging with Entity Framework

Postby dvoigt » Thu 17 Jul 2008 08:58

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
 
Posts: 2
Joined: Thu 17 Jul 2008 08:16

Postby dvoigt » Thu 17 Jul 2008 12:03

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.
dvoigt
 
Posts: 2
Joined: Thu 17 Jul 2008 08:16

Postby danik » Thu 17 Jul 2008 15:58

dvoigt wrote: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
danik
 
Posts: 3
Joined: Thu 17 Jul 2008 15:30

Postby AndreyR » Fri 18 Jul 2008 11:30

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby DanielZ » Wed 23 Jul 2008 10:31

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
DanielZ
 
Posts: 4
Joined: Wed 23 Jul 2008 10:22

Postby AndreyR » Thu 24 Jul 2008 14:29

Hello, Daniel.

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

Regards, Andrey.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby superboy » Wed 06 Oct 2010 18:40

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 :D
superboy
 
Posts: 2
Joined: Wed 06 Oct 2010 18:30

Postby superboy » Thu 07 Oct 2010 06:35

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.
superboy
 
Posts: 2
Joined: Wed 06 Oct 2010 18:30

Postby AndreyR » Mon 11 Oct 2010 13:58

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?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle