Filtering and Sorting on Foreign Key columns

I am having the same issue with filtering and sorting.  I had overcome this issue before by binding to a View Model that used the Name strings, then on save I searched for the Id, which I used in the actual data model.  But this is clunky and hardly robust.  Do you simply not support sorting and filtering on foreign key columns?  That would render them practically useless for me ;-(

AldenG

Hello Alden,

I'm afraid that sorting on the foreign column "text" value is not possible as it is not present in the data bound to the grid. Thus, you should use either custom binding, or add the foreign text value as a property to the model which is bound to the grid. Similar to the way shown in this online demo.

Regarding the filtering, we have already implemented a drop down list which contains the foreign text values for the filtering menu. It will be available with the next service pack.

All the best,
Rosen
the Telerik team

Hi Rosen,

That's good news on the dropdown filter.  I'd love to figure out a way to make the sorting work with the foreign key column.  There's too much nice built-in functionality with it already where I'd rather not have to build a custom template and have all this extra code to maintain.  Do you think it would be possible to add something along the lines of a SortProperty property to the grid column?  This would be similar to what's in the RadGrid where you can bind the data to one property, but have the sort apply against a different property.  I know we'd still have to update our model to include the "text" value, but overall this seems like it would be the cleanest solution for us.  Let me know if you think this is something your development team would consider for a future release.  Thanks!

Regards,
Brian

Hi Brian,

As I have mentioned in my previous reply, you may expose the foreign field name in the ViewModel bound to the grid and sort on this property. I have attached a small sample which demonstrates basic implementation of such approach. Note that an internal build is used in the project, which has the filtering menu modification I have mentioned previously. 

Regards,
Rosen
the Telerik team

good tip ...the only think is that you would have to load the foreign field name with the name that matches the foreign key. In your example you are using a loop with random values to feed 'CategoyName' which is okay for the demo:
 
but in a real scenario that would require some "extras" calls to the database to pull that info, woudlnt it?
hmmm I need to think how I can do this.....

keep up the good work!

The problem with this approach is that the Name field is not unique, so it is not possible to find an Id even if you do have a Name and are willing to do an extra db query in the Edit or Create methods.  For example, a table of employee names can have 2 "John Smith" entries.

Hi Rosen,

I'm having a similar issue with sorting as well.  I tried adding a property for the foreign key text value.  But since my repository is hooked up with a database and the repository returns the IQuerable<T> type, the sort will occur at the database level.  And eventually a error will be thrown saying my foreign key text property doesn't exist in the database.  Is there a good place or event where we can replace the sort expression?  i.e. we still bind the foreign key column with CategoryId but we replace the CategoryId sort expression with Category.Name.

Thanks,

Ben

Hi Ben,

As I have mentioned in a previous message you may use custom data binding to implement your own sorting routing which can handle the foreign text.

Regards,
Rosen
the Telerik team

Rosen,

I have run into what might be a bug in the filterable foreign key column.  Have you guys tested it when the id is a guid? 

On my current project, I use a mvc grid with a forign key and everything works fine for editing and displaying. I was using the latest official release, 2011.3.1115 and noticed that filter menus on foreign key columns would always show up with empty values.  I saw this post and updated my Telerik reference, script and content folders with the 2011.3.1122 version.  Now I notice that the filter menu DOES display correct values, yeah!, but when I attempt to actually filter, I get the error: Telerik.Web.Mvc.Infrastructure.Implementation.FilterParserException: Expected token".  In older posts from 2009, this error was due to underscores in the field name.

My filter is u2ConstraintTypeId~eq~e6506dfa-3df4-e011-9cb4-0016356d24f9 and this field is in the viewmodel I use to bind to the grid.  Below is the POST which causes the error, which shows the filter at the bottom.

POST http://localhost.:60992/u2ConstraintValue HTTP/1.1

x-requested-with: XMLHttpRequest

Accept-Language: en-us

Referer: http://localhost.:60992/u2ConstraintValue

Accept: text/plain, */*; q=0.01

Content-Type: application/x-www-form-urlencoded

Accept-Encoding: gzip, deflate

User-Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.3)

Host: localhost.:60992

Content-Length: 108

Connection: Keep-Alive

Pragma: no-cache

page=1&size=10&orderBy=ConstraintText1-asc&filter=u2ConstraintTypeId~eq~e6506dfa-3df4-e011-9cb4-0016356d24f9

Hi,

I'm afraid that filtering on GUIDs is not supported. Maybe you could consider using a ViewModel and converting the GUIDs to String.

All the best,
Rosen
the Telerik team

Rosen, and others reading this,

I was able to get the filter working properly on a foreign column whose id column is of type Guid, but it was NOT trivial.  I spent a few hours of trial and error, and I'm writing this to save you the time.  My project is MVC3 with C#, .NET 4.0, EF4.1, repository pattern using the Telerik 1122 build (See earlier post).

Here is what you do.  If you're binding with ajax, then you probably already have ViewModels in place to avoid the javascript serializer errors anyway, but if not, you need to create ViewModels for both the model your working with and the model of the forign key data table.  You can find documentation on that in other places. 

Say we have two tables, Table A and Table B.  Table A has a foreign key to Table B called B_id and Table B's primary key is B_id and these are both guids.  Your Viewmodels, say vmTableA and vmTableB most likely represent this exactly. 

Identify what in your project is vmTableA and vmTableB and do the following:
In vmTableA, change the type of B_id from Guid to String.
In vmTableB, add a new property, public string B_idString {get; set;} //leave B_id there as a Guid
In the controller for TableA, you're fetching the data somewhere( most likely the Index call) and you need to do some Linq to Objects after your Linq to EF, because the IQueryable EF will not allow you to convert a Guid to a String.  Below is an example of commenting out the viewmodel hydrating with Linq to EF and then using a Linq to object. 

[

GridAction]

public ActionResult Index()

 {

 var m = TableARepository.All;

//.Select(s => new vmTableA

//{

// A_Id = s.A_id,
// B_Id = s.B_id,

 // OtherProperties = s.OtherProperties

//});
  

var model = (

from s in m.AsEnumerable()

select new vmTableA

{

    A_Id = s.A_Id,

    B_Id = s.B_id.ToString(),

    OtherProperties = s.OtherProperties

}

).ToList();

 

// Add viewbag of all B Types to populate the dropdownlist in Shared/EditorTemplates/GridForeignKey.cshtml

var dl = TableBRepository.All;

var DataList = (

from s in dl.AsEnumerable()

select new vmTableB

{

    B_IdString = s.B_Id.ToString(),

    BName = s.BName

}

).ToList();

//this adds an empty one at the top
DataList.Insert(0,

 

new vmTableB { B_IdString = Guid.Empty.ToString(), BName = "Select Type..." });

 

ViewBag.BTypes = DataList;
return View(new GridModel<vmTableA> { Data = model });

}

Then just update your View for Table A accordingly.

columns.ForeignKey(o => o.B_Id,

(System.Collections.

IEnumerable)ViewBag.BTypes , "B_IdString", "BName").Width("12%");

 

Hi Rosen and others reading, 


The projects you attached is very interesting, I would like to know how do you add the dropdownlist to the filter, could you please add some information about this?

I'm in the same situation than Brian, I'm developing my project with MVC 3 with VB.NET, NET 4.0, EF4.1 and the Telerik release (that I thought it was the latest one) is 2011.3.1115. Does it affect?

Thank you. 

Hi Rosen,

Thanks for your replies.  I got caught up in another project and am finally getting back to this.  The custom data binding route would work, but isn't desirable for us from a coding and maintenance standpoint.  Our application has a large number of grids to store various settings information and outside of the foreign key sorting we are able to create these pages with some smart mvc templates and minimal code.  But to do the custom data binding would require us to write a lot of code unique to each grid, which would greatly add to our development effort.  If it would be possible, I really think adding a 5th (optional) parameter to the ForeignKey constructor for the sort property would be the ideal solution for us.  I actually hacked up the grid javascript source code a bit today to see if this concept would work and the changes were pretty minimal (although my hack was hard-coding some things specifically related to our naming conventions.)  Let me know your thoughts.  Thanks again for the help and advice.

Regards,
Brian

Hi Brian,

As I have pointed in my previous message, sorting or grouping on a value which is not in the DataSource to which grid is bound is not possible. This is in fact the case when foreign key column is used, the foreign text value usually is from a separate DataSource not the one from which grid is populated.

Regards,
Rosen
the Telerik team

Hi Rosen,
I think I'm running into the same issue that Ben posted earlier, where I've included the sort field as part of my ViewModel, but my dataset is returning an IQueryable which is trying to do the sort directly against the database through Entity Framework.  With the custom filtering and sorting functionality, is it possible to only use the custom sorting?  And within that, is it possible to use the standard sorting for all but a select few columns or is it that once you use the custom sorting you have to manually sort every column?  Thanks for your patience with this.

Regards,
Brian

Hi Brian,

I'm afraid that there is not way for only "partial" custom binding to be used. Thus, you will need to implement the entire custom binding functionality.

Greetings,
Rosen
the Telerik team

Hi Rosen,

Thanks for the info.  I ended up writing a customization to the Telerik source to accommodate my sorting issue.  It's a generic customization, so let me know if there would be any interest in me sharing what I did.

So I'll stop bothering you about sorting now :-).  My next question is about the filtering.  When I click on the filter for the foreign key column, the dropdowns that should be populating with "is equal to" and "is not equal to" are coming up blank.  I tried stepping through the javascript code and when the logic is looping through the localization strings these strings don't include filterForeignKeyEq or filterForeignKeyNe.  Which is strange because I see them in the grid.js file.  Has anyone else run into this that might have an idea what's going on?  I'm running this against the standard dll that doesn't have my customization with the non-minified javascript files.  Thanks for the help.

Regards,
Brian

Brian,

The issue was fixed after the last 2011 release.  I resolved the filter issue for my project with Telerik build 2011.3.1122. Please read my previous posts for more explanation.  This particular internal build is included with the project Rosen posted to this question on Dec 9th, and I would assume any later build would include the fix.

- Bill

Hi Bill,

Thanks for the quick reply.  I'm running on the 2011.3.1306 build (finally convinced my boss to spring for the commercial license!), so I would think that the fix should be valid in this build.  Did you have to do anything to get it working besides update to the 1122 build?  Maybe I'll try rolling back to the version in Rosen's example and see if it works in that build for me.

Brian

Brain,

I would try with the 1122 version just to eliminate the possibility of your build not having the fix while developing.  I dont think you should have any problems unless the id of your foreign key is a guid.  In that case, read my post on this thread from Jan 5th.  I was binding ajax so a server binding solution would be different.  The basic gist of my ajax solution was to use viewModels and create a (string) surrogate in the foreign key model.

Hi Brain,

Are you able to observe the same issue on our live demos. The version used there is Q3 2011 SP1.

Regards,
Rosen
the Telerik team

Hi Rosen,

The demo site worked fine for me.  I'm thinking I have a bad configuration setting somewhere or something like that.  I'm going to try creating a project from scratch next week to see if I can get past the issue and then try to figure out what's different with my main project.  I'll let you know what I find.

Thanks,
Brian

Hi Rosen,

I figured it would be something stupid, and it was.  When I updated my project to the latest version of the controls I neglected to update the files in the App_GlobalResources folder.  Once I did that, the filter dropdowns started working properly.  Looks great now!

Regards,
Brian

Hi Brian,

Great, I'm glad that you have managed to address the issue you were facing.
 
Regards,
Rosen
the Telerik team

Hi Rosen,

So it took almost 24 hours before I ran into another situation where I need your help...:-)

I have two grids that are on the same page.  The first grid binds to ObjectA and just has a Name and Id property.  The second grid binds to ObjectB and has Name, Id, and ObjectAId properties.  The ObjectB grid uses a ForeignKey column for the ObjectAId field.  So when the user makes an edit to the ObjectA grid and changes the Name, I want that change to be reflected in the ForeignKey column in the ObjectB grid.  I have javascript code that fires after the user saves the ObjectA grid.  This code gets the ObjectB grid and calls the ajaxRequest method.  I was expecting that the foreign key data would be refreshed in this situation, but it still shows with the old Name value.  And if I try to edit the column the dropdown list still shows the old Name value as well.  I tried the rebind method on the grid too with the same result.  But to get the updated values to show up in the ObjectB grid I need to refresh the entire page.

Is there a different approach I should be using?  Or is this a situation that isn't supported with the current build?

As always, thanks for your help.

Regards,
Brian

Hello Brian,

The requested functionality is only supported when using server-side binding, as the values for the foreign key list are serialized only on initial page load. Therefore, when ajax binding is used those values will not be refreshed. 

Another approach will be not to use the built-in foreign key column, but a custom editor template in which the DropDownList is populated through ajax.

All the best,
Rosen
the Telerik team

Hi Rosen,

Once again, thanks for the reply.  I know I'm persistent, but I'm determined to try to avoid writing the custom sorting and filtering...:-)  I might take a crack at writing some jQuery logic in my GridA OnSave event to make an ajax call to try to refresh the column data in GridB, but I think I've got a good handle on what I'd need to do there.  I'll let you know how it turns out.

Regards,
Brian

I am very interested in the drop down for filtering (since right now I would have to implement 3 seperate drop downs in the toolbar or something like that),  You mentioned it would be in the next service pack. When is that service pack due out (in particular for the open source version or is that going to be a paid only feature...)

Thanks.

I too am running into this "Expected Token" problem when applying a filter on the grid for a Guid-based foreign key. Surely this can't be forever "unsupported", can it? The use of Guid FKs is becoming commonplace. I am using a viewmodel for the base table, but REALLY don't want to have to further complicate this because of what appears to be a limitation or bug when filtering on a Guid FK.

Telerik: Is there any update on this approach?

I'm adding my vote to the requests for an update and improvement for this problem.  I'm still using an old approach based on custom editor template.  But it's annoying and less than elegant.

It should not have to be so difficult or time-consuming for the developer to implement custom solutions filtering/sorting on foreign key columns with drop down lists that have value and text fields for each selection item.  Better to have a more useful foreign key column for the the MVC Grid than the current version.

Let's hope the Telerik MVC Team improves the situation sooner rather than later!