Using LinqDataSource with SPGridView

If you have, like me, struggled with the SPGridView and the ObjectDataSource when doing filtering, sorting, and paging you will find this post extremely helpful.

I have set out to make the LinqDataSource work with the SPGridView against SharePoint to support paging, sorting and filtering out of the box (as much as possible).
In order to make use of the LinqDataSource you have to make a couple of changes to your web.config file to make your enviroment support .NET 3.5 (and of course you have to install .NET runtime 3.5)

.NET 3.5 feature can be downloaded from here.

So, for this demo I´ll add a simple contacts list and add some values to it:
image

Then, I´m using an aspx page with an update panel (better UI support), a linq datasource, and finally a SPGridView.
Let´s go.

The markup


<asp:LinqDataSource runat="server" ID="LinqDS" OnSelecting="LinqDS_Selecting" />


<div style="width:50%">
<asp:UpdatePanel runat="server" UpdateMode="Conditional">
<ContentTemplate>
<SharePoint:SPGridView runat="server" ID="ContactsGridView"
AutoGenerateColums="false"
FilterDataFields="fname, lname, country"
FilteredDataSourcePropertyName="Where"
FilteredDataSourcePropertyFormat='{1} == "{0}"'
PageSize="5"
AllowPaging="true"
AllowFiltering="true"
AllowSorting="true"
DataSourceID="LinqDS">

<Columns>
<asp:BoundField HeaderText="Firstname"
DataField="fname"
SortExpression="fname" />
<asp:BoundField HeaderText="Lastname"
DataField="lname"
SortExpression="lname" />
<asp:BoundField HeaderText="Country"
DataField="country"
SortExpression="country" />
</Columns>

</SharePoint:SPGridView>
</ContentTemplate>
</asp:UpdatePanel>
</div>

Some things to note here:

Notice the OnSelecting on the data source. I´m gonna populate the data source from that method in the codebehind.
The FilteredDataSourcePropertyName=”Where” tells the SPGridView to use the Where property on the data source when filtering.
The FilteredDataSourcePropertyFormat='{1} == “{0}”‘ sets up the Where clause like where country == “USA” for example.

So, lets see the codebehind.

The code


protected void Page_Load(object sender, EventArgs e)
{
ContactsGridView.PagerTemplate = null;
}

protected void LinqDS_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
IEnumerable<SPListItem> contacts = GetDataFromContactsList();

e.Result = from contact in contacts
select new
{
fname = contact["FirstName"].ToString(),
lname = contact.Title,
country = contact["WorkCountry"].ToString()
};
}

private IEnumerable<SPListItem> GetDataFromContactsList()
{
using (SPSite site = new SPSite("http://developer))
using (SPWeb web = site.OpenWeb())
{
var items = web.Lists["Contacts"].Items.OfType<SPListItem>();

return items;

}
}

As you might notice there´s not a lot of code (most of is really SharePoint data access code).
In the selecting event handler I´m just making an anonymous type from the SPListItems in the contacts list.
Also notice that you have to set the PageTemplate to null on the SPGridView.

So, does it work?

Paging

image

Just works…

Sorting by the lastname column

image

Like a charm…

Filtering by the country column

image

Yeah…this is great!!

It all just works. This is fantastic.

Hope this helps someone out there. I will never use anything other than linq in the future, that´s for sure.

, ,

  1. #1 by Sebastiaan Brozius on September 7, 2009 - 16:32

    Hi,

    Great article! I have one question to which I hope you can provide the answer.
    I try to do this, inside a WebPart, only not connecting to a SharePoint-list, but to a custom SQL-table, which I connect to using the LinqDataSource.
    The data show real fine in the SPGridView, bu t when I try to select a filter-value, the list of values keeps giving me the “Loading”-status…

    Could you perhaps give me a pointer as to where to look?

    Regards,
    Sebastiaan

    • #2 by Johan Leino on September 7, 2009 - 20:06

      Hi Sebastian,

      I did a small test on my dev box with some code going against a northwind database. I got it working without doing anything special so I can´t really give you any pointers without looking at some code.
      BTW, what is the reason why you don´t use LinqToSql for this, that is what the LinqDataSource is really perfect for?

      • #3 by Sebastiaan Brozius on September 7, 2009 - 23:07

        Hi Johan,

        I do use a LinqDataSource, which makes use of a DataContext (LinqToSql-thingy).

        I cannot get the ‘filter’-list in the webpart to show me anything other than sort ascending/descending and a grayed out ‘clear filter’ and ‘loading…’.

        I’m at home right now, but will try tomorrow at work with a very simple new project.
        I did not use the GetDataFromContactList-method, because I use the LinqDataSource… I did use the selecting-methd (handling the selecting-event)…

        As far as I can tell with my limited knowledge, it should work as you’ve demonstrated.

        Kind regards

      • #4 by Sebastiaan Brozius on September 8, 2009 - 10:51

        Hi Johan,

        Just tested some things. First a new webpart, that I put on a single page, which worked.
        Then I added/checked the needed things to an existing webpart I had been fooling around with yesterday, and no luck… That webpart was on a page where there were more custom webparts with SPGridViews. After removing these from the page, it works.
        However, I am curious how to get this to work with more than one custom webpart on the page.

        Kind regards

      • #5 by Sebastiaan Brozius on September 8, 2009 - 12:04

        Hi Johan,

        Reporting back again😉
        I got it to work….
        Since I’m creating webparts… I must not forget to give every object an ID…
        And when using a webpart with a linqdatasource and a webpart which uses the dataclass as datasource, filtering goes awry.

        So, everything works…
        Now for the images when a column is filtered….

        Kind regards

      • #6 by Sebastiaan Brozius on September 8, 2009 - 13:33

        Hi Johan,

        One more question…
        What happens when you Filter a column, and then sort that column?
        When I do this, the filter gets lost ?

        Kind regards

      • #7 by Sebastiaan Brozius on September 8, 2009 - 16:28

        And I got that last one sorted out as well.

        In the SPGridView DataBound-event, re-apply the where-clause of the filter.

        if (string.IsNullOrEmpty(ContactsGridView.FilterFieldName) == false)
        {
        LinqDS.Where = string.Format("{0} == \"{1}\"", ContactsGridView.FilterFieldName, ContactsGridView.FilterFieldValue);
        }

        Now my next challenge is to get this all working with an entity which has child-tables and where I want to include these (with ‘translated’ IDs) in my SPGridView and even DetailsView….

        Any pointers??😉

        Kind regards

      • #8 by Johan Leino on September 8, 2009 - 19:29

        Hey,

        man you are fast. Here I´ve been away working and you have updated your own questions…which is great so that other people could see your progress. I don´t really understand your last question (what do you mean with translated IDs). I may have some pointers if I knew what you are trying to accomplish.

      • #9 by Sebastiaan Brozius on September 9, 2009 - 08:29

        Hi,

        Suppose I’ve got a table (Parent) with a relation to another table (Child) in it.
        In my SPGridView I want to show properties of one of the records of the related table, but I do not want to use another LinqDataSource; since it is attached to the record in Parent, I would think that through that record, properties of an attached record from Child should be displayable in the SPGridView as well.

        Suppose the Parent-table stores products, and the Child-table Suppliers. I want to link to the supplier by it’s record ID, so I do not have to store an (almost) complete supplier-record inside the product-record. Somehow, I think I should be able to show the supplier name, a property of the related record based on the ID, instead of it’s ID, AND be able to sort/filter on it.

        Oh, and one more thing… The FilteredDataSourcePropertyFormat-string should be “{1} == \”{0}\””.
        Somehow Name and Value get switched when offered to the FiteredDataSourcePropertyName…. (Try it with a value that has a space in it….)

        Kind regards

      • #10 by Johan Leino on September 9, 2009 - 10:23

        Hello,

        ok so now I know what you are after. Thanks also for the input on FilteredDataSourcePropertyFormat, I had a feeling something was wrong with that one because I got some strange behaviour when trying this out with Northwinf customers (the filtering worked sometimes and sometimes not).

        So, I suppose you are doing some sort of join between the parent table and the child. Not really sure if this will work with LinqToSql but I have tried this with “normal” entities (Like Customer.Address.Street). My experience is that it works with anonymous types and the dot notation inside the markup. Otherwise you wanna call some function at databinding time to fetch the data you want, something like this in markup:


        Text='<% # GetChildTableData(Container.DataItem) % >'

        I don´t know if this will help you…

  2. #11 by Sebastiaan Brozius on September 9, 2009 - 12:18

    Hi,

    I don’t do any join, at least, not explicitly.
    When you have an entity which relates to another entity, in Linq, the child-entity is present in the parent-entity. So yes, you can then retrieve the property of the related child-entity through the dot-notation.

    However, this does not seem to work with this method, unfortunately. I think because the LinqDataSource only has knowledge of a single table in the datacontext.

    But I’ll try some things out and keep you updated😉

    • #12 by Sebastiaan Brozius on September 10, 2009 - 09:58

      Hi,

      I can get the value from the child-record to show by using the selecting-event of the LinqDataSource and using, for example:
      e.Result = var record from Parent
      select new {
      ParentID = record.ParentID,
      Name = Parent.Name,
      ChildProperty = Child.Property,
      ...
      }

      Somehow, however, this screws up the filtering-part. Even when I disallow filtering on the columns from the child-record, the SPGridView only gives me “Loading…” in the WebPart for the filter.

      At the moment, I’ve also added a property in my DataContext-code which returns the child-property (effectively: public string ChildProperty() { get {return this.Chils.Property; } }). This doesn’t do the trick, unfortunately…

    • #13 by Sebastiaan Brozius on September 10, 2009 - 14:49

      Both methods seem to work. The problem with the “Loading…”-bit seems to be that I had e “this.Page.Repsonse.Write”-thingy on the page. After removing/disabling that, the Filter-list shows up, but when now selecting one of the values, I get an error that the property has no translation to SQL…
      The member ‘Parent.ChildProperty’ has no supported translation to SQL.

      Back to the drawingnoard… again…

      • #14 by Johan Leino on September 10, 2009 - 15:13

        Hi Sebastian,

        Great that you have started your own little investigation of this (saw your blog post. Keep up the good work!

      • #15 by Sebastiaan Brozius on September 14, 2009 - 13:15

        Small update.

        I cannot, so far, get the correct value/displayfield to show as filter-value AND be able to correctly filter.

        I’m trying some more things, but so far, no good…

        Problem might also be solved if I can display a different value for the filterlist than I am actually filtering on… But I don’t know if and how this can be done…

        When I try to do this without the LinqDataSource but with a datacontext, I get an error that the data source is not available after selecting the value to filter on.

        To be continued…

      • #16 by Sebastiaan Brozius on September 15, 2009 - 14:01

        Finally, I have my filtering working!!😀

        Yesterday while I drove home (about an hours drive), I thought of a different approach to my problem; why not try to handle the stuff while applying the filter. Trick, at least for me, was to figure out how to do this.

        Basically, it comes down to NOT supplying the FilteredDataSourcePropertyName and FilteredDataSourcePropertyFormat for the SPGridView and setting the AutoGenerateWhereClause to true.

        Then, at the same spot where the filter is re-applied when not wanting to lose the filter when sorting, handle the filter!

        For now, still only one filter can be active at a time, opposed to SharePoint-lists, which can have multiple filters active at the same time.

        My DataBound-event of my SPGridView now looks as follows:

        //First check if there is a filter applied
        if (string.IsNullOrEmpty(gridview.FilterFieldName) == false)
        {
        //Then re-apply the filter with exceptions for specified columns
        if (gridview.FilterFieldName == "Country")
        {
        if (linqDataSource.WhereParameters["ChildID"] == null)
        { linqDataSource.WhereParameters.Add("ChildID", System.Data.DbType.Int32, this.dataContext.Childs.Where(c => c.Country == gridview.FilterFieldValue).Select(c => c.ChildID).First().ToString()); }
        }
        else if (gridview.FilterFieldName.EndsWith("ID"))
        {
        if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
        { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, System.Data.DbType.Int32, gridview.FilterFieldValue); }
        }
        else
        {
        if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
        { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, gridview.FilterFieldValue); }
        }
        }
        else if (linqDataSource.WhereParameters.Count > 0)
        { linqDataSource.WhereParameters.Clear(); }

        So here’s what I’m doing. First I check to see if a filter should be applied:

        //First check if there is a filter applied
        if (string.IsNullOrEmpty(gridview.FilterFieldName) == false)
        { ...

        Next, I reapply the filter, but here I also handle my exceptions (for example filtering on integer-values) and my Child-properties. For filtering on the Child-properties, I catch the requested FilterFieldName and add a custom WhereParameter to the LinqDataSource:

        //Then re-apply the filter with exceptions for specified columns
        if (gridview.FilterFieldName == "Country")
        {
        if (linqDataSource.WhereParameters["ChildID"] == null)
        { linqDataSource.WhereParameters.Add("ChildID", System.Data.DbType.Int32, this.dataContext.Childs.Where(c => c.Country == gridview.FilterFieldValue).Select(c => c.ChildID).First().ToString()); }
        }

        Notice that I enter a DbType in the WhereParameter. Because I filter on an integer, I get the error that Operator ‘==’ incompatible with operand types ‘Int32’ and ‘String’ when I ommit this step.

        I also handle the ID-fields to include the DbType in the WhereParameter.

        else if (gridview.FilterFieldName.EndsWith("ID"))
        {
        if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
        { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, System.Data.DbType.Int32, gridview.FilterFieldValue); }
        }

        For the other columns, I just add a standard WhereParameter where I use the gridview.FilterFieldName als Parameter-name and gridview.FilterFieldValue as it’s value.

        else
        {
        if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
        { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, gridview.FilterFieldValue); }
        }
        }

        And if there should be no filtering done, I remove all WhereParameters. Otherwise, clearing a filter will still give filtered results.

        else if (linqDataSource.WhereParameters.Count > 0)
        { linqDataSource.WhereParameters.Clear(); }

        I’ll be posting more stuff when I run into it at my own WordPress-blog🙂

      • #17 by Sebastiaan Brozius on September 15, 2009 - 14:38

        Frak… found a flaw…
        When filtering a column which yields a single row and then filtering a column which should yield more rows, only one row is returned. Somehow, the previous filter is still active…

      • #18 by Sebastiaan Brozius on September 15, 2009 - 15:14

        And fixed that flaw…
        Inside the DataBound-event, at the end of the check if there is a filter applied, (so before the closing-parenthesis of the initial ‘if there is a filter being applied’) insert this:

        while (linqDataSource.WhereParameters.Count > 1)
        {
        linqDataSource.WhereParameters.RemoveAt(0);
        }

        So it should be somewhat like this:

        if (string.IsNullOrEmpty(gridview.FilterFieldName) == false)
        {
        //Then re-apply the filter with exceptions for specified columns

        while (linqDataSource.WhereParameters.Count > 1)
        {
        linqDataSource.WhereParameters.RemoveAt(0);
        }
        }
        else if (linqDataSource.WhereParameters.Count > 0)

        The ‘flaw’ could be a step-up to multi-column filtering… I’ll investigate this some more…

      • #19 by Sebastiaan Brozius on September 15, 2009 - 16:35

        Another funny thing…
        With paging enabled and actually having multiple pages, I get some results shown on the second page that already have been shown on the first page..
        I’ve got my pagesize set to 5, 7 results in my LinqDataSource, and when ‘paging’, I effectively see only 5 of the 7 records. On the second row I do not get the 2 records that are ‘missing’ on the first page, but the third and fourth row from the first page, again…

        Any pointers? Except not using Paging, that is😛

  3. #20 by Sebastiaan Brozius on November 25, 2009 - 10:24

    Hi Johan,

    I’ve updated my own blog (sorry for the shameless plug😉 ) with s solution-file which incorporates the things I ran into. So if you’re interested, take a look see…

    This and some of your other articles were a lot of help to me🙂

    Cheers!!

  4. #21 by Aly on December 29, 2011 - 17:54

    Hello,

    I’m trying to use your code but no luck, could you please help me by correcting my code?
    Thank you in advance.

    Code here:

    using (SPSite dsite = new SPSite(“http://myURL”))
    {
    using (SPWeb dweb = dsite.OpenWeb())
    {
    SPList dlist = dweb.Lists[“libraryName”];
    IEnumerable elements = dlist.Items.OfType();

    var Result = from elt in elements
    select new
    {
    ID = elt[“ID”].ToString(),
    Form = elt[“Form”].ToString(),
    Author = elt[“Author”].ToString()
    };

    grid.DataSource = Result;
    grid.DataBind();

    this.Controls.Add(grid);
    }
    }

    • #22 by Johan Leino on January 9, 2012 - 12:04

      so what’s not working then??…one thing (don’t know if the code has been escaped properly though) is that it should be dlist.Items.OfType<SPListItem>()…or maybe (which is really the same) from SPListItem item in dlist.Items

  5. #23 by Sandeep on June 6, 2012 - 12:10

    Its really nice article Johan. Also Sebastiaan’s code snippets helped me alot.
    I have successfully implemeted sorting paging and filtering. I am stuck at a problem here. when I do filtering on grid’s first page its working perfectly, but problem is when I navigate to grid’s another page and do filtering, grid is showing “no results found.

    Any help will be appreciated.

  6. #24 by Anne on June 26, 2013 - 17:00

    Has anyone figured out a solution to the above error condition? I have the same thing happening.
    When I apply a filter on the grid, and I am still on a page index that would be a part of the index range for the filtered data, then everything works just fine. If I select the “Last” page, it goes to the end or last page of the filtered data’s page index range. However, if I am currently displaying a page which has an index that would be outside of the filtered data’s page index range, and I then apply that same filter, the grid will show “no results found” message.
    It doesn’t adjust the current display appropriately. Meaning, if I am currently displaying a page (of which its index is outside the page index range of the filtered data’s index range), then when I apply a filter the results should be that the “Last” page of the filtered data should be displayed (not an error stating “no data found”). Say you have 10 pages of data with no filter applied, and when the filter is applied there is only 5 pages of data. If you were currently displaying page 6 (or above) of the unfiltered data, and you applied the filter, the resulting display should be page 5 of the filtered data.
    Can anyone help me with this issue? Any help will be greatly appreciated. Thanks in advance.

  1. In Development
  2. Twitter settimana del 2010-12-05 — .mark.net il blog di Marco Trova
  3. 2010 in review « Johan Leino

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: