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>?

• svick: Could you explain your logic? Why are you doing it this way? If there are rows with both tmp and tmp2 (which are bad variable names, BTW) as their name, why is it okay to get any one of them? What is the schema of your table? How many rows does your table have? Does it have any indexes?
• Srcee: @svick tmp, tmp2 and tmp3 are basically the same string, but with different encoding.
• Srcee: @svick myTable has a primary key which is int and autoincremented. It doesn't have indexes.
• svick: Can't you modify your database so that it used only one encoding?
• svick: Also, are you performing this query in a loop, or something like that?

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();

• Srcee: Tnx for your answer. But will you pls explain how will this fasten the execution?
• svick: @Srcee It will always make a single query, while your code might make two queries. Making smaller number of queries is usually more efficient.
• Olivier Jacot-Descombes: @Srcee: You did not specify what you wanted to have optimized (execution speed, code size, code maintainablity, others?). My solution will be faster, because it queries the database only once in any case.
• Srcee: I wanted to fasten the time, so I guess your solution will work.