Join with where condition is not working

It is probably taking it from

.WhereEquals("CMS_User.UserID", userInfo.UserID)

Can you confirm that the user info exists and it has a user ID on it?

Can you also confirm that if you do pass the user ID that rows are returned (copy the SQL statement and see if it works in SQL management studio). It could be one of the joins is causing no rows to return.

Hi Trevor,

Thanks for your response. UserInfo is there and UserId is also exists. And yes when I replace @CMS_User_UserID with the value in SQL I get the data.

When I do like below I am getting the data. Don't know why the first one is not working.
.Where("CMS_User.UserID=" + userInfo.UserID)

Now I change the query to like below and it is working.

 UserInfoProvider.GetUsers()                            
  .Source(user => user.Join<ContactInfo>("CMS_User.UserID", "OM_Contact.ContactOwnerUserID")                            .Join<SubscriberInfo>("OM_Contact.ContactID", "Newsletter_Subscriber.SubscriberRelatedID")                            )                            .Where("CMS_User.UserID="+userInfo.UserID)                            .Columns("Newsletter_Subscriber.SubscriberID").Distinct();

Thanks.

Hi Trevor,

But I will be happy to know why the other one is not working...

Thanks.

Very interesting...yeah i would like to know too! It may be a bug, my guess is for some reason it wans't passing the @CMS_User_UserID parameter, good debugging though that you used the normal .Where("") instead of the WhereEquals to test.

I would report it to Kentico if you are on Version 11, if you are before version 11 then reporting hte bug may not do much good unless you can confirm it's still an issue in version 11.

Yes this is happening in Kentico11.

Thanks.

How is your userInfo in WhereEquals("CMS_User.UserID", userInfo.UserID) been defined?

Hi, This is the code I have written.

UserInfo userInfo = UserInfoProvider.GetUsers().WhereEquals("UserGuid", userRegistrationInfo.UserGuid).FirstOrDefault();

    var subscriberID = UserInfoProvider.GetUsers()                                .Source(user => user.Join<ContactInfo>("CMS_User.UserID", "OM_Contact.ContactOwnerUserID")                                .Join<SubscriberInfo>("OM_Contact.ContactID", "Newsletter_Subscriber.SubscriberRelatedID")                                )                                .WhereEquals("CMS_User.UserID",  userInfo.UserID)                                .Columns("Newsletter_Subscriber.SubscriberID").Distinct();

I also tried this and get the same result as your initial one. You may want to submit this to our support team and log it.

Where("CMS_User.UserID", QueryOperator.Equals, userInfo.UserID)

@joyanta got some more information for you. The query generated by the API contains the parameter to prevent SQL injection. (https://devnet.kentico.com/articles/kentico-8-technology-dataquery-api)

Response.Write(UserInfoProvider.GetUsers().Source(user => user.Join<ContactInfo>("CMS_User.UserID", "OM_Contact.ContactOwnerUserID").Join<SubscriberInfo>("OM_Contact.ContactID", "Newsletter_Subscriber.SubscriberRelatedID")).WhereEquals("CMS_User.UserID", userInfo.UserID).Columns("Newsletter_Subscriber.SubscriberID").Distinct().ToString(true));

By adding the true to the ToString, it will expand the parameter and show the real value. In your case, did the API not work?

Hi, Sorry for late response.. you mean to say passing true in the string. If so I am still not getting the value in the query.

string str = DocumentHelper.GetDocuments() .Types("CMS.BookingEvent") .Path(parentPath) .OnSite("Test") .Culture("en-us") .WithCoupledColumns().ToString("true");

Output:

SELECT * FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN CONTENT_BookingEvent AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[BookingEventID] AND V.ClassName = N'CMS.BookingEvent' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID] WHERE [NodeSiteID] = @NodeSiteID AND ([NodeAliasPath] = @NodeAliasPath AND [DocumentCulture] = @DocumentCulture)

Thanks.