"ORDER BY sort type must be comparable in order" with dynamic data

Question

I am trying to create a Dynamic Data site that should allow the administrator to directly edit data in most tables in the database.

So far I have the EDMX and POCO classes, all of which are bound to the interface used to apply DataAnnotations in the fields.
I want to have an editable grid for each table, so I edited the ListDetails template and followed these instructions , which allows me to have inline editing in the ListView.
With all these features, I can display and edit data. It works.

But when I click on the header (this is a LinkButton with the Sort command and the column name as CommandArgument) of the ForeignKey column, I always get the following exception (but sorting works on the “simple” property):

[EntitySqlException: ORDER BY collation must be comparable order. Member Access Expression, Row 6, Column 3.]
Microsoft.AspNet.EntityDataSource.EntityDataSourceView.ExecuteSelect (DataSourceSelectArguments arguments) +1325
System.Web.UI.DataSourceView.Select (DataSourceSelectArguments arguments, callback DataSourceViewSelectCallback) +21
System.Web.UI.WebControls.DataBoundControl.PerformSelect () +138
System.Web.UI.WebControls.ListView.PerformSelect () +167
System.Web.UI.WebControls.BaseDataBoundControl.DataBind () +30
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound () +105 System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender (EventArgs e) +22 System.Web.UI.Control.PreRenderRecursiveInternal () +83
System.Web.UI.Control.PreRenderRecursiveInternal () +155
System.Web.UI.Control.PreRenderRecursiveInternal () +155
System.Web.UI.Control.PreRenderRecursiveInternal () +155
System.Web.UI.Control.PreRenderRecursiveInternal () +155
System.Web.UI.Control.PreRenderRecursiveInternal () +155
System.Web.UI.Control.PreRenderRecursiveInternal () +155
System.Web.UI.Page.ProcessRequestMain (Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +974

An example of the table I'm trying to display and sort (I show and edit LINK_ENTITES_MODELISEES, I'm trying to sort by LOV_LOB column):

 [MetadataType(typeof(ILINK_ENTITES_MODELISEES_MetaData))] public partial class LINK_ENTITES_MODELISEES : ILINK_ENTITES_MODELISEES_MetaData { public int id_entite_modelisee { get; set; } public short id_entite { get; set; } public short id_lob { get; set; } public System.DateTime date_val_debut { get; set; } public System.DateTime date_val_fin { get; set; } public virtual LOV_ENTITE LOV_ENTITE { get; set; } public virtual LOV_LOB LOV_LOB { get; set; } } public partial interface ILINK_ENTITES_MODELISEES_MetaData { [Key] [Required(ErrorMessage = "id_entite_modelisee is required")] int id_entite_modelisee { get; set; } [Required(ErrorMessage = "id_entite is required")] short id_entite { get; set; } [Required(ErrorMessage = "id_lob is required")] short id_lob { get; set; } [Required(ErrorMessage = "date_val_debut is required")] [DataType(DataType.Date)] System.DateTime date_val_debut { get; set; } [Required(ErrorMessage = "date_val_fin is required")] [DataType(DataType.Date)] System.DateTime date_val_fin { get; set; } [Display(Name = "entite")] LOV_ENTITE LOV_ENTITE { get; set; } [Display(Name = "lob")] LOV_LOB LOV_LOB { get; set; } } [MetadataType(typeof(ILOV_LOB_MetaData))] [DisplayColumn("libelle", "libelle", false)] public partial class LOV_LOB : ILOV_LOB_MetaData { public short id { get; set; } public string libelle { get; set; } public System.DateTime date_val_debut { get; set; } public System.DateTime date_val_fin { get; set; } } public partial interface ILOV_LOB_MetaData { [Key] [Required(ErrorMessage = "id is required")] short id { get; set; } [Required(ErrorMessage = "libelle is required")] [StringLength(5)] string libelle { get; set; } [Required(ErrorMessage = "date_val_debut is required")] [DataType(DataType.Date)] System.DateTime date_val_debut { get; set; } [Required(ErrorMessage = "date_val_fin is required")] [DataType(DataType.Date)] System.DateTime date_val_fin { get; set; } } 

This probably doesn't work because it tries to sort the property of the object, not the shortcut that it uses, but I would expect Dynamic Data to handle this (it uses the property of the first row as a display value, why can't it use it to sort ? Moreover, I also tried to add the DisplayColumn attribute with the same result).

I tried to handle the ListView.OnSorting event to manually change the EntityDataSource.OrderBy property by adding the value of it.LOV_LOB.libelle manually for testing. This did not work. I also tried to process EntityDataSource.OnSelecting to find out what the value of EntityDataSource.OrderBy is (if I do not set it manually, it is always null , even if sorting works). It seems that after this event it is ignored or replaced. And Exception does not indicate which OrderBy is trying to apply, so I'm not sure what it is trying to do. I tried to implement IComparable , but that didn't work. I overridden ToString () to provide a display value, it doesn't work either.

I do not know. Any suggestion?

Answer 1

What I finally did:

Since the Sort command did not work, I ran it, specifying the sort options themselves:

• In the template associated with the OP, I changed the command name in the heading CommandName = "Sort" to user CommandName = "CustomSort"
• Add a mapping to the ListView on the aspx page: OnItemCommand="ListView1_ItemCommand"

• On the CodeBehind page, I processed a user command (I know that storing information in Session is a bad idea. See this question ):

   protected void ListView1_ItemCommand(object sender, ListViewCommandEventArgs e) { if (e.CommandName == "CustomSort") { var sortInfos = Session["SortInfos"] as SortInfos; var sortDirection = SortDirection.Ascending; if (sortInfos != null && sortInfos.Sort == e.CommandArgument.ToString()) { sortDirection = sortInfos.SortDirection.HasValue && sortInfos.SortDirection == SortDirection.Ascending ? SortDirection.Descending : SortDirection.Ascending; } //Get columns metadata var data = table.Columns.SingleOrDefault(c => c.Name == e.CommandArgument.ToString()); string filter = null; if (data is MetaForeignKeyColumn) filter = String.Format("{0} {1}", data.SortExpression, sortDirection == SortDirection.Ascending ? "asc" : "desc"); else filter = String.Format("it.{0} {1}", data.SortExpression, sortDirection == SortDirection.Ascending ? "asc" : "desc"); GridDataSource.OrderBy = filter; GridDataSource.AutoGenerateOrderByClause = false; Session["SortInfos"] = new SortInfos() { Sort = e.CommandArgument.ToString(), SortDirection = sortDirection }; } } 

  GridDataSource is my EntityDataSource object.
  SortInfos is just a POCO class with Sort and SortDirection properties

It worked very well.

Answer 2

Sort by LOV_LOB.id or one of the other LOVE_LOB attributes. Since the LOV_LOB object itself is not sorted traditionally, that is, a number or datetime, or in alphabetical order, but the attributes are.