mandag den 27. juni 2011

LINQ and GridView

I normally don’t use GridView a lot. but once in a while its nice to be able to fast show some data. if you assign the data source on a GridView a LINQ query it seems to work.

But if you add conditions to the query you will properly see the below error

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Well, Google and you’ll find a few solutions, the one I preferred where using linked and the apply AsExpandable. That solved one issue, but then you will get

The data source does not support server-side data paging

lastly, once this is done, you might get an error when doing the actually paging

The GridView 'GridView1' fired event PageIndexChanging which wasn't handled.

One way to handle this is to convert the result to a data table. a comment on this blog gave a simple way of doing that. So here is a short example of it all

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    Dim predicate = PredicateBuilder.False(Of ModelprovExchangeQueues.FseQuarantine)()
    For Each email In CurrentUser.emails
        Dim emailAddress As String = email.Email
        predicate = predicate.Or(Function(x) x.RecipientAddresses.Contains(emailAddress))
    Next
    Dim query = _db.FseQuarantine.AsExpandable().Where(predicate)
    Dim dt As Data.DataTable = query.ToDataTable

    GridView1.AllowPaging = True
    GridView1.DataSource = dt
End Sub

Protected Sub GridView1_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
    GridView1.PageIndex = e.NewPageIndex
    GridView1.DataBind()
End Sub