Join between custom Page types giving error

joyanta sen asked on October 4, 2018 19:53

Hi, I am trying to join multiple page types(custom) but getting error like "The multi-part identifier could not be bound" Custom page types: EventSessionProvider PattanSession

EventSessionProvider.GetEventSessions()
                        .Source(eventinfo=>eventinfo.Join<PattanSession>("custom_EventSession.EventSessionID", "custom_PattanSession.EventSessionID"))
                        .Columns("EventCreditTypeID")
                        .WhereGreaterOrEquals("custom_PattanSession.SessionStartDate", DateTime.Now)
                        .Select(c => c.EventCreditTypeID)
                        .Aggregate((crdTypeIds, nextCrdTypesIds) => crdTypeIds + ";" + nextCrdTypesIds)
                        .Split(';')
                        .Distinct()
                        .Where(id=>!string.IsNullOrEmpty(id));

This is creating following sql query

        SELECT  [EventCreditTypeID], [ClassName]
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND)
 INNER JOIN custom_EventSession AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[EventSessionID] AND V.ClassName = N'custom.EventSession' 
 LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID] 
 INNER JOIN CMS_Document ON [custom_EventSession].[EventSessionID] = [custom_PattanSession].[EventSessionID]
WHERE custom_PattanSession.SessionStartDate >= getdate() 

I am not sure why CMS_Document is coming at the end in the join instead PattanSession?

I then tried with this, which resolve the CMS_Document issue but still getting error "The multi-part identifier "custom_EventSession.EventSessionID" could not be bound."

  EventSessionProvider.GetEventSessions()
                           .Source(eventinfo => eventinfo.Join(new QuerySourceTable("custom_PattanSession", "PS"), "custom_EventSession.EventSessionID", "PS.EventSessionID"))
                           .Columns("EventCreditTypeID")
                           .WhereGreaterOrEquals("PS.SessionStartDate", DateTime.Now)
                           .Select(c => c.EventCreditTypeID)
                           .Aggregate((crdTypeIds, nextCrdTypesIds) => crdTypeIds + ";" + nextCrdTypesIds)
                           .Split(';')
                           .Distinct()
                           .Where(id => !string.IsNullOrEmpty(id));

This create following query:

SELECT [EventCreditTypeID], [EventCreditTypeID], [ClassName]
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) 
INNER JOIN custom_EventSession AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[EventSessionID] AND V.ClassName = N'custom.EventSession' 
LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID] 
INNER JOIN custom_PattanSession AS PS ON ***custom_EventSession.EventSessionID***  = [PS].[EventSessionID]
WHERE PS.SessionStartDate >= GETDATE()                              

custom_EventSession.EventSessionID is throwing error "The multi-part identifier "custom_EventSession.EventSessionID" could not be bound."
Could you please help me to correct the query.

Thanks.

Recent Answers


Peter Mogilnitski answered on October 5, 2018 01:41

it should be C.custom_EventSession instead of custom_EventSession.EventSessionID

0 votesVote for this answer Mark as a Correct answer

joyanta sen answered on October 5, 2018 23:47

HI, Thanks for your response. It worked. Could you please tell me why the first example is not working(attached below). But that works when I use Kentico out of the box Page types Like Users and thats the way it is specified in the documents too.

EventSessionProvider.GetEventSessions()
                    .Source(eventinfo=>eventinfo.Join<PattanSession>("custom_EventSession.EventSessionID", "custom_PattanSession.EventSessionID"))
                    .Columns("EventCreditTypeID")
                    .WhereGreaterOrEquals("custom_PattanSession.SessionStartDate", DateTime.Now)
                    .Select(c => c.EventCreditTypeID)
                    .Aggregate((crdTypeIds, nextCrdTypesIds) => crdTypeIds + ";" + nextCrdTypesIds)
                    .Split(';')
                    .Distinct()
                    .Where(id=>!string.IsNullOrEmpty(id));

Thanks.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.