# Prioritizing one column over the other column of the same table [closed]

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<ActionResult<IEnumerable<MyClass>>> GetByReference(string reference)
{
var myClassPkColumn = _context.MyClass
.Where(myClass => myClass.PkColumn.Equals(reference))
.Take(1)
.ToList();

if (myClassPkColumn.Count > 0)
{
return myClassPkColumn;
}
else
{
var myClassDescription = _context.MyClass
.Where(myClass => 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 => 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.

• Can you show your orderby code? Something like this .OrderByDescending(myClass => myClass.PkColumn.Equals(reference)) before your take should give you what you are looking for – CharlesNRice Mar 2 '20 at 14:18
• Generally we like to see whole classes to review, there is not quite enough code here to do a good review. – pacmaninbw Mar 2 '20 at 15:35

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 => 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 <= 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 => myClass.PkColumn.Equals(reference) ||
myClass.Description.Contains(reference))
.OrderBy(x => x.PkColumn == reference ? 0 : 1)
.Take(8)
.ToList();

• It works but without the ternary conditional operator however, so basically what @charlesNRice said. I dont know what I messed up on my first attempt with orderBy but im glad I got it working now. Thanks to all who contributed! – Vrankela Mar 3 '20 at 14:19