The great thing about LINQ is that it gives you strongly typed objects to use in your code (with IntelliSense) and these objects map to existing database objects, Again, LINQ is nothing more than a thin facade over these pre-existing database objects, The following table shows the mappings that are between the database objects and the LINQ objects.
On the left side, you are dealing with your database, The database is the entire entity – the tables, views, triggers, stored procedures – everything that makes up the database, On the LINQ side of this, you have an object called the DataContext object.A DataContext object is bound to the database, For the required interaction with the database, it contains a connection string, it will manage all of the transactions that occur, it will take care of any logging, and it will manage the output of the data, The DataContext object completely manages the transactions with the database on your behalf.
Tables, as you saw in the example, are converted to classes. This means that if you have a Products table, you will have a Product class, You will notice that LINQ is name-friendly in that it changes plural tables to singular to give the proper name to the class that you are using in your code, In addition to database tables being treated as classes, you will find that database views are also treated as the same, Columns, on the other hand, are treated as properties, This gives you the ability to manage the attributes (names and ·type definitions) of the column directly.
Relationships are nested collections that map between these various objects, This gives you the ability to define relationships that are mapped to multiple items, It is also important to understand the mapping of stored procedures, These actually map to methods with in your code from the DataContext instance, The next section takes a closer look at the DataContext and the table objects within LINQ, When dealing with the architecture of LINQ to SQL, you will notice that there are really three layers to this – your application, the LINQ to SQL layer, and the SQLServer database, As you saw from the previous examples, you can create a strongly typed query in your application’s code:
This in turn gets translated to a SQL query by the LINQ to SQL layer, which is then supplied to the database on your behalf:
In return, the LINQ to SQL layer takes the rows coming out of the database from this query and turns the returned data into a collection of strongly typed objects that you can easily work with.
The DataContext Object
Again, the DataContext object manages the transactions that occur with the database that you are working with when working with LINQ to SQL, There is actually a lot that you can do with the DataContext object, In instantiating one of these objects, you will notice that it takes a couple of optional parameters, These options include:
¤ A string that represents the location of the SQL Server Express database file or the name of the SQLServer that is used
¤ A connection string
¤ A connection string
The first two string options also have the option of including your own database mapping file, Once you have instantiated this object, you are then able to programmatically use it for many types of operations.
One of the simpler things that you can accomplish with the DataContext object is to run quick commands that you write yourself using the ExecuteQuery<T> () method. For instance, if you are going to pull all the products from the Products table using the ExecuteQuery<T> () method, your code would be similar to the following:
In this case, the ExecuteQuery<T> () method is called passing in a query string and returning a collection of Product objects. The query utilized in the method call is a simple Select statement that doesn’t require any additional parameters to be passed in. Because there are no parameters passed in with the query, you will instead need to use the double quotes as the second required parameter to the method call, If you were going to optionally substitute any values in the query, you would construct
your ExecuteQuery<T> () call as such:
IEnumerable<Product> myProducts = dc.ExecuteQuery<Product>(‘SELECT ∗FROMPRODUCTS WHEREUnitsInStock > (O)’, 50) ;
In this case, the (0) is a placeholder for the substituted parameter value that you are going to pass in, and the second parameter of the ExecuteQuery<T> () method is the parameter that will be used in the substitution.
The Connection property actually returr .in instance of the System, Data Sql Client, SqlConnection that is used by the DataContext object, This is ideal if you need to share this connection with other ADO.NET code that you might be using in your application, or if you need to get at any of the SqlConnection properties or methods that it exposes. For instance, getting at the connection string is a simple affair:
NorthwindDataContext de = new NorihwindDataContext();
If you have an ADO.NET transactionthatyou can use,you are able to assign that transaction to the DataContext object instance using the Transaction property, You can also make use of transactions using the Transaction Scope object that is from the .NET 2.0 Framework:
In this case, the Transaction Scope object isused and if one of the operations on the database fails, everything will be rolled back to the original state.
Other Methods and Properties of ttre DataContext, Object
In addition to the items just described, a number of other methods and properties are available from the DataContext object, The following table shows some of the available methods from DataContext.
In addition to these methods, the DataContext object exposes some of the properties shown in the following table.
The Table<TEntlty> Object
The Table<TEntity> object is a representation of the tables that you are working with from the database, For instance, you saw the use of the Product class, which is a Table<Product> instance, As you will see throughout this chapter, a number of methods are available from the Table<TEntity> object. Some of these methods are defined in the following table.