I have a many to many situation where three entities are interchained (see table creation scripts below). I have created m:n-relations for the Card entity and the CardHolder entity in the llbl-designer. When I try to fetch a collection of AccessLevel entities with a prefetch of the Card collection (via CardHolderCardAccessLevel) I believe that I need to specify an additional filter for the CardHolderId to get the desired result (as I only want the access levels for a specific card holder). I can't get to work. I've tried to tapp in on the aliases of the FilterRelations och the prefetch object used and a bunch of other variants. Is it possible to get this three-fold scenario working in LLBL?

Here's a variant of the code:

        private static PrefetchPath2 GetNormalAccessLevelPrefetch(IPredicateExpression cardHolderFilter)
        {
            var cardHolderCardAccessLevelOnCardHolderRelations = new RelationCollection();
            cardHolderCardAccessLevelOnCardHolderRelations.Add(CardHolderCardAccessLevelEntity.Relations.CardHolderEntityUsingCardHolderId);

            var prefetch = new PrefetchPath2(EntityType.AccessLevelEntity);
            prefetch.Add(AccessLevelEntity.PrefetchPathCardHolderCardAccessLevels, 0, cardHolderFilter, cardHolderCardAccessLevelOnCardHolderRelations);
            var rel = new RelationCollection();
            foreach (IRelation item in AccessLevelEntity.PrefetchPathCardCollectionViaCardHolderCardAccessLevel.FilterRelations)
            {
                rel.Add(item);
            }
            rel.Add(CardHolderCardAccessLevelEntity.Relations.CardHolderEntityUsingCardHolderId);
            prefetch.Add(AccessLevelEntity.PrefetchPathCardCollectionViaCardHolderCardAccessLevel, 0, new PredicateExpression(CardHolderCardAccessLevelFields.CardHolderId == 3), rel);
            return prefetch;
        }

... and here's the exception thrown:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias? 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]: Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?

Source Error: 


Line 14:         public CardHolderAccessLevelData GetCardHolderAccessLevelDataByCorporateId(string corporateId)
Line 15:         {
Line 16:             return Channel.GetCardHolderAccessLevelDataByCorporateId(corporateId);
Line 17:         }
Line 18: 


Source File: C:\Projects\IdCardAccessControl\Contracts\Proxies\AccessControlServiceClient.cs    Line: 16 

Stack Trace: 


[FaultException`1: Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?]
   System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +9464367
   System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +345
   IdCardAccessControl.Contracts.IAccessControl.GetCardHolderAccessLevelDataByCorporateId(String corporateId) +0
   IdCardAccessControl.Contracts.Proxies.AccessControlServiceClient.GetCardHolderAccessLevelDataByCorporateId(String corporateId) in C:\Projects\IdCardAccessControl\Contracts\Proxies\AccessControlServiceClient.cs:16
   IdCardManagement.BizLogic.AccessControlLogic.GetAccessControlProductsForPerson(String corporateId) in C:\Projects\IdCardMgmt\BizLogic\AccessControlLogic.cs:35
   IdCardManagement.Presenter.Controls.AccessControlControlPresenter.Select() in C:\Projects\IdCardMgmt\Presenter\Controls\AccessControlControlPresenter.cs:42
   IdCardManagement.Presenter.Controls.AccessControlControlPresenter.OnCorporateIdChangedView(Object sender, EventArgs e) in C:\Projects\IdCardMgmt\Presenter\Controls\AccessControlControlPresenter.cs:35
   IdCardManagement.Web.Controls.AccessControlControl.HandleCorporateIdSetEvent() in C:\Projects\IdCardMgmt\Web\Controls\AccessControlControl.ascx.cs:49
   IdCardManagement.Web.Controls.AccessControlControl.set_CorporateId(String value) in C:\Projects\IdCardMgmt\Web\Controls\AccessControlControl.ascx.cs:30
   IdCardManagement.Web.Authenticated.Profile.OnPersonLoadedCompletePersonControl(Object sender, EventArgs e) in C:\Projects\IdCardMgmt\Web\Authenticated\Profile.aspx.cs:33
   IdCardManagement.Web.Controls.PersonControl.HandlePersonLoadedCompleteEvent() in C:\Projects\IdCardMgmt\Web\Controls\PersonControl.ascx.cs:157
   IdCardManagement.Web.Controls.PersonControl.LoadPerson() in C:\Projects\IdCardMgmt\Web\Controls\PersonControl.ascx.cs:140
   IdCardManagement.Web.Controls.PersonControl.Page_Load(Object sender, EventArgs e) in C:\Projects\IdCardMgmt\Web\Controls\PersonControl.ascx.cs:120
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +91
   System.Web.UI.Control.LoadRecursive() +74
   System.Web.UI.Control.LoadRecursive() +146
   System.Web.UI.Control.LoadRecursive() +146
   System.Web.UI.Control.LoadRecursive() +146
   System.Web.UI.Control.LoadRecursive() +146
   System.Web.UI.Control.LoadRecursive() +146
   System.Web.UI.Control.LoadRecursive() +146
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1 

CREATE TABLE [dbo].[AccessLevel](
    [AccessLevelId] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_AccessLevel] PRIMARY KEY CLUSTERED 
(
    [AccessLevelId] ASC
)) ON [PRIMARY]



CREATE TABLE [dbo].[Card](
    [CardId] [bigint] IDENTITY(1,1) NOT NULL,
    [CardNumber] [nvarchar](20) NULL,
    [CardStatus] [int] NULL,
 CONSTRAINT [PK_Card] PRIMARY KEY CLUSTERED 
(
    [CardId] ASC
)) ON [PRIMARY]




CREATE TABLE [dbo].[CardHolder](
    [CardHolderId] [bigint] IDENTITY(1,1) NOT NULL,
    [CorporateId] [nvarchar](50) NULL,
    [EmployeeNumber] [nvarchar](50) NULL,
    [Surname] [nvarchar](50) NULL,
    [GivenName] [nvarchar](50) NULL,
    [Comment] [nvarchar](250) NULL,
    [Picture] [varbinary](max) NULL,
 CONSTRAINT [PK_CardHolder] PRIMARY KEY CLUSTERED 
(
    [CardHolderId] ASC
)
) ON [PRIMARY]


CREATE TABLE [dbo].[CardHolderCardAccessLevel](
    [AccessLevelId] [bigint] NOT NULL,
    [CardHolderId] [bigint] NOT NULL,
    [CardId] [bigint] NOT NULL,
    [Comment] [nvarchar](500) NULL,
    [ValidFrom] [datetime] NULL,
    [ValidTo] [datetime] NULL,
 CONSTRAINT [PK_CardHolderCardAccessLevel] PRIMARY KEY CLUSTERED 
(
    [AccessLevelId] ASC,
    [CardHolderId] ASC,
    [CardId] ASC
)
) ON [PRIMARY]


ALTER TABLE [dbo].[CardHolderCardAccessLevel]  WITH CHECK ADD  CONSTRAINT [FK_CardHolderCardAccessLevel_AccessLevel] FOREIGN KEY([AccessLevelId])
REFERENCES [dbo].[AccessLevel] ([AccessLevelId])
GO

ALTER TABLE [dbo].[CardHolderCardAccessLevel]  WITH CHECK ADD  CONSTRAINT [FK_CardHolderCardAccessLevel_Card] FOREIGN KEY([CardId])
REFERENCES [dbo].[Card] ([CardId])
GO


ALTER TABLE [dbo].[CardHolderCardAccessLevel]  WITH CHECK ADD  CONSTRAINT [FK_CardHolderCardAccessLevel_CardHolder] FOREIGN KEY([CardHolderId])
REFERENCES [dbo].[CardHolder] ([CardHolderId])
GO

I'm using ORM support classes version 3.0.10.111, Adapter, .NET 4, SQL Server 2008.

I'm not sure what all this code is doing.

You said you are fetching AccessCards..fine. Then you want to prefetch Cards along.. so you should use the following code:

            var prefetch = new PrefetchPath2(EntityType.AccessLevelEntity);
            prefetch.Add(AccessLevelEntity.PrefetchPathCardCollectionViaCardHolderCardAccessLevel);

Then you need to filter those cards by CardHolderId then you need to modify the above code to the following:

            var rel = new RelationCollection();
            rel.Add(CardEntity.Relations.CardHolderCardAccessLevelEntity...);

            var prefetch = new PrefetchPath2(EntityType.AccessLevelEntity);
            prefetch.Add(AccessLevelEntity.PrefetchPathCardCollectionViaCardHolderCardAccessLevel, 0, new PredicateExpression(CardHolderCardAccessLevelFields.CardHolderId == 3), rel);

This should be it, if I'm not missing anything.

That worked just fine!

I feel kind of embarrassed. I almost had it but messed it up for the reason of trying to re-use an already existing relation collection and predicate expression. Darn!

Well, many thanks for putting me in the right direction!