Software Development: LINQ (Language Integrated Query) and SQL
LINQ provides database query and update capabilities. LINQ is a part of ADO.NET – which is used to manipulate Microsoft SQL Server data.
LINQ is an Object-Relational Mapping (ORM) 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.
LINQ 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.
LINQ in the real world
I have just finished converting an Accounting package from ADO.Net to LINQ to Entities. The conversion was quick and problem free. The code was simple and easy to structure with a good response time.
But maintenance was a headache - too much of the esoteric with the only real benefit being Typed fields.
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
- LINQ to Entities does not perform as well as SQL with ADO.Net – multiple joins are slow
- Where LINQ does not scale well, performance will need to be improved by reverting to ADO.Net with SQL
- Although very powerful, the LINQ to Entities query code can get complex
- 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
- Maintenance and support of older versions is difficult
- Documentation of the latest versions is sparse
The Learning Curve
Learning about LINQ is not easy – there are very few books on LINQ 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.
The Visual Basic coding for LINQ, when one eventually gets down to it, has been very much simplified without much 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.
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 reduce Database development time
- LINQ prevents SQL injection attacks with Client compile-time safety
- LINQ is easier to code and debug – problems are picked up at compile-time
- There are fewer complexities with the LINQ syntax than with SQL and Stored Procedures
- Maintaining both SQL projects and LINQ projects is an overhead
Words of Caution
Even though there are many advantages to using LINQ, introduce this new paradigm with some circumspection.
- For simple Create, Read, Update and Delete (CRUD) procedures, there is no noticeable difference in response times between LINQ and ADO.Net. But LINQ 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 LINQ complexity.
- Make sure that all Business logic is encapsulated in a separate Class.
- Make sure that the use of Option Infer is restricted to LINQ operations only.
Is LINQ the Future?
LINQ to Entities is an exciting new technology and could be particularly productive. Especially in a development environment that uses Object Oriented Programming (OOP) extensively.
I prefer to program using ADO.Net and SQL for speed, maintainability, maturity and simplicity.
SQL is well established as a standard, and whatever its deficiencies, it is more efficient and easier to use. It is extremely unlikely to ever be replaced by LINQ.
Update on LINQ development
LINQ has proven most popular
There have been two new releases. After a gap of nearly 9 years, EF 6.2 has been released. It contains bug fixes, some minor improvements, and incorporates community contributions.
And EF Core 2.0 has been released. The Entity Framework Core is lightweight and open source. It uses the new .NET Standard 2.0.
Neither product is ready for prime time.