Handling joins in object query

Your question is quite oddly formatted.. Not sure if that is on purpose, but it is really hard to read.

Anyway, if you want to learn more about ObjectQuery I think the best thing you can do is to check following 2 articles which contain a lot of working examples of what you can do with ObjectQuery and how. It will be much more efficient then trying to explain it here.

Here you go:

http://devnet.kentico.com/articles/kentico-8-technology-dataquery-api

http://devnet.kentico.com/articles/kentico-8-technology-dataquery-advanced-api

Richard, I appreciate the attempt but I have read those articles many times. If you were to read them again with my question in mind you would find that it does not touch on doing a join with a custom table. Unfortunately your answer doesn't seem to apply unless I am missing something. You will need to point out exactly what part of those articles you are thinking apply otherwise I'll have to wait for others to answer.

 Thank you,
    Brandon

As far as the formatting of my question that is what the Kentico Q&A board is doing by default. Not sure why it decided to do that

How about this:

var joinQuery = UserInfoProvider.GetUsers()
    .Source(sourceItem => sourceItem.Join<UserSettingsInfo>("UserID", "UserSettingsUserID"))
    .WhereEmpty("UserPassword");

All, Please remember that when you answer this question I am asking in reference to custom tables...the above answer doesn't refer to custom tables at all. If custom tables are not involved in your answer then your answer is not valid. Thank you, Brandon

Brandon, if you create a class for your custom table on the code view for the custom table(inside of the kentico admin portal), that may help you out. Then maybe you can use that class to cast your custom table to an object. This could possibly make the above item work, but in any case you could possibly use something like the example on the following link. There are a couple of items in the article that may help you out. Note, none of them EXPLICITLY use custom tables, but having some knowledge of database tables and queries may help you use it.

http://devnet.kentico.com/articles/kentico-8-technology-dataquery-advanced-api

I believe you want to use Data Query instead of object query:

new DataQuery().From(new QuerySource("CMS_Users")
                      .LeftJoin("CustomTable_TableName", "CMS_Users.UserID",
                          "CustomTable_TableName.UserID")).Where("CustomTable_TableName.UserId", QueryUnaryOperator.IsNotNull);

Which is mentioned here.

Hi, when using joins, you can always start with the most left source the way you would retrieve it normally. In this case your source is a custom table so you just query custom table items for that table.

var query = 
    CustomTableItemProvider.GetItems("custom.yourtable");

Then you just extend this with the join using the Source method:

var query =
    CustomTableItemProvider.GetItems("custom.yourtable")
        .Source(s => s
            .LeftJoin(
                "Other_Table",
                "ItemID",
                "OtherID",
                new WhereCondition()
                    .WhereNotNull("OtherTable.OtherID")
            )
        );

Left remains current, table is added as right, and if not fully qualified, column names for the ON condition are prefix with latest left and the new right tables. Optionally you can add condition to JOIN, or to the query itself.

If the left most table does not have native Kentico API, you can always use low-level DataQuery.

This really mostly is information from those two articles plus documentation for custom tables (https://docs.kentico.com/display/API9/Custom+table+data), you just sometimes need to decompose individual pieces of the information, and put some of them together the right way. I am sure you will learn this as you work more with it. In general it is a good practice to make an automated test, or some empty page, and just try various combinations and observe how they work together.

Hope this helped.