What is different linq to sql where clause

Question

what is the different between the two

1)

context.connection.open()

var albums = (from a in context.Albums
              where a.id == id
             select a);

context.connection.close()

2)

context.connection.open()

var albums = (from a in context.Albums
             select a);

context.connection.close()

var result = albums.where((a)=>{a.id == id});

Would it be faster using the first one

Answer 1

The where clause in the second looks to be improper syntax, aside from opening/closing the connection they should evaluate to the same code. That is to say the SQL will be generated and executed when the result set is actually enumerated over.

You could omit the rest of the code and simply write:

var albums = from a in context.Albums
             where a.id == id
             select a;

or

var albums = context.Albums.Where(a => a.id == id);

They will evaluate to the same exact thing when the results are enumerated.

Answer 2

There's a very slight difference due to what are called degenerate queries. Aside from the connect timing, the first is effectively calling:

var albums = contact.Albums
                    .Where(a => a.id == id);

whereas the second will have an extra Select call in it:

var albums = contact.Albums
                    .Select(x => x)
                    .Where(a => a.id == id);

Having said that, I would certainly expect LINQ to SQL to issue the same SQL in both cases.

Answer 3

Quintin is right. There would only be a (huge) difference if you would immediately evaluate the query:

var albums = (from a in context.Albums
              where a.id == id
              select a)
             .ToList(); // force query evaluation
                        // 'SELECT * from Albums where id = ...'

versus

var albums = (from a in context.Albums
              select a)
              .ToList(); // force query evaluation 
                         // 'SELECT * from Albums'

var result = context.Albums.Where(a => a.id == id); // filters the list in memory

Answer 4

To understand why there is no difference, consider how the LINQ to SQL provider works. When you use tables off the DataContext, you are making use of the IQueryProvider. Each time you use a clause like Where, Select, OrderBy, and so on, you aren't executing anything - each of those expressions is just given to the IQueryProvider, and the IQueryProvider makes a big long list of clauses.

When you execute the query by iterating the results, those expressions are gathered up, combined together, and turned into a T-SQL query. So it doesn't really matter when you add the expressions or what order you add them in, so long as it happens before you foreach over the results.