Linq/SQL Query Sorting for Multiple LIKE conditions

Question

I have a table in a db that has a structure like this:

Date
{
    ID,
    Username,
    Label,
    DateTime
}

I want to be able to perform a basic .Contains search in linq on the Username and Label columns, but there's a catch. I'd like to order by the number of LIKE matches. Here is what I mean by this:

If I have an instance of Data = {ID=1,Username="Shawn",Label="Whatever",DateTime=1/1/2010}

1) If I search User="awn" and Label="What" the result has a match order of 2.

2a) User="haw" and Label="cat" has a match order of 1

2b) User="Doug" and Label="ever" has a match order of 1

3) User="Chris" and Label="Fish" has a match order of 0

I'd like to be able to sort on the match order, where an order of 2 is better than 1, then within each match order I'd like to sort by date.

I'd ideally like to be able to do this in linq, but I'm willing to give straight SQL code a chance.

Answer 1

Coould you try:

from row in db.Dates
let x = row.UserName.Contains(username) ? 1 : 0
       + row.Label.Contains(label) ? 1 : 0
order by x descending
select new {row.Id, row.UserName, row.Label,
     Value = x};

If not, the same in TSQL using CASE WHEN ... THEN 1 ELSE 0 END