Hi,

I have gotten confused about fetching related records, and would really appreciate your help. Can you please help me?

The situation is this:

In ASP.NET 2.0, using LLBLGen Pro 2.0 and SQL Server 2000, I have a drop down list box that I'd like to do a query for related data when the selection has changed.

In the drop down listbox, there is data for "Venue". When the user changes the "Venue", I'd like for LLBLGen to find all of the "Note" records attached to the "Venue". The Venue and Note tables are connected with a VenueNote table. The code for the table definitions is below.

Table Design:

CREATE TABLE [ilys4u_TeamGratitude].[Venue](
    [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (newid()),
    [Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Address] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Note] [varchar](6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [Venue_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [ilys4u_TeamGratitude].[Note](
    [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (newid()),
    [UserID] [uniqueidentifier] NOT NULL,
    [Note] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Created] [datetime] NOT NULL,
 CONSTRAINT [Note_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [ilys4u_TeamGratitude].[Note]  WITH CHECK ADD  CONSTRAINT [User_Note_FK1] FOREIGN KEY([UserID])
REFERENCES [ilys4u_TeamGratitude].[User] ([ID])


CREATE TABLE [ilys4u_TeamGratitude].[VenueNote](
    [VenueID] [uniqueidentifier] NOT NULL,
    [NoteID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [VenueNote_PK] PRIMARY KEY CLUSTERED 
(
    [NoteID] ASC,
    [VenueID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [ilys4u_TeamGratitude].[VenueNote]  WITH CHECK ADD  CONSTRAINT [Note_VenueNote_FK1] FOREIGN KEY([NoteID])
REFERENCES [ilys4u_TeamGratitude].[Note] ([ID])
GO
ALTER TABLE [ilys4u_TeamGratitude].[VenueNote]  WITH CHECK ADD  CONSTRAINT [Venue_VenueNote_FK1] FOREIGN KEY([VenueID])
REFERENCES [ilys4u_TeamGratitude].[Venue] ([ID])

It would be great if the returned recordset could be databound to a Grid control.

Thank you in advance, Mike

I'm having trouble visualizing your scenario. Depending on what your looking for there are 2 different actions.

If you want to retrieve the venue and all related notes you the prefetchPath. It would look something like VenueEntity.prefetchNotesViaVenueNoteOnVenueId. (vice-versa from the Notes Entity).

If you want a collection of Notes filtered on a selected Venue create a RelationPredicateBucket like this

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(NoteEntity.Relations.VenueNoteEntity);
filter.PredicateRelations.Add(VenueNoteFields.VenueId == [user input]);

then fetch enity collection using the filter object.

I believe one of these scenarios is what your looking for.