Click here to Skip to main content
11,496,146 members (454 online)
Click here to Skip to main content

One-Many and One-One relationship using LINQ to SQL

, 5 Jul 2009 CPOL 70.7K 1.1K 51
One-Many and One-One relationship using LINQ to SQL
This is an old version of the currently published article.

One-Many and One-One relationship using LINQ to SQL

Introduction

Previous LINQ,Silverlight,WCF,WPF and WWF articles

Simplest LINQ to SQL example

Encapsulated LINQ classes with Set and Get

One-Many and One-One relationship

Source code
 

Introduction
 

In this article we will start with a basic LINQ to SQL example and then see how we can implement one-many and one-one relationship using ‘Entityref’ and ‘EntitySet’. We have also attached a source which demonstrates the same in a practical manner.

Catch my videos for WCF, WPF, WWF, LINQ , Silverlight,Design patterns , UML and lot on http://www.questpond.com
 

Previous LINQ,Silverlight,WCF,WPF and WWF articles
 

Below are some of my old articles which you would like to refer in case you are not acquainted with .NET 3.5 basic concepts.

  1. This article talks about a complete 3 tier implementation using LINQ :- http://www.codeproject.com/KB/aspnet/SaltAndPepper.aspx
     
  2.  WCF FAQ series covering simple to advance concepts :- http://www.codeproject.com/KB/aspnet/WCF.aspx
     
  3. WPF and Silverlight FAQ series covering layout, animation and bindings. :- http://www.codeproject.com/KB/WPF/WPFSilverLight.aspx 
     
  4. Windows work flow FAQ series covering state machines and sequential work flow in detail. :- http://www.codeproject.com/KB/WF/WWF.aspx 
     

Simplest LINQ to SQL example
 

So let’s first start with a simple LINQ to SQL example and then we will try to understand how we can establish relationship in LINQ entities.
 

Step 1:- Define Entity classes using LINQ
 

When we design project using tiered approach like 3-tier or N-tier we need to create business classes and objects. For instance below is a simple class which defines a class which is mapped to a country table as shown below. You can see we how the class properties are mapped in one to one fashion with the table. These types of classes are termed as entity classes.
 

In LINQ we need to first define these entity classes using attribute mappings. You need to import “System.Data.Linq.Mapping;” namespace to get attributes for mapping. Below is the code snippet which shows how the ‘Table’ attribute maps the class with the database table name ‘Customer’ and how ‘Column’ attributes helps mapping properties with table columns.
 

[Table(Name = "Customer")]
public class clsCustomerEntityWithProperties
{
private int _CustomerId;
private string _CustomerCode;
private string _CustomerName;

[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;
}
}

[Column(DbType = "int", IsPrimaryKey = true)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}

Below is a more sophisticated pictorial view of the entity classes mapping with the customer table structure.

Step 2:- Use the datacontext to bind the table data with the entity objects.
 

The second step is use the data context object of LINQ to fill your entity objects. Datacontext acts like a mediator between database objects and your LINQ entity mapped classes.
 

So the first thing is to create the object of datacontext and create a active connection using the SQL connection string.

DataContext objContext = new DataContext(strConnectionString);


The second thing is to get the entity collection using the table data type. This is done using the ‘gettable’ function of the datacontext.
 

Table<clsCustomerEntity> objTable =
objContext.GetTable<clsCustomerEntity>();


Once we get all the data in table collection it’s time to browse through the table collection and display the record.

foreach (clsCustomerEntity objCustomer in objTable)
{
Response.Write(objCustomer.CustomerName + "<br>");
}

You can get the above source code which is attached with this article.
 

Encapsulated LINQ classes with Set and Get
 

In the previous example we had exposed the entity class properties as public properties, which violate the basic rule of encapsulation. You can define setter and getter functions which encapsulate the private properties.
 

[Table(Name = "Customer")]
public class clsCustomerEntityWithProperties
{
private int _CustomerId;
private string _CustomerCode;
private string _CustomerName;

[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;
}
}

[Column(DbType = "int", IsPrimaryKey = true)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}
 

One-Many and One-One relationship
 

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 one-many relationship while address and phone has one-one relationship.
 

To define one-many relationship between customer and address classes we need to use ‘EntitySet’ attribute. To define one-one relationship between address and phone class we need to use ‘EntityRef’ attribute.

Note :- You need to define primary key attribute for every entity class or else the mapping relationship will not work. 

So below is the class entity snippet for customer class which shows how it has used ‘EntitySet’ to define one-many relationship with address class. Association is defined using ‘Association’ attribute. ‘Association’ attribute has three important properties storage , thiskey and otherkey. ‘storage’ defines the name of private variable where the address object is stored, currently it is ‘_CustomerAddresses’. ‘ThisKey’ and ‘OtherKey’ defines which property will define the linkage , for this instance it is ‘CustomerId’. In other words both ‘Customer’ class and ‘Address’ class will have ‘CustomerId’ property in common.’ThisKey’ defines the name of property for customer class while ‘OtherKey’ define the property of 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 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 relationship between address class and 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;
}
}
}

Phone class which was aggregated 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.

So the first step is to create the data context object with connection initialized.
 

DataContext objContext = new DataContext(strConnectionString);

Second step is fire the query. Please note we are just firing the query for customer class. LINQ engine ensures that all the child tables data is extracted and place as per relationship 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 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 something as shown below. Every customer has multiple addresses and every address has a phone object.
 

Source code
 

We have also attached a source which has customer, address and phone tables. The sample code demonstrates a simple LINQ example, LINQ example with properties and relationship LINQ example with ‘entityref’ and ‘entityset’.
 


 

You can also download Source code from here

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shivprasad koirala
Architect http://www.questpond.com
India India

I am a Microsoft MVP for ASP/ASP.NET and currently a CEO of a small
E-learning company in India. We are very much active in making training videos ,
writing books and corporate trainings. Do visit my site for 
.NET, C# , design pattern , WCF , Silverlight
, LINQ , ASP.NET , ADO.NET , Sharepoint , UML , SQL Server  training 
and Interview questions and answers


Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
QuestionAddress and Phone can be one-to-many relationship Pin
onmyway13323-Oct-12 22:46
memberonmyway13323-Oct-12 22:46 
GeneralMy vote of 4 Pin
Armando de la Torre26-Jun-12 12:09
memberArmando de la Torre26-Jun-12 12:09 
GeneralHow to acheive same thing w/ a stored procedure Pin
mbcvamsidhar3-May-11 7:07
membermbcvamsidhar3-May-11 7:07 
GeneralHow to acheive same thin w/ a stored procedure Pin
mbcvamsidhar3-May-11 7:07
membermbcvamsidhar3-May-11 7:07 
GeneralMy vote of 5 Pin
Pravin Patil, Mumbai26-Jan-11 5:18
memberPravin Patil, Mumbai26-Jan-11 5:18 
GeneralEntity Relationship using XML mapping file ... Pin
rjschweiger1-Oct-09 15:01
memberrjschweiger1-Oct-09 15:01 
GeneralProblem while getting the records passing parameters.. [modified] Pin
vurugonda30-Jul-09 10:16
membervurugonda30-Jul-09 10:16 
GeneralHi Pin
prasad021-Jul-09 4:30
memberprasad021-Jul-09 4:30 
GeneralRe: Hi Pin
Shivprasad koirala1-Jul-09 5:04
memberShivprasad koirala1-Jul-09 5:04 
GeneralRe: Hi Pin
vurugonda30-Jul-09 10:14
membervurugonda30-Jul-09 10:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 6 Jul 2009
Article Copyright 2009 by Shivprasad koirala
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid