Hi,

LLBL v2.6 I have the following query:

var searchResults = CodeEntity.GetAllAsQuery() .Where(ic => ic.Unno.Contains(searchText) || ic.Psn.Contains(searchText)) .Take(20) .OrderBy(i => i.Unno) .Select(ic => new CodeSearch { CodeID = ic.ImdgCodeId, UNNo = ic.Unno, PSN = ic.Psn, Variation = ic.Variation }) .ToList();

CodeEntity.GetAllAsQuery simply has: public static IQueryable<ImdgCodeEntity> GetAllAsQuery() { LinqMetaData meta = new LinqMetaData(); return from i in meta.Code select i; }

I would expect that the sql generated has a 'top 20' in it, but there is no 'TOP' emitted. In my results there are only 20 objects but running the SQL generated there are several hundred rows returned.

Am I using Take() incorrectly somehow?

Thanks, Ciaran.

Would you please post the genarted SQL.

It might be you ran into an old bug. Be sure to use the latest runtime lib builds. Also, 'take' is only really useful on the far outside of the query, so put Take, TakePage, Skip/Take at the outside of the query.

So my query should be more like: .Where(...) .OrderBy(...) .Select(...) .Take(...) .ToList(...)?

Should the OrderBy() be before or after the select?

I've tried putting the Take() after the select and the sql is still the same, where can I download the latest runtime assemblies?

Sql generated is: SELECT [LPA_L1].[ImdgCodeId] AS [ImdgCodeID], [LPA_L1].[Unno] AS [UNNumber], [LPA_L1].[Psn] AS [ProperShippingName], [LPA_L1].[Variation] FROM (SELECT [LPLA_1].[ImdgCodeID] AS [ImdgCodeId], [LPLA_1].[UNNO] AS [Unno], [LPLA_1].[VARIANT] AS [Variant], [LPLA_1].[VARIATION] AS [Variation], [LPLA_1].[CLASS] AS [Class], [LPLA_1].[SUBLABEL1] AS [Sublabel1], [LPLA_1].[SUBLABEL2] AS [Sublabel2], [LPLA_1].[PSN] AS [Psn], [LPLA_1].[PG] AS [Pg], [LPLA_1].[EMS] AS [Ems], [LPLA_1].[MP] AS [Mp], [LPLA_1].[FP] AS [Fp], [LPLA_1].[LQ] AS [Lq], [LPLA_1].[EQ] AS [Eq], [LPLA_1].[TECHNAME] AS [Techname], [LPLA_1].[TREATAS] AS [Treatas], [LPLA_1].[DGLPHRASE] AS [Dglphrase], [LPLA_1].[SPECPROV] AS [Specprov], [LPLA_1].[PACKINS] AS [Packins], [LPLA_1].[PACKPROV] AS [Packprov], [LPLA_1].[IBCINS] AS [Ibcins], [LPLA_1].[IBCPPROV] AS [Ibcpprov], [LPLA_1].[UNTANKINS] AS [Untankins], [LPLA_1].[TANKPROV] AS [Tankprov], [LPLA_1].[STOWCAT] AS [Stowcat], [LPLA_1].[EXPLIM] AS [Explim], [LPLA_1].[ULINEEMS] AS [Ulineems] FROM [SRCL_WasteTrak].[dbo].[ImdgCode] [LPLA_1] ) [LPA_L1] WHERE ( ( ( ( ( ( ( [LPA_L1].[Unno] LIKE @Unno1) OR ( [LPA_L1].[Psn] LIKE @Psn2))))))) ORDER BY [LPA_L1].[Unno] ASC',N'@Unno1 nvarchar(3),@Psn2 nvarchar(3)',@Unno1=N'%2%',@Psn2=N'%2%'

Thanks, Ciaran

The secure section of our website available to customers, has a download page. Select the version from the left menu to see the available downloads, including the latest build.

Hi Walaa,

I have downloaded the lastest assemblies and am using them now, but the sql emitted still does not have a 'TOP' in it. Again, my results have the correct number of items, but these appear to be getting 'trimmed' after the data has been returned.

My code is: var searchResults = ImdgCodeEntity.GetAllAsQuery() .Where(ic => ic.Unno.Contains(searchText) || ic.Psn.Contains(searchText)) .OrderBy(i => i.Unno) .Take(Settings.Default.GridItemsToReturn) // is 100 .Select(ic => new ImdgSearch { ImdgCodeID = ic.ImdgCodeId, UNNumber = ic.Unno, ProperShippingName = ic.Psn, Variation = ic.Variation }) .ToList();

The resulting SQL is: exec sp_executesql N'SELECT [LPA_L1].[ImdgCodeId] AS [ImdgCodeID], [LPA_L1].[Unno] AS [UNNumber], [LPA_L1].[Psn] AS [ProperShippingName], [LPA_L1].[Variation] FROM (SELECT [LPLA_1].[ImdgCodeID] AS [ImdgCodeId], [LPLA_1].[UNNO] AS [Unno], [LPLA_1].[VARIANT] AS [Variant], [LPLA_1].[VARIATION] AS [Variation], [LPLA_1].[CLASS] AS [Class], [LPLA_1].[SUBLABEL1] AS [Sublabel1], [LPLA_1].[SUBLABEL2] AS [Sublabel2], [LPLA_1].[PSN] AS [Psn], [LPLA_1].[PG] AS [Pg], [LPLA_1].[EMS] AS [Ems], [LPLA_1].[MP] AS [Mp], [LPLA_1].[FP] AS [Fp], [LPLA_1].[LQ] AS [Lq], [LPLA_1].[EQ] AS [Eq], [LPLA_1].[TECHNAME] AS [Techname], [LPLA_1].[TREATAS] AS [Treatas], [LPLA_1].[DGLPHRASE] AS [Dglphrase], [LPLA_1].[SPECPROV] AS [Specprov], [LPLA_1].[PACKINS] AS [Packins], [LPLA_1].[PACKPROV] AS [Packprov], [LPLA_1].[IBCINS] AS [Ibcins], [LPLA_1].[IBCPPROV] AS [Ibcpprov], [LPLA_1].[UNTANKINS] AS [Untankins], [LPLA_1].[TANKPROV] AS [Tankprov], [LPLA_1].[STOWCAT] AS [Stowcat], [LPLA_1].[EXPLIM] AS [Explim], [LPLA_1].[ULINEEMS] AS [Ulineems] FROM [SRCL_WasteTrak].[dbo].[ImdgCode] [LPLA_1] ) [LPA_L1] WHERE ( ( ( ( ( ( ( [LPA_L1].[Unno] LIKE @Unno1) OR ( [LPA_L1].[Psn] LIKE @Psn2))))))) ORDER BY [LPA_L1].[Unno] ASC',N'@Unno1 nvarchar(3),@Psn2 nvarchar(3)',@Unno1=N'%2%',@Psn2=N'%2%'

Could you run a similar statement and check if a 'top' is emitted?

Thanks, Ciaran.

This might be the cause (check this thread): http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14137

Well I knew that my search results was containing the correct amount of results, but I thought that perhaps the engine was bringing all the results back and then limiting results in-memory.

If the underlying DataReader is closing after 'Take(N)' rows, then that's fine by me

Thanks, Ciaran