1) In a DynamicRelation, why is it not allowed to have a Table on the left-hand side and a DerivedTableDefinition on the right hand side? It seems to me to produce a query which is arse-about-face and maybe a right join (I try to avoid using Right Joins)

2) Why must an alias be used? It seems to be only necessary (in some circumstances?) because the Main Table is on the right.

3) The "Targeting a DerivedTableDefinition for an entity fetch" section is very confusing to me. It starts by saying "When fetching entities which data is coming from a Derived Table, the target of the fetch has to be set to the DerivedTableDefinition..." but what if the data is not coming from a Derived Table but just the Real Table as in the examples - does it mean "When fetching entities where data is coming from a query involving a Derived Table..."?

I am struggling with a generated query: The output is currently DECLARE @AllocatedFlag1 Bit; SET @AllocatedFlag1=1 DECLARE @ID2 Int; SET @ID2=2583 DECLARE @ID3 Int; SET @ID3=164 SELECT [fred].[ship_seq] AS [ID], [fred].[ship_full_nm] AS [Name], [fred].[registration_authority_cd] AS [RegistrationAuthorityCode], [fred].[tug_nm] AS [TugName], [fred].[ship_interface_ind] AS [ShipboardInterface], [fred].[fleet_cd] AS [FleetCode], [LPA_M1].[FollowUpMessagesCount], [LPA_M1].[HasPriorityMessages] FROM ( (SELECT [OSS_DOCUMENT_TRANSACTION].[ship_seq] AS [ShipID], SUM(CAST(follow_up_ind AS INT)) AS [FollowUpMessagesCount], CONVERT(BIT, CASE WHEN SUM(CAST(priority_ind AS INT)) > 0 THEN 1 ELSE 0 END) AS [HasPriorityMessages] FROM ( [OSS_DOCUMENT]
INNER JOIN [OSS_DOCUMENT_TRANSACTION] ON [OSS_DOCUMENT].[doc_id]=[OSS_DOCUMENT_TRANSACTION].[doc_id]) WHERE ( ( [OSS_DOCUMENT_TRANSACTION].[voyage_seq] IS NULL AND [OSS_DOCUMENT_TRANSACTION].[allocated_ind] = @AllocatedFlag1)) GROUP BY [OSS_DOCUMENT_TRANSACTION].[ship_seq]) [LPA_M1]
RIGHT JOIN [] [LPA_f2] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ship_seq]) WHERE ( ( ( [OSS_SHIP].[ship_seq] IN (@ID2, @ID3) OR [OSS_SHIP].[fleet_cd] IS NOT NULL))) ORDER BY [OSS_SHIP].[ship_full_nm] ASC

It is this line that is the problem RIGHT JOIN [] [LPA_f2] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ship_seq])

1) it should be inserting [OSS_SHIP] not [] 2) I've no idea where LPA_f2 is coming from - I used the alias "fred" for the right-side alias and the SelectListAlias ie. public static RelationPredicateBucket CreateBucket() { RelationPredicateBucket result = new RelationPredicateBucket(); DynamicRelation relation = new DynamicRelation(messagesDerivedTable, JoinHint.Right, EntityType.ShipDFNEntity, "fred", ShipDFNFields.ID == ShipDFNFields.ID.SetObjectAlias, messagesDerivedTableAlias));

result.Relations.Add(relation);
result.SelectListAlias = "fred";

return result;

}

3) Also, [LPA_M1].[ship_seq] is not quite right - it should be [LPA_M1].[ShipID] as that is the field alias used in the Derived Table

Again, I can't see the need for an alias on OSS_SHIP here, what I am ideally looking for is DECLARE @AllocatedFlag1 Bit; SET @AllocatedFlag1=1 DECLARE @ID2 Int; SET @ID2=2583 DECLARE @ID3 Int; SET @ID3=164 SELECT [OSS_SHIP].[ship_seq] AS [ID], [OSS_SHIP].[ship_full_nm] AS [Name], [OSS_SHIP].[registration_authority_cd] AS [RegistrationAuthorityCode], [OSS_SHIP].[tug_nm] AS [TugName], [OSS_SHIP].[ship_interface_ind] AS [ShipboardInterface], [OSS_SHIP].[fleet_cd] AS [FleetCode], [LPA_M1].[FollowUpMessagesCount], [LPA_M1].[HasPriorityMessages] FROM ( (SELECT [OSS_DOCUMENT_TRANSACTION].[ship_seq] AS [ShipID], SUM(CAST(follow_up_ind AS INT)) AS [FollowUpMessagesCount], CONVERT(BIT, CASE WHEN SUM(CAST(priority_ind AS INT)) > 0 THEN 1 ELSE 0 END) AS [HasPriorityMessages] FROM ( [OSS_DOCUMENT]
INNER JOIN [OSS_DOCUMENT_TRANSACTION] ON [OSS_DOCUMENT].[doc_id]=[OSS_DOCUMENT_TRANSACTION].[doc_id]) WHERE ( ( [OSS_DOCUMENT_TRANSACTION].[voyage_seq] IS NULL AND [OSS_DOCUMENT_TRANSACTION].[allocated_ind] = @AllocatedFlag1)) GROUP BY [OSS_DOCUMENT_TRANSACTION].[ship_seq]) [LPA_M1]
RIGHT JOIN [OSS_SHIP] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ShipID]) WHERE ( ( ( [OSS_SHIP].[ship_seq] IN (@ID2, @ID3) OR [OSS_SHIP].[fleet_cd] IS NOT NULL))) ORDER BY [OSS_SHIP].[ship_full_nm] ASC

I can nearly get this by using string.Empty instead of Fred and not setting SelectListAlias which make the query clearer by removing the alias but still no Table name specified??

[] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ship_seq])

Cheers Simon

simmotech wrote:

1) In a DynamicRelation, why is it not allowed to have a Table on the left-hand side and a DerivedTableDefinition on the right hand side? It seems to me to produce a query which is arse-about-face and maybe a right join (I try to avoid using Right Joins)

It made the code a lot simpler and there's no need for the other situation.

Right joins aren't bad, they're converted to left joins by the RDBMS if required, i.o.w. it doesnt really matter.

2) Why must an alias be used? It seems to be only necessary (in some circumstances?) because the Main Table is on the right.

derived tables always have to have an alias in SQL

3) The "Targeting a DerivedTableDefinition for an entity fetch" section is very confusing to me. It starts by saying "When fetching entities which data is coming from a Derived Table, the target of the fetch has to be set to the DerivedTableDefinition..." but what if the data is not coming from a Derived Table but just the Real Table as in the examples - does it mean "When fetching entities where data is coming from a query involving a Derived Table..."?

If you're fetching an entity which data is in a derived table, you have to set the target to teh alias of the derived table (otherwise the entity fetch will cause that the query will simply try to retrieve the data from the fields the entity is mapped on). If you just JOIN with a derived table, no worries, then you don't have to do anything. The query engine will by default produce a query which fetches from the table fields the entity is mapped on. if you thus want to change that because you've to fetch from a derived table, you've to set that target.

I am struggling with a generated query: The output is currently DECLARE @AllocatedFlag1 Bit; SET @AllocatedFlag1=1 DECLARE @ID2 Int; SET @ID2=2583 DECLARE @ID3 Int; SET @ID3=164 SELECT [fred].[ship_seq] AS [ID], [fred].[ship_full_nm] AS [Name], [fred].[registration_authority_cd] AS [RegistrationAuthorityCode], [fred].[tug_nm] AS [TugName], [fred].[ship_interface_ind] AS [ShipboardInterface], [fred].[fleet_cd] AS [FleetCode], [LPA_M1].[FollowUpMessagesCount], [LPA_M1].[HasPriorityMessages] FROM ( (SELECT [OSS_DOCUMENT_TRANSACTION].[ship_seq] AS [ShipID], SUM(CAST(follow_up_ind AS INT)) AS [FollowUpMessagesCount], CONVERT(BIT, CASE WHEN SUM(CAST(priority_ind AS INT)) > 0 THEN 1 ELSE 0 END) AS [HasPriorityMessages] FROM ( [OSS_DOCUMENT]
INNER JOIN [OSS_DOCUMENT_TRANSACTION] ON [OSS_DOCUMENT].[doc_id]=[OSS_DOCUMENT_TRANSACTION].[doc_id]) WHERE ( ( [OSS_DOCUMENT_TRANSACTION].[voyage_seq] IS NULL AND [OSS_DOCUMENT_TRANSACTION].[allocated_ind] = @AllocatedFlag1)) GROUP BY [OSS_DOCUMENT_TRANSACTION].[ship_seq]) [LPA_M1]
RIGHT JOIN [] [LPA_f2] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ship_seq]) WHERE ( ( ( [OSS_SHIP].[ship_seq] IN (@ID2, @ID3) OR [OSS_SHIP].[fleet_cd] IS NOT NULL))) ORDER BY [OSS_SHIP].[ship_full_nm] ASC

It is this line that is the problem RIGHT JOIN [] [LPA_f2] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ship_seq])

1) it should be inserting [OSS_SHIP] not [] 2) I've no idea where LPA_f2 is coming from - I used the alias "fred" for the right-side alias and the SelectListAlias ie. public static RelationPredicateBucket CreateBucket() { RelationPredicateBucket result = new RelationPredicateBucket(); DynamicRelation relation = new DynamicRelation(messagesDerivedTable, JoinHint.Right, EntityType.ShipDFNEntity, "fred", ShipDFNFields.ID == ShipDFNFields.ID.SetObjectAlias, messagesDerivedTableAlias));

result.Relations.Add(relation);
result.SelectListAlias = "fred";

return result;

}

3) Also, [LPA_M1].[ship_seq] is not quite right - it should be [LPA_M1].[ShipID] as that is the field alias used in the Derived Table

Again, I can't see the need for an alias on OSS_SHIP here, what I am ideally looking for is DECLARE @AllocatedFlag1 Bit; SET @AllocatedFlag1=1 DECLARE @ID2 Int; SET @ID2=2583 DECLARE @ID3 Int; SET @ID3=164 SELECT [OSS_SHIP].[ship_seq] AS [ID], [OSS_SHIP].[ship_full_nm] AS [Name], [OSS_SHIP].[registration_authority_cd] AS [RegistrationAuthorityCode], [OSS_SHIP].[tug_nm] AS [TugName], [OSS_SHIP].[ship_interface_ind] AS [ShipboardInterface], [OSS_SHIP].[fleet_cd] AS [FleetCode], [LPA_M1].[FollowUpMessagesCount], [LPA_M1].[HasPriorityMessages] FROM ( (SELECT [OSS_DOCUMENT_TRANSACTION].[ship_seq] AS [ShipID], SUM(CAST(follow_up_ind AS INT)) AS [FollowUpMessagesCount], CONVERT(BIT, CASE WHEN SUM(CAST(priority_ind AS INT)) > 0 THEN 1 ELSE 0 END) AS [HasPriorityMessages] FROM ( [OSS_DOCUMENT]
INNER JOIN [OSS_DOCUMENT_TRANSACTION] ON [OSS_DOCUMENT].[doc_id]=[OSS_DOCUMENT_TRANSACTION].[doc_id]) WHERE ( ( [OSS_DOCUMENT_TRANSACTION].[voyage_seq] IS NULL AND [OSS_DOCUMENT_TRANSACTION].[allocated_ind] = @AllocatedFlag1)) GROUP BY [OSS_DOCUMENT_TRANSACTION].[ship_seq]) [LPA_M1]
RIGHT JOIN [OSS_SHIP] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ShipID]) WHERE ( ( ( [OSS_SHIP].[ship_seq] IN (@ID2, @ID3) OR [OSS_SHIP].[fleet_cd] IS NOT NULL))) ORDER BY [OSS_SHIP].[ship_full_nm] ASC

I can nearly get this by using string.Empty instead of Fred and not setting SelectListAlias which make the query clearer by removing the alias but still no Table name specified??

[] ON [OSS_SHIP].[ship_seq] = [LPA_M1].[ship_seq])

Cheers Simon

Please post the code which produces these queries.

The alias I was talking about was for the Table not the DerivedTable - the docs and especially the examples seem to imply that it is mandatory. i.e. neither of the examples return data from the derived tables yet they specify an alias for Orders and use SelectAliasList

In my scenario, data is being returned from both the Table and the DerivedTable whereas the docs seem to imply data must come from one or the other.

This change got the right derived table alias and allowed me to use the field alias as used in the derived table query DynamicRelation relation = new DynamicRelation(messagesDerivedTable, JoinHint.Right, EntityType.ShipEntity, string.Empty, ShipDFNFields.ID == ShipDFNFields.ID.SetObjectAlias(messagesDerivedTableAlias).SetFieldAlias("ShipID"));

Cheers Simon

PS After all of that the query is too slow to be usable anyway

simmotech wrote:

The alias I was talking about was for the Table not the DerivedTable - the docs and especially the examples seem to imply that it is mandatory. i.e. neither of the examples return data from the derived tables yet they specify an alias for Orders and use SelectAliasList

Ah! Yes, the alias is mandatory, as the logic to find back elements which are already in the list is then easier, and aliases specified by the developer are leading to easier code because we then don't have to fake aliases or reverse engineer the query and try to find predicate with target...

In my scenario, data is being returned from both the Table and the DerivedTable whereas the docs seem to imply data must come from one or the other.

Hmm.... That's an unusual scenario, i'd say. The thing is that an entity already has a target, i.e the table(s) it's mapped on, and the only situation where the target is a derived table, is when that original target is wrapped inside a derived table with extra logic (which is the case with linq stuff for example).

You can solve this btw, to wrap everything in yet another derived table and that sole derived table is then your target. Though it's a bit odd you'd want to fetch a part of an entity from a derived table, ignoring fields in a table which other fields are used to populate the entities.

PS After all of that the query is too slow to be usable anyway

Hmm... sorry to hear that. Derived tables are indeed slowing things down in the RDBMS, as they often lead to less optimal paths.

However, it might be something less optimal in your db as well. Sqlserver for example uses the tempdb for derived tables sometimes, could it be that the tempdb is small and resizing it on the fly takes a long time?

Otis wrote:

simmotech wrote:

The alias I was talking about was for the Table not the DerivedTable - the docs and especially the examples seem to imply that it is mandatory. i.e. neither of the examples return data from the derived tables yet they specify an alias for Orders and use SelectAliasList

Ah! Yes, the alias is mandatory, as the logic to find back elements which are already in the list is then easier, and aliases specified by the developer are leading to easier code because we then don't have to fake aliases or reverse engineer the query and try to find predicate with target...

Now I'm confused again My query is working without an alias.

In my scenario, data is being returned from both the Table and the DerivedTable whereas the docs seem to imply data must come from one or the other.

Hmm.... That's an unusual scenario, i'd say. The thing is that an entity already has a target, i.e the table(s) it's mapped on, and the only situation where the target is a derived table, is when that original target is wrapped inside a derived table with extra logic (which is the case with linq stuff for example).

You can solve this btw, to wrap everything in yet another derived table and that sole derived table is then your target. Though it's a bit odd you'd want to fetch a part of an entity from a derived table, ignoring fields in a table which other fields are used to populate the entities.

I suppose it is but actually I was using some of your blog's demo code to add some additional fields to an entity via its factory. ShipDFN is a small subset of fields from Ship to display in a TreeView but I also need to have two lots of summary information and a derived table is quicker than the two scalar querys I was using previously. I suppose I could also have used an Entity based on a View or a Dynamic List etc. but I thought this a good opportunity to learn how to use derived tables in code.

PS After all of that the query is too slow to be usable anyway

Hmm... sorry to hear that. Derived tables are indeed slowing things down in the RDBMS, as they often lead to less optimal paths.

However, it might be something less optimal in your db as well. Sqlserver for example uses the tempdb for derived tables sometimes, could it be that the tempdb is small and resizing it on the fly takes a long time?

Oops - forgot to uncomment a predicate to limit the date range. Now works more like I expected .

Thanks for the help.

Cheers Simon