Exploring LINQ, SQLMetal and SqlTac

If you’re a .NET developer, working with or without a database on the back-end, your world is about to change. The emergence of LINQ and SQLMetal technologies will mark a fundamental change to your development approach to collections, and provide a simpler, more consistent way of accessing your database.

This article will provide you with an overview of these technologies, the details you need to get started, and links to more sources of information.

LINQ

The information in this article on LINQ can be compared to the first broad-brush stroke in the process of painting a landscape. Let’s start with the “5 Ws” executive summary.

What

LINQ stands for Language-Integrated Query. The LINQ technology provides a set of extensions to the .NET framework that allow developers to use the .NET language of their choice to issue queries against any data source. LINQ does not constrain you to just reading and writing records to and from your RDBMS. Your data source can be an XML file or a collection of objects in memory. It does not matter where or how the objects were loaded into memory.

LINQ’s great strength is that it offers a uniform approach to querying, via:

• LINQ to Objects – querying a collection of objects
• LINQ to SQL (originally called DLINQ)- for managing and querying relational data, as objects
• LINQ to XML (sometimes called XLINQ) – for querying XML

Why learn and master three approaches when LINQ handles them all?

Who

Microsoft developed the LINQ technology as a means for .NET Developers to query data sources from the .NET language (as opposed to SQL). Microsoft intends LINQ not to be “just another tool in the .NET developers’ toolkit”, but rather a fundamentally new approach to .NET development.

When

At the time of publication of this article, LINQ is available as a community technology preview. All samples in this article were written with May 2006 CTP. However, a March 20007 CTP has just been released by Microsoft. The code supplied in this article has been verified on both CTPs, but the article does not cover any of the new features of the March 07 CTP.

The .NET framework version 3.5 will support LINQ, and the 3.5 framework will be included with Orcas (the next release of Visual Studio). No official release date has been supplied by Microsoft but, unofficially, shipment is predicted in the second half of 2007.

Where

• Download Microsoft’s LINQ CTP – 2006 May
• Download Microsoft’s Visual Studio “Orcas” CTP – 2007 March

The May 06 CTP took me around 5 minutes to download and install. The March 07 “Orcas” CTP contains LINQ and many more new features and took me around 5 hours to download and install.

Why

LINQ incorporates some Functional Programming concepts, and addresses a broad range of development scenarios. I have found that the more I develop with it, the more places I would use it. Why? I can accomplish what I need to do in one statement, rather than a block of code. Like any new technology, there is an initial learning curve but ultimately LINQ allows a.NET developer to focus on what needs to be accomplished, rather than the details of how.

A simple LINQ application in two minutes

Here is a complete and simple LINQ example. After installing LINQ, you should be able to get this up and running in 2 minutes

• Download and Install Microsoft’s LINQ CTP – 2006 May
• Start Visual Studio 2005
• Choose Menu
• File | New | Project
• Select Project Type of “Visual C# | LINQ Preview”, then choose a Template of “LINQ Console Application”, Click “OK” (When using March 2007 CTP select “Visual C# | Windows”, then Template of “Console Application”)

• You will receive the following dialog, but just click “OK”. You will not receive this message with the March 2007 CTP and. Personally, I have not encountered any LINQ feature that worked incorrectly

• Open the Program.cs file that appears in the solution window

• Hit F5 to run this application.

TIP: If you are trying things out with LINQ and nothing seems to work, make sure you have added a using statement for System.Query at the top of your source module.

Coding in LINQ: developer details

I began my investigation of LINQ as replacement for a homegrown data access layer but I quickly realized that LINQ represented more than that. It represented a fundamental change in the way I would write code dealing with any collection. LINQ provides you with functionality that replaces the custom code you used to write over and over again. First let’s start by looking at details on changes to the C# and VB.Net languages.

New keywords to the .NET languages

In order to support LINQ, the var and from keywords have been added to the .NET languages. An example use of var is as follows:

The variable x is assigned a fixed type of an int by the compiler (implicitly typed local variable). Yes, many of us remember var from VB6 but the difference here is that the var keyword causes the compiler to determine a type, and the type of x is fixed and locked for its lifetime (in VB6, the variable could subsequently be assigned to a string or a float).

The following keywords can only be used in a from statement.

• in
• join
• where
• orderby
• group
• select

LINQ to objects: querying a collection of objects

LINQ comes with many new operators used to perform queries against any collection. Rather than writing custom code, querying against an in-memory collection is performed in one standard library (LINQ). For the moment, it is best to think of these new operators as methods. All but two operators (Range and Repeat) are available to any collection.

I will focus on the basic form (syntax) for using the vast majority of the LINQ Operators:

Some operators do not take any parameters. Others are overloaded with 0, 1 or 2 parameters. Many of the operators that take one parameter require that parameter be a Lambda expression or a Predicate expression:

• A Lambda expression is essentially an anonymous function or, in other words, a function without a name. They are a more-compact replacement for anonymous methods in .NET 2.0.
• Predicate expression: Many operators require a Lambda expression that returns a Boolean value; this is referred to as a Predicate expression.

Quick sample:

Line 2 calls the Count() operator, supplying as parameter the Lambda expression, num => num < 5. There are two parts to this expression: the first part, num, is a name choosen by you. It is a name of a new variable which has a very limited scope. The second part, num < 5, is a predicate expression. It is an anonymous function with a return type of a boolean. So this line counts the number of items in the array named “nums” that have a value of less than 5. Those items are highlighted below:

The Count() operator, iterates over each item in the collection, calling the anomymous function once for each item in the collection. Each item in the collection is assigned to the variable num before the anomymous function is called. An internal variable is incremented every time the predicate expression evaluates to true, resulting in the variable result being assigned the value 4. Any of following operators can be called on an existing collection.

• Restriction produces subset of original collection
  • .Where(Predicate)
• Projection transform your collection to different type
  • .Select(Projection)
  • .SelectMany(Lambda)
• Partitioning produces subset of original collection
  • .Take(int)
  • .Skip( int)
  • .TakeWhile(Predicate)
  • .SkipWhile(Predicate)
• Ordering produces an ordered collection
  • .OrderBy(IComparer)
  • .OrderByDescending(IComparer)
• Grouping produces a group collection
  • .GroupBy(IEqualityComparer)
• Set product a new set from the original collection
  • .Distinct()
  • .Union(Collection)
  • .Intersect(Collection)
  • .Except(Collection)
• Conversion transforms the collection
  • .ToArray()
  • .ToList()
  • .ToDictionary()
  • .OfType<T>()
• Element returns one element from the collection
  • .First(Predicate)
  • .FirstOrDefault(Predicate)
  • .ElementAt(int)
• Quantifiers returns a subset of the collection
  • .Any(Predicate)
  • .All(Predicate)
• Aggregate returns an aggregate based on the collection
  • .Count(Predicate)
  • .Sum(Lambda)
  • .Count(Lambda)
  • .Min(Lambda)
  • .Max(Lambda)
  • .Average(Lambda)
  • .Aggregate(Lambda)
• Miscellaneous
  • .Concat(Collection)

Using FROM – code samples

Let’s take a look at a few very simple examples, in order to obtain a feel of LINQ and its power and simplicity. Later on, we’ll move on to examples of querying a database.

Selection

Outputs:

6
5
4
2
4

Filtering

Ordering

Outputs:

4
4
2

Projections

A Projection allows you to transform your collection into another collection, containing objects of a new type. The new type can be instance of an existing class or an anomymous type. I think projection is one of the highlights/strengths of LINQ.

Outputs:

n:5 O:6

n:4 O:5

n:3 O:4

n:1 O:2

n:3 O:4  

Grouping

Outputs:

Numbers (5) with Rmndr of 2
Numbers (4,1) with Rmndr of 1
Numbers (3,3) with Rmndr of 0

This highlights the advantage of using from to query collections, over the use of operators; namely that from supports the querying of one or more collections in a single statement via a join.

Must See:

• Download Source
• Channel 9 – Video

In addition to these, you should definitely check out the samples folder that is installed with LINQ (C:\Program Files\LINQ Preview\Samples\C#\SampleQueries). There is one solution containing a single application demonstrating over 300 samples. The samples are nicely organized, allowing you to see and run one sample at a time.

NOTE:
The samples that shipped with the May 06 CTP no longer appear to be present with the Orcas (Mar 07) CTP. At the time of publication, a set of updated samples for the Oracas CTP were available from Charlie Calvert’s blog: http://blogs.msdn.com/charlie/archive/2007/03/04/samples-update.aspx

SqlMetal and LINQ to SQL

Again, let’s start with the “5 Ws – Executive Summary”

What

LINQ to SQL is a replacement for ADO.NET to perform operations on your database. LINQ to SQL allows .NET developers to operate on the database using the same development approach as we saw before for dealing with in-memory collections. LINQ to SQL returns collections which are strongly typed. Strongly typed objects are backed by a corresponding pre-defined class.

A data access layer can be thought of as an object view of your relational database. In years past, people have coded their data access layer by hand, the result being that there was seldom any consistency between items created at the beginning, middle and the end of the project. Not anymore, SqlMetal will always produce a consistent data access layer:

• SQLMetal is a command line tool that can generate the data access layer in seconds.
• SqlMetal will produce either C# or VB.Net code.
• SqlMetal generates a strongly typed data access layer. This is great for reducing runtime error. Now those runtime errors pop up in the development cycle as compile errors, reducing stress on the QA dept, Support dept and upper management, later in the life cycle.

NOTE:
LINQ to SQL does not require you use SqlMetal to build your strongly typed classes. If you like to incur an excess amount of time and frustration, you can code them by hand, but SqlMetal makes them quickly and consistently.

Who

• SqlMetal and LINQ to SQL was developed by Microsoft as part the LINQ technology and will generally be used by:
• Developer responsible for the schema, or
• Database Administrator (DBA)
• .NET Developers needing to access information in a database

NOTE:
.NET developers will use LINQ to SQL together with the output generated by SqlMetal.

When

• Run SqlMetal just before releasing a new version of your schema to the development staff.
• It is important for each generated output to be assigned a unique version number. Additionally, it should be checked into source control.

Running the tool is quick and easy, but pushing the generated output to the development staff on a daily basis will likely result in extreme frustration and lost productivity. Batching modifications to the database schema will help minimize the number of times that development staff will be disrupted. It is important to synchronize the new version of the database schema with the output from SqlMetal. This is like a hand and glove, it is critical to know which glove in a pile of 30 or more go to the hand in question. Unfortunately, SqlMetal provides little help in knowing which of the 30 generated versions for SqlMetal goes with any particular instance of the database. Yes, when a database is under development and schedules are tight, it is not unheard of to have 30 or more versions of your database schema in a period of three months.

Where

• Download Microsoft’s LINQ CTP – 2006 May
• The SqlMetal executable for the May 06 CTP can, by default, be found in C:\Program Files\LINQ Preview\bin. For the March 2007 CTP, it’s C:\Program Files\Microsoft Visual Studio 9.0\SDK\v3.5\Bin

Why

SqlMetal builds a data access layer in seconds. The output is not just a first generation data access; SqlMetal’s output includes all defined relationships (based foreign keys) between your tables. SqlMetal will produce a class for each table and, optionally, classes for all views, stored procedures and user-defined functions.

Microsoft refers to these generated classes as business entities. Business entities are basically a thin, lightweight strongly typed container for data, with no associated database overhead and generally no business logic. Business entities can stand alone and apart from the database. This makes them ideal candidates to be passed between layers of your system. SqlMetal builds the classes as partial classes, allowing you to extend these classes. However, note that business entities are not to be confused with business objects. The most useful extensions of these classes (entities) are via interfaces. Find commonality and express it in those entities via interfaces. The most common error developers make when trying to extend these classes is to add methods which belong in a business object. See more on why not to do this later in this article.

SqlMetal can generate strongly typed interfaces for stored procedures and user-defined functions. From the developer point of view, it is now a complete breeze to call either a stored proc and/or a user-defined function.

The following are a couple of issues which will become evident when you start using a tool which generates your data access layer. A little pre-planning now will save you huge pain and suffering later.

Issuse #1: The quality of your database design, or the lack of it, is now propagated into the source code generated by SqlMetal, which will be used by all of your .NET development staff. Before SqlMetal, an experienced developer would hand code a class for each table, and in the process hide poor column and table names, since he or she seldom had authority to modify the database itself. SqlMetal does not allow you to just rebuild a single table at a time. Keeping hand coded classes up to date over the life a project is extremely painful. The answer is to correct the database design, the real source of the problem. It is more important now than ever to get a clean, crisp, and concise database design before imposing it on the .NET developers. I cannot overstate the importance of this.

If you have a good database design, then SqlMetal will expose its robustness. Similarly, a poor database design will translate to the generated code. SqlMetal has nothing to assist you in measuring the quality of your database design. However, one such tool, SqlTac by CaberComputing, Inc. is designed to do this.

Issue #2: Planning on obfuscating your source code to protect your investment? LINQ to SQL uses reflection to perform its magic. Some planning upfront will save you a huge amount of heartache later. You know, “later” being the week before your product is supposed to ship, and everyone is already pretty tense and nothing is going as planned. Understand what you need to obfuscate before starting. Obfuscate the business logic that is contained in your business objects. Do not obfuscate your business entities, since they contain only data and not your secret sauce, and especially since LINQ to SQL uses reflection and will not recognize the mangled names. Yes, practice and perfect your obfuscation early in the development life cycle. It has impact on other aspects as well (serialization, testing, QA, and support depts.).

Using SqlMetal: developer details

There are numerous command line options for SqlMetal. To see them all, open a DOS window at the command prompt enter SqlMetal with no parameters and press enter.

To generate source code from SQL database directly, execute the following:

SqlMetal generates a single source file (C# or VB.NET) containing entity classes, as discussed earlier, and a class which inherits from System.Data.DLinq.DataContext (System.Data.Linq.DataContext in the March 07 CTP). This new class is the conduit by which you retrieve objects from the database and submit changes back to it.

Using LINQ to SQL: Developer Details

Having installed LINQ and run SqlMetal with the above command line, you can create a new LINQ project in Visual Studio and start querying the data. NOTE: If you’re using the May 06 CTP, make sure you choose a LINQ Preview project template when creating a new project in Visual Studio 2005. For March 2007 CTP, there are no special templates for LINQ The AdventureWorks.cs file generated by SqlMetal contains definitions for many classes. The following examples will make use of the following classes/members contained in the AdventureWorks.cs (change DLInq to Linq in the class names for the Orcas CTP):

• AdventureWorks – Inherits from System.Data.DLinq.DataContext
• HumanResources.Departments – System.Data.DLinq.Table<Department>
• HumanResources.Department – Class(Entity by definition)

The following examples were written with the May 2006 CTP. There are minor differences to the source for the March 2007 CTP. Source for both CTPs and be found in the code download link at the start of this article. The differences are in the namespaces for LINQ and how SqlMetal builds table names.

Read

Add

Update

Delete

Benefits of Link to SQL over ADO.NET

LINQ to SQL uses ADO.NET under the covers but offer several additional benefits:

• Reduces complexity
  • No plumbing
  • Simpler – No more Open and Closing of connections
• Fewer lines of code
  • Fewer to write
  • Fewer to maintain
• Strong Typing
  • Compiler type checking of the expression
  • No need for lines of embedded TSQL in C# code
  • No brittle points prone to run time failures

Must See

• Download Source
• LINQ to SQL Documentation

SqlTac

5 Ws – Executive Summary

What

Caber Computing, Inc. has developed an application called SqlTac.

Every database from a design and development point of view has a lifecycle, Yes, it gets design, documentation, reviewed, changed; refinements and enhancements, tracking of modifications, cloned for test purposes, announcements / communicate of these changes,

SqlTac is the only tool on the market that supports the database’s lifecycle.

Who

Who in your organization will use this tool depends on your organization. Typically one of the following:

• Developer responsible for the schema OR
• Database Administrator (DBA)

NOTE: .NET developers, QA and Tech support can use the output generated by SqlTac.

When

The commercial version of SqlTac will be released shortly after LINQ technology is released by Microsoft.

Where

Caber Computing, Inc.

Why

SqlTac addresses the issues list below and many more:

Design: SqlTac will measure the quality of your current database design, identify specific items of concern and generate SQL statements to correct these design deficiencies.

Knowledge: Building a database which solves a business problem is a complex task, requiring a vast amount of brain power to create and maintain. Typically, the thoughts surrounding the guiding principles used to develop your database schema fail to get documented, and that knowledge is lost over time. People leave and memories fade. SqlTac allows you to capture your domain knowledge; to easily identify what pieces of your domain knowledge have not been recorded. SqlTac records your domain knowledge in your database, not by creating additional table(s), but using a standard SQL interface. Now your domain knowledge can be seen when using Microsoft SQL Server Management Studio. By storing this knowledge in your database, it will not get lost. Now, it is backed up whenever your database is backed up.

IntelliSense: Having now captured your domain knowledge, you need to take advantage of this fact, sharing this information to the .NET developers of your team. This information will be extremely valuable to all .NET developers. SqlTac can expose your domain knowledge to the .NET developer’s favorite development tool (VS Studio), via IntelliSense. .NET Developers live their life in VS Studio; having this knowledge in VS Studio is critical and provides huge value. SqlTac does this by incorporating it into SqlMetal’s generated output as XML style comments.

Help Files: SqlTac will allow you to build standard help files of your domain knowledge with just a few mouse clicks. That’s right, help files; share them with the QA and Tech support folks. Upper Management is always asking what your team has been doing for the last “n” months. Send them the help file; this will keep the dogs at bay.

Diffs: What new in this release; SqlTac will compare any two versions of a database and identify all additions, changes and modifications. Copy the information and paste to Word, Excel and/or Outlook then email it to the troops.

Validates: Ensure fundamentals; SqlTac will validate all SQL statements, and ensure they all compile. This includes all stored procedures, views, user-defined functions, check constraints and triggers.

Using SqlTac: developer details

Before running SqlMetal or SqlTac to produce your data access layer, there are a few steps I would recommend.

Step 1: Determine the quality of your database design by measurement. I would strongly suggest a database with a rating of less than 90percentneeds to be reviewed and have modifications performed before continuing. When it comes to a database design a 65 is not a passing grade. We all know the real world dictates releasing a less than optimal schema.

• Identify discrete and specific issues that need to be addressed.
• Address these issues by modifying (fixing) your database design.

Step 2: Make sure you have captured important domain knowledge relative to the changes made in Step1.

Step 3: Verify that the SQL statements in your database still compile. This includes all stored procedures, views, triggers, check constraints and user defined functions.

Step 4: Assign a version number to your schema. When you release the schema refer to it by the published version number.

Step 5: Run a Diff on the new version and the previous version of your schema. Capture the differences and distribute them to development, QA and your management, when you’re ready to release this new version.

Step 6: Check in to source control your schema and scripts to re-build the database. This would include scripts to load the reference data which ships with an empty database.

Step 7: Most source control systems allow you to place a label on your files. Place labels on all relevant checked in files. The label should contain the version discussed earlier.

Now that you’re ready to build your data access layer, use SqlTac in place of SqlMetal. SqlTac builds output which is fully compatible with SqlMetal. It will auto assign a version number to each build. When building the source code, SqlTac will extract your domain knowledge entered in Step 2 and include it as XML comments. This provides .NET developers with your domain knowledge at their fingertips, via IntelliSense in their favorite development environment, Visual Studio. With XML comments, you can generate Help files which can be given to QA, Tech support and upper management. Upper management is always wondering what’s happening, what’s taking so long, and so on – so throw them a bone. Give them a Help file.

After generating your data access layer,

• Re-compile and run your unit test,
• Re-compile and test your application