"The ORDER BY sort key(s) type must be order-comparable" with dynamic data

Question

I'm trying to create a Dynamic Data website which should allow an administrator to directly edit the data in most tables in a database.

So far, I have an EDMX and POCO classes, all attached to an Interface used to apply the DataAnnotations on 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 a ListView.
With all of these, I can display and edit data. It works.

But when I click on the header (it is a LinkButton with the Sort Command and the column name as CommandArgument) of a ForeignKey column, I always get the following exception (but the sorting works on "simple" properties) :

[EntitySqlException: The ORDER BY sort key(s) type must be order-comparable. Near member access expression, line 6, column 3.]
Microsoft.AspNet.EntityDataSource.EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1325
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +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

Example of the table I'm trying to display and sort (I'm displaying and editing LINK_ENTITES_MODELISEES, I'm trying to sort on the 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; }
}

It probably doesn't work because it's trying to sort on the object property and not the label it's using, but I would expect Dynamic Data to handle that (it uses the first string property as the display value, why can't it use it for sorting ? Moreover I also tried to add the DisplayColumn attribute, with the same result).

I tried to handle the event ListView.OnSorting to manually edit the EntityDataSource.OrderBy property, adding the value it.LOV_LOB.libelle manually, for testing. It didn't work. I also tried to handle EntityDataSource.OnSelecting to see what is the value of EntityDataSource.OrderBy (if I don't set it manually it is always null even when the sorting works). It looks like it is ignored or replaced after this event. And the Exception doesn't specify what OrderBy it's trying to apply, so I'm not sure what it's trying to do. I tried to implement IComparable but it didn't work. I overrided the ToString() to provide the display value, it didn't work either.

I'm out of idea. Any suggestion ?

Unknown Unknown · Accepted Answer · 2016-05-21T15:55:05

Sort on LOV_LOB.id or one of the other LOVE_LOB attributes. As a LOV_LOB object itself has not sortable conventionally, i.e by number or datetime, or alphabetically, but it's attributes are.

"The ORDER BY sort key(s) type must be order-comparable" with dynamic data

2 Answers