Hi,

I have a table of images which are to be iterated through one by one in either direction. They are in date order and when two images have the same date order then the one with the lower integer primary key is considered to come before an image with a higher primary key.

So a web page request might be sent an image id of 10 and a parameter indicating that the next (later) image should be retrieved.

The sql should come out like this...

SELECT TOP 1
    g2.GalleryImageID

FROM
    tbl_Gallery_Image g1

JOIN
    tbl_Gallery_Image g2

ON
    (g2.Date > g1.Date
    OR
    (g2.Date = g1.Date and g2.GalleryImageID > g1.GalleryImageID))
      AND
      g1.GalleryID = g2.GalleryID

WHERE
    g1.GalleryImageID = 10

ORDER BY
    g2.Date,
    g2.GalleryImageID

So is this possible with LLBLGen code?

Cheers, Ian.

Hi Ian, yes it's possible Please read LLBLGenProHelp - Using generated code - Filtering and sorting - Advanced filtering usage - Custom filters for EntityRelations

I think your code should look like this (assuming Adapter, LLBLGen2.0 and C#):

//As you are joining to the same table and no such relation exists, you must create it:
IEntityRelation newRel = new EntityRelation(TblGalleryImageFields.GalleryImageID , TblGalleryImageFields.GalleryImageID, RelationType.ManyToMany);

// here add to predicate all your criteria... Remember to use SetObjectAlias for "G1" and "G2" aliases. Here you can use Non-equi clauses (>, <, >=, etc).
IPredicateExpression customONClauseFilter = ...

// adding the relation
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(newRel, "G1", "G2", JoinHint.Inner).CustomFilter = customONClauseFilter 

// important. This makes the magic
filter.Relations[0].CustomFilterReplacesOnClause = true;

// other criteria
filter.PredicateExpression.Add(TblGalleryImageFields.GalleryID.SetObjectAlias("G1") == 10);

// sorter. Here add the sort clause
ISortExpression sorter = ...

// fetching the TOP 1 row
EntityCollection<TblGalleryImageEntity> coll = new EntityCollection ....
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(coll , filter, 1, sorter);
}

The code isn't tested, but should work. Please let me know if you make it.

Here's my code...

PredicateExpression expression1 = new PredicateExpression();
            PredicateExpression expression2 = new PredicateExpression();
            expression2.Add(GalleryImageFields.Date.SetObjectAlias("G1") == GalleryImageFields.Date.SetObjectAlias("G2"));

            if (isNext)
            {
                expression1.Add(GalleryImageFields.Date.SetObjectAlias("G1") < GalleryImageFields.Date.SetObjectAlias("G2"));
                expression2.AddWithAnd(GalleryImageFields.GalleryImageId.SetObjectAlias("G1") < GalleryImageFields.GalleryImageId.SetObjectAlias("G2"));
            }
            else
            {
                expression1.Add(GalleryImageFields.Date.SetObjectAlias("G1") > GalleryImageFields.Date.SetObjectAlias("G2"));
                expression2.AddWithAnd(GalleryImageFields.GalleryImageId.SetObjectAlias("G1") > GalleryImageFields.GalleryImageId.SetObjectAlias("G2"));
            }

            expression1.AddWithOr(expression2);

            IEntityRelation newRel = new EntityRelation(GalleryImageFields.GalleryId, GalleryImageFields.GalleryId, RelationType.ManyToMany);

            RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(newRel, "G1", "G2", JoinHint.Inner).CustomFilter = expression1;

            bucket.PredicateExpression.Add(GalleryImageFields.GalleryImageId.SetObjectAlias("G2") == entity.GalleryImageId);

            ISortExpression sorter = new SortExpression();

            SortOperator sortDirection;

            if (isNext)
            {
                sortDirection = SortOperator.Descending;
            }
            else
            {
                sortDirection = SortOperator.Ascending;
            }

            sorter.Add(GalleryImageFields.Date.SetObjectAlias("G1") | sortDirection);
            sorter.Add(GalleryImageFields.GalleryImageId.SetObjectAlias("G1") | sortDirection);

            EntityCollection<GalleryImageEntity> collection = new EntityCollection<GalleryImageEntity>();

            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(collection, bucket, 1, sorter);

and here's the SQL...

exec sp_executesql N'SELECT TOP 1 [newbury].[dbo].[tbl_Gallery_Image].[GalleryImageID] AS [GalleryImageId], [newbury].[dbo].[tbl_Gallery_Image].[GalleryID] AS [GalleryId], [newbury].[dbo].[tbl_Gallery_Image].[Title], [newbury].[dbo].[tbl_Gallery_Image].[Description], [newbury].[dbo].[tbl_Gallery_Image].[Location], [newbury].[dbo].[tbl_Gallery_Image].[Date], [newbury].[dbo].[tbl_Gallery_Image].[Image] FROM ( [newbury].[dbo].[tbl_Gallery_Image] [LPA_G1] INNER JOIN [newbury].[dbo].[tbl_Gallery_Image] [LPA_G2] ON
[LPA_G1].[GalleryID]=[LPA_G2].[GalleryID] AND ( [LPA_G2].[Date] < [LPA_G1].[Date] OR ( [LPA_G2].[Date] = [LPA_G1].[Date] AND [LPA_G2].[GalleryImageID] < [LPA_G1].[GalleryImageID]))) WHERE ( ( [LPA_G1].[GalleryImageID] = @GalleryImageId1)) ORDER BY [LPA_G2].[Date] DESC,[LPA_G2].[GalleryImageID] DESC',N'@GalleryImageId1 int',@GalleryImageId1=72

Firstly, how do I set an alias on the columns being returned?

Secondly, why is it outputting '[LPA_G1].[GalleryImageID] = @GalleryImageId1' when the code says, 'bucket.PredicateExpression.Add(GalleryImageFields.GalleryImageId.SetObjectAlias("G2") == entity.GalleryImageId);'? Isn't that the wong alias?

Cheers, Ian.

Hello,

why do you need to set an alias on the columns being returned? If you do a fetchentitycollection it will store the data in entities so you ahve fields that are already defined. If you want to change their names, you can use the designer to rename a field of an entity.

Secondly, why is it outputting '[LPA_G1].[GalleryImageID] = @GalleryImageId1' when the code says, 'bucket.PredicateExpression.Add(GalleryImageFields.GalleryImageId.SetObjectAlias("G2") == entity.GalleryImageId);'? Isn't that the wong alias?

The alias are autogenerated during the request creation so it's normal, the alias is just for your code but it's not the same name that will be used.

Hi,

I think the problem is that [newbury].[dbo].[tbl_Gallery_Image].[GalleryImageID] for example is ambiguous.

Here's the stack trace...

[SqlException (0x80131904): The multi-part identifier "newbury.dbo.tbl_Gallery_Image.GalleryImageID" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.GalleryID" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Title" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Description" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Location" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Date" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Image" could not be bound.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857482 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735094 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +253

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "newbury.dbo.tbl_Gallery_Image.GalleryImageID" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.GalleryID" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Title" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Description" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Location" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Date" could not be bound. The multi-part identifier "newbury.dbo.tbl_Gallery_Image.Image" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.] SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +408 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) +632 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) +821 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) +186 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses) +47 NWN.DAL.PhotoGalleryDB.GetNextOrPreviousImage(GalleryImageEntity entity, Boolean isNext) in C:\Documents and Settings\XP\My Documents\Visual Studio Projects\NWN.DAL\Fun\PhotoGalleryDB.cs:67 NewburyToday.Fun.GalleryImage.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\NewburyToday\Fun\Gallery\GalleryImage.aspx.cs:22 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

I think I might have got it to work by setting the first table's alias to string.Empty.

It works. Thanks for your help.