Wizz’s Coding Solutions

It is very easy when you are familiar with SQL to constrain yourself to treating Linq queries in a similar fashion and not take advantage of some of the new features that Linq allows. Of course, Linq does impose its own constraints, as does each Linq provider, so it isn’t always easier.

It is definitely worth getting your head around the fact that in Linq, you can query a query.  With some providers, this might mean that you are querying the results of a query, but with others, it can mean that you are simply building another query, which will only actually be executed when you try to retrieve the results.

For example,

Dim q = From p in DataContext.People Where p.Surname=”Smith”

q = From p in q Where p.FirstName=”John”

In Linq to SQL, this is perfectly efficient as it will only run SELECT * FROM Person WHERE Surname=’Smith’ AND FirstName=’John’ or something similar.

Equally, you can also combine multiple queries into a single statement, such as…

Dim q = From p in DataContext.People Select p.ID, p.Surname Order By p.Surname Select ID

…and of course you don’t need to worry about the order of the statements – notice a Select can come after an Order By!  The thing to remember is that each Select/Group effectively creates a different context (as in a variable context), so you have different variables available at each point in the query.

Bookmark this post

A Linq query will always start with From item in datasource

When I initially started using Linq, I thought that was a little odd, and why didn’t Microsoft just use SQL style Select columns from datasource.  I thought about this for a minute, and realised that the problem with that is that you can’t use Intellisense to get the column names because you don’t actually know what they are until you’ve specified the data source.

Another reason for this is that the Select part of a Linq query is not compulsory, so it makes more sense to start with the part that is.

You might find it a little confusing having to specify item if you are thinking in SQL terms, as you would usually just include a table name and possibly an alias in SQL.  The point in item is that a Linq query is a bit like a For loop in a single statement.  You are effectively looping around the items in the data source.  An individual provider might not actually implement it that way (Linq to SQL doesn’t – if you use a Where clause, it is translated to SQL instead of retrieving all the data and checking what matches), but that is the abstraction that we are using.  We don’t reference the item by using the datasource name because we might still want to access something else in the datasource separately in the query.

Bookmark this post

Linq is an abbreviation for Language Integrated Query.  The idea of it is that you can write a query on a source of data inline as part of your code.  It is particularly clever because Linq allows different providers to be used so that different types of data can be queried.  There are Linq providers that are part of the .NET Framework for querying anything based on IEnumerable (including Lists, Collections, Arrays, etc.), XML, SQL Server databases (using Linq to SQL). 

There are also providers available for querying Oracle, MySQL, various webservices (one of the earliest ones I came across was Linq to Amazon).  It is a very powerful system with lots of flexibility.

Another point about Linq is that you can build queries on top of queries and depending on the provider, it only compiles and executes the final query when you try to access the data.  For example, you could do a query like

Dim people = From p in DB.People Where p.FirstName=”John”

people = From p in people Where p.Surname=”Smith”

Once you try to access people, (eg. by looping through it), Linq will execute an SQL query something like

SELECT [ID], [FirstName], [Surname] FROM [Person] WHERE [Person].[FirstName]=’John’ AND [Person].[Surname]=’Smith’

Bookmark this post