[Tutorial] LINQ data query language Part 1 - Setting up a project

This is the first part of a series that is dedicated to LINQ, which is a new data query language that was introduced with .NET framework 3.0. It facilitates and generalizes working with different kinds of data sources (LINQ To SQL, LINQ To XML, LINQ To Entities, etc).

This part shows how to configure a project for using LINQ and serves as fundament for all following parts. A console project is used for simplification but LINQ can be used in any type of project. The AdventureWorksDB is used for all data queries.

• Add a new data connection to the project using the Server Explorer
  
  
• Add a new item of type LINQ to SQL Classes to your project.
  
  
• Some new files will be auto-generated and added to the project.
  
  
• Open the dbml file and add some tables from the data source using the Server Explorer by dragging them to the design view of the dbml file.
  
  
  
• Initialize an object of the auto-generated AdventureWorksDataContext class and display some product data.
  
  
• Run the application and you will get the following output.
  
  

[Tutorial] LINQ data query language
Part 1 - Setting up a project

[Tool] LINQPad - Query Analyzer for LINQ queries

Visual Studio does not contain any sort of query analyzer for LINQ at the moment. You have to manually write test code if you want to test your queries before implementing them in your projects. LINQPad fills this gap and adds several additional features that you soon don't want to miss anymore.

The download for this tool can be found here:
http://www.albahari.com/LINQPad.exe

The homepage with further instructions for this tool can be found here:
http://www.linqpad.net

• The GUI has the look-and-feel of traditional query analyzers
  
  
• You can write and test your queries in different proamming languages including C#, VB and SQL
  
  
• You can display the LINQ processing instructions as well as the SQL queries that are executed in the background
  
  
  
  

[Tool] LINQPad - Query Analyzer for LINQ queries

[Tool] Un-associate file types in Windows Vista

Deleting a user file type association in Windows Vista is a difficult task. You can change file type associations easily but you cannot delete them without modifying the registry manually.

There is a tool that greatly facilitates and automates this task. The download for this tool can be found here:
http://www.winhelponline.com/articles/231/1/An-utility-to-unassociate-file-types-in-Windows-Vista.html

• Let's say that you accidentally associated a file extension with an application and that you want to delete the association.
  
  
• Run the unassociate tool, select the wrong file association and either delete the user association or delete the whole file type from the system. The necessary registry changes will be done automatically.
  
  

[Tool] Un-associate file types in Windows Vista

[Tutorial] ADO.NET Entity Framework Part 5 - Updating data in the data source

The last parts concentrated on how to query data from the DB using the Entity Framework. This part shows how to update data in the DB. You need to have the correctly configured project from the last part as prerequisite for the following example.

• Update the value of the property that you want to change (product standard cost for the example). Then call the SaveChanges() method. That's it! You now already have updated the value in the DB.
  
  
• Concurrency checks on properties can be activated. If there are concurrency problems on these properties (someone has modified the data at the same time you did) then there will be an optimistic concurrency exception.
• No more hassle with SQL statements that need to be modified anymore. The entity data model just needs to be updated if the data structure has changed.
• Custom verification logic to assure that updated data is conforming to certain business rules can be applied. The auto-generated classes in the data model are already prepared for this purpose. They are providing partial classes and partial methods that can be extended.
  
  
• Add a new class to the project (AdventureWorksEntities.cs for the example).
  
  
• Modify the code within the new class to implement the partial class Product and the partial method OnStandardCostChanging(...). Then add the constraint that the standard cost cannot be less than 0.
  
  
• Assign a value that is less than 0 in the main function of the project.
  
  
• Run the application and you will get the expected exception.
  
  

[Tutorial] ADO.NET Entity Framework
Part 5 - Updating data in the data source

[Tutorial] ADO.NET Entity Framework Part 4 - Creating inherited entities

The last part explained how to trace the automatically generated SQL statements when using the Framework in combination with LINQ. This part explains some basics on creating inherited entities. You need to have the correctly configured project from the last part as prerequisite for the following examples.

• Let's say that all products that have the Boolean MakeFlag attribute set to true are sold out and must be manufactured. The following example shows how to query these products from the DB.
  
  

You will have to write much verification code, if you want to do special operations on products that are sold out. But there is a much more elegant way of solving this task. Using the Entity Framework you may create virtual/inherited entities that only exist in your business layer.

• Add a new entity from within the model view of the ADO.NET Entity Data Model file ("AdventureWorks.edmx" for the example).
  
  
• Select as base type the entity that you want to inherit from and give the new entity a meaningful name (base type Product and name SoldOutProduct for the example).
  
  
• The new entity will be created in the model and will be automatically linked to the Product entity. Delete the attribute that you want to use as key for distinguishing a SoldOutProduct from a Product (delete the MakeFlag attribute from the Product entity for the example).
  
  
• Add conditions within the mapping details of the SoldOutProduct and Product entities (add a table mapping of SoldOutProduct to Product and add that if MakeFlag is true it is a SoldOutProduct if it is false then it is a Product for the example)
  
  
• You may now use the new SoldOutProduct entity in your queries and also in your project to add special operations and treatment.
  
  

[Tutorial] ADO.NET Entity Framework
Part 4 - Creating inherited entities

[Tutorial] ADO.NET Entity Framework Part 3 - Accessing data and using LINQ (2/2)

The last part showed how to use LINQ in combination with the Entity Framework. This part explains how to trace the automatically generated SQL statements that are executed in the background. You need to have the correctly configured project from the last part as prerequisite for the following examples.

• Cast the query object into an "ObjectQuery<Product>" for being able to access the "ToTraceString()" function.
  
  
• Run the application and you will get the following output.
  
  
• Now change the LINQ query so that there are additional constraints.
  
  
• Run the application again to compare the differences in the generated SQL statements.
  
  
  
• Now add a Lambda expression to see what type of SQL statements are generated in this case.
  
  
• Run the application and you will get the following output.
  
  
  

[Tutorial] ADO.NET Entity Framework
Part 3 - Accessing data and using LINQ (2/2)

[Event] Winwise Solutions Conference 2008

Winwise provides, together with Microsoft France, 15 technical sessions on current and upcoming Microsoft technologies. Our best architects, experts and consultants will be presenting and trying to give you a good overview of the future of software development in the Microsoft world.

Some of the MVPs that will be presenting are:

• Roch Baduel
• Gaëtan Bouveret
• Thomas Lebrun
• Matthieu Mezil
• Sébastien Picamelot
• Florent Santin
• Michel Perfetti
• Philippe Sentenac

The exact program can be found here:
http://wsc08.winwise.fr/sessions

Please inscribe yourself here:
http://wsc08.winwise.fr/Inscription


The conference is taking place the 10 September 2008 from 09:30 to 18:30 in Paris and is free of charge. Don't miss it, if you are near Paris at this date!

[Event] Winwise Solutions Conference 2008

[Tutorial] ADO.NET Entity Framework Part 2 - Accessing data and using LINQ (1/2)

The last part of the series described how to set up a project for using the Framework. This part shows how to easily query information from the configured data sources. You need to have a console project with an ADO.NET data model that links to the AdventureWorks DB as prerequisite for the following examples.

The first example shows how to easily display information from within the DB with very little code.

• Initialize an object of the auto-generated AdventureWorksEntities class and display some product data.
  
  
• Run the application and you will get the following output.
  
  

This is the easiest but also the most inflexible way of accessing data from the configured entities. It will not really be sufficient if you need more control over the data. Using LINQ will give you everything necessary to really have maximum flexibility.

• The following example displays the same results as the last example but this time by using LINQ.
  
  
• A good practice is to externalize the creation of the LINQ query for better extensibility, readability and understanding. I also restricted the result to have just 20 elements since we don't need to query all products.
  
  
• Run the application and you will get the following output.
  
  

[Tutorial] ADO.NET Entity Framework
Part 2 - Accessing data and using LINQ (1/2)

[Tutorial] ADO.NET Entity Framework Part 1 - Setting up a project

This is the first part of a series of posts dedicated to the new ADO.NET Entity Framework, that was released with the latest service pack SP1 for Visual Studio .NET 2008. It can be used in any type of project, including WCF webservice projects, where data access is needed.

The Framework greatly simplifies the source code implementation and provides an abstraction layer to easily access data and execute data related operations. It becomes even more powerful when combined with LINQ.

This first part provides all steps necessary to configure a project for using the ADO.NET Entity Framework. Future parts will cover more advanced topics.

The download for Visual Studio .NET 2008 SP1 can be found here:
http://msdn.microsoft.com/en-us/vstudio/products/cc533447.aspx

The example is done using the AdventureWorks DB that can be downloaded from here:
http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

• Add a new item of type ADO.NET Entity Data Model to your existing project.
  
  
• A wizard will be displayed in which you may either select to generate the model out of an existing DB or to generate a blank model that you may separately map to a DB (select generate from a DB for the example).
  
  
• Select your data source for the data connection and select the DB you want to make available (select SQL Server and AdventureWorks DB for the example).
  
  
• Accept the suggested configuration and select the DB objects that you want to make available (select only tables for the example).
  
  
  
• The necessary files, references and data structures are now created and linked automatically. The file "AdventureWorksDesigner.cs" contains the auto-generated classes in this example. The App.Config file contains the connection string to the DB. The project is now prepared for the use of the Entity Framework!
  
  
  

[Tutorial] ADO.NET Entity Framework
Part 1 - Setting up a project