Language Integrated Queries (LINQ) and Visual Basic
The SQL language, used to manipulate Microsoft SQL Server data, was developed in 1974 and is showing its age. SQL has been continually extended. And as the SQL language has grown – so have the complexities.
LINQ is a better version of SQL – it is a part of ADO.NET and is integrated with VB.NET. It automatically maps the different data types of Visual Basic and SQL Server. LINQ now has the long missing option of using Visual Basic functionality in the data selection process.
LINQ provides statement completion, auto correction and IntelliSense. LINQ enables compile-time validation of queries so that bugs are caught before code execution. Strongly typed data provides greater reliability, fewer mistakes, easier debugging and faster development. LINQ creates code that is easy to understand and maintain.
There are many variations of LINQ – LINQ to Entities, LINQ to XML, and LINQ to Objects.
The Features of LINQ to Entities
- LINQ will optimize the code at compile and run-time
- A LINQ query can be bound to any List, Grid or Combo Box Control
- Fields are no longer treated as Objects – they now have full Type checking
- Queries can be easily iterated
- LINQ can handle all the SQL options, like Filtering, Sorting, Grouping, Totalling, Min, Max, etc
- LINQ has a more natural syntax than SQL to query and update a database
- LINQ simplifies query logic – no more complex Foreign Key Joins
- The logic for Adds, Deletes and Updates is much simpler than with ADO.Net
- LINQ uses Lazy Loading to initialise and use objects only when needed
- The underlying SQL query can be extracted from the LINQ query
But:
- LINQ to Entities does not perform as well as ADO.Net – yet
- LINQ to Entities is immature
- Complex queries loose strong typing – all fields become strings
- Error handling is primitive
These deficiencies are improved in VS2012.
The Learning Curve
Learning about LINQ is not easy – there are very few books on LINQ 2010 using Visual Basic. The Internet has mainly irrelevant documentation that show out-dated examples. And trying to make sense of complex C # examples is just a waste of time.
The Visual Basic coding for LINQ, when one eventually gets down to it, has been very much simplified. This is mainly due to the Visual Basic « Option Infer » Type inference which eliminates Object Oriented complexities.
Partial Classes
LINQ to Entities uses partial classes and methods to allow update, validation and Business logic to be easily added in separate code modules. The logic of the Partial Class can be encapsulated in a Business Library.
The 3-Tier Architecture layers can still be split between a Presentation Tier, a Business Logic Tier (using a VB.Net Class Library) and a Data Tier (using LINQ). But all the logic takes place on the Client – the Server only handles Database processing.
From the IT Manager’s perspective
- LINQ can significantly reduce Database development time
- LINQ prevents SQL injection attacks with Client compile-time safety
- LINQ is easier to maintain and debug – problems are picked up at compile-time
- There are fewer complexities with the LINQ syntax than with SQL and Stored Procedures
LINQ and Stored Procedures
LINQ is a much better platform for database development than Stored Procedures. There are no longer efficiency reasons for using Stored Procedures.
There are many reasons why Stored Procedures should no longer be used:
- The logic used by Stored Procedures is crude and esoteric
- Stored Procedures are difficult to create, debug, change and maintain
- The routines for debugging Stored Procedures are primitive
- Visual Basic with ADO.Net provides better error handling
- Data validation is far easier to maintain with Visual Basic
- Stored Procedures provide minimal protection from SQL injection attacks
- Source control is not available
- Stored Procedures are not cached and are not pre-compiled
- Stored Procedures are no longer faster and not better optimized
- Business logic should be in code and not in the database
A Note of Caution
Even though there are many advantages to using LINQ, introduce this new paradigm with some circumspection.
- LINQ to Entities is in its infancy. The number of changes between each new release of Visual Studio has been, and will continue to be extensive.
- Ensure that programmers code with simplicity and maintainability in mind. There is no advantage in substituting SQL and Stored Procedure complexity with Object Oriented complexity.
- Make sure that all Business logic is encapsulated in a Class.
- Make sure that the use of Option Infer is restricted to LINQ operations.
LINQ is the Future
LINQ to Entities is an exciting new technology and particularly productive. Expect major performance improvements when Visual Studio 11 is released.
