Optimization of code for searching in db

Question

This is my code:

var test = (from x in myDb.myTable
            where (x.name == tmp || x.name == tmp2 || x.name == tmp3) && x.unit == u
            select x).FirstOrDefault();

if (test == null)
    test = (from x in myDb.myTable
            where (x.name == tmp || x.name == tmp2 || x.name == tmp3)
            select x).FirstOrDefault();

How to optimize it>?

Answer 1

var test = (from x in myDb.myTable
            where (x.name == tmp || x.name == tmp2 || x.name == tmp3)
            select x)
           .AsEnumerable()
           .OrderBy(x => x.unit == u ? 0 : 1)
           .FirstOrDefault();

I removed the x.unit == u condition. Instead I sort the items to make the ones where this condition would be met to appear first. FirstOrDefault then makes the rest.

I split the EF part from the LINQ-to-objects part with AsEnumerable() as I am not sure if EF can translate the order by to SQL. If it can, you can try this

var test = (from x in myDb.myTable
            where (x.name == tmp || x.name == tmp2 || x.name == tmp3)
            orderby x.unit == u ? 0 : 1
            select x)
           .FirstOrDefault();