SQL or LINQ to SQL or LINQ to Entities
SQL (Structured Query Language) is the standard language used to communicate with a relational database. It is used to update or retrieve data from a database.
LINQ (Language-Integrated Query) is a newer methodology for querying collections, which includes LINQ to Entities and LINQ to SQL.
LINQ to SQL is used to query Microsoft SQL Server databases. It maps the query into SQL and retrieves the results from the database.
LINQ to Entities is much like LINK to SQL, but with a Relational Mapper, it supports a large range of database software.
The potential of LINQ
When LINQ was first announced, the possibilities seemed great.
Here was a way to replace the complexities of the SQL (Structured Query Language) and Stored Procedures with a cohesive method of maintaining Database records using Visual Basic. LINQ uses simpler queries compared to the SQL Query Language. LINQ enables code to be written faster. LINQ will catch errors at compile time instead of at run time.
Has the potential for programmer productivity and system efficient technology been realised by LINQ?
LINQ to SQL: The Advantages and the Negatives
The benefits of LINQ to SQL are many:
- Visual Basic Functions can be used in record selection
- LINQ to SQL has strong data-typing providing quality and error free coding
- Queries are written in Visual Basic and the results are returned as strongly-typed objects
- LINQ to SQL has statement completion, auto correction and IntelliSense
- Syntax checking is done immediately when entering the Visual Basic code
- LINQ to SQL has automatic mapping to SQL Server database tables
- LINQ to SQL is tuned to SQL Server
- LINQ to SQL has support for Stored Procedures
- Set-up time is fast
- The learning curve has been dramatically reduced
- The latest Visual Studio release has greatly improved the quality of the software
There are few negatives when using the new paradigm:
- For simple CRUD (Create, Read, Update and Delete) logic, there are no noticeable inefficiencies. But for more complex logic, the programmer may need to use the older technologies.
- Documentation and literature are sparse
- There is a steep learning curve using the LINQ to SQL Query syntax.
The future of LINQ to SQL
With the advent of LINQ to Entities, Microsoft started pushing this as their key strategic direction. It is now the recommended data access methodology.
But LINQ to SQL is still being supported. .NET 4.0 contains a number of performance and usability enhancements, as well as updates to the class designer and code generation.
Enhancements to LINQ to SQL will continue but at lower pace – it is now a reasonably mature product.
LINQ to Entities: The Advantages and the Negatives
- It can map any object to any other object (SQL Server, Microsoft Access, Oracle, etc)
- It can handle different database vendors, protocols, services
- It more powerful and flexible than LINQ to SQL, with greater capabilities
The disadvantages of LINQ to Entities:
- The complexities are great and the learning curve is steep.
- Set-up time is high
- It is complex and less intuitive
- There are inefficiencies as the LINQ is not tuned to a specific database
- It is not a mature technology: Changes between versions are large and problems abound
The arrival of Visual Studio 2017 has brought about much greater stability in the LINQ technology.
LINQ to SQL is now a superb, mature and efficient technology. It provides huge productivity benefit for the Visual Basic programmer. Now that the rate of change has reduced, the new technology can be used with confidence.
The tried and trusted ADO.Net, with its ability to handle both SQL Server and Microsoft Access databases, is still the simplest and most efficient option. Using the same technology for both SQL Server and Microsoft Access facilitates development and lowers maintenance support costs.
SQL is well established as a standard, and whatever its deficiencies, it is still efficient and reasonably easy to use. It will take some time before it is replaced by LINQ.
All of the above is dependant upon a stable Visual Basic compiler. The latest VB compiler is untried and untested. Companies would be well advised to wait for the first service pack, before using the new compiler in a production environment.