current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 3 down vote favorite

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

share|improve this question
1  
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? – svick Jan 21 '13 at 15:46
    
@svick tmp, tmp2 and tmp3 are basically the same string, but with different encoding. – Srcee Jan 21 '13 at 18:45
    
@svick myTable has a primary key which is int and autoincremented. It doesn't have indexes. – Srcee Jan 21 '13 at 18:46
1  
Can't you modify your database so that it used only one encoding? – svick Jan 21 '13 at 18:50
    
Also, are you performing this query in a loop, or something like that? – svick Jan 21 '13 at 18:54

1 Answer 1

up vote 6 down vote accepted 
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();
share|improve this answer
    
Tnx for your answer. But will you pls explain how will this fasten the execution? – Srcee Jan 21 '13 at 18:43
    
@Srcee It will always make a single query, while your code might make two queries. Making smaller number of queries is usually more efficient. – svick Jan 21 '13 at 18:52
    
@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. – Olivier Jacot-Descombes Jan 21 '13 at 21:45
    
I wanted to fasten the time, so I guess your solution will work. – Srcee Jan 22 '13 at 7:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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