Hello.

I have 2 tables, with a key relationship between them. I need to filter on data from both tables but I also need to return data from both tables.

Adding a relation via the RelationPredicateBucket creates an INNER JOIN in the generated SQL but doesn't include the fields of the second table in the SELECT.

I added in a prefetch condition to ensure that the content of the second table is also returned. This works.

However, when viewing the generated SQL it appears to generate two seperate queries. One query on the original table INNER JOINing on the child table and a second query to pull back the rows from the second table.

This seems like more load on the database than is neccesary; is this the correct way to reproduce my desired query or is there a better solution?

Effectively the SQL I want is:

SELECT t1.Field1, t1.Field2, ..., t2.Field1, t2.Field2, ...
FROM t1 INNER JOIN t2 ON t1.Field = t2.Field
WHERE t1.Field1 = X AND t2.Field1 = Y

The current code I have is:

EntityCollection<ContentImageQueueEntity> queue;
IRelationPredicateBucket filter;
IPrefetchPath2 prefetch;
ISortExpression sorter;

queue = new EntityCollection<ContentImageQueueEntity>(new ContentImageQueueEntityFactory());

filter = new RelationPredicateBucket();
            filter.Relations.Add(ContentImageQueueEntity.Relations.ContentEntityUsingMasterId);

filter.PredicateExpression.Add(new FieldCompareNullPredicate(ContentImageQueueFields.DateTimeProcessed, null, false));

prefetch = new PrefetchPath2((int)EntityType.ContentImageQueueEntity);
prefetch.Add(ContentImageQueueEntity.PrefetchPathContent);

sorter = new SortExpression(ContentImageQueueFields.ContentImageQueueId | SortOperator.Ascending);

using (DataAccessAdapter adapter = new DataAccessAdapter(this.DatabaseConnectionString)
{
                adapter.FetchEntityCollection(queue, filter, 0, sorter, prefetch);
}

Which seems to produce the below SQL:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT t1.Fields1, ... FROM ( t1  INNER JOIN t2  ON  t2.Field=t1.Field ) WHERE ( t1.Field1 = @Param1 AND t2.Field1 = @Param2 ) ORDER BY t1.FieldX ASC

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT t2.Field1, ... FROM t2  WHERE ( ( ( t2.Field = @Param1)))

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

SELECT t1.Field1, t1.Field2, ..., t2.Field1, t2.Field2, ... FROM t1 INNER JOIN t2 ON t1.Field = t2.Field WHERE t1.Field1 = X AND t2.Field1 = Y

If you want to fetch fields from more than one table in the same query, then you have to use one of the flat dta structures (TypedList or DynamicList).

Right.

So relations are only added when you want to filter on that table? If you want to return fields from another table the relation is of no use?

Prefetch is generally the method used to populate sub-objects of an entity? But it does result in a seperate query for each item found in the initial query?

So relations are only added when you want to filter on that table?

Not only that, relations are added when you want to join, whether this is for filterng or sorting on another table's field or to just select fields from another table.

If you want to return fields from another table the relation is of no use?

No, you should use a relation here too.

Prefetch is generally the method used to populate sub-objects of an entity? But it does result in a seperate query for each item found in the initial query?

A query is produced for each prefetchPath you use.