Custom Software Apps & SharePoint Consulting

Why use Linq instead of SQL?

To give you an idea of what Linq looks like, and how it compares to SQL code, below are some very simple queries in both SQL and Linq. This assumes a Customer table with columns ID and Name:

 

1111Linq queries are all strongly typed since classes are auto-generated to match your database schema. As a result, you also get intellisense all the way through composing a query. For example, in the above query, after typing “c.” you would get an intellisense popup showing the properties of the Customer class, just like regular coding in Visual Studio. This also means that if you change the name of a table or column in the database, you no longer have to hunt through all your stored procedures to change references to the old name, instead you can just follow the trail of build errors in your code. You can even be tricky by doing name refactor from the old name to the new name before updating the schema.Strongly Typed Queries

Automatic Joins on Foreign Key Columns

The benefit of strong typing is taken one step further by automatic joins on foreign key references. This can best be demonstrated with an example. Now, in addition to a Customer table with columns ID and Name, we’ll use a Purchase table with columns ID, Description, and (foreign key) CustomerID:

22222

Foreign key reference properties are also generated automatically in the other direction. So, you could get a list of all the Purchase descriptions associated with a Customer object customer using the following expression: customer.Purchases.Select (p => p.Description). ToList ()

The above expression uses the Linq extension method query style, instead of the special query syntax. The two query styles are functionally equivalent, and in fact the compiler first translates query syntax into extension method calls before compiling the code.

No More CRUD!

In the previous examples, the amount of code needed to perform queries in SQL versus Linq doesn’t look so bad, but of course you can’t just type that SQL code into your C# file, you have to generate a bunch of CRUD first. Then you have to use a bunch of adapter code just to get at the data. Linq queries can be immediately inserted into your code, no questions asked. But so far, we’ve only covered selects – what about inserts, updates, and deletes? Glad you asked, but in order to answer this, I need to introduce the DataContext class, since you need a sense of context in order to keep track of state.

Timothy Brehm
Senior Software Consultant @ Entrance
Timothy has 15 years of software development experience, 10 developing and supporting custom applications in the overall energy sector. His experience ranges from E&P all the way through the energy chain to refining and petrochemicals. He’s a committed bibliophile, board game aficionado, and works to resolve specific business issues with custom and vendor software, BI, and data integration needs.

Share this post with your friends

Share on facebook
Share on reddit
Share on twitter
Share on linkedin