LINQ to SQL takes care of translating LINQ expressions to equivalent T-SQL and passing it on to the database for execution and then returning the results back to the calling application by tracking changes made to the objects. LINQ to SQL reduces a lot of programming time. It comes with two different design time tools which are used for converting the relational database objects into object definitions.
LINQ to SQL, not only provides the feature of querying or referring to the relational objects, but it also has the ability to create a database and database objects. In this chapter, we'll examine some of the features that are involved in creating the entity objects, populating data to the database tables, querying and manipulating data in the database, and so on.
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; } }
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.
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.
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.
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 |
---|---|
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
This is to detect the conflicts by optimistic concurrency. There is a timestamp or
|
|
This boolean value determines if the member holds a discriminator value for a LINQ to SQL inheritance hierarchy. |
|
This value can be set to true or false to indicate whether the column allows a null value or not. |
|
This is used to get the unique identifier when implemented in the derived class. |
|
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}
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 |
---|---|
|
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. |
|
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. |
|
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. |
|
This property is similar to the |
|
This is to impose a unique constraint on the foreign key to have a one-to-one relationship. |
|
This specifies the member as a foreign key in the association relationship. |
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.
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 |
---|---|
|
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. |
|
This is of type string which represents the name of the stored procedure or the user-defined function in the database. |
This attribute is used to refer to the parameters of the stored procedure or function in the database. This attribute has two properties:
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
.
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.
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.
Method |
Description |
---|---|
|
Deletes an existing database from the server. The database is identified by the connection string used in the DataContext. |
|
Creates a new database in the server. |
|
Returns true if the database already exists and if the attempt to open the database succeeds. |
|
This method is very useful for executing any command at the database server. It returns the number of rows affected. The signature of the
Parameters can be passed to An exception is thrown if the number of parmeters in the parameter array is less than what is expected by the command string. If any of the parameters is null, it is converted as |
|
This method is used for executing an SQL Query. It returns output as objects which match to the entity objects. Parameters can also be passed to the Query.
|
|
This returns the modified objects from the collection of objects in DataContext. This operation returns three different read-only collections such as:
The disadvantage is that the returned collections will have the following constraints:
|
|
This command provides Argument exception is thrown if the argument is null. It returns only the first query command and it does not return additional commands. |
|
This method is useful for hashing algorithms and data structures as hash tables. It returns an integer for the current object it is called from, but does not guarantee to be unique. Objects used as keys in the |
|
Returns the runtime type of the current instance of the object. |
|
This method is to refer to any of the database table. It returns the result as an object which corresponds to the entity object defined. This method is very useful for strongly typed DataContext. |
|
This method refreshes the state of the object with the data in the database. It refreshes the fields and properties of the object. |
|
Any changes made using the entity objects through the DataContext object should be sent back to the database server to restore the data. This |
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.
In object-oriented programming, all objects have references. So, if we assign an object to two different variables, the value is not assigned to the variables. The variables will refer to the same object using the object identity. When we execute queries, the data is returned in the form of rows from the relational database. If we execute the same query, another set of same rows is returned from the database because the rows do not have any key to identify them. The primary key which exists in the database is to identify the rows for uniqueness. So, whenever the same data is fetched from the database multiple times from the front end application, it comes as different instances. If I execute the same query three times, it will return the three result-sets with three instances.
In LINQ to SQL, we use DataContext for referring to the database objects. Here, DataContext is an object which is supposed to have object identity. Whenever a new row is fetched from the database through DataContext, it is logged in an identity table and a new object will be created. If the same row is fetched again, the DataContext will take care of sending the same instance of the object created at the first time. So the identity table is a cache table which will provide the object instances if the same object has already been created.
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}
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
.
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.
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.
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.
We have used projections for fetching data from the database tables in different ways. Queries should be flexible enough to get the data in whichever format we like. Getting data as XML is another important requirement in applications nowadays. Using LINQ to SQL, we can easily build XML elements. The following code shows how to get data from the Items
table into an XML file:
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");
XElement
is an object of LINQ to XML, which is the main object to create an XML file. The previous query is a mix of LINQ to XML and LINQ to SQL to fetch records and present it in XML format. The XElement
has the direct method to save its value as XML file. The XElement
takes care of creating the XML tree while the LINQ to SQL query takes care of fetching records for the XML tree. The final output of the above XML file will be as follows:
<?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>
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:
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.
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.
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 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);
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:
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.
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.
Transaction is a service in which, a series of actions either succeed or fail. If it fails, all the changes made by the transaction are undone automatically. The DataContext takes care of handing transactions. It makes use of the transaction if one is already created, otherwise it creates one transaction on its own for all the updates that happen through the DataContext.
LINQ to SQL is a new feature supported by ADO.NET. So LINQ to SQL should be able to make use of other features of ADO.NET. ADO.NET uses a connection object which takes the connection string as parameter for connecting to the database. When we create a DataContext, we can make use of the connection created by ADO.NET. LINQ to SQL will use the same connection for its queries and updates to the database. For example, the ADO.NET connection to the database Deserts
in the local server will be as follows:
SqlConnection connection = new SqlConnection("PersistSecurity Info=False;Initial Catalog=Deserts; Integrated Security=SSPI;server=(local)"); connection.Open;
The Deserts
DataContext can use the connection object for the queries and updates to the database. After performing the task, the connection should be closed by the DataContext object.
Deserts db = new Deserts(connection); var icecreams = from cat in db.Items where cat.CategoryID == 1 select cat; db.Connection.Close();
The different ways of handling transactions, are stated as follows:
Explicit Local Transaction: When SubmitChanges
method is called, and if the transaction property is set, then the SubmitChanges
method is executed in the same transaction context.
Explicit Distributed Transaction: LINQ to SQL queries can also be called within the scope of the transaction. The SubmitChanages
method can be called for submitting the execution of the queries.
Implicit Transaction: When the SubmitChanges
method is called, LINQ to SQL checks to see if the call is within the scope of a transaction or if the transaction property is set. If it is present, it executes within the transaction, otherwise it starts a local transaction and executes the commands.
We have seen how to save data to the database and use transaction objects to save the data safely into multiple databases. When we save the changes back to the database, it is not guaranteed that the data will remain the same, since we read it the last time. There are chances that other users might be using the same application and will be updating the same information that we are also trying to update.
Optimistic concurrency conflict occurs when we attempt to submit the changes we made, and at the same time another user has updated the same record. To resolve this, LINQ to SQL has some properties for the members by which we can easily find out the members in conflict and then handle it. To detect conflicts when the application has changed the value of the member, we have to use the property called UpdateCheck
associated with the ColumnAttribute
of the member. We can include the members for detecting the optimistic concurrency conflicts using this ColumnAttribute
with UpdateCheck
property. This UpdateCheck
property has three enumerated values—Always, Never
, and WhenChanged
. Following are the different scenarios where we use the different properties of UpdateCheck
.
UpdateCheck.Always
: Always use this member to detect conflicts.
UpdateCheck.Never
: Never use this member to detect conflicts.
UpdateCheck.WhenChanged
: Use this member for detecting conflicts only when the application has changed the value of the member.
The following code is an example that represents, the Description
column, and it should never be used for checking the update conflicts:
[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.
If the stored procedure or function is dropped on the empty surface of the designer, the designer creates the DataContext method with the return type, automatically generated. This automatically generated type has the name, which is that of the stored procedure or the function with the name of the return field used by the stored procedure or function.
If the object is dropped on the entity class, then the designer creates the DataContext method with the return type, which is the same as that of the entity class.
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:
One which just executes the stored procedure or the function and returns the result.
The second type is used for database operations like insert, update and delete for an entity class. This is called to store the modified records of the entities to the database.
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.
18.223.106.79