Dabbling in LINQ to ADO.NET

I'll be doing a training seminar in Stockholm next month and I've been trying to create some compelling examples to illustrate various concepts.  Although it's keeping me busy, there's a  lot I've been working on that's blogworthy.

One concept in LINQ to SQL that's I am pretty fond of is the use of Entities. There's so much you can do with Entities that it would take quite a few posts to do them justice, but I'll show a simple example of how they can be used.  Assuming we're using SQL Server 2005 for this example, I have a database table with the following table definition:

GO /****** Object: Table [dbo].[Bank] Script Date: 01/05/2008 21:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Bank](
             [BankID] [int] IDENTITY(1,1) NOT NULL,
             [Name] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
             [URL] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
             [bigLogo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
             [smallogo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
             CONSTRAINT [PK_Bank] PRIMARY KEY CLUSTERED (
           [BankID] ASC )
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
          ,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)


          ON [PRIMARY] )
          ON [PRIMARY]
       GO
SET ANSI_PADDING OFF

So the first thing we need to do is create a class with the same definition (note, if you are using a member name that's the same as the column name in the db, no additional steps need taken. If you use different names, then you need to specify a Name value .  Field names are not case sensitive [meaning as long as they have the same spelling as the field in the db, the capitalization can be different]). So you use [Column(Name="DBColumnName")] public Type AliasNameYouWantToUse

Now, we have a Bank class which corresponds to the Bank table in the database.  For this sample, I created a static class with a static property named Globals and ConnectionString respectively.  Now, I compose a quick LINQ query to retrieve all of the data in the table except records where the Bank's Name is PNC:

The syntax is pretty straightforward on the LINQ side so it doesn't really need explaining - if you can't tell what it's doing though, it's selecting each value that doesn' t have 'PNC' as a name.  The Select statement is using a new C# language feature known as Anonymous types meaning that, a new type is being created without a traditional definition - that's why the new keyword is used.

When I first learned LINQ though, I wondered how this worked.  Was the whole table retrieved from the database and then records restricted via LINQ?  If so, this would seem very wasteful b/c by virtue of the restriction, you know you're only using a subset of the total values so why pull down everything?  Fortunately that's not what happens.  GetTable doesn't acttually pull down everything from the table - it's merely an instruction about how to generate the query it will use. As you probably have noticed, nowhere in here have we defined anything reminiscent of the CommandText property you'd use with a IDBCommand object.  To prove that you aren't pulling down a whole table as you might with the old TableDirect instruction, run profiler before this code block executes.  The results will show the following (assuming you are logging the same values).

Here's the resulting output:

exec sp_executesql N'SELECT [t0].[BankID], [t0].[Name], [t0].[URL]
FROM [Bank] AS [t0]
WHERE [t0].[Name] <> @p0',N'@p0 nvarchar(3)',@p0=N'PNC'

And just for the record, nothing before this ran a query on this table let alone one without a restriction.  The output for running this code is shown below:

This brings up one other point... I could have changed the Console.WriteLine to just write out one or more of the properties of 'row'.  In general, I make a rule of *always* overriding the ToString() method in any object I create just so that I can use it for meaningful information. With LINQ in particular, this becomes particularly useful b/c had I not used an anonymous type, (so the last line would read 'select b;' instead of 'select new {b.BankID, b.Name, b.URL}; I would have had the following useless output:

 I could get around that by formatting the output in the Console.WriteLine method but that's might inelegant. Instead, by overriding the ToString() method, I can get the following output instead:

<I''ve truncated several records just for the sake of readability>