0
\$\begingroup\$

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.

\$\endgroup\$
2
  • 2
    \$\begingroup\$ 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 \$\endgroup\$ – CharlesNRice Mar 2 '20 at 14:18
  • 1
    \$\begingroup\$ Generally we like to see whole classes to review, there is not quite enough code here to do a good review. \$\endgroup\$ – pacmaninbw Mar 2 '20 at 15:35
1
\$\begingroup\$

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();
\$\endgroup\$
1
  • \$\begingroup\$ 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! \$\endgroup\$ – Vrankela Mar 3 '20 at 14:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.