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

Question

This is a followup to this question 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?

score 1 · Answer 1 · answered Aug 16 '11 at 17:43

1

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.

answered Aug 16 '11 at 17:43

Quango

12,338
6
48
83

There are two problems here. First, while this logic is translatable to SQL, not all complex logic is; if the logic gets more complex than this, then it might not translate and you're back to square one. The second problem is that the SQL produced from your query will give different results than his logic in code. The `LTRIM` and `RTRIM` operators will return `NULL` if `NULL` is passed into it, but in this case, the code checks for null and returns empty, your results will return `null`. This is different from the desired output. You should correct your code to produce the same output. – casperOne Aug 16 '11 at 17:48
By computed field do you mean something in a view in the database that I select from instead of the table? – Tom Hamming Aug 16 '11 at 17:49
@Mr. Jefferson: You shouldn't do this in a view either; if you are going to query repeatedly, then you are better off transforming the value once, storing it, and then querying for the value when you need it. The assumption here being that you have many more reads than writes. – casperOne Aug 16 '11 at 17:50
No, not all complex logic is translatable into SQL. If SQL can't do it then the code must, and to sort ten thousand records requires you calculate your complex function ten thousand times. Every time you access it. Hence - if you're gonna use it - save it. – Quango Aug 16 '11 at 18:27

score 1 · Answer 2 · answered Aug 16 '11 at 17:44

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.

Do you think that a property that a grid column is bound to in the UI should always be based on a field that is stored in the DB, instead of being calculated dynamically? Excluding cases like counts of child elements, that is. — Tom Hamming, Aug 16 '11 at 17:51
Mr. Jefferson: There's not one definitive answer to that. It's about usage. If you are going to read that column over and the number of times it's going to be written to is very small compared to the number of writes, then I say absolutely, it should be stored in the database. However, if the column is written to many times but queried very little, then I'd leave it to the display to perform the transformation. You have to measure what is best for your system. Given your example though, it seems you have the latter condition (many reads, few writes). — casperOne, Aug 16 '11 at 17:55

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

2 Answers2