Creating Object Models in LINQ

How to Creating Object Models in LINQ

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.

Step 1 – Creating a LINQ Solution

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

Step 2 – Mapping Northwind Customers

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. 

Step 3 – Querying Database Data

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.

Step 4 – Mapping Relationships Across Tables

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

Step 5 – Strongly-Typing the DataContext Object

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.

Step 6 – Using Code Generation to Create the Object Model

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

By Kalit Sikka   Popularity  (1393 Views)