how to bring the control out of the sql statement once the where condition is met in C#

 Stream stream = SystemStreams.Where(st => st.getName().Equals(s.OBJECT_NAME))
                              .Single();

in the above query in C# even if my condition of where clause is met my program reads all the records. due to which its开发者_开发知识库 becoming really slow. Can anyone help?

Just use FirstOrDefault() instead:

Stream stream = SystemStreams.Where(st => st.getName().Equals(s.OBJECT_NAME))
                             .FirstOrDefault();

or even shorter:

Stream stream = SystemStreams.FirstOrDefault(st => st.getName().Equals(s.OBJECT_NAME))

Note that FirstOrDefault() will return null if no match is found, otherwise the first item that matches.

Edit in response to comments:

In the SQL providers I have looked at Single() is translated to

SELECT TOP 2 bar,baz from foo where <some condition>

this means if there is only one match you still have to compare all records to try and find that second match - this is necessary because Single() must throw an exception if there is no match at all or more than one match.

FirstOrDefault() on the other hand gets translated to

SELECT TOP 1 bar,baz from foo where <some condition>

This means it can stop after the first match and return null or the record accordingly.

use SystemStreams.FirstOrDefault(query) instead of .Where

Its probably because your function getName() cannot be converted from an expression to a SQL statement, the whole record must be fetched and the where clause is evaluated locally (not on the database).