The Missing LINQ

Visual FoxPro's (VFP) Data Manipulation Language (DML) is one of VFP's most compelling features.

It is also the most obvious feature VFP developers miss in .NET languages such as C# and Visual Basic. However, Language Integrated Query (LINQ), a new query language for .NET developers, is a new feature in the upcoming releases of C# 3.0 and Visual Basic 9.0 that addresses these shortcomings.

LINQ's core features will seem very familiar to Visual FoxPro developers. LINQ provides the ability to execute SELECT statements as part of the core .NET languages, C# and Visual Basic. Anyone familiar with Visual FoxPro's query commands or T-SQL's SELECT syntax will find familiar commands and capabilities. However, LINQ does not aim to reproduce VFP/SQL Server features exactly. Instead, LINQ provides many unique features that go much beyond simple data query capabilities. Therefore, knowing other query languages is an advantage for developers who want to take advantage of LINQ, but at the same time, I recommend not getting too hung up on whether certain things are exactly identical to standardized SELECT-syntax. LINQ is a separate language with different features and somewhat different syntax.

A Feature Overview

So what exactly does LINQ do? Let me put it this way: The very first time I got a private introduction to LINQ quite some time ago, Anders Hejlsberg (the "father of C#") told me the goal was to create query abilities inside of C# and Visual Basic that could “query anything that has structure.” So what is it that “has structure”?

Well, in C# and Visual Basic, quite a lot as it turns out. First and foremost of course: data. This means that you can use LINQ to query data sources such as ADO.NET DataSets or SQL Server tables and views. But LINQ can query a lot more. XML also “has structure”. LINQ allows queries against any XML data source including an XML file or an XML string in memory. Objects also have structure. And of course, everything in .NET is an object. In fact, it turns out that LINQ is an engine that mainly queries objects, and features used to query “other” things, such as data or XML, are sitting on top of the object query engine.

Let's take a look at an example-an array of strings. Since both arrays and strings are objects in .NET, you can use LINQ to query from string arrays. Consider the following Visual Basic array of names:

Dim names As String()
names = New String(4)
names(0) = "Smith"
names(1) = "Snyder"
names(2) = "Baker"
names(3) = "Jonson"
names(4) = "Ballmer"

Or the C# equivalent:

string[] names;
names = new string[5];
names[0] = "Smith";
names[1] = "Snyder";
names[2] = "Baker";
names[3] = "Jonson";
names[4] = "Ballmer";

Using LINQ you can query from these arrays. First I'll show you an equivalent of SQL Server's SELECT *. In Visual Basic, you'll use this LINQ syntax to return all “fields” and all “records” from this array:

From name In names Select name

Or in C#:

from name in names select name;

As you can see, this is not exactly like a SELECT statement you know from VFP and SQL Server, but still similar. In T-SQL you would use this equivalent:

SELECT name FROM names

You can see two main differences between these simple LINQ selects and the simple T-SQL SELECT. First, the LINQ statement seems to be backward. While T-SQL specifies first what to select and then where to select it from, LINQ goes the opposite way by specifying the source (the “from” part) first. In the world of strong typing and IntelliSense, the LINQ approach makes more sense. From a functional point of view however, the result remains the same.

Second, T-SQL simply says “from names” while LINQ uses the seemingly more complex “from name in names” syntax. LINQ supports more possible sources than T-SQL. In T-SQL, “names” must be a table (or some equivalent source such as a view). In LINQ, the source could be any object containing other objects of any complexity. The above LINQ example specifies that within the “names” array, I expect items that I choose to each refer to “name”, allowing me to then use that “name” in various ways. In this very simple example LINQ queries the entire “name” into the result list, but in more complex examples (see below), LINQ can use the “name” item in different ways.

The LINQ examples I've shown you so far are not very exciting since the resulting list is exactly the same as the source array. However, I'll now spice things up a little bit. Consider this Visual Basic example:

From name In names _
    Order By name _
    Where name.StartsWith("S") _
    Select name

Or, once again, the C# equivalent:

from name in names
    orderby name
    where name.StartsWith("S")
    select name;

These queries return only the names that start with “S” and sorts the result set. You can see how to use each item (referred to as “name” in this case) as part of the overall syntax. Without the “name in names” syntax, you couldn't use name.StartsWith().

Now suppose I choose an array of complex objects instead of a simple string array, such as an array of customer objects, where each object has first and last name properties (among others). I might form a query like so:

From customer In customers _
    Order By customer.FirstName _
    Where customer.LastName.StartsWith("S") _
    Select New { customer.FullName, customer.Address}

In addition to the fact that this example uses properties on the “name” items, the actual “select” part of the statement is somewhat unusual. Instead of returning a list of customer objects, this example returns a list of new objects where each object in the list has “FullName” and “Address” properties.

Note: Since each LINQ feature that I'll discuss works equally well in Visual Basic and C# and the features have similar syntax, I will stop listing separate language examples.

Now I'll improve this example further by messing with the return value. Keep in mind that LINQ can return any object, allowing for much greater flexibility than you would typically expect from query statements. Consider this example:

From customer In customers _
    Order By customer.FirstName _
    Where customer.LastName.StartsWith("S") _
    Select New CustomerEditForm(customer.Key)

In this example, the result is a list of customer edit forms, each of which is instantiated with the primary key of the customer from the source list.

This example shows a very interesting ability of LINQ queries: The result set can consist of things that weren't even in the source. This is possible since LINQ has all the capabilities of .NET languages at its disposal.

Other Data Sources

To keep the initial examples simple I've only used arrays as the data source in my examples. LINQ allows you to easily envision other sources, such as collections. The limited scope of this article means that I cannot nearly do the possibilities justice, but consider possible sources such as the collection of controls on a form (query all controls and join them with some other data source for instance), or the list of currently running processes. Also note that it does not matter where the collection originates. It is possible, for instance, to query a collection of stock quotes returned from a Web service.

Most developers seem to instinctively associate LINQ with the ability to query tabular data from SQL Server or a DataSet. Considering that querying from such a data source is the main feature of most query languages, it is an understandable assumption, and that assumption is correct. (Albeit that assumption is often too limited. I want to make sure you understand that querying a conventional data source is just one possible case).

One variation of queries over conventional data sources are queries against data that already exists in a DataSet (regardless of where that data originated). The following C# query, which assumes that a DataSet named “dsCustomers” has been created beforehand, returns items from a table within a DataSet:

DataTable customers = dsCustomers.Tables[0];
var customerQuery = orders.ToQueryable();
var result = from c  in customerQuery
    where c.Field<string>("Name").StartsWith("S")
    select new {FullName = c.Field<string>("FullName")};

You may have expected different syntax. For instance, you must first retrieve a reference to the table within the DataSet you're interested in (DataSets are like in-memory database containers and can contain any number of tables). Then, you have to access that table as a “queryable” data source so you call the ToQueryable() method on the DataTable object. This is due to an implementation peculiarity of DataSets, which use .NET 1.1 style collections rather than generic collections. (If you are interested in the exact technical details: DataSets implement IEnumerable and not IEnumerable<T>, which is what LINQ is based upon).

In addition, standard DataSets do not expose individual fields in a strongly typed fashion, but instead, every field in a DataSet is of type “object” (which can be seen as a generic type). However, in order to query certain field types such as strings in a meaningful way, you must know their type. An “object” typed field, for instance, does not have a StartsWith() method. To solve this problem, you can either cast the type to something meaningful on the fly and also deal with other related issues such as checking for null-values, or, you can use the generic Field<T>(fieldName) method, which allows you to directly specify the type of the field in question (such as `.Field(“Name”)``).

Note: See the sidebar “Generics”.

Much of what I said here only applies for default DataSets. Typed DataSets (DataSets with a defined structure), on the other hand do, not have these problems. Therefore, if you used a typed DataSet in this example you could have used the following syntax:

var result = from c  in dsCustomers.Customers
    where c.Name.StartsWith("S")
    select new { c.FullName };

LINQ to Databases

An extension to LINQ known as LINQ to Databases, allows a completely different way of accessing data. LINQ to Databases allows direct queries into SQL Server databases.

Of course, as mentioned above, LINQ always requires some sort of object-setup to perform queries. SQL Server (currently?) does not expose any of the database tables and fields as objects, so at least for the time being, you have to create client-side classes that represent tables in SQL Server. You can do this by hand, but typically you'll use Microsoft's LINQ to Databases designer that will integrate into Visual Studio. Figure 1 shows that tool in action.


Once SQL Server objects are exposed to LINQ by means of client-side .NET objects as demonstrated here, you can run LINQ queries against those data sources, as demonstrated in the following C# example:

NorthwindDataContext db = new NorthwindDataContext();
Table<Customer>customers = db.GetTable<Customer>();
var result = from c in�customers
                 where c.LastName.StartsWith("S")
                 select new {c.CompanyName, c.ContactName};

This example creates a data context and a DataTable object. Consider this conceptually like opening a connection to the database and the table you are interested in. Note that I say conceptually, because in reality, LINQ doesn't open a connection until the actual query executes. However, LINQ needs these first four lines of code to figure out where the actual data source resides.

The actual query is a LINQ query using standard C# syntax (or standard Visual Basic syntax if you choose to write in Visual Basic). The actual query that runs on SQL Server, however, is executed in standard T-SQL syntax. The above example results in a server-side T-SQL query similar to the following:

SELECT CompanyName, ContactName
    FROM Customers
    WHERE LastName LIKE 'S%'

The “translation” of the queries happens by means of a fascinating technology known as expression trees. A complete discussion of expression trees is beyond the scope of this article (for more details, visit www.code-magazine.com and www.VFPConversion.com). However, the short conclusion to the long story is that LINQ's expression trees allow you to execute any expression that you can form in C# or Visual Basic that is sensible for queries on SQL Server.

XML Support

LINQ has a special dialect known as LINQ to XML that you can use to query and create XML. Like LINQ to Databases, LINQ to XML also needs to represent XML in some sort of objectified fashion. For this purpose, LINQ to XML provides a few new classes for the specific purpose of creating and parsing XML. Think of these classes as an alternative to the XMLDOM and other XML parsing mechanisms.

Two of the main classes for LINQ to XML's XML parsing are the XElement and XAttribute classes. The following C# example takes an in-memory XML string and loads it into an XElement object:

XElement customers = XElement.Parse(
    @"<customers>
        <customer>
          <name>Smith</name>
        </customer>
        <customer>
          <name>Jones</name>
        </customer>
      </customers>");

Once you have XML available inside an XElement object you can use it in LINQ to XML queries:

from c in customers.Descendants("customer")
    select c.Element("name").Value;

You can also use LINQ to XML to create XML on the fly as the result set of queries. In C#, this happens by means of using XElement and XAtrtribute objects as the result set. Visual Basic goes a step further and supports XML directly as part of its native syntax. This Visual Basic example creates an XML string containing the names of all the files in the root directory:

Dim result As XElement = _
    <Files><%= From file In My.Computer.FileSystem.GetFiles("c:\") _
                   Where file.IndexOf(".") > -1 _
                   Select <File><%= file %></File> %>
    </Files>
Dim xml As String = result.ToString()

Of course, even in Visual Basic you can alternatively use XElement and XAttribute objects to achieve this result.

Above and Beyond

This short article hardly manages to scratch the surface of what's possible in LINQ and how powerful this new engine is. CoDe Magazine features several more articles on LINQ as well as related topics such as new C# language features. You can view these articles online at www.code-magazine.com/focus/vfp and www.VFPConversion.com.