Creating Object Models in LINQ
In this article, you will learn how to map a class to a database table, and how to retrieve objects.
1. Click the Start | Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005 menu command.
2. Click the Tools | Options menu command
3. In Microsoft Visual Studio, click the File | New | Project… menu command
4. In the New Project dialog, in Project types, click Visual C# | LINQ Preview
5. In Templates, click LINQ Console Application
6. Provide a name for the new project by entering “DLinqHOL” in the Name field
7. Click OK
8. At the warning dialog, click OK
1. Create an entity class to map to the Customer table by entering the following code in Program.cs (put the Customer class declaration immediately above the Program class declaration):
[Table(Name="Customers")]
public class Customer
{
[Column (Id=true)]
public string CustomerID;
}
The Table attribute maps a class to a database table. The Column attribute then maps each field to a table column. In the Customers table, the primary key is CustomerID. This is used to establish the identity of the mapped object. You designate this by setting the Id parameter to true. An object mapped to the database through a unique key is referred to as an entity. Here instances of Customer class are entities.
2. Add the following code to declare a City property:
[Table(Name="Customers")]
public class Customer
{
[Column (Id=true)]
public string CustomerID;
private string _City;
[Column(Storage = "_City")]
public string City
{
get { return this._City; }
set { this._City = value; }
}
}
Fields can be mapped to columns as shown in step 1, but in most cases properties would be used instead. When you declare public properties, you must specify the corresponding storage field using the Storage parameter to the Column attribute.
3. Enter the following code within the Main method to specify the link to the Northwind database, and to establish a connection between the underlying database and the code-based data structures:
static void Main(string[] args)
{
// Use a standard connection string
DataContext db = new DataContext(
@"C:\Program Files\LINQ Preview\Data\northwnd.mdf");
// Get a typed table to run queries
Table<Customer> Customers = db.GetTable<Customer>();
}
The Customers table acts as the logical, typed table for queries. It does not physically contain all the rows from the underlying table but acts as a proxy for strongly typed queries.
The next step retrieves data from the database. This is accomplished using the DataContext object which is the main conduit by which you retrieve objects from the database and submit changes back.
1. The connection has been established; however no data is actually retrieved until a query is executed. This is known as lazy or deferred evaluation. Add the following query for London-based customers:
static void Main(string[] args)
{
// Use a standard connection string
DataContext db = new DataContext(
@"C:\Program Files\LINQ Preview\Data\northwnd.mdf");
// Get a typed table to run queries
Table<Customer> Customers = db.GetTable<Customer>();
// Attach the log showing generated SQL to console
// This is only for debugging / understanding the working of DLinq
db.Log = Console.Out;
// Query for customers in London
var custs =
from c in Customers
where c.City == "London"
select c;
}
2. Add the following code to execute the query and print out the results:
static void Main(string[] args)
{
// Use a standard connection string
DataContext db = new DataContext(
@"C:\Program Files\LINQ Preview\Data\northwnd.mdf");
// Get a typed table to run queries
Table<Customer> Customers = db.GetTable<Customer>();
// Query for customers in London
var custs =
from c in Customers
where c.City == "London"
select c;
foreach(var cust in custs)
{
Console.WriteLine("ID={0}, City={1}", cust.CustomerID, cust.City);
}
Console.ReadLine();
}
The example in step 1 of task 3 shows a query. It is executed when the code above consumes the results. At that point, a corresponding SQL command is executed and objects are materialized. This concept is called ‘lazy evaluation’. It allows queries to be composed without incurring the cost of an immediate round-trip to the database, query execution and object materialization. The query expressions are not evaluated until the results are needed. The code above results in the execution of the query defined in step 1 of task 3.
3. Press F5 to debug the solution
4. Press ENTER to exit the application
The call to the Console.ReadLine method prevents the console window from disappearing immediately. In subsequent tasks, this step will not be stated explicitly.
1. After the Customer class definition, create the Order entity class definition with the following code, indicating that Orders.Customer relates as a foreign key to Customers.CustomerID:
[Table(Name="Orders")]
public class Order
{
private int _OrderID;
private string _CustomerID;
private EntityRef<Customer> _Customer;
public Order() {this._Customer = new EntityRef<Customer>();}
[Column(Storage="_OrderID", DBType="Int NOT NULL IDENTITY",
Id=true, AutoGen=true)]
public int OrderID
{
get { return this._OrderID; }
// No need to specify a setter because AutoGen is true
}
[Column(Storage="_CustomerID", DBType="NChar(5)")]
public string CustomerID
{
get { return this._CustomerID; }
set { this._CustomerID = value; }
}
[Association(Storage="_Customer", ThisKey="CustomerID")]
public Customer Customer
{
get { return this._Customer.Entity; }
set { this._Customer.Entity = value; }
}
}
2. In this step the Customer class is annotated to indicate its relationship to the Order class. This is not strictly necessary, as defining it in either direction is sufficient to create the link; however, it allows you to easily navigate objects in either direction. Add the following code to the Customer class to see the association from the other direction:
public class Customer
{
private EntitySet<Order> _Orders;
public Customer() { this._Orders = new EntitySet<Order>(); }
[Association(Storage="_Orders", OtherKey="CustomerID")]
public EntitySet<Order> Orders
{
get { return this._Orders; }
set { this._Orders.Assign(value); }
}
…
}
3. You can now access Order objects directly from the Customer objects, or vice-versa. Modify the Main method with the following code to demonstrate an implicit join:
// Query for customers who have placed orders
var custs =
from c in Customers
where c.Orders.Any()
select c;
foreach (var cust in custs)
{
Console.WriteLine("ID={0}, Qty={1}", cust.CustomerID, cust.Orders.Count);
}
4. Press F5 to debug the solution
1. Add the following code above the Customer class declaration:
public class Northwind : DataContext
{
// Table<T> abstracts database details per table/date type
public Table<Customer> Customers;
public Table<Order> Orders;
public Northwind(string connection) : base(connection) { }
}
2. Make the following changes to the Main method to use the strongly-typed DataContext.
// Use a standard connection string
Northwind db = new Northwind(
@"C:\Program Files\LINQ Preview\Data\northwnd.mdf");
// Query for customers from Seattle
var custs =
from c in db.Customers
where c.City == "Seattle"
select c;
foreach( var cust in custs)
{
Console.WriteLine("ID={0}", cust.CustomerID);
}
3. Press F5 to debug the solution
This optional feature is convenient since calls to GetTable<T> are not needed. Strongly typed tables can be used in all queries once such a class derived from DataContext is used.
1. Generating the database table relationships can be tedious and prone to error. Until Visual Studio is extended to support LINQ, you can run a code generation tool, SQLMetal, manually. Click the Start | Programs | Microsoft Visual Studio 2005 | Visual Studio Tools | Visual Studio 2005 Command Prompt menu item.
2. Execute the following command to change directory to the project location:
cd "C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\DLinqHOL\DLinqHOL"
3. Generate the entire Northwind class hierarchy, annotated with primary key and foreign key designations by entering the following command:
"C:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:.\SQLExpress /database:"c:\program files\LINQ Preview\data\northwnd.mdf" /pluralize /code:Northwind.cs
4. In Microsoft Visual Studio, in the Solution Explorer, click the DLinqHOL | Add | Existing Item menu command.
5. Locate the new Northwind.cs file, then click Add
6. In Program.cs, remove the Northwind, Order, and Customer classes
7. In Solution Explorer, double-click Northwind.cs
8. On the line starting with public partial class, select CProgramFilesLINQPreviewDataNorthwndMdf, replace it with a shorter name Northwind. Search and replace to make sure that all instances are changed.
This step is not necessary, but provides a better-formatted name. Here we have not explicitly named the database when attaching the MDF file. Hence the name of the database is the full pathname of the MDF file. For a suitably named database, the generated class name would be more appropriate.
9. For New name, enter "Northwind"
10. Click OK, then Apply
11. Press F5 to debug the solution
Now let’s try a query in Program.cs with the generated classes in Northwinds.cs
12. Rerun the query from Task 3, step 1
Northwind db = new Northwind(
@"C:\Program Files\LINQ Preview\Data\northwnd.mdf");
// Query for customers in London
var custs =
from c in db.Customers
where c.City == "London"
select c;
foreach (Customer c in custs)
{
Console.WriteLine(c.CompanyName);
}
13. Press F5 to debug the solution
14. So far we have run queries that retrieve entire objects. But you can also select the properties of interest. The following query retrieves only the ContactName property.
var q =
from c in db.Customers
where c.Region == null
select c.ContactName;
foreach (var c in q) Console.WriteLine(c);
Console.ReadLine();
15. It is also possible to create composite results, as in traditional SQL where an arbitrary collection of columns can be returned as a result set. In DLinq, this is accomplished through the use of anonymous types. Modify the code as shown to create a new object type to return the desired information:
var q =
from c in db.Customers
where c.Region == null
select new{Company=c.CompanyName, Contact=c.ContactName};
foreach (var c in q)
Console.WriteLine("{0}/{1}", c.Contact, c.Company);
Console.ReadLine();
16. Press F5 to debug the application
17. Change the code as follows to do a join:
var ids = (
from c in db.Customers
from e in db.Employees
where c.City == e.City
select e.EmployeeID )
.Distinct();
foreach( var id in ids)
{
Console.WriteLine(id);
}
18. Press F5 to debug the solution