LINQ helps you define relationships using EntitySet
and EntityRef
. To understand how we can define relationships using LINQ, let’s consider the below example where we have a customer who can have many addresses and every address will have phone details. In other words, customer and address has a one-many relationship while address and phone has a one-one relationship.
To define a one-many relationship between the customer and address classes, we need to use the EntitySet
attribute. To define a one-one relationship between the address and phone class, we need to use the EntityRef
attribute.
Note: You need to define a primary key attribute for every entity class or else the mapping relationship will not work.
Shown below is the class entity snippet for the customer class which shows how it has used EntitySet
to define one-many relationship with the address class. Association is defined using the Association
attribute. TheAssociation
attribute has three important properties: storage
, thiskey
, and otherkey
. storage
defines the name of the private variable where the address object is stored. Currently it is _CustomerAddresses
. ThisKey
and OtherKey
define which property will define the linkage; for this instance, it is CustomerId
. In other words, both the Customer
class and the Address
class will have a CustomerId
property in common. ThisKey
defines the name of the property for the Customer
class while OtherKey
defines the property of the addresses class.
[Table(Name = "Customer")]
public class clsCustomerWithAddresses
{
private EntitySet<clsAddresses> _CustomerAddresses;
[Association(Storage = "_CustomerAddresses",
ThisKey="CustomerId", OtherKey = "CustomerId")]
public EntitySet<clsAddresses> Addresses
{
set
{
_CustomerAddresses = value;
}
get
{
return _CustomerAddresses;
}
}
}
Below is the complete code snippet with other properties of the customer class:
[Table(Name = "Customer")]
public class clsCustomerWithAddresses
{
private int _CustomerId;
private string _CustomerCode;
private string _CustomerName;
private EntitySet<clsAddresses> _CustomerAddresses;
[Column(DbType="int",IsPrimaryKey=true)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
[Column(DbType = "nvarchar(50)")]
public string CustomerCode
{
set
{
_CustomerCode = value;
}
get
{
return _CustomerCode;
}
}
[Column(DbType = "nvarchar(50)")]
public string CustomerName
{
set
{
_CustomerName = value;
}
get
{
return _CustomerName;
}
}
[Association(Storage = "_CustomerAddresses",
ThisKey="CustomerId", OtherKey = "CustomerId")]
public EntitySet<clsAddresses> Addresses
{
set
{
_CustomerAddresses = value;
}
get
{
return _CustomerAddresses;
}
}
}
To define the relationship between the address class and the phone class, we need to use the EntityRef
syntax. So below is the code snippet which defines the relationship using EntityRef
. All the other properties are same except that we need to define the variable using EntityRef
.
public class clsAddresses
{
private int _AddressId;
private EntityRef<clsPhone> _Phone;
[Column(DbType = "int", IsPrimaryKey = true)]
public int AddressId
{
set
{
_AddressId = value;
}
get
{
return _AddressId;
}
}
[Association(Storage = "_Phone",
ThisKey = "AddressId", OtherKey = "AddressId")]
public clsPhone Phone
{
set
{
_Phone.Entity = value;
}
get
{
return _Phone.Entity;
}
}
}
Below is a complete address class with other properties:
public class clsAddresses
{
private int _Customerid;
private int _AddressId;
private string _Address1;
private EntityRef<clsPhone> _Phone;
[Column(DbType="int")]
public int CustomerId
{
set
{
_Customerid = value;
}
get
{
return _Customerid;
}
}
[Column(DbType = "int", IsPrimaryKey = true)]
public int AddressId
{
set
{
_AddressId = value;
}
get
{
return _AddressId;
}
}
[Column(DbType = "nvarchar(50)")]
public string Address1
{
set
{
_Address1 = value;
}
get
{
return _Address1;
}
}
[Association(Storage = "_Phone",
ThisKey = "AddressId", OtherKey = "AddressId")]
public clsPhone Phone
{
set
{
_Phone.Entity = value;
}
get
{
return _Phone.Entity;
}
}
}
The phone class which is aggregated with the address class:
[Table(Name = "Phone")]
public class clsPhone
{
private int _PhoneId;
private int _AddressId;
private string _MobilePhone;
private string _LandLine;
[Column(DbType = "int", IsPrimaryKey = true)]
public int PhoneId
{
set
{
_PhoneId = value;
}
get
{
return _PhoneId;
}
}
[Column(DbType = "int")]
public int AddressId
{
set
{
_PhoneId = value;
}
get
{
return _PhoneId;
}
}
[Column(DbType = "nvarchar")]
public string MobilePhone
{
set
{
_MobilePhone = value;
}
get
{
return _MobilePhone;
}
}
[Column(DbType = "nvarchar")]
public string LandLine
{
set
{
_LandLine = value;
}
get
{
return _LandLine;
}
}
}
Now finally we need to consume this relationship in our ASPX client behind code:
The first step is to create the data context object with the connection initialized:
DataContext objContext = new DataContext(strConnectionString);
The second step is firing the query. Please note that we are just firing the query for the customer class. The LINQ engine ensures that all child table data is extracted and placed as per the relationships defined in the entity classes.
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerWithAddresses>()
select objCustomer;
Finally, we loop through the customer, loop through the corresponding addresses object, and display phone details as per the phone object.
foreach (clsCustomerWithAddresses objCustomer in MyQuery)
{
Response.Write(objCustomer.CustomerName + "<br>");
foreach (clsAddresses objAddress in objCustomer.Addresses)
{
Response.Write("===Address:- " + objAddress.Address1 + "<br>");
Response.Write("========Mobile:- " + objAddress.Phone.MobilePhone + "<br>");
Response.Write("========LandLine:- " +
objAddress.Phone.LandLine + "<br>");
}
}
The output looks as shown below. Every customer has multiple addresses and every address has a phone object.