首頁>Program>source

只是對"跳過和获取"應该如何工作感到好奇.我正在获取要在客戶端看到的結果,但是当我掛接AnjLab SQL Profiler並查看正在執行的SQL時,它看起来好像是在查詢並將整个行集返迴给 客戶。

是真的返迴所有行,然後在客戶端使用LINQ排序並縮小範圍吗?

我已经尝試使用Entity Framework和Linq to SQL来實現; 两者似乎具有相同的行為。

不確定這有什麼區別,但是我在VwD 2010中使用C#。

有见識吗?

public IEnumerable<Store> ListStores(Func<Store, string> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    var context = new TectonicEntities();
    totalRecords = context.Stores.Count();
    int skipRows = (page - 1) * pageSize;
    if (desc)
        return context.Stores.OrderByDescending(sort).Skip(skipRows).Take(pageSize).ToList();
    return context.Stores.OrderBy(sort).Skip(skipRows).Take(pageSize).ToList();
}

产生的SQL(註意:我不包括Count查詢):

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[LegalName] AS [LegalName], 
[Extent1].[YearEstablished] AS [YearEstablished], 
[Extent1].[DiskPath] AS [DiskPath], 
[Extent1].[URL] AS [URL], 
[Extent1].[SecureURL] AS [SecureURL], 
[Extent1].[UseSSL] AS [UseSSL]
FROM [dbo].[tec_Stores] AS [Extent1]

经過进一步的研究,我發現以下功能可以達到我期望的方式:

public IEnumerable<Store> ListStores(Func<Store, string> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    var context = new TectonicEntities();
    totalRecords = context.Stores.Count();
    int skipRows = (page - 1) * pageSize;           
    var qry = from s in context.Stores orderby s.Name ascending select s;
    return qry.Skip(skipRows).Take(pageSize);           
}

产生的SQL:

SELECT TOP (3) 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[LegalName] AS [LegalName], 
[Extent1].[YearEstablished] AS [YearEstablished], 
[Extent1].[DiskPath] AS [DiskPath], 
[Extent1].[URL] AS [URL], 
[Extent1].[SecureURL] AS [SecureURL], 
[Extent1].[UseSSL] AS [UseSSL]
FROM ( SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[LegalName] AS [LegalName], [Extent1].[YearEstablished] AS [YearEstablished], [Extent1].[DiskPath] AS [DiskPath], [Extent1].[URL] AS [URL], [Extent1].[SecureURL] AS [SecureURL], [Extent1].[UseSSL] AS [UseSSL], row_number() OVER (ORDER BY [Extent1].[Name] ASC) AS [row_number]
    FROM [dbo].[tec_Stores] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 3
ORDER BY [Extent1].[Name] ASC

我真的很類似第一个選項的工作方式; 傳遞lambda表達式进行排序.有什麼方法可以在LINQ to SQL orderby語法中完成相同的工作吗? 我尝試使用qry.OrderBy(sort).Skip(skipRows).Take(pageSize),但這最终给了我与第一段代碼相同的結果.使我相信我的問题在某種程度上与OrderBy有關。

==================================

解決的問题

必须將傳入的lambda函式包裝在Expression中:

Expression<Func<Store,string>> sort
最新回復
  • 5月前
    1 #

    以下作品可以實現我一直在尋找的簡單性:

    public IEnumerable<Store> ListStores(Expression<Func<Store, string>> sort, bool desc, int page, int pageSize, out int totalRecords)
    {
        List<Store> stores = new List<Store>();
        using (var context = new TectonicEntities())
        {
            totalRecords = context.Stores.Count();
            int skipRows = (page - 1) * pageSize;
            if (desc)
                stores = context.Stores.OrderByDescending(sort).Skip(skipRows).Take(pageSize).ToList();
            else
                stores = context.Stores.OrderBy(sort).Skip(skipRows).Take(pageSize).ToList();
        }
        return stores;
    }
    

    為我修複的主要問题是將Func排序引數更改為:

    Expression<Func<Store, string>> sort
    

  • 5月前
    2 #

    只要您不像 queryable.ToList().Skip(5).Take(10)那樣做 ,它不会返迴整个記錄集。

    Take

    只做 Take(10).ToList() ,做一个 SELECT TOP 10 * FROM .

    Skip

    跳過的工作方式略有不同,因為TSQL中没有" LIMIT"功能.但是,它將基於此ScottGu博客文章中描述的工作建立一个SQL查詢。

    如果您看到返迴了整个記錄集,則可能是因為您正在執行 ToList()

  • 5月前
    3 #

    此處的Entity Framework 6解決方案...

    http://anthonychu.ca/post/entity-framework-parameterize-skip-take-queries-sql/

    例如

    using System.Data.Entity;
    ....
    int skip = 5;
    int take = 10;
    myQuery.Skip(() => skip).Take(() => take);
    

  • 5月前
    4 #

    我建立了簡單的擴充套件名:

    public static IEnumerable<T> SelectPage<T, T2>(this IEnumerable<T> list, Func<T, T2> sortFunc, bool isDescending, int index, int length)
    {
        List<T> result = null;
        if (isDescending)
            result = list.OrderByDescending(sortFunc).Skip(index).Take(length).ToList();
        else
            result = list.OrderBy(sortFunc).Skip(index).Take(length).ToList();
        return result;
    }
    

    簡單易用:

    using (var context = new TransportContext())
    {
        var drivers = (from x in context.Drivers where x.TransportId == trasnportId select x).SelectPage(x => x.Id, false, index, length).ToList();
    }
    

  • 5月前
    5 #

    尝試一下:

    public IEnumerable<Store> ListStores(Func<Store, string> sort, bool desc, int page, int pageSize, out int totalRecords)
    {
        var context = new TectonicEntities();
        var results = context.Stores;
        totalRecords = results.Count();
        int skipRows = (page - 1) * pageSize;
        if (desc)
            results = results.OrderByDescending(sort);
        return results.Skip(skipRows).Take(pageSize).ToList();
    }
    

    實際上,最後一个.ToList()實際上不是必需的,因為您將返迴IEnumerable ...

    將有2个資料庫呼叫,一个用於計數,一个在執行ToList()時进行。

  • algorithm:QuickSort最壞的情况-什麼時候会發生?
  • ftp:如何停止Filezilla更改換行符?