EDIT:
db
.Table
.Select(t=>new{t.Record,DisplayName=t.Name??t.CompanyName})
.OrderBy(t.DisplayName);
Would create SQL similar to:
SELECT [t1].[Record], [t1].[DisplayName]
FROM (
SELECT
[t0].[Record],
COALESCE([t0].[Name],[t0].[CompanyName]) AS [DisplayName]
FROM [Table] AS [t0]
) AS [t1]
ORDER BY [t1].[DisplayName]
This looks like it fulfills your requirements. You could happily tack Skip and Take to the end of this statement to support pagination.
To account for Year (as per comment):
db
.Table
.Where(t=>t.Year>1990)
.Select(t=>new{t.Record,DisplayName=t.Name??t.CompanyName})
.OrderBy(t.DisplayName);
You last requirement would be as simple as writing a new class to encapsulate the result:
public class Result
{
public int Record
{
get;
set;
}
public string DisplayName
{
get;
set;
}
}
Then the query would become:
db
.Table
.Where(t=>t.Year>1990)
.Select(t=>new Result{t.Record,DisplayName=t.Name??t.CompanyName})
.OrderBy(t.DisplayName);
However, I am starting to think that if you were to create a view of the SQL query:
SELECT [t1].[Record], [t1].[DisplayName],[t1].[Year]
FROM (
SELECT
[t0].[Record],
COALESCE([t0].[Name],[t0].[CompanyName]) AS [DisplayName],
[t0.Year]
FROM [Table] AS [t0]
) AS [t1]
ORDER BY [t1].[DisplayName]
and then to drag this view onto your Dbml surface, you might get better mileage.
EDIT:
If you can, the optimal approach would be to alter the source table as follows, so that DisplayName becomes a computed column at source.
ALTER TABLE [Table]
ADD [DisplayName] As
(COALESCE([Name],[CompanyName]))
I finally grok your question (I didn't realise DisplayName was only for ordering purposes, and not required to be maintained). Here's your query:
db
.Table
.Where(t=>t.Year>1990)
.Select(t=>new {Item=t,DisplayName=t.Name??t.CompanyName})
.OrderBy(a=>a.DisplayName)
.Select(a=>a.Item);
TADA!