Attention: open in a new window. PDFPrint

Stepping into LINQ to SQL

A great capability of LINQ is that it makes it possible to use several datasources like SQL or XML the same way as List and Array elements for queries. This allows easy encapsulation of the datasource behind. Setting up LINQ to use an Microsoft SQL Server 2008 datasource is very straightforward. There are several ways to setup a project to use LINQ to SQL. The easiest way is using the O/R-Designer to create the LINQ to SQL classes.

Lets assume, we created a new database called "linqdemo" on our SQL Server and we use the following script to create our database and fill it with some information:

 

CREATE TABLE Manufacturer (

      ID int primary key identity(1, 1),

      [Name] nvarchar(255) not null

);

GO

CREATE TABLE Car (

      ID int primary key identity(1, 1),

      Color nvarchar(255) not null,

      MaxSpeed float not null,

      Manufacturer int REFERENCES Manufacturer (ID) NOT NULL

);

GO

DECLARE @LID INT;

 

INSERT INTO Manufacturer

            ([Name])

VALUES      ('SpaceCar3000');

SET @LID=@@IDENTITY;

 

INSERT INTO Car

            ([Color], [MaxSpeed], [Manufacturer])

VALUES      ('Green', 120.0, @LID);

INSERT INTO Car

            ([Color], [MaxSpeed], [Manufacturer])

VALUES      ('Red', 140.0, @LID);

INSERT INTO Car

            ([Color], [MaxSpeed], [Manufacturer])

VALUES      ('Yellow', 200.0, @LID);

 

INSERT INTO Manufacturer

            ([Name])

VALUES      ('RoadRacer');

SET @LID=@@IDENTITY;

INSERT INTO Car

            ([Color], [MaxSpeed], [Manufacturer])

VALUES      ('Blue', 240.0, @LID);

 

INSERT INTO Manufacturer

            ([Name])

VALUES      ('FairDrive');

SET @LID=@@IDENTITY;

INSERT INTO Car

            ([Color], [MaxSpeed], [Manufacturer])

VALUES      ('Black', 160.0, @LID);

 

 

Now we have a simple database with some nonsense data that we can use to generate our LINQ classes.

Go to your Project-Explorer and choose to add a new element to your project. Choose the LINQ to SQL-class and set its name to LinqDemo.dbml. Visual Studio will open the .dbml file and show you some designer display. Go to the Server-Explorer and add a new Databaseconnection, if not yet done. Select linqdemo as database for the new connection and open up the table file. Just drag the two created tables (car and manufacturer) to the LinqDemo.dmbl designer window to create two new LINQ-classes. You will notice, that the designer detects the foreign-key column automatically an arrow to the diagram.

Shows two LINQ classes in the O/R designer.

Now we are able to use LINQ to request information from our database. Lets select all colors of the cars of the manufacturer SpaceCar3000 like we did in this Some LINQ Experience Article:

            LinqDemoDataContext linqdemo =

                new LinqDemoDataContext(

                    "Data source=.; Initial catalog=linqdemo;"

                    + " Integrated security=SSPI");

 

            var colorsOfSC3000 =

                from car in linqdemo.Car

                where car.Manufacturer1.Name.Equals("SpaceCar3000")

                select car.Color;

 

            Console.Write("Colors of Cars manufactured by SpaceCar3000: ");

            foreach (String color in colorsOfSC3000)

            {

                Console.Write(color + " ");

            }

            Console.WriteLine(); 

 

So, you might notice, that the query looks very much the same as the query on the LinkedList in the article I noticed before. We also do not need to play aroud with joins as LINQ helps us defining the Manufacturer1 value that holds the joined reference. You could of couse use the ID of the reference by requesting car.Manufacturer instead and build your own join, but you do not need to do so.

For those who are scared about what LINQ is really doing on the database the use of the SQL Profiler might be useful:

exec sp_executesql N'SELECT [t0].[Color]
FROM [dbo].[Car] AS [t0]
INNER JOIN [dbo].[Manufacturer] AS [t1] ON [t1].[ID] = [t0].[Manufacturer]
WHERE [t1].[Name] = @p0',N'@p0 nvarchar(12)',@p0=N'SpaceCar3000'


So LINQ only wrappes the SQL Statement you would most likely execute to get the information out of your database.

Of course it also works the other way round so you are able to insert data into the database using LINQ to SQL as well. Just create the desired objects and add them to the tables they belong to and execute the SubmitChanges() command on your LINQ to SQL context. To add a new manufacturer and a car that is produced by this manufacturer just try the following code:

 

            Manufacturer nmanf = new Manufacturer

            {

                Name = "OldStuff"

            };

 

            Car ncar = new Car

            {

                Color = "Cyan",

                MaxSpeed = 30.0f,

                Manufacturer1 = nmanf

            };

 

            linqdemo.Manufacturer.InsertOnSubmit(nmanf);

            linqdemo.Car.InsertOnSubmit(ncar);

 

            try

            {

                linqdemo.SubmitChanges();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

 

The code is very self explaining:

  1. Create the objects to be added (a new Manufacturer-Object and a new Car object)
  2. Add them to the context object
  3. Execute SubmitChanges() on the context to write changes to the database

It is more efficient to submit all changings at the end of the process. To add multiple objects at a time there is the InsertAllOnSubmit-method. One interesting fact is that LINQ automatically resolves dependencies if you set the object reference instead of the referenced id (set Manufacturer1 instead of Manufacturer). After the execution we can request our new data from the database:

 

SELECT * FROM dbo.Car C INNER JOIN dbo.Manufacturer M

ON C.Manufacturer = M.ID;


And we can see that the new Car has been inserted into our database.

Result of inserting data using LINQ.

Updating data is just more easier as it works just like changing data within a collection. Just take a look at the following example:

            var cx =

                from car in linqdemo.Car

                select car;

            foreach (Car car in cx)

            {

                car.Color = car.Color.ToLower();

            }

 


The example changes all color values to lower-case. To submit changes again, you have only to call linqdemo.SubmitChanges();.

To delete data from the database use DeleteOnSubmit or DeleteAllOnSubmit.