DataContext is an object that corresponds to the relational database object by which all other objects are referred to or accessed. It takes a string or a connection object that implements IDbConnection as the parameter to connect to a particular database object. It takes care of translating the Language Integrated Queries into T-SQL queries to execute against the SQL Server 2000 or 2005 database, and then translating the results back to the calling application.

We can have the strongly typed DataContext, which has the definition of all objects in the database. It's not only used for accessing existing tables of the database, but is also for creating a new database. DataContext is a collection of all the objects of the database.

Following is the code example that refers to the Icecreams database and then points to the Categories table:

DataContext dataCon = new DataContext("Data Source=.sqlexpress;
Initial Catalog=IceCreams; Integrated Security=true");

This DataContext is not strongly typed; so, if we want to refer to a table in the database, we should use the GetTable method of the DataContext, and then refer to a table.

Table<Categories> categories = dataCon.GetTable<Categories>();

To avoid using this method of referring to the database table, we can use strongly typed DataContext:

IceCreams dataBase = new IceCreams("Data Source=.sqlexpress;Initial Catalog=IceCreams;Integrated Security=true");

We can make use of the web.config or app.cofig, depending on whether the application is web-based or desktop-based, to store the connection string and referring to that for the connection string parameter. The dataBase data context as shown in the above code, is a strongly typed DataContext which has all the table collections declared in it. A sample of the DataContext would look like this:

public class IceCreams: DataContext
{
public Table<Categories> Categories;
public Table<Items> Items;
public IceCreams(string connection) : base(connection) {}
}

The queries which use the above DataContext can directly point to the database tables without using the GetTable method.

The Icecreams DataContext contains three different table collections declared in it. All three tables should have it's definition with columns and it's attributes.

Before we go into details of other properties of DataContext, we will see what are entity classes and how we can use that to refer to the database objects.

Entity classes are the objects which represent the database tables. In the previous example, the table collections of the Icrecreams data context, contain three tables for which we need to add the definitions of each table with its columns and its attributes.

System.Data.Linq.Mapping is the namespace that contains the definition for all the attributes. We have to include this in the project to specify the attributes.

The definition of the Categories table would look like this:

[Table(Name = "Categories")]
public class Categories
{
private int categoryID;
private string category;
private string description;
[Column(Name= "CategoryID", IsPrimaryKey=true,
IsDbGenerated=true, DbType="int NOT NULL
IDENTITY",CanBeNull=false)]
public int CategoryID
{
get { return categoryID; }
set { categoryID = value; }
}
[Column(Name="Category", DbType="nvarchar(1000)")]
public string Category
{
get { return category; }
set { category = value; }
}
[Column(Name="Description", DbType="nvarchar(1000)")]
public string Description
{
get { return description; }
set { description = value; }
}
}

The class should be defined with the Table attribute with the Name property. The Name property value corresponds to the database table name. If not specified, it is assumed that the table name is same as the class name. Once the table is defined, the fields or columns of the table should be defined similarly. To define the columns, a name should be given, and in addition to that, we should also specify the exact type of the table column which corresponds to the T-SQL column declaration. There are other properties like IsDbGenerated to mention the field value that is auto-generated during record insertion.

All these properties are the same as the properties, declared by the T-SQL for the database objects. Some properties like type of the column and IsDbGenerated should be specified only while creating a new database.

The instances of classes declared as tables can be stored in the database. These instances are called entities, and the classes are called entity classes.

We will define the Items entity as follows:

[Table(Name = "Items")]
public class Items
{
[Column(Name = "ItemID", IsPrimaryKey = true, IsDbGenerated =
true, DbType = "int NOT NULL IDENTITY", CanBeNull = false)]
public int ItemID;
[Column(Name = "CategoryID")]
public int CategoryID;
[Column(Name = "Name", DbType = "nvarchar(1000)")]
public string Name;
[Column(Name = "Ingredients", DbType = "nvarchar(1000)")]
public string Ingredients;
[Column(Name = "ServingSize", DbType = "nvarchar(1000)")]
public string ServingSize;
[Column(Name = "TotalFat", DbType = "int")]
public int TotalFat;
[Column(Name = "Cholesterol", DbType = "int")]
public int Cholesterol;
[Column(Name = "TotalCarbohydrates", DbType = "int")]
public int TotalCarbohydrates;
[Column(Name = "Protein", DbType = "int")]
public int Protein;
}

All tables may not have auto-generated key fields. If the table has an auto-generated field, the insert operation should not insert any value to the field which has the IsDbGenerated property, set to true. In this case, we can restrict assigning values to the table columns. All columns should be defined as properties of the entity class. The identity or auto-generated column should not have any definition for the set method which will avoid setting any values to the property. Following is an example for creating the same Item class as above, but using smart properties. Smart properties are auto-implemented properties that do not have any private fields declared specifically.

[Table(Name = "Items")]
public class Items
{
[Column(Name = "ItemID", IsPrimaryKey = true, IsDbGenerated =
true, DbType = "int NOT NULL IDENTITY", CanBeNull = false)]
public int ItemID { get; private set;
}
[Column(Name = "CategoryID")]
public int CategoryID { get; set; }
[Column(Name = "Name", DbType = "nvarchar(1000)")]
public string Name { get; set; }
[Column(Name = "Ingredients", DbType = "nvarchar(1000)")]
public string Ingredients { get; set; }
[Column(Name = "ServingSize", DbType = "nvarchar(1000)")]
public string ServingSize { get; set; }
[Column(Name = "TotalFat", DbType = "nvarchar(1000)")]
public string TotalFat { get; set; }
[Column(Name = "Cholesterol", DbType = "nvarchar(1000)")]
public string Cholesterol { get; set; }
[Column(Name = "TotalCarbohydrates", DbType = "nvarchar(1000)")]
public string TotalCarbohydrates { get; set; }
[Column(Name = "Protein", DbType = "nvarchar(1000)")]
public string Protein { get; set; }
}

Attributes

We have seen some attributes and their properties for creating entity classes. There are a lot of other attributes and properties that support the creation of entity classes. These attributes are used by LINQ to SQL to create corresponding SQL queries in the database that relate to the entity objects. All attributes are defined in the System.Data.Linq.Mapping namespace.

Database Attribute

The database attribute is an attribute that specifies the database into which we should look for the objects and data. The database can also be specified by the connection. But if it is not specified by the connection, by default the name specified by the attribute will be taken as the database. This attribute can be applied on strongly-typed DataContext. Database attribute has a Name property which gives the name for the database.

[Database(Name="Deserts")]
public class Deserts: DataContext
{
public Table<Categories> Categories;
public Table<Items> Items;

The Database attribute is optional here. Deserts is the name of the database. If this attribute is not specified, by default the name of the Deserts DataContext class will be taken as the name of the database.

It is always better to use a connection string to connect to a specific database. The above example illustrates the usage of database attribute in LINQ to SQL.

Table Attribute

This is similar to database attribute. It refers to the individual table or view in the database. It can be applied on the entity class, which can refer to the database table or view.

[Table(Name="Categories")]
public class Categories
{
[Column(Name= "CategoryID", IsPrimaryKey=true,
IsDbGenerated=true, DbType="int NOT NULL IDENTITY",
CanBeNull=false)]
public int CategoryID{ get ; private set ; }
}

Categories is the entity class on which the Table attribute is applied, to specify the corresponding database table objects. If the attribute is not specified, the class will be taken by default as the table.

All classes that have the table attribute defined are considered as persistent classes by LINQ to SQL. The mapping is done for a single table only. Each entity class must be mapped to only one class. We cannot have multiple classes mapping to the same table in the database.

It is always good practice to use the same name as the database table for the entity class, or leave the name of the table attribute undefined and give the same class name to the database table object.

Column Attribute

In the Categories entity class given previously, we have a CategoryID column which represents the actual column of the database table. But to specify what type of column it is and what the behaviour of the column should be, we have different properties for the column attribute.

Property	Description
Name	This property is used to specify the name of the column. This property is optional. It takes the class member name as default if the name property is not mentioned.
Storage	This property is used to specify the variable or the object in which the column value is stored. By default, all the values are set by the public property of the class member. Using this property, we can directly access the storage member and can override the access method.
DbType	This specifies type of the database column. It is the same as the text used to define the column using T-SQL. If not specified, the same type will be taken as the one defined by the member of the entity class. DLINQ will take care of converting it to the equivalent T-SQL type.
IsPrimaryKey	This is a boolean property that specifies whether the column is a key column for the table or not. Each table will have a primary key that is unique to identify the table rows. This property is set to true if it is a part of the primary key. If more than one member has this property set to true, it means that the members are a part of the composite primary key.
IsDbGenerated	Usually, primary key values of the tables are auto-generated. It means that the value will be generated by the system whenever there is a new row inserted to the table. This property can be applied to the database column which has the primary key property set to true.
IsVersion	This is to specify the timestamp property of the column. The column having the timestamp property shows the version of the row. On every update that happens to a row of the table, the timestamp will get updated with a new value.
updateCheck	This is to detect the conflicts by optimistic concurrency. There is a timestamp or IsVersion=true property which gives the version of the row to identify the conflict. In case none of the columns are specified as IsVersion=true, then the version has to be identified by comparing the old value with the current value of the column/member. To specify which member should be used for detecting the conflicts by LINQ to SQL, the member should be given an updateCheck value. It has three different enumerated values. Always—always use this column for conflict detection. Never—never use this column for conflict detection. WhenChanged—use this column only when the value is changed by the application.
IsDiscriminator	This boolean value determines if the member holds a discriminator value for a LINQ to SQL inheritance hierarchy.
CanBeNull	This value can be set to true or false to indicate whether the column allows a null value or not.
TypeId	This is used to get the unique identifier when implemented in the derived class.
Expression	This is used to define the column which is a computed column in the database.

We have used different attributes and properties for members of the entity classes to define the database tables and the classes.

[Column(Name = "ItemID", IsPrimaryKey = true, IsDbGenerated = true,
DbType = "int NOT NULL IDENTITY", CanBeNull = false)]
public int ItemID { get; private set;}

The previous code shows the definition of the class member ItemID. It defines the ItemID as a primary key and is auto-generated. It also specifies that the member is an identity column of type integer and is an identity.

The column can also be specified as a property of the entity class. The value is stored in the private variable while the property is a public property. We can control the access of the member value by defining the storage as private. The set method definition for the property is present even though it is an auto-generated value. This is because the auto-implemented properties should define both get and set properties, shown as follows:

[Column(Name = "ItemID", IsPrimaryKey = true, IsDbGenerated = true,
DbType = "int NOT NULL IDENTITY", CanBeNull = false)]
public int ItemID { get; private set}

Association Attribute (Foreign Keys)

The association attribute refers to the relationship between tables, using foreign keys. Association property represents a single reference or collection of references to entity classes. These properties are given as follows:

Property	Description
Name	This property specifies the name of the property. This is same as the name that gets generated when we define the relationship between the tables in SQL Server Database. This name distinguishes the multiple relationships between the entity classes.
Storage	This is similar to the storage of the column attribute. It is also used to specify the name of the storage member for the property. It is used to directly interact with the value instead of going through the public property.
ThisKey	This property has a list of names of one or more members of the entity class that are a part of the relationship on this side of the entity class. If the members are not specified, the primary key members are taken as default for the relationship.
OtherKey	This property is similar to the Thiskey property but on the other side of the entity that makes up the relationship.
IsUnique	This is to impose a unique constraint on the foreign key to have a one-to-one relationship.
IsForeignKey	This specifies the member as a foreign key in the association relationship.

Relationships

In relational databases, tables are linked to each other by a relationship called foreign keys. This will bring the parent-child relationship between the tables. LINQ to SQL supports the creation of foreign keys between tables with the attribute called association. This association also brings the master detail relationship between the tables.

In the earlier section Entity Classes, we saw the concept of creating entity classes by creating Categories and Items classes. With these classes, we can create the database. We can say that each item in the Item table comes under a particular category. So here, Categories is the master for the Items detail table in the database. To represent that, we have foreign key relationships between the tables in the database. The same foreign key relationships should also be represented between these two classes. This can be done using EntitySet and EntityRef properties.

Since the relationship is one-to-many between Categories and Items table, the Categories entity class should have an EntitySet property for Items. EntitySet is a property which represents the set of entities that is of the same entity type. Here, Items is an entity set which represents the set of items that belongs to a category entity. This property should have the association attribute defined. This attribute defines the relationship between tables.

EntityRef is a property that represents the other end of a relationship. We have set the Items as EntitySet within the Categories entity class. The other side of the relationship, that is, the Items entity class, should also define its relationship with the Categories entity. EntityRef is used for giving the reference between the entity classes.

[Table(Name="Categories")]
public class Categories
{
[Column(Name = "CategoryID", Id=true, AutoGen=true,
DBType="int NOT NULL IDENTITY")]
public int CategoryID;
[Column(Name = "Category", DBType="nvarchar(1000)")] //,
UpdateCheck=UpdateCheck.Always)]
public string Category;
[Column(Name="Description", DBType="nvarchar(1000)")] // ,
UpdateCheck=UpdateCheck.Always)]
public string Description;
private EntitySet<Items> _Items;
[Association(Storage="_Items", OtherKey="CategoryID")]
public EntitySet<Items> Items
{
get { return this._Items; }
set { this._Items.Assign(value); }
}
public Categories() { this._Items = new EntitySet<Items>(); }
}
[Table(Name="Items")]
public class Items
{
[Column(Name = "ItemID", IsPrimaryKey = true, IsDbGenerated =
entity classespropertiestrue, DbType = "int NOT NULL IDENTITY", CanBeNull = false)]
public int ItemID { get; private set; }
[Column(Name = "CategoryID")]
public int CategoryID { get; set; }
[Column(Name = "Name", DbType = "nvarchar(1000)")]
public string Name { get; set; }
[Column(Name = "Ingredients", DbType = "nvarchar(1000)")]
public string Ingredients { get; set; }
…
…
…
[Association(Storage = "_Categories", ThisKey = "CategoryID")]
public Categories Categories
{
get { return this._Categories.Entity; }
set { this._Categories.Entity = value; }
}
public Items() { this._Categories = new EntityRef<Categories>(); }

You can see the EntitySet<Items> private variable, which refers to the detail, entity class Items. The definition for the entity set has the association attribute added to it. This attribute has the property, OtherKey, added to it. It refers to the primary key in the database table which corresponds to this entity class, and is compared with the related entity class. There is also a property called ThisKey which refers to the key field in the current table. If not specified, it automatically refers to the primary key of the table.

The Items table, will refer back to the Categories table using the EntityRef class. The association attribute of the Categories property has the ThisKey attribute which refers to column on this entity class. The attribute also has a property called Storage that shows which private member holds the value of the property. If not specified, the public accessor will be used by default. This is also used by the column property.

Both the entity classes have a constructor which is defined to create the EntitySet object in the Categories class, and to initialize the EntityRef object in Items entity class.

Function Attribute

This attribute is to specify the method in the DataContext which will be translated as a call to a database stored procedure or a user defined function. This attribute has the parameter which specifies the name of the actual database stored procedure or user-defined function.

Property	Description
IsComposable	This is a boolean value. False indicates mapping to a stored procedure in the database. True indicates mapping to a user-defined function in the database.
Name	This is of type string which represents the name of the stored procedure or the user-defined function in the database.

Parameter Attribute

This attribute is used to refer to the parameters of the stored procedure or function in the database. This attribute has two properties:

1. Name—specifies the name of the parameter, stored in a procedure or a function in the database. If not specified, the parameter is assumed to have the same name as the method parameter. In the example given under the stored procedure attribute section, the method has the parameter attribute with the name as Category and the method has a parameter Category.

2. DbType—this is to specify the type of the parameter. If not specified, it will be translated according to the type specified by the method parameter.

Inheritance Mapping Attribute

This represents the inheritance hierarchy for the entity classes. Classes can inherit from another class. Inherited classes, or derived classes, take advantage of gaining all the non-private data and characteristics of the base class they are derived from. A derived class also includes its own data and characteristics. Now the derived class can be represented by its own type as well as by base class type. Following is an example for inheriting a class from a base class.

public class BaseClass
{
public BaseClass() { }
}
public class DerivedClass : BaseClass
{
public DerivedClass() { }
}

The entity classes used in LINQ to SQL can have the same inheritance mapping to achieve the previous inheritance facility. The InheritanceMapping attribute is used for mapping classes for inheritance hierarchy.

Now let us say we have the Items table that can contain different item types, like Cakes and Icecreams. If we want to keep the two items having different characterestics separately in the base Items table, we can have the InheritanceMapping attribute to map these classes in the inheritance hierarchy.

[Table(Name="dbo.Items")]
[InheritanceMapping(Code="Icecreams", Type=typeof(Icecream))]
[InheritanceMapping(Code="Cakes", Type=typeof(Cake))]
public partial class Item : INotifyPropertyChanging, INotifyPropertyChanged
{
[Column(Storage="_CategoryName", DbType="NVarChar(50)", IsDiscriminator=true)]
public string CategoryName
{
get{}
set{}
}

In the previous code, the Table attribute shows the name of the base class which is the base table. The InheritanceMapping attribute, maps the classes which are derived or inherited from the base Item class. All classes that are mapped to the inheritance hierarchy must be mapped to a single table.

There is a property called IsDiscriminator set to true for the column CategoryName in the base class Item. This is to denote the base class property which discriminates the inherited classes. It means that the value of the CategoryName field denotes which class to instantiate at runtime. There is another property called IsDefault which can be set to true and assigned to any of the classes. It means that whichever class has this default property set to true, will be the default class if the discriminator value does not match with any of the expected values for the derived classes.

In the above section, we have seen the usage of DataContext and the Table collections for the DataContext. In the previous examples, we have named the DataContext as Deserts with two different table collections as Categories and Items. The entity classes represent these two tables and columns through the properties, types and attributes used. Using these details, we can easily create a new database and delete the existing database with the methods supported by DataContext object. While creating the database, it is not possible to create all types of the database objects, like user defined functions and stored procedures. LINQ to SQL does not support creation of stored procedures and functions, but it can reference it and execute it. Creating these kinds of databases is useful in situations like creating the database objects while deploying the application. We can also have runtime entity classes and create the equivalent database object using LINQ to SQL. DataContext has a method called CreateDatabase, which will create a database at the location specified by the connection string, which is passed as a parameter to the DataContext object. For example, create the typed DataContext object that points to the local SQL server and has the table collections.

private void btnCreateDatabase_Click(object sender, EventArgs e)
{
Deserts db = new Deserts("Data Source=.sqlexpress;Initial
Catalog=Deserts;Integrated
Security=true");
if (!db.DatabaseExists())
{
db.CreateDatabase();
}
}
public class Deserts: DataContext
{
public Table<Categories> Categories;
public Table<Items> Items;
public Deserts(string connection) : base(connection) {}
}

Define the tables the same way as the one given in the previous section. DatabaseExists is a method, used to check if any database with the same name already exists in the server or not. The CreateDatabase method takes the responsibility of creating the new database in the server specified in the connection string. DeleteDatabase is a method of the DataContext which deletes the existing database from the server.

if (db.DatabaseExists())
{
db.DeleteDatabase();
}

Using DataContext, we not only can refer to the databases, but also to many of the objects within the database. There are different methods which support this feature.

After creating the database, the table object would look like the following:

We have seen how to create the tables for storing data. LINQ to SQL supports data manipulation through entity classes. Assigning values or changing values are similar to what we do with normal classes. LINQ to SQL tracks all the changes that happen to entity class objects and sends the data back to the database. For the tables we created in the above sections, we will try to insert records one-by-one. First, we will see how to insert records to the Categories table. As we named the database as Deserts, they are of a different category such as Icecreams, Cakes and Snacks. The following method shows the sample code for inserting these three desert categories into the Categories table:

// Create different varieties of deserts such as Icecreams, Cakes and snacks
private void CreateCategories()
{
Deserts dataBase = new Deserts("Data Source=.sqlexpress;Initial
Catalog=Deserts;Integrated
Security=true");
// Icecreams
Categories icecreams = new Categories
{
Category = "Icecreams",
Description = "Icecreams Varieties"
};
dataBase.Categories.Add(icecreams);
// Cakes
Categories cakes = new Categories
{
Category = "Cakes",
Description = "Cakes Varieties"
};
dataBase.Categories.Add(cakes);
// Snacks
Categories snacks = new Categories
{
Category = "Snacks",
Description = "Snacks Varieties"
};
dataBase.Categories.Add(snacks);
dataBase.SubmitChanges();

the above method, CreateCategories first creates a DataContext dataBase object of type Deserts and points to the existing database in the server. Using the Categories entity class, define the categories of Deserts and add it to the dataBase data context. After adding all the categories, submit it to the database using the SubmitChanges method, which converts these entity objects to the equivalent SQL commands and executes at the database level.

We have created categories, and inserted records into the database. Now we have to create items for each category, which makeup the details table for the Categories master table. While creating the item table, we should also pass the corresponding categoryID, which is the auto-generate field of the Categories table. In order to get the categoryID, we have to create the Category entity class using the dataBase data context by comparing the category value. Following is an example for creating items for the category Icecreams:

private void CreateItemsforIcecreams()
{
Deserts dbDeserts = new Deserts("Data Source=.sqlexpress;Initial
Catalog=Deserts;Integrated
Security=true");
// Query for a specific category
string category = "Icecreams";
var icecreams = dbDeserts.Categories.Single(c => c.Category ==
category);
// Add Item1
Items item1 = new Items
{
CategoryID = icecreams.CategoryID,
Ingredients = "cream, milk, sugar, corn syrup, cocoa and chocolate
liquor, whey, cellulose gum, mono and diglycerides,
carrageenan, polysorbate 80,
carob bean gum, guar gum",
Name = "Chocolate Fudge Icecream",
ServingSize = "4oz Scoop (113 grams)",
Protein = "4g",
TotalCarbohydrates = "35g",
TotalFat = "15g",
Cholesterol = "50mg"
};
icecreams.Items.Add(item1);
// Add Item2
Items item2 = new Items
{
CategoryID = icecreams.CategoryID,
Ingredients = "corn syrup, vanilla extract, guar gum, cream,
nonfat milk, sugar, mono & diglycerides,
locust bean gum, carrageenan, annatto color",
Name = "Vanilla Icecream",
ServingSize = "4oz Scoop (113 grams)",
Protein = "4g",
TotalCarbohydrates = "26g",
TotalFat = "16g",
Cholesterol = "65mg"
};
icecreams.Items.Add(item2);
dbDeserts.SubmitChanges();
}

In the above example, we have a variable called category initialized with the value Icecreams. This value is used for filtering the record from the Categories table. The record in which the value of the field category equals the value of the variable category will be returned to the caller and is stored in the object icecreams. Using this object, we can easily retrive all the column values including the CategoryID, which got generated during the insertion of this category record. Now using this CategoryID and the Item entity class, we can easily insert records into the Items table.

It is not that we will be inserting records to the tables, all the time. Many times we might need to modify the column values or delete the entire record itself. Let us see how we can update the value of a column in the Category table and delete an item from the Item table.

The following example picks the category from the Categories table where the value of the field category is equal to Icecreams. After picking the value of the entity object, the description of the object is modified to a new value. Similar to this, the item which has the name Vanilla Icecream is taken into the entity object of type Items and then removed from the list of items available for this category. After making the changes, all the changes are sent back to the database for updating using the SubmitChanges method. Refer to the following code:

private void ModifyIcecreamCategoryandDeleteanItem()
{
Deserts dbDeserts = new Deserts("Data Source=.sqlexpress;Initial
Catalog=Deserts;Integrated
Security=true ");
// Query for a specific category
string category = "Icecreams";
Categories icecreams = dbDeserts.Categories.Single
(c => c.Category == category);
icecreams.Description = "Modified Description for
Icecream Category";
foreach (Items item in icecreams.Items)
{
if (item.Name == "Vanilla Icecream")
icecreams.Items.Remove(item);
}
dbDeserts.SubmitChanges();
}

We have created the database, and database tables using entity classes and LINQ to SQL. We have also seen how to manipulate data using database table objects. Using the same database, we will see how to query the database. We have seen a lot of SQL queries in day-to-day programming for fetching records from the database objects. These queries would have been written using the SQL stored procedures, or as strings in .NET and passed as text command to the database server for execution and returning the result.

For example, fetching the items information from the database requires writing SQL statements, creating a command object and executing the SQL query through command objects. An SQL query is not LINQ query, but it is a T-SQL query. We have to depend on so many .NET objects to fetch the information from a database. The developer who writes code should also be aware of the T-SQL statements. Following is the code to fetch the item information from the database using T-SQL:

{
string queryString =
"SELECT CategoryID, Name, ItemID, Ingredients, ServingSize,
TotalFat, Cholesterol, TotalCarbohydrates, Protein FROM Items
WHERE (CategoryID = 1)";
using (SqlConnection connection = new SqlConnection(
"Data Source=.sqlexpress;Initial Catalog= Deserts;Integrated Security=true"))
{
SqlCommand command = new SqlCommand(
queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
}
}
finally
{
reader.Close();
}
}
}

LINQ to SQL queries can be used in situations where we have to build and execute a query from the front end application. By this, we can avoid building SQL query strings. For example, the following code fetches records from the Items table were the category is equal to Icecreams. This is an equivalent of the previous example for fetching the items' information using T-SQL queries in .Net 1.1 and 2.0 Framework.

private void SampleQueries()
{
Deserts db = new Deserts("Data Source=.sqlexpress;Initial
Catalog=Deserts;Integrated Security=true ");
var icecreams = from ice in db.Items
where ice.CategoryID == 1
select ice;
foreach (var itms in icecreams)
{
System.Console.Writeline(itms.Name) ;
}
}

CategoryID for the icecreams category items is passed as the parameter to the where clause of the query where it will match the items and then retrieve the records. The query is just an expression against the variable of type Items. Here the variable icecreams is actually of type Item. The actual query will get executed when the foreach statement is called. This is similar to the command object in ADO.NET. First the command text will be passed as a parameter to the command object. The actual execution of the command takes place only when any of the execution methods like ExecuteNonQuery or ExecuteScalar is called against the command object. The query object returns results as an IEnumerable<Items>.

The following figure shows the query assignment and execution:

The following figure shows a query expression assigned to the variable:

As it is said that the execution will take place only when the foreach statement executes, that is, when the actual enumeration takes place, it is also true that the execution will take place as many number of times as we have the foreach statement, which refers to the variable in which the query has returned the result-set which is the set, of table rows returned by the query that has been executed.

var icecreams = from cat in db.Items
where cat.CategoryID == 1
select new { cat.Name, cat.Categories.Description };
foreach (var itms in icecreams)
Console.WriteLine(itms.Name);
foreach (var itms in icecreams)
Console.WriteLine(itms.Name);
foreach (var itms in icecreams)
Console.WriteLine(itms.Name);

This example displays item name values from the rows returned by the query. The query returned the result-set into the variable icecreams, which is of type Items. The three foreach loops use the same variable to get the items information display. Here the query gets executed three times, one each at the foreach statement execution. This process is time consuming, and also the execution gives poor performance. This execution is called deferred execution.

There is a way to eliminate this process of multiple executions for the same query. Just convert the results into an array or a list using the operators ToList and ToArray. So the previous code will look like this:

var icecreams = from cat in db.Items
where cat.CategoryID == 1
select new { cat.Name, cat.Categories.Description };
var lst = icecreams.ToList();
foreach (var itms in lst)
Console.WriteLine(itms.Name);
foreach (var itms in lst)
Console.WriteLine(itms.Name);
foreach (var itms in lst)
Console.WriteLine(itms.Name);

Here the execution takes place only once when the resultant rows in the variable icecreams is converted to a list using the ToList operator, and assigned to the variable, lst. Now we can use this variable lst in the future, any number of times. This avoids the multiple execution of the query or deferred execution.

Queries with Multiple Entities

In the previous examples, we have seen classes with a collection of classes. For example, the Categories entity class has a collection of Items class. This kind of relationship builds the foreign key relationship at the database level. Normally in SQL queries, we have to refer to these two objects when we need a join operation for the query. As we have the collection of classes referred in the main class, we can refer to the objects easily. For example, we would be writing the query as follows to join two tables for the query without using the relationship.

var qry = from cat in db.Categories
join items in db.Items on cat.CategoryID equals item.CategoryID
where cat.Category == "Icecreams"
select new { itms.Name, itms.Categories.Category };

If we have table collections defined inside the class, the same query will look like this:

var query = from itms in db.Items
where itms.Categories.Category == "Icecream"
select new { itms.Name, itms.Categories.Category };

This query uses the table collection defined in the entity classes, and we use the object members directly in the query where clause. Following is the query built by LINQ to SQL for both the query expressions.

query = {Select [t0].[Name], [t1].[Category]
from [Items] as [t0]
inner join [Categories] as [t1] ON [t1].[CategoryID] =
[t0].[CategoryID]
where [t1].[Category] = @p0}

Remote Queries and Local Queries

We have seen some query expressions like this:

string category = "Icecreams";
Categories icecreams = dbDeserts.Categories.Single
(c => c.Category == category);
foreach (Items item in icecreams.Items.Where
(itm => itm.Protein = "4g"))
{
Console.WriteLine(item.Name) ;
}

The first statement fetches the category details for category that equal to Icecreams. The second is the foreach loop, which takes care of executing the query that fetches all items falling under that category. This execution takes place at the server and then the result comes to the client application.

LINQ to SQL has a new feature called remote queries for EntitySet. In the previous example, the query would have returned the EntitySet of all rows from the table first, and then the filtering is applied using the where clause. It is not required to bring in all the records to the local application place and then to filter the records. EntitySet implements IQueryable<T>, and these queries can be executed remotely. If EntitySet is already loaded, the subsequent queries are executed locally. This helps us in keeping the EntitySet local and running the queries multiple times. Unnecessary database calls and data transfer is avoided, and also, the EntitySet can be serialized.

The drawback in this type of query and having the EntitySet local is that, data will not be the latest. This means that the local copy of data may not be the same as the one on the server. Someone might have changed the records after creation of the local EntitySet. The local execution is an in-memory collection which is IEnumerable<T>. The remote queries reflect the database changes. If the database tables are involved in concurrent changes, then different execution of the same query will result with different EntitySets.

Deferred Loading

LINQ to SQL supports a process called deferred loading which means that the data loading, or fetching the data, happens only when it is required. For example, in a query, we might have used an object which has some related objects also; but we may not be using the related objects all the time and we will be using the main object only. So the data is fetched only for the main object, but not for the related object.

Following is an example for deferred loading. The query contains the object Categories, which refer to the entity object Categories which has a related object Items. The query uses only the Categories object. The following figures show the deferred loading process in details. The query has only a select statement for the categories. The query expression assigned by LINQ to SQL to the variable also has only the select statement for the Categories table.

// Deferred Loading
var DefQuery = from cats in db.Categories
where cats.Category == "Icecreams"
select cats;
Console.WriteLine("--Deferred Loading--");
foreach (Categories categ in DefQuery)
{
foreach (Items itm in categ.Items)
{
Console.WriteLine(itm.Name);
}
}

In the foreach loop, we refer to the Items table which is related to the Categories table and also the categories entity has the entity collection for the items. When we refer to the related object Items, LINQ to SQL assigns the query expression as given below and then executes it query to fetch records from the table.

The deferred query allows us to reduce the time and cost involved in executing the queries. We can have a join between entities to fetch records, but in that case, the result would be a projection which brings a huge amount of data and not an entity result set. Entities are objects which have an identity and the results can be persisted. Projections are not entities and cannot be persisted.

Immediate Loading

It is not that we don't require related table records all the time. Sometimes we might have to fetch rows from related tables also. In certain applications, we might want to show both, master and details table records together. For example, if you want to list down the items information for a particular category you selected, you should get all the information from the table. You cannot wait for the items to get loaded after selecting the category. This kind of retrieval of data from both the tables together is called immediate loading. It is exactly the opposite to deferred loading. LINQ to SQL provides a LoadWith operator that allows us to load the related table's data also. The following query expression fetches the records from the Categories table, as well as the records from the related table Items that matches with categoryID.

using (Deserts DesertsContext = new Deserts("Persist Security
Info=False;Initial Catalog=Deserts;Integrated
Security=SSPI;server=(local)"))
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Categories>(c => c.Category);
options.LoadWith<Items>(c => c.Name);
DesertsContext.LoadOptions = options;
Categories cat = DesertsContext.Categories.Single<Categories>
(c => c.CategoryID == 1);
}

In the previous example, we used DataLoadOptions which defines the DataContext load options. It loads all the tables that have a relationship with the main table. Here, Categories entity class has an association with the Items entity. So whenever the Categories entity gets loaded, the Items entity will also get loaded for the corresponding category.

The following image shows the data loaded in the cat variable of type Categories. It clearly shows that three Items in the Icecreams category are also loaded along with the category. You can see the option IsDeferred, which is false. It shows that the loading is not deferred loading.

There is a disadvantage in using immediate loading or loading of any entity object with respect to performance. As there are some fields like Category description, Item Ingredients and other fields that may not be required immediately. These fields can be loaded with a delay, or maybe fetched whenever required.

This option can be set to the entities using the Object Relational Designer also. We will see more details about this later in this chapter, but for now, consider entities and the Properties page for each property in the entity. There is a property called Delay Loaded, which can be set to True, in case, delay loading is required for the entity object field, or False in case immediate loading is required for the field. By setting the property to True, the field data will be loaded with a delay.

Projections

All the queries that we have seen previously are for entity objects, for fetching records from the database tables. There are situations where we may not require all columns of the tables. We might require only two or three columns out of many columns in the tables. LINQ to SQL query supports this feature for getting values of only one or more columns.

For example, we might want to know the name of the ice-creams and their ingredients. We may not be interested in any other details about the ice-creams. So, the query will look like this:

var projItems = from itms in db.Items
where itms.CategoryID == 1
select new {itms.Name, itms.Ingredients};

The equivalent query expression that is assigned to the variable would be like this:

You can also construct new objects with the use of projection queries. For example, if you want to create a new object which has only the names and ingredients of ice-creams, then the query would be as follows:

var projectionItems = from itms in db.Items
where itms.CategoryID == 1
select new {Itemname = itms.Name, itms.Ingredients}
into newTable orderby newTable.Itemname
select newTable;

This query has a new object called newTable, which will get created based on the Select statement, which selects Name and Ingredients of the items. We can also order the result-set using one of the column values.

var IcecreamsasXML =
new XElement("Icecreams",
from itms in db.Items
where itms.CategoryID == 1
select new XElement("Icecream",
new XElement("Name", itms.Name),
new XElement("ServingSize", itms.ServingSize),
new XElement("Protein", itms.Protein),
new XElement("TotalCarbohydrates", itms.TotalCarbohydrates),
new XElement("TotalFat", itms.TotalFat),
new XElement("Cholesterol", itms.Cholesterol)
)
);
IcecreamsasXML.Save(@"c:demoIcecreams.xml");

<?xml version="1.0" encoding="utf-8"?>
<Icecreams>
<Icecream>
<Name>Chocolate Fudge Icecream</Name>
<ServingSize>4oz Scoop (113 grams)</ServingSize>
<Protein>4g</Protein>
<TotalCarbohydrates>35g</TotalCarbohydrates>
<TotalFat>15g</TotalFat>
<Cholesterol>50mg</Cholesterol>
</Icecream>
<Icecream>
<Name>Vanilla Icecream</Name>
<ServingSize>4oz Scoop (113 grams)</ServingSize>
<Protein>4g</Protein>
<TotalCarbohydrates>26g</TotalCarbohydrates>
<TotalFat>16g</TotalFat>
<Cholesterol>65mg</Cholesterol>
</Icecream>
<Icecream>
<Name>Black Walnut Icecream</Name>
<ServingSize>4oz Scoop (113 grams)</ServingSize>
<Protein>6g</Protein>
<TotalCarbohydrates>25g</TotalCarbohydrates>
<TotalFat>19g</TotalFat>
<Cholesterol>50mg</Cholesterol>
</Icecream>
<Icecream>
<Name>Cotton Candy Icecream</Name>
<ServingSize>4oz Scoop (113 grams)</ServingSize>
<Protein>4g</Protein>
<TotalCarbohydrates>32g</TotalCarbohydrates>
<TotalFat>12g</TotalFat>
<Cholesterol>45mg</Cholesterol>
</Icecream>
</Icecreams>

Joins

When we say joins, the first thing we think about is the foreign key relationship between the database tables, which is very useful when we join the tables using queries. For example, to get all the items that belong to a particular category in the Categories table, we usually join both the tables using the query and fetch the details. However in LINQ to SQL, it is not always the case. We can join tables irrespective of their relationship. For example, we can fetch records from the Categories and Items table in which CategoryID is a key field in the Category table, and is the foreign key in the Items table, which identifies the corresponding items. The following code fetches the category from the Categories table and the corresponding item name from the Items table having a join on the CategoryID field.

var QryCategory =
from s in db.Categories
join c in db.Items on s.CategoryID equals c.CategoryID
select new {catgry = s.Category,itemname = c.Name};

The variable, QryCategory, in the query will contain query text which is shown as follows:

The following query is another example of a join query which extracts information from both the tables and inserts the rows into a new runtime table.

var QueriesCategory =
from s in db.Categories
join c in db.Items on s.CategoryID equals c.CategoryID into categoryitems
select new { s, categoryitems };

The following screenshot shows a query that is generated by LINQ to SQL and is assigned to the variable QueriesCategory:

Raw SQL Query

In some cases, we may feel that the DLINQ query is not sufficient enough to handle a query or we may just want to have a direct SQL query to be performed against the database. We used to perform this using the SQLCommand object, having the command type as text and the command text will have the raw SQL query as text. This way of executing the raw SQL, directly against the database is also possible using DataContext. DataContext has a method, ExecuteQuery, which takes the query text as a parameter, and converts the results to objects.

IEnumerable<Items> results = db.ExecuteQuery<Items>
(@"select c1.category as Category, c2.Name as ItemName
from category as c1, Items as c2
where c1.categoryID = c2.categoryID");

The output of the query will be assigned to the Items object.

Query Result

We can visually see the query text that actually gets executed at the database. LINQ to SQL takes the query expression and converts it to a database equivalent query. This tool helps us to see the query generated by LINQ to SQL for the query expression.

For example, consider the following simple query and try to execute it.

// Normal way of writing joins between two tables
var qry = from cat in db.Categories
join items in db.Items on cat.CategoryID equals items.CategoryID
where cat.Category == "Icecreams"
select items;

After assigning the query expression to the qry variable, if you place the mouse pointer over qry, we will get the query text, shown as follows:

The full text of the T-SQL query generated by LINQ to SQL would be this:

{SELECT [t1].[ItemID], [t1].[CategoryID], [t1].[Name], [t1].[Ingredients], [t1].[ServingSize], [t1].[TotalFat], [t1].[Cholesterol], [t1].[TotalCarbohydrates], [t1].[Protein]
FROM [Categories] AS [t0]
INNER JOIN [Items] AS [t1] ON [t0].[CategoryID] = [t1].[CategoryID]
WHERE [t0].[Category] = @p0

If we expand the query that is shown for the qry variable, we can see an option to view the results of the query. We can see the description against the Results View option saying Expanding the Results view will enumerate the IEnumerable. It means that the value assigned to qry will only contain the query text. It will not have the result of the query execution as long as it is enumerated.

This is how the result is shown when the Results View is expanded.

Stored Procedures

Similar to database and database tables, LINQ to SQL also supports stored procedures. We can map the entity and DataContext classes to the database and tables which give strongly typed access to the database table objects. In the same way, LINQ to SQL also supports the feature of having methods which can be mapped to the database stored procedure. This will give a strongly typed access method and the IntelliSense feature to the stored procedures. The result-set returned by the stored procedure is also a strongly typed collection. We can create entity methods for the stored procedure manually and map it to the corresponding stored procedure or we can use the Object Relational Designer tool to map the stored procedures.

LINQ to SQL maps stored procedures to the methods using the function attribute, and if required, it uses the parameter attribute. The function attribute supports name property which specifies the name of the method that corresponds to the database stored procedure object.

Let us create a simple stored procedure using entity classes, created in the previous examples. This stored procedure will take Category as a parameter and return the number of items present in the database for the category. Let us name the stored procedure as GetNumberofItemsforCategory. The SQL text for the stored procedure will look like the following:

CREATE PROCEDURE [dbo].[GetNumberofItemsforCategory]
@Category nvarchar(50)
AS
BEGIN
declare @itemCount int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select @itemCount = count(Items.Name) from Items, Categories
Where Items.CategoryID = Categories.CategoryID
and Category = @Category
Return @itemCount
END

The stored procedure takes one input parameter, @Category, which takes the category value, and returns the @itemCount that contains the number of items present in the database for the category.

The equivalent method for the above stored procedure will be as follows:

[Function(Name = "dbo.GetNumberofItemsforCategory")]
public int GetNumberofItemsforCategory([Parameter(DbType =
"NVarChar(50)")] string Category)
{
IExecuteResult result = this.ExecuteMethodCall(this,
(MethodInfo)(MethodInfo.GetCurrentMethod())), category);
return ((int)(result.ReturnValue));
}

The above method has the Function attribute with the name which is same as the GetNumberofItemsforCategory database stored procedure. This method also defines the parameters with the Parameter attribute which has the property Name that has a parameter name Category assigned to it. The function uses an ExecuteMethodCall execution method, which actually takes care of executing the stored procedure. There is a MethodInfo class that executes the stored procedure using the GetCurrentMethod method, by passing the parameter to the stored procedure. The result which is of type IExecuteResult has a property RetunValue that actually returns the value returned by the stored procedure.

The above method GetNumberofItemsforCategory, should be a part of the DataContext entity class. The DataContext class will look like the this:

[Database(Name = "Deserts")]
public class Deserts : DataContext
{
public Table<Categories> Categories;
public Table<Items> Items;
public Deserts(string connection) : base(connection) { }
[Function(Name = "dbo.GetNumberofItemsforCategory")]
public int GetNumberofItemsforCategory([Parameter(DbType = "NVarChar(50)")] string category)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), category);
return ((int)(result.ReturnValue));
}
}

By using the method, GetNumberofItemsforCategory inside the DataContext object, the stored procedure directly gets mapped to the method in the DataContext class.

Following is the code to access and execute the stored procedure. The method is a strongly typed method which can be accessed directly using the DataContext object, and the resultant value is returned by the method.

Let us create another stored procedure which will return a result-set. Here, the result-set is not pre-defined. Let's see how we can define and access the stored procedure through LINQ to SQL. The text for the stored procedure is as follows:

CREATE PROCEDURE [dbo].[SelectItemDetails](@param nvarchar(50))
AS
SELECT * FROM Items where ([Name] = @param)

This stored procedure, returns all the rows from the Items table for the passed parameter value which should be the name of the item in the Items table.

The equivalent DataContext class method for the previous stored procedure would be as follows:

[Function(Name = "dbo.RuntimeShapesforResults")]
public ISingleResult<Items> RuntimeShapesforResults([Parameter(DbType = "NVarChar(20)")] string param)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), param);
return ((ISingleResult<Items>)(result.ReturnValue));
}

The previous method used the ISingleResult interface, which is of type, Items. Using the above method, we can execute stored procedure by passing the parameter value, shown as follows:

// Stored procedure which returns single resultset
ISingleResult<Items> result = db.SelectItemDetails("Chocolate Fudge Icecream");
foreach (Items item in result)
{
Console.WriteLine(item.Name + item.CategoryID);
}

ISingleResult, which is of the type Items is used here to store the result that is returned by the stored procedure. Then we can use a variable of type Items and loop through the returned result to get the output as we want. The following screenshot shows you this:

We will have another stored procedure that returns two result-sets. We will use the DataContext method and the entity classes to access the stored procedure result-sets. The stored procedure is like this. The stored procedure will return two result-sets; one is from the Categories table, and the other from the Items table. Following is the SQL syntax for the stored procedure:

CREATE PROCEDURE [dbo].[MultipleResults]
AS
select * from Categories
select * from Items

The corresponding DataContext method for this stored procedure would be as follows:

[Function(Name = "dbo.MultipleResults")]
[ResultType(typeof(Categories))]
[ResultType(typeof(Items))]
public IMultipleResults MultipleResults()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((IMultipleResults)(result.ReturnValue));
}

In the previous method declaration, note the ResultType attribute used for the number of results expected from the output and their type. In the stored procedure, we are using two SQL queries; one for returning the categories and the other for returning the items.

To access the results after execution, we have to use the GetResult method of MultipleResults, shown as follows:

IMultipleResults results = db.MultipleResults();
// First Result set which is of type Categories
foreach (Categories Cats in results.GetResult<Categories>())
{
Console.WriteLine("Cateegory:" + Cats.Category);
}
// Second result set which is of type Items
foreach (Items itms in results.GetResult<Items>())
{
Console.WriteLine("Item Name:" + itms.Name +" Category:" + itms.Categories.Description);
}

The first foreach loop will refer to the first result-set of the stored procedure, and the second, will return the second result-set of the stored procedure.

Let us create another stored procedure which will return a result-set. Here the result-set is not pre-defined. It is based on the value passed to the input parameter. Let us see how we can define and access a stored procedure through LINQ. The text for the stored procedure is as follows:

CREATE PROCEDURE [dbo].[RuntimeShapesforResults](@param nvarchar(20))
AS
IF(@param = ‘Items')
SELECT * FROM Items
ELSE IF(@param ='Categories')
SELECT * FROM Categories

The stored procedure returns all the rows from the Items table if the passed parameter value is equal to Items, and it returns all data from the Categories table if the parameter value is equal to Categories.

The equivalent DataContext class method for the previous stored procedure would be as follows:

[Function(Name = "dbo.RuntimeShapesforResults")]
[ResultType(typeof(Categories))]
[ResultType(typeof(Items))]
public IMultipleResults RuntimeShapesforResults
([Parameter(DbType = "int")] System.Nullable<int> param)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), param);
return ((IMultipleResults)(result.ReturnValue));
}

Following is the code for calling the stored procedure and getting the results by passing the parameter value. If we pass the value 1 to the parameter, the result would be the list of Categories, and if the parameter value is 2 then the result would be the list of Items.

IMultipleResults runtimeResultforItems = db.RuntimeShapesforResults(2);
foreach (Items itm in
runtimeResultforItems.GetResult<Items>())
{
Console.WriteLine(itm.Name);
}
IMultipleResults runtimeResultforCategories =
db.RuntimeShapesforResults(1);
foreach (Items itm in
runtimeResultforCategories.GetResult<Items>())
{
Console.WriteLine(itm.Name);
}

The result view for the previous code would look like this:

User-Defined Functions

User defined functions are similar to stored procedures. We can map the method defined on a class to a user-defined function by using the function attribute. The body of the method constructs the expression and passes it to the DataContext, which executes the function expression and returns the result.

For example, following is a function that returns the ItemName for the passed itemID.

CREATE FUNCTION GetItemName(@itemID int)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @itemName nvarchar(100)
Select @itemName= [Name] from Items where IItemID = @itemID
RETURN @itemName
END

The equivalent method for the DataContext would be as follows:

[Function(Name = "dbo.GetItemName", IsComposable = true)]
[return: Parameter(DbType = "VarChar(100)")]
public string GetItemName([Parameter(Name = "itemID",
DbType = "int")] int @itemID)
{
return ((string)(this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
@itemID).ReturnValue));
}

If you see the attribute for the method, it is the same one used for the stored procedure. Only the name is the difference here. The execution is also similar to the stored procedure. We can call the function as follows:

string itemName = db.GetItemName(1);

Class Generator Tool

In all the previous examples, we have seen different ways of creating database objects using LINQ to SQL support. This is fine while creating a new database and its objects, and mapping the same with the entity classes. If we have a database that already exists for our application to use, we will end up creating corresponding class objects, which will consume a lot of our time. In order to avoid this, LINQ to SQL comes with a new tool called SQLMetal, which takes care of creating the entity class objects for the existing database. The same thing can also be done using the Object Relation Designer, which we are going to see later in this chapter. However the advantage of using SQLMetal is that it is a command line tool that can be used in the automated build process. All we have to do is to use the tool and provide the database name, location and format in which we want the objects. It is a command line utility that automates the task. To see the different options available with SQLMetal, type sqlmetal / at the command prompt.

SQLMetal supports two different formats for objects. One is the entity classes in different languages like Visual Basic or C# and the other is the XML format. Then functionality involved in SQLMetal is of two steps, explained as follows:

1. Extracting the information format from the database and creating a .dbml file. This is the intermediate file generated for customization. From this DBML file, we can generate code and mapping attributes.

2. Generating a code output file.

This advanced feature comes with some exceptions. SQLMetal cannot extract a stored procedure that calls itself. The nesting level of the database objects, like views, functions, and stored procedures, should not exceed 32.

For creating the entity classes for the Deserts database that we created through the previous examples, the command would look like this:

sqlmetal /server:.SQLExpress /database:c:demoDeserts.mdf
/pluralize/namespace:Deserts /code:Deserts.cs

The above command will create the Deserts.cs file which contains the entity classes, and their relationship and definitions for the objects. This will create the classes using C# language. If you want to get the classes in VB, just rename the code as Deserts.vb instead of Deserts.cs to identify the language to be used. SQLMetal also has an option to specify the language. We can use that as well for creating the entity classes.

Using SQLMetal, we can create the DBML as follows:

sqlmetal /server:.SQLExpress /database:c:demoDeserts.mdf /dbml:Deserts.dbml

We can also use this:

Sqlmetal /dbml:deserts.dbml c:demoDeserts.mdf

The same entity objects created above can also be created in XML format. The command for that is as follows:

sqlmetal /server:.SQLExpress /database:c:demoDeserts.mdf /pluralize /namespace:Deserts /code:Deserts.xml

This code will produce an XML file containing all the entity objects. The output of this would look like this:

Either class file or XML which is generated by the tool may not have proper names for the classes which we might want to rename or modify for better understanding. This cannot be done directly while creating classes. To achieve this, we have to first generate the XML file. In the XML file we can modify or annotate it with a class and property attribute to modify the attributes of tables and columns. After doing this, we can use this modified XML file to generate the object model. This can be done using the following command:

SqlMetal /namespace:Deserts /code:Deserts.cs Deserts.xml

The SQLMetal takes all the information from the XML file and generates the class file. The XML file acts as the metadata for generating the class file. It contains attributes that can be set to change the behaviour of the tables or columns. For example, the attributes for the columns are as follows:

<Column
Name = "Column-Name"
Hidden = "true|false"
Access = "public|private|internal|protected"
Property = "property-name"
DBType = "database-type"
Type = "CLR-type"
Nullable = "true|false"
IsIdentity = "true|false"
IsAutoGen = "true|false"IsVersion = "true|false"
IsReadOnly = "true|false"
UpdateCheck = "Always|Never|WhenChanged" />

The Table has attributes such as:

<Table
Name = "Table-Name"
Hidden = "true|false"
Access = "public|internal"
Class = "element-class-name"
Property = "context-or-schema-property-name" >

Some of the attributes are very common to many of the elements and some are specific to some elements. For example, Name and Hidden are very common to all the elements.

SqlConnection connection = new SqlConnection("PersistSecurity
Info=False;Initial Catalog=Deserts; Integrated Security=SSPI;server=(local)");
connection.Open;

Deserts db = new Deserts(connection);
var icecreams = from cat in db.Items
where cat.CategoryID == 1
select cat;
db.Connection.Close();

[Column(Name="Description", DbType="nvarchar(1000)",
UpdateCheck= UpdateCheck.Never)]
public string Description
{
get; set;
}

LINQ to SQL object relational designer is the visual design surface to create the entity objects and bind the controls to the LINQ to SQL objects with relationships. O/R designer is used to create an object model in an application that maps to the database objects. Database object not only means that it can map to database tables, but we can map stored procedures and user-defined functions too. For objects like stored procedures and functions, the DataContext cannot have an entity class created, but has corresponding methods to create the expressions. The designer has its surface split into two different areas. Entities Pane on the left and the Methods Pane on the right of the surface. The Entities Pane , is the main pane, which displays the entity classes that adds to the DataContext. The Methods Pane lists the methods of the DataContext, which are mapped to the databasestored procedures and user-defined functions.

Now we shall see how we can create a new application and create entity classes and methods of the DataContext.

Create a new project and add a new class Item of type LINQ to SQL Classes to the project. Now you can see the DataClasses1 file getting added to the project. It has two files named—DataClasses1.dbml, and DataClasses.cs, associated with the project. The .dbml file is the surface for the designer which will be empty in the beginning. The DataClasses.cs is the file that contains the corresponding code for the entity objects and the methods added to the DataContext. Now open the Server Explorer and expand the database that you want to use, and locate the table and stored procedure objects. Now drag-and-drop the table objects from the list of database objects shown in the Server Explorer to the surface of the designer. As soon as the first object is dropped onto the surface, the DataContext is configured with the connection information using the database connection information. The entity class for the object dropped on the surface also gets added to the DataContext. In this way, we can create all entity classes for the database tables and views.

Similar to the tables and views, we can add stored procedures and functions to the surface. As soon as we drop the stored procedure or function, the designer creates the corresponding method and adds it to the DataContext. These methods are listed in the Methods Pane, which is on the right side of the designer. In LINQ to SQL, both stored procedures and functions are mapped to the entity classes using the function attribute. It shows all the methods added to the designer. We can hide or unhide the Methods Pane using the option given when you right-click on the designer surface.

Drag-and-drop of stored procedures and functions into the design surface makes a lot of difference depending on where we drop it on the surface. The return type of the generated DataContext method differs based on the place it is dropped.

We can modify the return type of the method after adding it to the DataContext. The method code generated for the DataContext would be as follows:

[Function(Name = "dbo.SelectItemDetails")]
public ISingleResult<Items> SelectItemDetails ([Parameter(DbType ="NVarChar(50)")] string param)
{
IExecuteResult result = this.ExecuteMethodCall(this,
(MethodInfo)(MethodInfo.GetCurrentMethod())), param);
return ((ISingleResult<Items>)(result.ReturnValue));
}
[Function(Name = "dbo.MultipleResults")]
[ResultType(typeof(Categories))]
[ResultType(typeof(Items))]
public IMultipleResults MultipleResults()
{
IExecuteResult result = this.ExecuteMethodCall(this,
(MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((IMultipleResults)(result.ReturnValue));
}
[Function(Name = "dbo.RuntimeShapesforResults")]
[ResultType(typeof(Categories))]
[ResultType(typeof(Items))]
public IMultipleResults RuntimeShapesforResults([Parameter(DbType =
"int")] System.Nullable<int> param)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), param);
return ((IMultipleResults)(result.ReturnValue));
}
[Function(Name = "dbo.GetItemName", IsComposable = true)]
[return: Parameter(DbType = "VarChar(100)")]
public string GetItemName([Parameter(Name = "itemID",
DbType = "int")] int @itemID)
{
return ((string)(this.ExecuteMethodCall(this,
(MethodInfo)(MethodInfo.GetCurrentMethod())),
@itemID).ReturnValue));
}

There are two types of methods:

We have seen the usage of the first option in our previous examples. Now let us try to use the second option of creating a stored procedure for insert operation of an entity class and map that to the corresponding entity class in the DataContext.

Let's create the database stored procedure for inserting records into the category table as follows:

CREATE PROCEDURE [dbo].[InsertintoCategory]
( @category nvarchar (100) = NULL,
@description nvarchar (100) = NULL
)
AS
INSERT into Categories (CategoryName, Description)
VALUES (@category, @description)

Now expand the server explorer and locate the stored procedure. Drag-and-drop the stored procedure on the designer surface to create the corresponding DataContext method. The generated DataContext method would look like this:

[Function(Name="dbo.InsertintoCategory")]
public int InsertintoCategory([Parameter(DbType="NVarChar(100)")]
string category, [Parameter(DbType="NVarChar(100)")] string
description)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), category,
description);
return ((int)(result.ReturnValue));
}

Now select the entity object and open Properties of the entity class. Entity class has properties like Insert, Update, and Delete, as shown in the following screenshot:

In the Properties window, click on the Use Runtime option against Insert property for the Category entity class. The Configure Behavior window opens for mapping the stored procedure to the entity class.

There are two options, Use runtime and Customize. If we select the Use runtime option, the system automatically generates the logic for Insert, Update, and Delete at runtime. If we select the Customize option, then we have to select the stored procedure from the list and configure the properties as follows:

Like this, we can create stored procedures for update and delete also, and then map them to the entity class which will simplify the operation of updating the modified records of the entity to the database.

We can easily create the windows form with data bound controls using the designer. For the database and the tables created in the previous sections, we will see how we can create a windows application with entity classes and controls bounded using the Relational Designer. By using this designer, we can reduce a lot of our time in creating the forms with controls. We can use LINQ to SQL queries for fetching the records and filtering them. We can also bind the controls to the data source that is created using the objects built by the designer.

Choose the menu option Data, and then Add New Data Sources. From the selected data sources select the Object option and click on the Next button.

In the next window, select the Category entity table to add the corresponding data source and then click on the Finish button, so that the data source gets added to the project.

You can add as many entities to the project as you need in the application. Now open the windows form and open the available Data Sources list in the project using the Data Sources explorer.

Now open Form1.cs [Design]. Select the Data menu option, then Show Data Sources which will open the Data Sources explorer and display the Category data source that we created. You can see a drop-down next to the Category data source name which gives two options—Details and DataGridView. Let us choose the Details view option and then drag-and-drop the fields we need to place on the form.

We do have display options for each field as shown in the following screenshot. Before placing the field onto the form, we can choose the control type.

On placing the fields on the surface, we can also see a navigation bar and the editing controls getting added to the form. This automatic placement of editing controls reduces our design time for designing the form.

We have created the data source object and placed all the controls on the form. Now we need to get the data from the database through the data source and bind it to the controls. The important thing required for this is the connection to the database. We know that we have the connection DataContext which has the connection information. Add the following code to the form:

public partial class Form1 : Form
{
private DataClasses1DataContext connection = new
DataClasses1DataContext();
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
categoryBindingSource.DataSource = connection.Categories;
}
}

We are assigning the same connection created by the DataContext to the data source and setting the Categories object as the source of data. Now save the application and execute it. We can see a form with controls, with editing facility attached to it. The save option is disabled as we have not enabled it and we have not added any code for saving.

Before we look at the editing features, we will add another data source with detailed view for the items in each category that we select in the form. Now stop the execution and open the design surface of the designer.

Add the data source for the second object Items, similar to the one we created for the Category entity object. Now open the Data Sources explorer. We can see the Items data source added to the Category data source as it is the related detail table for the categories. There is a separate Item data source added for the entity Item.

Open the form design surface using the Data Sources explorer, and select the DataGridView option for the Item details entity within the Category data source. Drag-and-drop the Item data source on the form, which will create the DataGridView to display the items which are linked to the category selected.

Now save the application and execute it. You can see the form working with the navigation feature. On selecting the category, you can see the related items displayed in the items data grid.

Now we have one more thing left. The Save button is still disabled.

Click on the Save button in the navigation bar and enable it. Then select the Onclick event and write the following code:

connection.SubmitChanges();

Now execute the application and navigate through the records. Edit the records and try saving it using the save option. We have other options such as insert and delete which we can enable by adding additional code to it. This is the simplest way of creating the application using the relational designer.

We have seen how we can create the classes and their relationship using the database objects and the object relation designer. Now we will see how we can create inheritance mapping using the relation designer. For the sake of inheritance mapping, let us add a new column to the Items table called CategoryType, which will hold the different types of items and act as the discriminator for the derived classes. Now let us see how we can drive two classes such as Cake and Icecream from the Items class using the object relation designer.

Open the designer surface and add two new classes to it by choosing the option to add classes as shown below:

Adding the classes will not add any tables to the database, or there is no table that exists with the same name in the database. These are new entities which are empty and which are going to be derived from the entity class, Item. Now select the Item class and right-click to choose the option Inheritance. Once you choose the Inheritance option, you can find the dialog to select the base class and the new derived class as shown as follows. Select Item as base class and Icecream as derived class. Select the Item class again, and choose the option Inheritance, then select Item as base class and Cake as the derived class.

You can see the Inheritance association link between the base class and derived classes and also the derived classes are empty without any properties or methods. These classes will make use of the members of the base class.

Now select the Inheritance arrow of one of the derived class, right-click and select the properties. In the Properties window, you can see different properties like Derived Class Code, Discriminator Property, and Inheritance Default.

Select the Discriminator field CategoryType, which we added to the entity class Item. The corresponding database column value of this field is used for discriminating the derived entity classes. The Derived Class Code property denotes the descriminator field value used to specify the derived class type. Inheritance Default is the property used to denote the default class if the value does not match the discriminator values.

Follow the same thing to set the properties for the second derived class Cake. Save the file and build the project once.

To cross-check the code that is created by the object relational designer, open the class file of the designer. Following is the code for the Item entity.

[Table(Name="dbo.Items")]
[InheritanceMapping(Code="Icecream", Type=typeof(Icecream))]
[InheritanceMapping(Code="Cake", Type=typeof(Cake))]
public partial class Item : INotifyPropertyChanging,
INotifyPropertyChanged
{
// the members definition goes here
}

The code generated for the derived classes would look like this:

public partial class Icecream : Item
{
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate();
partial void OnCreated();
#endregion
public Icecream()
{
OnCreated();
}
}
public partial class Cake : Item
LINQ to SQLobject relational designer{
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate();
partial void OnCreated();
#endregion
public Cake()
{
OnCreated();
}
}

The derived class does not have any specific members in it. It shares the same members defined by the base class Item. We can override the members, or include the class-specific members to the derived class. By doing this, we can achieve the Inheritance feature on the entities level.

In this chapter, we have seen different features of LINQ to SQL. We have seen how to create and manipulate the database objects using the DataContext object of LINQ to SQL. We have also seen the different members of the data context and how we can make use of the DataContext members to work with database data. Also, we covered the different query features, working with stored procedures, working with user-defined functions and handling concurrency conflicts using LINQ to SQL. There are some good class generator tools like SQLMetal and object relation designer which support and provide easy ways of creating, manipulating, and working with entity objects. We have also seen some examples of creating derived classes from the base class using the inheritance mapping attribute, and also through object relation designer. Whatever we covered so far in this chapter is to give you an understanding of how we can make use of the LINQ to SQL feature for some of our database-related operations through applications. There are a lot of other features supported by LINQ to SQL, like constructing XML, lots of other queries, and handling transactions during database updates.