Make sql where clause prioritize one prefix-like-search above another

Question

I have an sql table with some nvarchar columns, lets call them 'Title' and 'Value'.

I currently use a linqtosql query to make a prefix search to find rows in this table, like so

var result = from _item in GetTable()
    where _item.Title.StartsWith( "hello" ) || _item.Value.StartsWith( "hello" )
    select _item;
return result.Take( 100 );

which generates the corresponing sql:

@p0=N'hello%'
@p1=N'hello%'
SELECT TOP (100) *
FROM [Table] AS [t0]
WHERE ([t0].[Title] LIKE @p0) OR ([t0].[Value] LIKE @p1)

which works somewhat, but lets say I want to prioritize the Value column over the Title column. ie if the _item.Value.StartsWith constraint returns 100 results, only those are returned. If it returns 25 results, the remaining 75 are filled from the 'Title' constraint.

I could do it using several queries, but that could fill my result set with duplicate entries.

Can this be done in linqtosql or do I have to manually write an sql query to fix it? In either case, how can it be done?

Answer 1

Can you add an ORDER BY? (Don't know how to do this in linq)

ORDER BY CASE WHEN [t0].[Value] LIKE @p1 THEN 0 ELSE 1 END