LINQ and Visual Basic .Net
LINQ (Dot NET Language Integrated Query) provides
database query and update capabilities. LINQ is a part of ADO.NET – which is used to
manipulate Microsoft SQL Server data.
LINQ is a better version of SQL – it automatically maps the different data types of
Visual Basic and the SQL Server database. LINQ now has the long missing option of Visual Basic
functionality in the data selection process – Microsoft Access had this from inception.
LINQ now goes further – as well as using Visual Basic, it has IntelliSense when selecting
columns from a Table and it has strongly-typed variable checking.
There are many variations of LINQ – LINQ to SQL, LINQ to Entities, LINQ to XML, LINQ
to Objects. LINQ to Entities replaces LINQ to SQL which is no longer supported by Microsoft.
Using LINQ is a delight
I have just finished converting an Accounting package from ADO.Net to LINQ to Entities. The
conversion was quick and problem free. The code is simple, easy to structure and easy to maintain.
The resulting response time is good.
Nev
The Features of LINQ to Entities
- When a change is made to the database, the mapping files can be simply recompiled
- LINQ has a batch update feature
- LINQ will optimise the code at compile and run-time
- A LINQ query can be bound to any List, Grid or Combo Box Control
- DataSets and DataTables are no longer required – but a LINQ query may be converted to
an ADO.Net DataTable when needed
- 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
- SQL can be executed from LINQ
- LINQ can access different databases with the same syntax (but not yet Microsoft Access)
- LINQ has a more natural syntax than SQL to query and update a database or object
- LINQ simplifies query logic – no more complex Foreign Keys Joins
- The logic for Adds, Deletes and Updates is much simpler than with ADO.Net
- Lambda functions can pass values to LINQ
- Multiple Transactions are easily handled
- LINQ uses Lazy Loading to initialise and use objects only when needed
- Stored Procedures can be called from within LINQ
- The underlying SQL query can be extracted from the LINQ query
But …
- LINQ to Entities does not perform as well as ADO.Net – multiple joins are slow
- In the situations where LINQ does not scale well, performance will need to be improved by
reverting to ADO.Net
- Although very powerful, the LINQ to Entities query code can get quite complex (well, different)
- LINQ to Entities is immature
- Some time may have to be spent optimising Queries for best performance
- Complex queries loose strong typing – all fields become strings
- Error handling is primitive
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 C# gibberish examples is just a complete waste of time. Hopefully
Visual Studio 11 will also be accompanied by adequate documentation.
The Visual Basic coding for LINQ, when one eventually gets down to it, has been very much
simplified
without any of the Object Oriented complexities. This is due mainly to the Visual Basic 'Option
Infer' Type inference.
There are many examples available from Microsoft on the new syntax for Joins, Grouping, etc
that make the programmer's life easier.
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 additions
can be encapsulated in a Business Class Library.
Each LINQ context has a SavingChanges Event fired for each Added, Modified or Deleted Record.
This can ensure that the Business rules and restrictions are always adhered to.
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 Tier 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
Words 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.
- For simple Create, Read, Update and Delete (CRUD) procedures, there is no noticeable difference
in response times between LINQ and ADO.Net. But performance can degrade with complex Selects
and large table reads.
- 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.