Hi I have built a query with LLBLGen 2.5 to filter a m:n result.
The relation is
Tpub->TPubCart->CartDest you can see it as "One Publication (TPub) can be in many Sections (CartDest)"
The function to get the top 5 documents that are news and are in section 2 goes like this:
public static IList<TpubEntity> SelectTopSpecialSection(int a_LimitToTop)
{
DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection<TpubEntity> allItems =
new EntityCollection<TpubEntity>(new TpubEntityFactory());
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(TpubEntity.Relations.PubCartEntityUsingFkIdPub);
filter.Relations.Add(PubCartEntity.Relations.CartdestEntityUsingFkIdCartella);
//Filter on main entity
filter.PredicateExpression.Add(TpubFields.News == 1);
// filter on related entity
filter.PredicateExpression.Add(CartdestFields.IdCartella == 2);
//Sort descending
ISortExpression sorter = new SortExpression(TpubFields.DataPub | SortOperator.Descending);
adapter.FetchEntityCollection(allItems, filter, a_LimitToTop, sorter);
return allItems;
}
Now going to the SQLProfiler I see this query beeing executed:
exec sp_executesql N'SELECT [ermes1].[dbo].[TPub].[idPub] AS [IdPub], [ermes1].[dbo].[TPub].[pubblicatorelog] AS [Pubblicatorelog], [ermes1].[dbo].[TPub].[pubblicatorefull] AS [Pubblicatorefull], [ermes1].[dbo].[TPub].[responsabile] AS [Responsabile], [ermes1].[dbo].[TPub].[nom_file_orig] AS [NomFileOrig], [ermes1].[dbo].[TPub].[nom_file_new] AS [NomFileNew], [ermes1].[dbo].[TPub].[Origine], [ermes1].[dbo].[TPub].[fk_tipofile] AS [FkTipofile], [ermes1].[dbo].[TPub].[data_pub] AS [DataPub], [ermes1].[dbo].[TPub].[dat_val] AS [DatVal], [ermes1].[dbo].[TPub].[date_insert] AS [DateInsert], [ermes1].[dbo].[TPub].[titolo] AS [Titolo], [ermes1].[dbo].[TPub].[abstract] AS [Abstract], [ermes1].[dbo].[TPub].[contenuto] AS [Contenuto], [ermes1].[dbo].[TPub].[download] AS [Download], [ermes1].[dbo].[TPub].[News] FROM (( [ermes1].[dbo].[TPub] INNER JOIN [ermes1].[dbo].[PubCart] ON [ermes1].[dbo].[TPub].[idPub]=[ermes1].[dbo].[PubCart].[fk_id_Pub]) INNER JOIN [ermes1].[dbo].[cartdest] ON [ermes1].[dbo].[cartdest].[id_cartella]=[ermes1].[dbo].[PubCart].[fk_id_cartella]) WHERE ( ( [ermes1].[dbo].[TPub].[News] = @News1 AND [ermes1].[dbo].[cartdest].[id_cartella] = @IdCartella2)) ORDER BY [ermes1].[dbo].[TPub].[data_pub] DESC',N'@News1 int,@IdCartella2 int',@News1=1,@IdCartella2=2
So first question : Is this efficient? it seems to take long in duration. Is there a better way to solve the same problem?
Second question : Why there is no TOP statement in the select when I specified for example in the parameter I pass to the function the number to limit that (say 5)?
I would have expected something like:
SELECT TOP 5 [ermes1].[dbo].[TPub]....
Thanks
