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.