none
如何优化LINQ的OrderBy语句 RRS feed

  • 问题

  • 你好,我需要对一个数据表进行排序,然后取其前100项目。

    peopleMsgDatas =
                            entities.PeopleMsgDatas
                                .OrderBy(p => p.MsgId)
                                .Where(p => p.LastJobFlag == jobFlag)
                                
                                .Take(100)
                                .ToList();

    他生成的SQL是

    SELECT TOP (100) 
        [Project1].[MsgId] AS [MsgId], 
        [Project1].[PeopleFid] AS [PeopleFid], 
        [Project1].[PeopleCode] AS [PeopleCode], 
        [Project1].[PeopleName] AS [PeopleName], 
        [Project1].[Action] AS [Action], 
        [Project1].[Enable] AS [Enable], 
        [Project1].[LastJobFlag] AS [LastJobFlag], 
        [Project1].[HomeDeptFid] AS [HomeDeptFid]
        FROM ( SELECT 
            [Extent1].[MsgId] AS [MsgId], 
            [Extent1].[PeopleFid] AS [PeopleFid], 
            [Extent1].[PeopleCode] AS [PeopleCode], 
            [Extent1].[PeopleName] AS [PeopleName], 
            [Extent1].[Action] AS [Action], 
            [Extent1].[Enable] AS [Enable], 
            [Extent1].[LastJobFlag] AS [LastJobFlag], 
            [Extent1].[HomeDeptFid] AS [HomeDeptFid]
            FROM [dbo].[PeopleMsgData] AS [Extent1]
            WHERE [Extent1].[LastJobFlag] = @p__linq__0
        )  AS [Project1]
        ORDER BY [Project1].[MsgId] ASC
    我希望ORDER BY 应该在SELECT子查询里面。


    ...

    2018年6月19日 4:05

全部回复

  • 你好,

    目前Entity Framework 生成SQL的机制是放在外面,暂时没有办法通过LINQ的方法来把Order By 放到子查询里面去。你如果想把Order By 放到子查询的里面去,需要使用EF的原生SQL, 就是直接使用你优化后的SQL。或者使用存储过程来做。

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年6月19日 9:36
    版主
  • ORDER BY 子句在里面(子查询sub query)做会报错(T-SQL语言约定),您试下以下句子,是会报错。

    SELECT * FROM 
    ( SELECT  TaskNo FROM  dbo.TASK_Loading ORDER BY TaskNo ASC)

    Msg 1033, Level 15, State 1, Line 2
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2018年6月20日 0:31
  • 我在SQL方面不是特别擅长,这两个语句都能执行的。到那时执行结果是一样的。


    ...

    2018年6月20日 1:38
  • 你好,

    LINQ 现在得版本是不能转换成你要的SQL, 除非使用原生SQL.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年6月28日 9:38
    版主