Prioritizing one column over the other column of the same table I am creating an API in .net core and I just could not figure out how to put the following Linq expression into a single call:

public async Task&lt;ActionResult&lt;IEnumerable&lt;MyClass&gt;&gt;&gt; GetByReference(string reference)
        {
             var myClassPkColumn = _context.MyClass
                .Where(myClass =&gt; myClass.PkColumn.Equals(reference))
                .Take(1)
                .ToList();

            if (myClassPkColumn.Count &gt; 0)
            {
                return myClassPkColumn;
            }
            else
            {
                var myClassDescription = _context.MyClass
                .Where(myClass =&gt; myClass.Description.Contains(reference))
                .Take(8)
                .ToListAsync();
                return await myClassDescription;
            }
        }


In my front-end I am creating an autocomplete function and this now serves my intended purpose but I am curious if I could have made this into a single call (I guess for performance purposes).

If not possible then could I have written this in a cleaner way?

I hope that it is understandable that both columns are of type string and the first one I am querying is the primary key column (with 2-3 characters) while the second one is a non unique one with up to 50 characters.

The issue I had with the first approach:

var myClassResult = _context.MyClass
                    .Where(myClass =&gt; myClass.PkColumn.Equals(reference) ||
                     myClass.Description.Contains(reference))
                    .Take(8)
                    .ToListAsync();


was that the result was always prioritizing the Description column over the PkColumn intead of my desired expectation which is the other way around. Even after I added orderBy I couldnt get it to show me the pkColumn result when I know there was one.
I believe there is not much to improve in here.

If you really want to make a one call to sql server, then you can move this logic to sql procedure.

You can visually improve first query and use FirstOrDefault:

var myClassPkColumn = _context.MyClass.FirstOrDefault(x =&gt; x.PkColumn == reference);

if (myClassPkColumn != null)
{
    return new MyClass[] { myClassPkColumn };
}


If you know that your PkColumn is always less or equal 3 characters, then you can add if statement and skip first query completely in case your reference has bigger length.

if (reference?.Length &lt;= 3)
{
    // query by PkColumn column
}


If you want to combine two queries into one you can add this OrderBy:

var myClassResult = _context.MyClass
                    .Where(myClass =&gt; myClass.PkColumn.Equals(reference) ||
                        myClass.Description.Contains(reference))
                    .OrderBy(x =&gt; x.PkColumn == reference ? 0 : 1)
                    .Take(8)
                    .ToList();