Browse DevX
Sign up for e-mail newsletters from DevX


LINQ Into Microsoft's New Query Capabilities : Page 4

Query features have long been a cornerstone of database applications, but with LINQ, Microsoft introduces query language features right inside of C# and VB.NET.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The LINQ functionality I have introduced so far introduces query features to an object-oriented environment. Nevertheless, you'd find it useful if you could use the same functionality and feature set seamlessly against "real" databases. DLINQ provides this type of functionality. DLINQ is a set of special classes provided in addition to regular LINQ features. DLINQ will provide object-oriented representations of database objects such as tables and fields. Listing 2 shows a DLINQ representation of the Customers table in the Northwind SQL Server demo database. Note that the class itself is just a standard C# class, but the attributes are DLINQ attributes. You can use them to map relational data onto objects and to express information that is not available in C# (such as a field being of type "nchar(5)").

Once database objects are represented in object notation, you can use LINQ to query data directly from the database. To do so, you first open a connection to the database. In DLINQ you'll do this through a DataContext object, which is conceptually very similar to a database connection. Once the context is established, the table mapping class has to be instantiated. DLINQ does this through a Table<> generic that is typed as the special mapping class. With these objects in place you can use standard LINQ syntax. The following example queries all customers from the SQL Server Northwind database whose company name starts with an "A".

DataContext context = new DataContext("Initial Catalog=Northwind;" + "Integrated Security=sspi"); Table<CustomerTable> customers = context.GetTable<CustomerTable>(); var result = from c in customers where c.CompanyName.StartsWith("A") select c;

Note that the syntax used in the actual query is standard C# LINQ syntax. SQL Server does not support methods on field names, nor does it support StartsWith() in any way. Nevertheless, this works perfectly fine. The WHERE clause in this statement is internally handled as a lambda expression by the C# compiler (see above). One of the advanced features of lambda expressions is the ability to compile them either as IL (Intermediate Language) that can be executed on the CLR, or to compile them as a pure data representation of itself known as an expression tree. Whether the compiler creates IL or expression trees depends on your exact use of the lambda expression. In all previous demos I've used in this article, the compiler would have compiled the lambda as IL. In this example, the compiler will create an expression tree.

Expression trees are language neutral since they are only data. This allows DLINQ to translate the expression into something the database can understand and thus, you can execute the example above on SQL Server.

To see how expression trees are represented in memory, you could take a lambda expression and assign it to an expression tree delegate, which forces the compiler to create an expression tree instead of IL code. You can then explore individual pieces of data within the expression tree.

Expression<Func<int,bool>> expr = para1 => para1 < 10; BinaryExpression body = (BinaryExpression)expr.Body; ParameterExpression left = (ParameterExpression)body.Left; ConstantExpression right = (ConstantExpression)body.Right; MessageBox.Show("Expression: " + left.Name + " " + body.NodeType + " " + right.Value);

The output of this code snippet is this:

Para1 LT 10

Some readers might be wondering whether that can really work with all possible expressions. Actually, yes. In a pure .NET environment, the list of possible expressions is unlimited, and some such expressions can be so complex that translations would be impossible. However, when running queries against a database you have a limited and well-defined list of possible expressions. After all, since you can only map SQL Server character fields to .NET strings, that limits the list of possible expressions to the features available for strings and character fields. The only exception is the ability to define custom field types in SQL Server 2005, but in that case, you'd create the custom fields using a .NET language, so no translation is needed.

Thanks for your registration, follow us on our social networks to keep up-to-date