Paging and sorting based on custom .NET properties with Linq to SQL

This is a followup to this questio开发者_如何学运维n about custom paging in ASP.NET.

Say I have a table defined:

CREATE TABLE Person
(
  ID int identity(1,1) not null primary key,
  FirstName varchar(50),
  LastName varchar(50)
)

And I have it mapped to a class via the Linq to SQL designer. I've defined this property on the Person class:

public string Name
{
  get
  {
    if (FirstName != null && FirstName != "" && LastName != null && LastName != "")
      return String.Format("{0} {1}", FirstName, LastName);
    else if (FirstName == null || FirstName == "")
      return LastName;
    else if (LastName == null || LastName == "")
      return FirstName;
    else
      return "";
  }
}

Say I've got a grid that is sorted by a column bound to that Name property, and I want to do paging. The natural way to do that with Linq to SQL is something like:

MyDBContext db = new MyDBContext();
var myData = db.Persons.OrderBy(p => p.Name).Skip(pageSize * pageIndex).Take(pageSize);
myGrid.DataSource = myData;
myGrid.DataBind();

Linq to SQL throws an error here, though, because it can't convert the Name property to SQL. I can put .AsEnumerable() in the string of filters, as noted in an answer to the question I just referenced, but that means I'm pulling all rows to the web server and sorting them there, which is not ideal if there are enough records in the table. Filtering and sorting should happen in the database.

The simple answer is to convert that Name property to SQL and make it part of a SPROC that returns the data, or a view, or something. But that doesn't feel right to me because I'm putting display logic in my database layer. It's even worse if the situation is more complex than my somewhat contrived example, and the property defined is more nontrivial.

Is there a way out, or am I stuck having to choose between performance and separation of concerns? Or is my concern about that kind of display logic in the database unfounded?

LINQ to SQL wants to run the query and the sort on the server - it can do this if the expression is translateable to SQL.

Your code as it stands isn't translatable, which is why you get the error.

However, if you refactor the query to compute the name, it will work:

var query = from p in db.Persons 
            let name = (p.FirstName + " " + p.LastName).Trim
            order by name  
            take 10
            select name;

If you try running this you find that LINQ-to-SQL does a good job of translating it for you:

SELECT [t2].[value]
        FROM (
            SELECT TOP (10) [t1].[value]
            FROM (
                SELECT LTRIM(RTRIM(([t0].[FirstName] + @p0) + [t0].[LastName])) AS [value]
                FROM [dbo].[Persons] AS [t0]
                ) AS [t1]
            ORDER BY [t1].[value]
            ) AS [t2]
        ORDER BY [t2].[value]

However, if the table gets larger I'd recommend against this. You're asking SQL to do a calculation repeatedly that ideally could be done once and stored. Consider a computed field FullName, which stores the result. This would require no calculation and could be indexed.

I think that your concern about the separation of display logic and database is unfounded, or rather, I think that the idea that the logic behind the Name property is display logic is.

There are many cases where values, even if for display purposes only, are pre-computed to help with querying, display, and optimization.

IMO, you should have a column on your Persons table called Name and you should have the logic in your business layer that performs the transformation of the other properties into the Name property, and then persists it along with the rest of the data.

Then, you would be able to query normally, and order on the DB level (and there are probably other cases in your data where this would come in handy as well).

Just because paging and sorting filters the result set down to a manageable level doesn't mean that performing operations for display logic repeatedly and often are acceptable.

Another way to think of it is that you are going to do the transformation and the write a handful of times. However, you are going to read this data many times, and you will be performing this transformation every time. If you aggregate all of the time spent on doing this transformation, it will add up to a significant amount, an amount you could save if you pre-compute the value and then just perform the appropriate query operations on it when you need it.