Linq(实体框架)与SQL在动态数据分页方面的对比

我一直在考虑使用实体框架从SQL TableAdapters切换到Linq，但有些元素让我停下了脚步，同时也让我抓狂。

我有一个ASP.NET 4.0 C#VS2010项目网站。

我的要求如下：

• 数据必须可分页
• 数据必须可动态排序（即ORDER BY SelectedColumnName ASC/DESC）
• 数据必须可根据web控件、文本框、下拉列表等进行筛选。同样，是动态的
• 返回的列必须是可动态构造的
• 使用大桌子时必须尽可能做出反应。寻呼对此有帮助
• 必须在前端显示记录信息。即"显示记录1-20/100"第5页，共20页"等。
  • 项目是一个内部可访问的网站。不在互联网上或任何地方

基于我的需求，我开始使用System.Linq.Dynamic命名空间来研究DynamicLinq。我取得了一些成功，但在很大程度上我的问题变得过于复杂。

对于我的需求，使用Linq和实体框架与标准SQL相比有什么真正的理由吗？

据我所知，标准SQL通过一种我可以理解和控制的查询语言为我提供了所需的所有控制。Linq是一种我并不太熟悉的语言，它不允许我像需要的那样开箱即用地让所有元素都充满活力

返回使用SQL存储的进程/表适配器进行查询是否错误？

我从Linq的实验中学到的一件事是，通过codeehind控制属性可以更好地控制信息，所以我可以使用这些数据来解析存储的proc，以便将数据输入到网格视图中。

这是我使用的Linq表达式的一个例子：

private void FetchData()
{
    using (var Context = new ProjectEntities())
    {
        string Fields = GetDynamicFields();
        var Query =
            Context.Users
            .Join(Context.UserStats,            // Table to Join
                u => u.msExchMailboxGuid,       // Column to Join From
                us => us.MailboxGuid,           // Column to Join To
                (u, us) => new                  // Declare Columns for the next Join
                {
                    ObjectGuid = u.objectGuid,
                    msExchMailboxGuid = u.msExchMailboxGuid,
                    CompanyName = u.CompanyName,
                    ResellerOU = u.ResellerOU,
                    DisplayName = u.DisplayName,
                    MBXServer = u.MBXServer,
                    MBXSG = u.MBXSG,
                    MBXDB = u.MBXDB,
                    MBXWarningLimit = u.MBXWarningLimit,
                    MBXSendLimit = u.MBXSendLimit,
                    MBXSendReceiveLimit = u.MBXSendReceiveLimit,
                    extensionAttribute10 = u.extensionAttribute10,
                    legacyExchangeDN = u.legacyExchangeDN,
                    UserPrincipalName = u.UserPrincipalName,
                    Mail = u.Mail,
                    lastLogonTimeStamp = u.lastLogonTimestamp,
                    createTimeStamp = u.createTimeStamp,
                    modifyTimeStamp = u.modifyTimeStamp,
                    altRecipient = u.altRecipient,
                    altRecipientBL = u.altRecipientBL,
                    DeletedDate = u.DeletedDate,
                    MailboxGuid = us.MailboxGuid,
                    Date = us.Date,
                    AssociatedItemCount = us.AssociatedItemCount,
                    DeletedItemCount = us.DeletedItemCount,
                    ItemCount = us.ItemCount,
                    LastLoggedOnUserAccount = us.LastLoggedOnUserAccount,
                    LastLogonTime = us.LastLogonTime,
                    StorageLimitStatus = us.StorageLimitStatus,
                    TotalDeletedItemSize = us.TotalDeletedItemSize,
                    TotalItemSize = us.TotalItemSize,
                    MailboxDatabase = us.MailboxDatabase
                })
            .Join(Context.TechContacts,         // Table to Join
                u => u.UserPrincipalName,       // Column to Join From
                tc => tc.UPN,                   // Column to Join To
                (u, tc) => new                  // Declare Final Column Names
                {
                    ObjectGuid = u.ObjectGuid,
                    msExchMailboxGuid = u.msExchMailboxGuid,
                    CompanyName = u.CompanyName,
                    ResellerOU = u.ResellerOU,
                    DisplayName = u.DisplayName,
                    MBXServer = u.MBXServer,
                    MBXSG = u.MBXSG,
                    MBXDB = u.MBXDB,
                    MBXWarningLimit = u.MBXWarningLimit,
                    MBXSendLimit = u.MBXSendLimit,
                    MBXSendReceiveLimit = u.MBXSendReceiveLimit,
                    extensionAttribute10 = u.extensionAttribute10,
                    legacyExchangeDN = u.legacyExchangeDN,
                    UserPrincipalName = u.UserPrincipalName,
                    Mail = u.Mail,
                    lastLogonTimeStamp = u.lastLogonTimeStamp,
                    createTimeStamp = u.createTimeStamp,
                    modifyTimeStamp = u.modifyTimeStamp,
                    altRecipient = u.altRecipient,
                    altRecipientBL = u.altRecipientBL,
                    DeletedDate = u.DeletedDate,
                    MailboxGuid = u.MailboxGuid,
                    Date = u.Date,
                    AssociatedItemCount = u.AssociatedItemCount,
                    DeletedItemCount = u.DeletedItemCount,
                    ItemCount = u.ItemCount,
                    LastLoggedOnUserAccount = u.LastLoggedOnUserAccount,
                    LastLogonTime = u.LastLogonTime,
                    StorageLimitStatus = u.StorageLimitStatus,
                    TotalDeletedItemSize = u.TotalDeletedItemSize,
                    TotalItemSize = u.TotalItemSize,
                    MailboxDatabase = u.MailboxDatabase,
                    // New Columns from this join
                    UPN = tc.UPN,
                    Customer_TechContact = tc.Customer_TechContact,
                    Customer_TechContactEmail = tc.Customer_TechContactEmail,
                    Reseller_TechContact = tc.Reseller_TechContact,
                    Reseller_TechContactEmail = tc.Reseller_TechContact,
                    Reseller_Name = tc.Reseller_Name
                })
            .Where(u => true)
            .OrderBy(GlobalVars.SortColumn + " " + GlobalVars.SortDirection)
            .Select("New(" + Fields + ")");
        // Add Extra Filters
        if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text)))
        {
            Query = Query.Where("CompanyName.StartsWith(@0)", SearchCompanyNameTextBox.Text);
        }
        // Set the Record Count
        GlobalVars.TotalRecords = Query.Count();
        // Add Paging
        Query = Query
            .Skip(GlobalVars.Skip)
            .Take(GlobalVars.Take);
        // GridView Datasource Binding
        GridViewMailboxes.DataSource = Query;
        GridViewMailboxes.DataBind();
    }
}

这是通过SQL查询实现相同功能的一个示例：

DECLARE @SQLSTATEMENT NVARCHAR(4000);
DECLARE @FieldList varchar(MAX);
DECLARE @OrderBy varchar(100);
DECLARE @OrderDirection varchar(100);
DECLARE @PageSize int;
DECLARE @StartRow int;
SET @FieldList = 'u.UserPrincipalName, u.Mail, us.TotalItemsize, tc.UPN';
SET @OrderBy = 'u.CompanyName';
SET @OrderDirection = 'ASC';
SET @PageSize = 20;
SET @StartRow = 80;
SET @SQLSTATEMENT = '
SELECT TOP(@PageSize) * FROM
(
SELECT ' + @FieldList + ' 
,row_number() OVER (ORDER BY @OrderBy ' + @OrderDirection + ') AS [row_number]
FROM Users As u
INNER JOIN UserStats as us
ON u.msExchMailboxGuid = us.MailboxGuid
INNER JOIN TechContacts AS tc
ON tc.UPN = u.UserPrincipalName
) AS r
WHERE r.[row_number] > @StartRow '
EXEC sp_executesql @SQLSTATEMENT,
N'@FieldList varchar(MAX), @OrderBy varchar(100), @OrderDirection varchar(100), @PageSize int, @StartRow int',
@FieldList, @OrderBy, @OrderDirection, @PageSize, @StartRow

我很感激这是一个悬而未决的问题，如果我能说得更清楚，我当然会的。然而，考虑到我的需求，我很难看到在我的情况下使用Linq的好处。Linq似乎不太擅长编写完全动态的查询，而且可能必须在任何地方执行SQL（因此SQL更快？）。

我已经构建了前端控件，这些控件将我需要的所有内容解析为全局变量，然后我可以用来调用数据：

    public class GlobalVars
    {
        public static int TotalRecords = 0;
        public static int TotalPages = 0;
        public static int CurrentPage = 0;
        public static int LowerPage = 0;
        public static int UpperPage = 0;
        public static int Take = 0;
        public static int Skip = 0;
        public static string SortColumn = "CompanyName";
        public static string SortDirection = "Ascending";
    }