usefulBee usefulBee - 11 months ago 42
C# Question

LINQ: Most Efficient Where Condition When Selecting a Single Item out of a Million

The assumption

There is around a million items in database and a stored procedure that
returns all items.

Without changing the stored procedure, what is the most efficient where condition to return a single item?


var item = db.GetInfo().Where(i => i.url == url).Select(i => i.ID);
var item = db.GetInfo().Where(i => i.ProductType == 5 && i.url == url).Select(i => i.ID);

Answer Source

most efficient where condition to return a single item

With the assumption that the SP "returns all items", if all you want is the first item that answers a condition then just use FirstOrDefault. If such an item exists it will return it the moment it is found. In the worst case it will be in o(n)

var item = db.GetInfo().FirstOrDefault(i => i.url == url)?.ID;
var item = db.GetInfo().FirstOrDefault(i => i.ProductType == 5 && i.url == url)?.ID

Both queries have the same efficiency of o(n) - If this would be executed in the database the existence of indexes on these fields would have made a difference. For example if the ProductType field had an index then the second query would have been more efficient

With no doubt the better option will be that this will happen in database and not in memory... but if that can't be changed..