¡¡Êý¾Ý¿â·ÖÒ³ÊÇÀÏÉú³£Ì¸µÄÎÊÌâÁË¡£Èç¹ûʹÓÃORM¿ò¼Ü£¬ÔÙʹÓÃLINQµÄ»°£¬Ò»¸öSkipºÍTake¾Í¿ÉÒԸ㶨¡£µ«ÊÇÓÐʱÓÉÓÚÏÞÖÆ£¬ÐèҪʹÓô洢¹ý³ÌÀ´ÊµÊý¾Ý¿â·ÖÒ³ÊÇÀÏÉú³£Ì¸µÄÎÊÌâÁË¡£Èç¹ûʹÓÃORM¿ò¼Ü£¬ÔÙʹÓÃLINQµÄ»°£¬Ò»¸öSkipºÍTake¾Í¿ÉÒԸ㶨¡£µ«ÊÇÓÐʱÓÉÓÚÏÞÖÆ£¬ÐèҪʹÓô洢¹ý³ÌÀ´ÊµÏÖ¡£ÔÚSQLServerÖÐʹÓô洢¹ý³ÌʵÏÖ·ÖÒ³µÄÒѾÓкܶ෽·¨ÁË¡£Ö®Ç°ÔÚÃæÊÔÖÐÓöµ½¹ýÕâÒ»ÎÊÌ⣬ÎÊÈçºÎ¸ßЧʵÏÖÊý¾Ý¿â·ÖÒ³¡£¸ÕºÃÉÏÖÜÔÚÒµÎñÖÐÒ²Óöµ½ÁËÕâ¸öÐèÇó£¬ËùÒÔÔÚÕâÀï¼òµ¥¼Ç¼ºÍ·ÖÏíһϡ£
Ò» ÐèÇó
ÕâÀïÒÔSQLServerµÄʾÀýÊý¾Ý¿âNorthWindΪÀý£¬ÀïÃæÓÐÒ»ÕÅProduct±í£¬ÏÖÔÚ¼ÙÉèÎÒÃǵÄÐèÇóÊÇÒªÒÔUnitPrice½µÐòÅÅÁУ¬²¢ÇÒ·ÖÒ³£¬Ã¿Ò»Ò³10Ìõ¼Ç¼¡£ÒªÇó·þÎñ¶Ë·ÖÒ³¡£²ÎÊýΪÿҳ¼Ç¼ÊýºÍÒ³Âë¡£
¶þ ʵÏÖ
Top·ÖÒ³
µ±Ê±²ÉÓõÄ×îÖ±½Ó×ö·¨¾ÍÊÇʹÓÃÁ½¸öTopÀ´ÊµÏÖ£¬ ×îºó·µ»ØµÄ½á¹ûÊÇÉýÐòµÄ£¬ÔÚC#´úÂëÀïÔÙ´¦ÀíһϾͿÉÒÔÁË¡£
ÕâÀï×÷ΪÑÝʾ£¬Óï¾äÖÐʹÓà * ΪÁË·½±ã£¬Êµ¼Ê¿ª·¢ÖÐÒªÌæ»»Îª¾ßÌåµÄÁÐÃû¡£ÏÂÃæµÄ·½·¨¼òµ¥°É¡£
SELECT TOP (@pageSize) * FROM ( SELECT TOP ( @pageSize * @pageIndex ) * FROM [Northwind].[dbo].[Products] ORDER BY UnitPrice DESC ) AS product ORDER BY product.UnitPrice |
µ«ÊÇÕâ¸ö´úÂëÊÇÓÐÎÊÌâµÄ£¬²»ÖªµÀ¸÷λ·¢ÏÖÁËûÓС£µ±·ûºÏÌõ¼þµÄ¼Í¼¼¯Ð¡ÓÚÿҳ¼Ç¼Êýʱ£¬Ã»ÓÐÎÊÌ⣬µ«Êǵ±´óÓÚ¾ÍÓÐÎÊÌâÁË£¬±ÈÈ磬ÔÚʵÀýÊý¾Ý¿âÖÐProductsÖÐÓÐ
77 Ìõ¼Ç¼£¬µ±Ã¿Ò³10Ìõ¼Ç¼£¬µÚ8Ò³Ö»Ó¦¸Ã·µ»Ø7Ìõ¼Ç¼£¬µÚ9Ò³Ó¦¸Ã·µ»Ø¿Õ£¬µ«ÊÇʹÓÃÈçÉϵķ½·¨£¬Ã¿´Î¶¼»á·µ»Ø10Ìõ¼Ç¼¡£
ÑØÓÃÉÏÃæµÄ˼·£¬°Ñ´úÂëÐÞ¸ÄΪÁËÈçϲÉÓÃÈý²ãSelect£¬×îÄÚÒ»²ã²éѯËùÓмǼ֮ǰµÄÊý¾Ý£¬È»ºóµÚ¶þ²ãÑ¡ÔñTop
PageSize¸öËùÓÐNOT IN µÚÒ»²ãÊý¾ÝÖеÄÊý¾Ý¼´¿É£¬ÒòΪʹÓÃÁËNOT INËùÒÔ²»´æÔÚµÚÒ»ÖÖ·½·¨ÖеÄbug
SELECT * FROM dbo.Products WHERE ProductID IN ( SELECT TOP ( @pageSize ) ProductID FROM dbo.Products WHERE ProductID NOT IN ( SELECT TOP ( @pageSize * (@pageIndex-1) ) ProductID FROM dbo.Products ORDER BY UnitPrice DESC ) ORDER BY dbo.Products.UnitPrice DESC ) ORDER BY dbo.Products.UnitPrice ASC |
ʹÓÃROW_NUMBER º¯Êý·ÖÒ³
Æäʵ»¹ÓÐÒ»ÖÖ×î¼òµ¥×îÖ±½ÓµÄ˼·£¬ÄǾÍÊDzÉÓÃÁÙʱ±í£¬¼´ÔÚÄÚ´æÖд´½¨Ò»¸ö±í±äÁ¿£¬¸Ã±äÁ¿Öаüº¬Ò»¸ö×ÔÔöÁУ¬±í¹Ø¼ü×ÖÁУ¬È»ºó½«´ýÅÅÐòµÄ±í°´ÕÕÅÅÐòÌõ¼þºÍ¹æÔò²åÈëµ½ÕâÕűíÖУ¬È»ºó¾Í¿ÉÒÔ½«×ÔÔöÁÐ×÷ΪÐкÅʹÓÃÁË£¬ÔڱȽÏÔçµÄÈçSQLServer
2000ÖУ¬Ö»ÄÜÕâÑù×ö£¬µ«ÊǶÔÓÚ´óÊý¾ÝÁ¿µÄ¼Ç¼¼¯£¬ÐèÒª´´½¨µÄÁÙʱ±íÒ²±È½Ï´ó£¬Ð§ÂʱȽϵͣ¬ÕâÀï¾Í²»½éÉÜÁË¡£
ÔÚSQLServer2005ÖÐÒýÈëÁËROW_NUMBER() º¯Êý,ͨ¹ýÕâ¸öº¯Êý£¬¿ÉÒÔ¸ù¾Ý¸ø¶¨ºÃµÄÅÅÐò×ֶιæÔò£¬Éú³É¼Ç¼ÐòºÅ£¬Æä»ù±¾Ó÷¨Îª£º
SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum , ¡¡¡¡* ¡¡¡¡FROM dbo.Products |
ÕâÑù£¬½á¹û¼¯ÖеÚÒ»ÁоÍΪ rownum£¬´Ó1¿ªÊ¼°´²½³¤Îª1µÝÔö£¬ÕâÓеãÀàËÆ´Ó1¿ªÊ¼²½³¤Îª1µÄ×ÔÔö×ֶΡ£
ÕâÀïÐèÒªÌáһϵÄÊÇ£¬Õâ¸öÓï¾äÖи³ÖµµÄrownumÁв»ÄÜʹÓÃÔÚµ±Ç°µÄwhereÓï¾äÖУ¬Ò²²»¿ÉÒÔ°ÑÕû¸öROW_NUMBER()Óï¾ä·Åµ½whereÖÐ×÷ΪÌõ¼þ£¬ÏÂÃæÁ½ÖÖʹÓ÷½Ê½¶¼ÊÇ´íÎóµÄ¡£
SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum , * FROM dbo.Products WHERE rownum BETWEEN 1 AND 10 »áÌáʾ´íÎó£º Invalid column name 'rownum'. SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum , * FROM dbo.Products WHERE ( ROW_NUMBER() OVER (ORDER BY City) AS rown ) BETWEEN 1 AND 10 »áÌáʾ´íÎó£º Incorrect syntax near the keyword 'AS'. ÕýÈ·µÄ×ö·¨ÊÇ£¬°Ñ²éѯµÄ½á¹û×÷Ϊһ¸öÄÚ²éѯ£¬ÔÙÔÚÍâÃæÌ×ÉÏÒ»¸öÍâ²éѯÓï¾ä£º SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum , * FROM dbo.Products ) AS temp WHERE temp.rownum BETWEEN 1 AND 10 ÓÐÁËÒÔÉÏ»ù´¡Ö®ºó£¬ÎÒÃǾͿÉÒÔÀûÓÃROW_NUMBERÕâ¸öÌØÐÔÀ´½øÐÐÅÅÐòÁË¡£ SELECT * FROM ( SELECT TOP ( @pageSize * @pageIndex ) ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum , * FROM dbo.Products ) AS temp WHERE temp.rownum > ( @pageSize * ( @pageIndex - 1 ) ) ORDER BY temp.UnitPrice |
²ßÂԺܼòµ¥£¬Ê×ÏÈÎÒÃÇѡȡ°üº¬Òª²éÒ³µÄÊý¾Ý£¬È»ºóʹÓÃROW_NUMERº¯Êý½øÐбàºÅ£¬
È»ºóÔÚÍâ²éѯÖÐÖ¸¶¨rownum´óÓÚÒ³Æðʼ¼Ç¼¼´¿É¡£ÕâÖÖ·½Ê½¼òµ¥¿ì½Ý¡£
ÕâÀﻹÓÐÒ»ÖÖʹÓÃCTEµÄ·½Ê½ (common_table_expression£¬¹«Óñí±í´ïʽ£¬²»ÊÇCTEËÄÁù¼¶Å¶£¬
ÎÒµÚÒ»´Î½Ó´¥µ½Õâ¸öÊÇÃæÊÔµÄʱºò±»Îʵ½ÈçºÎʹÓÃSQL±àдµÝ¹é£¬ ºÇºÇ)£¬Ê¹Óúܼòµ¥£¬¾ÍÊǰÑÄÚ²éѯ·ÅÔÚCTE
ÀïÃæ£¬ÈçÏ£º
WITH ProductEntity AS ( SELECT TOP ( @pageSize * @pageIndex ) ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum , * FROM dbo.Products ) SELECT * FROM ProductEntity WHERE ProductEntity.rownum > ( @pageSize * ( @pageIndex - 1 ) ) ORDER BY ProductEntity.UnitPrice |
ÕâÖÖÐÔÄܺÍÉÏÃæµÄÀàËÆ¡£µ«ÊÇÔÚijЩÇé¿öÏ£¬ ʹÓÃCTE»á±ÈÖ±½Ó²ÉÓÃÍâ½Ó²éѯ¾ßÓиüºÃµÄЧÂÊ¡£ÀýÈ磬ÎÒÃÇ¿ÉÒÔ½öʹÓÃCTEÀ´´æ´¢Ðкţ¬¹Ø¼ü×ÖÒÔ¼°ÅÅÐò×ֶΣ¬È»ºóÓÃÀ´ºÍÔ±í×öjoin²éѯ£¬ÈçÏ£º
WITH ProductEntity AS ( SELECT TOP ( @pageSize * @pageIndex ) ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum , ProductID ,--Ö÷¼ü£¬ UnitPrice--´ýÅÅÐò×Ö¶Î FROM dbo.Products ) SELECT * FROM ProductEntity INNER JOIN dbo.Products ON dbo.Products.ProductID = ProductEntity.ProductID WHERE ProductEntity.rownum > ( @pageSize * ( @pageIndex - 1 ) ) ORDER BY ProductEntity.UnitPrice |
ʹÓÃROW_NUMBERÀ´½øÐзÖÒ³ÊÇÒ»ÖÖʹÓúܹãµÄ·ÖÒ³·½Ê½£¬ ÔÚ±¾ÎÄ¿ªÍ·½²µ½ÔÚLINQÖпÉÒÔ²ÉÓõÄTAKE
ºÍ SKIPÓï¾ä£¬µ«ÊÇÓëÊý¾Ý¿â½»»¥Ö»ÄÜʹÓÃSQLÓï¾ä£¬LINQÔÚÄÚ²¿»á°ïÎÒÃÇת»¯ÎªºÏÊʵÄSQLÓï¾ä£¬Óï¾äÀïÃæÆäʵҲÊDzÉÓÃROW_NUMBERÕâÒ»º¯Êý£¬ÎªÁËÑÝʾ£¬ÎÒÃÇн¨Ò»¸öConsole³ÌÐò£¬È»ºóÔÚÀïÃæÌí¼ÓÒ»¸öLINQ
To SQLµÄÀ࣬ʹÓ÷½·¨·Ç³£¼òµ¥£¬ÈçÏ£º
List<Product> product; int pageSize = 10; int pageIndex = 8; using (ProductsDataContext context = new ProductsDataContext()) { product = context.Products.OrderByDescending(x => x.UnitPrice)//ÅÅÐò .Skip(pageSize * (pageIndex-1))//Ìø¹ýÇ°ÃæµÄ¼Ç¼ .Take(pageSize)//ѡȡÿһҳ¸öÊý .ToList(); } |
ÁÈÁȼ¸¾ä¾ÍʵÏÖÁË·ÖÒ³¡£
ÎÒÃÇÖªµÀLINQÆäʵÊǽ«C#±í´ïʽÊ÷ת»»³ÉÁËSQLÓïÑÔ£¬Í¨¹ýSQLServer
Profile ¹¤¾ß£¬ÎÒÃÇ¿ÉÒÔ¿´µ½³ÌÐò·¢Ë͸øSQLServerµÄÇëÇó£¬ÈçÏ£º

Translate LINQ to SQL
ÎÒ°ÑÏÂÃæµÄÓï¾ä¿½±´³öÀ´£¬¿ÉÒÔ¿´µ½
EXEC sp_executesql N'SELECT [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID],
[t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock],
[t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[UnitPrice] DESC) AS [ROW_NUMBER], [t0].[ProductID],
[t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID],
[t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued] FROM [dbo].[Products] AS [t0] ) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 ORDER BY [t1].[ROW_NUMBER]', N'@p0 int,@p1 int', @p0 = 70, @p1 = 10 |
ʹÓÃOFFSET FETCH×Ó¾ä·ÖÒ³
¼ÈÈ»LINQÕâô¼òµ¥µÄ¸ã¶¨ÁË·ÖÒ³£¬ÄÇôSQLServerÖÐÓÐûÓÐÀàËÆµÄ¼òµ¥µÄÓï¾ä¾ÍÄܸ㶨·ÖÒ³ÁË£¬´ð°¸ÊÇÓеģ¬ÄǾÍÊÇSQL
Server Compact 4.0ÖÐÒýÈëµÄOFFSET FETCH×Ӿ䡣
SELECT * ¡¡¡¡FROM dbo.Products ¡¡¡¡ORDER BY UnitPrice DESC ¡¡¡¡OFFSET ( @pageSize * ( @pageIndex - 1 )) ROWS ¡¡¡¡FETCH NEXT @pageSize ROWS ONLY; |
ÊDz»ÊǺÍLINQºÜÏñ£¬OFFSEETÏ൱ÓÚSKIP£¬FETCH NEXTÏ൱ÓÚTAKE¡£
¿ÉÒÔÔÚ¹ÙÍøÉÏÏÂÔØSQL Server CE 4.0£¬Ä¿Ç°½öÖ§³ÖSQL
Server 2012¼°SQL Server 2014£¬²»¹ý¿ÉÒÔʹÓÃMicrosoft WebmatrixÕâ¸ö¹¤¾ßÀ´ÓÃÕâһй¦ÄÜ¡£
±È½Ï
ÔÚÌÖÂÛÐÔÄÜ֮ǰ£¬Ê×ÏÈÐèÒªÃ÷È·µÄÊÇ£¬ÎÒÃÇÔÚ±àдSQLÓï¾äµÄʱºò£¬¾¡Á¿Òª¼õÉÙ²»±ØÒª×ֶεÄÊä³ö£¬ÎÄÖгöÓÚÑÝʾ£¬ËùÒÔ¶¼ÓõÄ*£¬ÔÚʵ¼ÊÖв»ÒªÕâÑù¡£»¹ÓоÍÊÇÒª¸ù¾ÝÒµÎñÂß¼£¬±ÈÈç²éѯÌõ¼þ£¬½¨Á¢ºÏÊʵľۺÏË÷ÒýºÍ·Ç¾ÛºÏË÷Òý£¬Ë÷Òý¶ÔÓÚ²éÕÒµÄЧÂÊÓ°Ïì·Ç³£´ó£¬SQLÖеÄË÷ÒýÆäʵ¾ÍÊǽ¨Á¢Ä³ÖÖÆ½ºâ²éÕÒÊ÷£¬ÈçBÊ÷À´½øÐУ¬Õâ·½ÃæµÄ֪ʶ¿ÉÒÔ¿´ÎÒ֮ǰдµÄËã·¨ÖеÄÎÄÕ£¬ÔÙÓоÍÊÇÁ˽âÒ»ÏÂSQL
Server µÄÒ»Ð©ÌØÐÔ±ÈÈçCTE£¬IN ºÍExistµÄÇø±ðµÈµÈ£¬ÓÐЩСµÄµØ·½¶ÔÐÔÄÜ¿ÉÄÜÓÐÒ»¶¨µÄÓ°Ïì¡£
ÔÚÉÏÃæÕâЩ´¦ÀíºÃÁËÖ®ºó£¬ÎÒÃÇÏÖÔÚÀ´ÌÖÂÛÄÇÖÖ·ÖÒ³·½°¸¸üºÃ¡£
²ÉÓÃTop ¨C Not In - Top·½°¸±È½Ï¸´ÔÓ£¬ÀïÃæ°üº¬ÁËinÓï¾ä£¬Ð§Âʲ»¸ß£¬µ«ÊǼæÈݸö°æ±¾µÄSQL
Server¡£
²ÉÓÃROW_Number·½·¨ÊµÏÖ·ÖÒ³ÄÑÒ×ÊÊÖУ¬Ð§Âʽϸߡ£LINQÖеÄSKIPºÍTAKEÒ²ÊDzÉÓÃÕâÖÖ·½Ê½À´½øÐзÖÒ³µÄ£¬Ó¦¸ÃÊÇĿǰ²ÉÓõıȽϹ㷺µÄ·ÖÒ³·½Ê½¡£
FFSET FETCH ·½·¨ÊÇSQL Server CE 4.0 ÖвÅÒýÈëµÄ£¬ÓÉÓÚ±¾ÎÄûÓÐSQL
Server 2012ÒÔ¼°²âÊÔÊý¾Ý£¬´Ócomparing-performance-for-different-sql-server-paging-methodsÕâÆªÎÄÕÂÀ´ÒÔ¼°Ô°×ÓÀïµÄSql
Server 2012 ·ÖÒ³·½·¨·ÖÎö(offset and fetch)£¬ÐÔÄÜÓ¦¸ÃÊDZȽϺõġ£
ÒÔÉÏÊǶÔSQLServerÊý¾Ý¿âSQL·ÖÒ³µÄÒ»µã×ܽᣬϣÍû¶ÔÄúÓÐËù°ïÖú¡£
|