Entity Framework使用原生sql查询，然后用PagedList分页 - 静夜沉思

作者在 2016-11-22 07:14:07 发布以下内容

因为要用到left join进行多表联合查询，使用LINQ的话及其复杂，不如干脆用原生sql了，使用原生sql查询可以很好的与PagedList分页插件结合。

Imports PagedList

Public Class vUser
    Public Property Username As String
    Public Property Password As String
    Public Property GroupName As String
End Class

Public Class HomeController
    Inherits BaseController

    Function Index(ByVal page As Integer?) As ActionResult
        Dim pageNumber = If(page, 1)
        Dim perpage = 2
        Dim user_count = db.Database.SqlQuery(Of Integer)("select count(*) from Users").First()
        pageNumber = Math.Max(pageNumber, 1)
        pageNumber = Math.Min(pageNumber, CInt(Math.Ceiling(user_count / perpage)))

        Dim users = db.Database.SqlQuery(Of vUser)("select u.*,g.Name as GroupName from Users u left join UserGroups g on g.ID=u.UserGroup_ID ORDER BY u.ID DESC OFFSET " + ((pageNumber - 1) * perpage).ToString + " ROWS FETCH NEXT " + perpage.ToString + " ROWS ONLY").ToList()

        Dim users1 = New StaticPagedList(Of vUser)(users, pageNumber, perpage, user_count)
        Return View(users1)
    End Function
End Class