ObjectQuery Join with Where

Revolution Golf asked on September 3, 2016 00:45

I am writing a scheduled task in Kentico that is pulling data via an ObjectQuery. I am joining 2 tables together and then adding a where clause. Unfortunately the ObjectQuery engine is not including the table name along with the column name in the where clause causing the sql statement to fail since both tables have that column name. Is there a way to tell the system to include the tablename? For the record we are currently using Kentico 8.1.

qbjectquery join where

Correct Answer

Revolution Golf answered on September 12, 2016 16:00

Before I post the full answer I'd like to give a shout out to Kristian Bortnik who came up with something very close to what I am going to provide. He had the best answer of anyone. In fact the main reason I am posting my answer is because I had had a slight difference that would potentially help with performance. For starters I added some class level variables and initialized them on class construction...so something like this:

private DataClassInfo _tableADataClassInfo = DataClassInfoProvider.GetDataClassInfo(tableAInfo.OBJECT_TYPE);
private DataClassInfo _tableBDataClassInfo = DataClassInfoProvider.GetDataClassInfo(tableBItem.CLASS_NAME);

You will notice that table b is done a little different and that is because it is a Custom Table. I wanted to make sure and include that so someone looking at this would be able to see it both ways. This particular solution requires you to get the class generated for a custom table and include it in you project. If you wish not to do that you can also create a constant in your solution that is the code name of the custom table and that can be used to get the DataClassInfo.

Once you have this you then do your where clause like this:

TableAInfoProvider.GetTableARecords()
    .Source(sourceItem => sourceItem.Join<tableBItem>("tableBKey", "tableAKey"))
    .WhereLessOrEquals(string.Format("{0}.TableBColumn", _tableBDataClassInfo.ClassTableName), value)
    .Or().WhereLessOrEquals(string.Format("{0}.TableAColumn", _tableADataClassInfo.ClassTableName), value)
    .ToList();

With this type set up you do not have to get the data class info every time the query is run for the same instance of the class and you now are taking advantage of the object for the table name. This means one less thing you have to change your code for to match the configuration of your Kentico system. That may not be a big deal to some people...but I think it is nice.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on September 3, 2016 08:06

Have you debugged the query within the SQL debugged within Kentico? Are you specifying columns in your query?

0 votesVote for this answer Mark as a Correct answer

Laura Frese answered on September 3, 2016 20:11

You might also consider writing a custom query then using DataQuery to call it that way

1 votesVote for this answer Mark as a Correct answer

Kristian Bortnik answered on September 4, 2016 10:46

You could just add the table name in the WHERE condition.

For example, if TableA and TableB have a column with the same name (SameColumn):

var data = TableAInfoProvider.GetTableAs()
    .Source(x => x.Join<TableBInfo>("TableAID", "TableAID"))
    .WhereEquals("custom_TableA.SameColumn", "test")
    .Result;

0 votesVote for this answer Mark as a Correct answer

Revolution Golf answered on September 6, 2016 16:39

Kristian, I already figured out how to get it to work by doing that but it doesn't seem like a very good way to handle the situation although this may be the best answer.

All, I would have hoped that if a team of developers were going to create a query language to take the place of linq they would have made sure they had something that had most of the benefits. I am finding a number of things missing and I am having to do a lot of workarounds for what doesn't seem like that complicated of a query. I hope that someone comes up with a better answer. If nothing else I hope the Kentico team has drastically improved this query language in the newer versions or at least come up with a way of giving the developer a choice of using their language or linq (of course with how they have done it that likely isn't easy).

I have looked at the custom query but I don't really like that solution.  Using a query language like linq has several advantages.  One is that the query relies on classes.  If you upgrade the code and it changes one of the underlying classes it becomes very obvious.  The other thing I like is that it is easier to know who is using a query to make sure you don't mess up another part of the app by changing a query for something you are currently working on.  This happens a lot when people use views for example.  Someone comes along and says hey...someone already created a view that looks like it will work for me.  After hooking in the view to their code someone comes along later working on one of the two areas just mentioned and says..you know I really need to adjust they view.  Adjusting it then causes a bug in the other area...one that may not be noticed right away and may not be the obvious when it is.  Something like this can cost a developer a decent amount of time.  Granted having a linq query isn't full proof but they can be written such that they are only accessible by a certain part of the code and with newer versions of Visual Studio (or with Visual Studio plugins) in can be quite obvious that another bit of code is using it and making you think twice about modifying it vs making a new query or making it configurable.

I apologize for going on that long.  The main point is that I chose the query language for a reason and I am not really looking for an alternative solution like the custom queries.  I am merely trying to understand the features of the Kentico query language.  So far I have one answer down the right path which actually matches my current code.  I look forward to seeing if anyone else can provide another option.

Thank you,
    Brandon

0 votesVote for this answer Mark as a Correct answer

Kristian Bortnik answered on September 9, 2016 08:38 (last edited on September 9, 2016 08:39)

Brandon, I totally understand your concerns, as well as your intention to fully rely on the ObjectQuery/DataQuery system. However, query system is relatively new, and Kentico is definitely willing to hear your feedback on their ideas page.

In your particular scenario of a "WHERE" condition with a column in both tables - I believe the main problem is the fact that you have to reference the object by its database table name, which could cause issues in future changes and work performed on the site.

One quick fix is to use the type information property to get the table name.

var data = TableAInfoProvider.GetTableAs()
    .Source(x => x.Join<TableBInfo>("TableAID", "TableAID"))
    .WhereEquals(TableAInfo.TYPEINFO.ClassStructureInfo.TableName + ".SameColumn", "test")
    .Result;

I am unsure about the performance overhead with doing this.

1 votesVote for this answer Mark as a Correct answer