none
sort ListView no supported translation exception RRS feed

  • Question

  • Any work arounds on how to do this without doing a ToList which breaks the deferred execution?

     

    All the code is built like the sample below, I don't really want to break it. The sample below is very very basic, and I just typed it all up from scratch, it is just to demonstrate the problem I'm having. The Data class if from auto-generated linq-to-sql code, so this is a corresponding Customer table with a LastName column. Is there some way that I can map the logic tier's properties to the Data tier's eqivalent?

     

    Code Snippet

    /* data tier */

    namespace Data {

    /* auto-generated entity from dbml */

    [Table(Name="dbo.Customer")]

    public partial class Customer {

    public string LastName { get; set }

    }

    }

     

    /* logic tier */

    namespace Logic {

    public partial class Customer {

    private Data.Customer customer;

    internal Customer (Data.Customer customer) {

    this.customer = customer;

    }

    public string LastName { get { return customer.LastName; } set { customer.LastName = vaue; } }

    }

     

    public static class Customers {

    public static IQueryable<Customer> All() {

    return new DataContext().Customers.Select(customer => new Customer(customer));

    }

    }

    }

     

    /* web tier */

    <script runat="server" language="c#">

    private void dataByLinq_Selecting(object sender, LinqDataSourceSelectEventArgs e) {

    e.Result = Customers.All();

    }

     

    protected override void OnInit(EventArgs e) {

    Load += (sender, e) =>

    {

    if (!IsPostBack)

    view.Sort("LastName", SortDirection.Ascending); /// EXCEPTION: Logic.Customer.LastName has no supported translation to SQL

    };

    }

       }

    <script>

    <asp:LinqDataSource runat="server" ID="dataByLinq" OnSelecting="dataByLinq_Selecting"/>

    <asp:ListView runat="server" ID="view" DataSourceID="dataByLinq">

    <LayoutTemplate>

    <table>

    <thead>

    <tr>

    <th><asp:LinkButton runat="server" CommandName="Sort" CommandArgument="LastName" /></th>

    </tr>

    </thead>

    <tbody><asp:PlaceHolder runat="server" ID="itemPlaceHolder" /></tbody>

    </table>

    </LayoutTemplate>

    <ItemTemplate><tr><td><%# Eval("LastName") %></td></tr></ItemTemplate>

    </asp:ListView>

    <asp:DataPager runat="server" PagedControlID="view" />

     

     

    Monday, September 8, 2008 8:51 PM

Answers

  •  

    So I've stumbled on my best answer while I was working on something else... Yeah, I would be able to update with these, but I'm updating/inserting with stored procedures anyway. Anyway I works, I'm happy.

     

     

    Code Snippet

    /* data tier */

    namespace Data {

    /* auto-generated entity from dbml */

    [Table(Name="dbo.Customer")]

    public partial class Customer {

    public int ID { get; set; }

    public string LastName { get; set }

    }

    }

     

    /* logic tier */

    namespace Logic {

    public partial class Customer {

    private Data.Customer customer;

    internal Customer () {

    this.customer = new Data.Customer();

    }

    public int ID { get { return customer.ID; } internal set { customer.ID = value; } }

    public string LastName { get { return customer.LastName; } set { customer.LastName = value; } }

    }

     

    public static class Customers {

    public static IQueryable<Customer> All() {

    return new DataContext().Customers.Select(customer => 

    new Customer{

    ID = customer.ID,

    LastName = customer.LastName

    }

    );

    }

    }

    }

     

    /* web tier */

    <script runat="server" language="c#">

    private void dataByLinq_Selecting(object sender, LinqDataSourceSelectEventArgs e) {

    e.Result = Customers.All();

    }

     

    protected override void OnInit(EventArgs e) {

    Load += (sender, e) =>

    {

    if (!IsPostBack)

    view.Sort("LastName", SortDirection.Ascending);

    };

    }

       }

    <script>

    <asp:LinqDataSource runat="server" ID="dataByLinq" OnSelecting="dataByLinq_Selecting"/>

    <asp:ListView runat="server" ID="view" DataSourceID="dataByLinq">

    <LayoutTemplate>

    <table>

    <thead>

    <tr>

    <th><asp:LinkButton runat="server" CommandName="Sort" CommandArgument="LastName" /></th>

    </tr>

    </thead>

    <tbody><asp:PlaceHolder runat="server" ID="itemPlaceHolder" /></tbody>

    </table>

    </LayoutTemplate>

    <ItemTemplate><tr><td><%# Eval("LastName") %></td></tr></ItemTemplate>

    </asp:ListView>

    <asp:DataPager runat="server" PagedControlID="view" />

     

     

    Friday, September 12, 2008 1:35 PM

All replies

  • Because you have wrapped the LINQ to SQL enabled Customer object in your own Customer object there is no way that the LinqDataSource can figure out how to generate the correct SQL to enable this.

    You could try switching to the ObjectDataSource which may be able to perform an in-memory sort instead.

    [)amien
    Tuesday, September 9, 2008 7:05 AM
    Moderator
  • That is not feasible in the environment I am working in because the bll resides on the webserver and piping 15k customers is not a happy experience. I was hoping I could tap into somewhere to override the default behavior, I believe I was going down the wrong path when I found references to BindableList<T>, I think this is something only useable in WPF? I had overriden the Sorting event beore so I could do a view.Sort("LastName, FirstName", SortDirection.Ascending) so maybe I can do this additional work here. It frustrates me because I know that the DataPager is still able to figure out how to work properly, therefore making me feel more strongly about finding a solution for this.
    Tuesday, September 9, 2008 12:48 PM
  • How hard would it be to extend the QueryProvider?... It seems wrong, but since I need something quick, couldn't I somehow extend the Provider so that when a LogicTier class is encountered, use the underlying DataTier class?

     

    /* logic tier */

    namespace Logic {

    public partial class Customer {

    private Data.Customer customer;

    internal Customer (Data.Customer customer) {

    this.customer = customer;

    }

    public string LastName { get { return customer.LastName; } set { customer.LastName = vaue; } }

    }

     

    public static class Customers {

    public static IQueryable<Customer> All() {

    return new DataContext().Customers.Select(customer => new Customer(customer));

    }

    }

    }

    Tuesday, September 9, 2008 8:55 PM
  •  

    So I've stumbled on my best answer while I was working on something else... Yeah, I would be able to update with these, but I'm updating/inserting with stored procedures anyway. Anyway I works, I'm happy.

     

     

    Code Snippet

    /* data tier */

    namespace Data {

    /* auto-generated entity from dbml */

    [Table(Name="dbo.Customer")]

    public partial class Customer {

    public int ID { get; set; }

    public string LastName { get; set }

    }

    }

     

    /* logic tier */

    namespace Logic {

    public partial class Customer {

    private Data.Customer customer;

    internal Customer () {

    this.customer = new Data.Customer();

    }

    public int ID { get { return customer.ID; } internal set { customer.ID = value; } }

    public string LastName { get { return customer.LastName; } set { customer.LastName = value; } }

    }

     

    public static class Customers {

    public static IQueryable<Customer> All() {

    return new DataContext().Customers.Select(customer => 

    new Customer{

    ID = customer.ID,

    LastName = customer.LastName

    }

    );

    }

    }

    }

     

    /* web tier */

    <script runat="server" language="c#">

    private void dataByLinq_Selecting(object sender, LinqDataSourceSelectEventArgs e) {

    e.Result = Customers.All();

    }

     

    protected override void OnInit(EventArgs e) {

    Load += (sender, e) =>

    {

    if (!IsPostBack)

    view.Sort("LastName", SortDirection.Ascending);

    };

    }

       }

    <script>

    <asp:LinqDataSource runat="server" ID="dataByLinq" OnSelecting="dataByLinq_Selecting"/>

    <asp:ListView runat="server" ID="view" DataSourceID="dataByLinq">

    <LayoutTemplate>

    <table>

    <thead>

    <tr>

    <th><asp:LinkButton runat="server" CommandName="Sort" CommandArgument="LastName" /></th>

    </tr>

    </thead>

    <tbody><asp:PlaceHolder runat="server" ID="itemPlaceHolder" /></tbody>

    </table>

    </LayoutTemplate>

    <ItemTemplate><tr><td><%# Eval("LastName") %></td></tr></ItemTemplate>

    </asp:ListView>

    <asp:DataPager runat="server" PagedControlID="view" />

     

     

    Friday, September 12, 2008 1:35 PM