Chapter 8 : Reading Objects with LINQ to SQL

It uses the mapping of classes to tables to translate LINQ queries to SQL commands and then materializes objects from the rows returned. The objects can be related to each other in a graph of objects that is managed by LINQ to SQL on your behalf.

Defining Inheritance

The relationship between entities is one key aspect of an object model. Inheritance is another. LINQ to SQL supports the mapping of an inheritance hierarchy to a single table or a view. Consider the example of three classes: base class and two levels of derived classes, )  and , that map to the Customers table, as shown in Figure 8.4. Because the Northwind database does not have a table with such data, let's first create a suitable table for mapping such a hierarchy. The Data Definition Language (DDL) statements shown next create a table for instances of the Customer hierarchy.



Figure 8.4 An inheritance class diagram.

Before running the following DDL statements, make sure that you are working with a copy of the Northwind database so that your original copy will remain unaffected. You can do so by copying the Northwnd.mdf database file to another directory and attaching it with SQL Server Express. Then you can run the following T-SQL commands in SQL Server Management Studio. (The Express version can be downloaded for free for SQL Express.) We will start by making copies of the Customers and Orders tables as the Customers2 and Orders2 tables and setting appropriate primary and foreign key constraints on them:

----  Copy Customers and Orders tables select * into Customers2 from customers

select * into Orders2 from Orders

 

----  Set up primary keys and foreign key

alter table customers2 add constraint customers2_pk primary key (customerid)

alter table orders2 add constraint orders2_pk primary key(orderid)

alter table orders2 add constraint orders2_customers2_fk foreign key(customerid) references customers2(customerid)

----  Add type discriminator and type--specific columns

alter table customers2 add Type smallint not null default O

 

alter table customers2 add Discount decimal

 

alter table customers2 add AccountExecutive nvarchar(256)

 

----  Change some rows to premier and elite customer type update customers2 set Type = 1, Discount = 5

where customerid in (

select customerid from orders group by customerid having count(customerid) between 1O and 15)

update customers2

set Type = 2, Discount = 1O, AccountExecutive = "Top Seller"

where customerid in (

select customerid from orders group by customerid having count(customerid) > 15)

Next, we need to make the schema and data suitable for an inheritance hierarchy. The rows for the three types of customers are differentiated using the inheritance discriminator column % . The mapping uses the discriminator values to indicate how LINQ to SQL should materialize a given row from the Customers table. Here we will use the type values L, 0, and 2 for , ) , and , respectively. Additional, type-specific data is stored in nullable columns. PremierCustomer gets a non-null ? " , and gets an 6"" = " * in addition to the inherited member ? " . The following handcrafted mapping shows how the hierarchy can be mapped to the Customers2 and Orders2 tables. The mapped types in the class hierarchy need to be specified in the mapping of the base class using the " + %% attribute. This ensures that LINQ to SQL knows about all derived classes it might encounter while reading data from the table mapped to multiple types in the hierarchy. The property of the " + %% attribute indicates the discriminator values used for deciding the type of the object to be constructed from a given row in the table. It corresponds to the column that has ? " set to . In the preceding example, if the % column contains the code L, LINQ to SQL creates a object. If it contains 0, a ) is created,
and so on. The default type is marked, setting the property ? to . It is used when LINQ to SQL encounters a value for the discriminator that is not specified in the " + %% attributes.

[Table(Name = "Customers2")]

[lnheritanceMapping(Code = O, Type = typeof(Customer), lsDefault = true)] [lnheritanceMapping(Code = 1, Type = typeof(PremierCustomer))] [lnheritanceMapping(Code = 2, Type = typeof(EliteCustomer))]

public class Customer

{

[Column(lsPrimaryKey = true)] public string CustomerlD; [Column(lsDiscriminator = true)] public short Type;

 

[Column]

public string Country;

[Association(OtherKey = "CustomerlD")]

public List<Order> Orders;

}

 

public class PremierCustomer : Customer

{

[Column]

public decimal Discount;

}

 

public class EliteCustomer : PremierCustomer

{

[Column]

public string AccountExecutive;

}

 

[Table(Name=”Orders2”)]

public class Order

{

[Column(lsPrimaryKey=true)]

public int OrderlD; [Column]

public string CustomerlD;

 

[Association(ThisKey="CustomerlD", lsForeignKey=true)]

public Customer Customer;

}

 

class NorthwindDataContext : DataContext

{

public Table<Customer> Customers2;

public Table<Order> Orders2;

 

public NorthwindDataContext(string s) : base(s) { }

}

The mapped classes can be used for queries that return results of mixed types or of a specific type, as shown in the next code segment. The first query returns rows for all the types and creates the instances of correct types based on the discriminator code. The second query returns results of types ) and due to the E % extension method. The discriminator value is used to filter out results of other types, and the constructed type is determined by the discriminator code in the row. The third query returns only instances.

II Get entities of each type including derived types var AllCustQuery = db.Customers2;

var PremierCustQuery = db.Customers2.OfType<PremierCustomer>();

var EliteCustQuery = db.Customers2.OfType<EliteCustomer>();

 

II Use type--restricted sequence for further queries

var UKPremierQuery = from c in db.Customers2.OfType<PremierCustomer>()

where c.Country == "UK"

select c;

In each case, the E % extension method is translated into an appropriate 57 clause in SQL on the discriminator column to reduce the amount of data fetched. The following SQL is generated for the )  ' shown before. It uses the SQL 57 clause to restrict the rows in the result to ) and its derived types. Thus, LINQ to SQL ensures efficient execution and minimal data transfer for inheritance mapping.

SELECT [tO].[Type], [tO].[AccountExecutive], [tO].[Discount], [tO].[CustomerlD], [tO].[Country]

FROM [Customers2] AS [tO]

WHERE ([tO].[Type] = IpO) OR ([tO].[Type] = Ip1)

----  IpO: lnput Smalllnt (Size = O; Prec = O; Scale = O) [2]

----  Ip1: lnput Smalllnt (Size = O; Prec = O; Scale = O) [1]

The designer in Visual Studio also supports inheritance mapping. Figure 8.5 shows the menu options used to define a derived class.



Figure 8.5 Defining inheritance in the designer.

After a derived class has been created using the New Inheritance dialog displayed by selecting the Inheritance menu option, the members from the base class need to be deleted from the derived class. This leaves only the members specific to the derived class. Then you can click the inheritance relationship, shown as an arrow from the derived class to the base class, to add information about the discriminant column and code values. Figure 8.6 shows the resulting class hierarchy for the Customers2 table.



Figure 8.6 A class hierarchy on the designer surface.

An inheritance hierarchy may be a target of a relationship. E  may point to any of the classes in the hierarchy. Likewise, an may also contain instances of any of the types in the hierarchy. For example, a salesperson managing multiple customers may have an that contains instances of derived classes as well. LINQ to SQL ensures that the materialization is done according to the inheritance mapping.