How to define One-Many and One-One relationships

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 EntitySetattribute. To define a one-one relationship between the address and phone class, we need to use the EntityRefattribute.

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. ThisKeyand 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.

Hide    Copy Code

[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:

Hide    Shrink      Copy Code

[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.

Hide    Shrink      Copy Code

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:

Hide    Shrink      Copy Code

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:

Hide    Shrink      Copy Code

[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:

Hide    Copy Code

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.

Hide    Copy Code

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.

Hide    Copy Code

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.