Home > Linq > LINQ # 4 – LINQ to SQL – (Part I ) (Data Context , Entity Classes)

LINQ # 4 – LINQ to SQL – (Part I ) (Data Context , Entity Classes)

 

1) LINQ to SQL takes care of translating LINQ expressions to equivalent T-SQL and passing it on to the database for execution and then returning the results back to the calling application by tracking changes made to the objects. LINQ to SQL reduces a lot of programming time. It comes with two different design time tools which are used for converting the relational database objects into object definitions.

2) LINQ to SQL, not only provides the feature of querying or referring to the relational objects, but it also has the ability to create a database and database objects

3)The main advantage of LINQ to SQL is DBMS interoperability,means we can use the same syntax to fire the query against SQLServer , MySQL ,Oracle etc.

DataContext

DataContext is an object that corresponds to the relational database object by which all other objects are referred to or accessed.

It takes a string or a connection object that implements IDbConnection as the parameter to connect to a particular database object.

It takes care of translating the Language Integrated Queries into T-SQL queries to execute against database, and then translating the results back to the calling application.

We can have the strongly typed DataContext, which has the definition of all objects in the database. It’s not only used for accessing existing tables of the database, but is also for creating a new database. DataContext is a collection of all the objects of
the database.

Suppose We have A database IceCream in the SqlServer so we will create DataContext for it as follows

DataContext dataCon = new DataContext("Data Source=.\sqlexpress;
Initial Catalog=IceCreams; Integrated Security=true"
);

 

The dataBase data context as shown in the above code, is a strongly typed DataContext which has all the table collections declared in it. A sample of the DataContext would look like this:

public class IceCreams: DataContext
{
public Table<Categories> Categories;
public Table<Items> Items;
public IceCreams(string connection) : base(connection) {}
}

EntityClass

1)Entity classes are the objects which represent the database tables

2)For each table we need to add the definitions of each table with its columns and its attributes.

3)System.Data.Linq.Mapping  is the namespace that contains the definition for all the attributes. We have to include this in the project to specify the attributes

Steps To add entity class LINQ to SQL classes (DataContext) as follows :-

a)By Saying Add new item to a console application select LINQ to SQL Classes :-

image

 

b) You wil see an window like below after clicking on add :-

image

c)Then just drag the table those you want in your application

image

 

Here we have added three tables as follows

 

image

 

d)we will have our datacontext class generated for above three tables automatically as follows :-

 
//——————————————————————————
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:4.0.30319.225
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//——————————————————————————

namespace xyz
{
   
using System.Data.Linq;
   
using System.Data.Linq.Mapping;
   
using System.Data;
   
using System.Collections.Generic;
   
using System.Reflection;
   
using System.Linq;
   
using System.Linq.Expressions;
   
using System.ComponentModel;
   
using System;
    
    
   
[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="dbMvc")]
   
public partial class IceCreams1DataContext : System.Data.Linq.DataContext
   
{
        
       
private static System.Data.Linq.Mapping.MappingSource mappingSource 
       = new AttributeMappingSource();
        
   
#region Extensibility Method Definitions
   
partial void OnCreated();
   
partial void InserttblClass(tblClass instance);
   
partial void UpdatetblClass(tblClass instance);
   
partial void DeletetblClass(tblClass instance);
   
partial void InserttblDivision(tblDivision instance);
   
partial void UpdatetblDivision(tblDivision instance);
   
partial void DeletetblDivision(tblDivision instance);
   
partial void InserttblStudent(tblStudent instance);
   
partial void UpdatetblStudent(tblStudent instance);
   
partial void DeletetblStudent(tblStudent instance);
   
#endregion
        
       
public IceCreams1DataContext() : 
               
base(global::System.Configuration.ConfigurationManager.ConnectionStrings["dbMvcConnectionString"].ConnectionString, mappingSource)
       
{
           
OnCreated();
       
}
        
       
public IceCreams1DataContext(string connection) : 
               
base(connection, mappingSource)
       
{
           
OnCreated();
       
}
        
       
public IceCreams1DataContext(System.Data.IDbConnection connection) : 
               
base(connection, mappingSource)
       
{
           
OnCreated();
       
}
        
       
public IceCreams1DataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
               
base(connection, mappingSource)
       
{
           
OnCreated();
       
}
        
       
public IceCreams1DataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
               
base(connection, mappingSource)
       
{
           
OnCreated();
       
}
        
       
public System.Data.Linq.Table<tblClass> tblClasses
       
{
           
get
           
{
               
return this.GetTable<tblClass>();
           
}
       
}
        
       
public System.Data.Linq.Table<tblDivision> tblDivisions
       
{
           
get
           
{
               
return this.GetTable<tblDivision>();
           
}
       
}
        
       
public System.Data.Linq.Table<tblStudent> tblStudents
       
{
           
get
           
{
               
return this.GetTable<tblStudent>();
           
}
       
}
   
}
    
   
[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.tblClass")]
   
public partial class tblClass : INotifyPropertyChanging, INotifyPropertyChanged
   
{
        
       
private static PropertyChangingEventArgs emptyChangingEventArgs 
= new PropertyChangingEventArgs(String.Empty);
        
       
private int _Class_ID;
        
       
private string _Class_Name;
        
       
private System.Nullable<bool> _IsActive;
        
   
#region Extensibility Method Definitions
   
partial void OnLoaded();
   
partial void OnValidate(System.Data.Linq.ChangeAction action);
   
partial void OnCreated();
   
partial void OnClass_IDChanging(int value);
   
partial void OnClass_IDChanged();
   
partial void OnClass_NameChanging(string value);
   
partial void OnClass_NameChanged();
   
partial void OnIsActiveChanging(System.Nullable<bool> value);
   
partial void OnIsActiveChanged();
   
#endregion
        
       
public tblClass()
       
{
           
OnCreated();
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Class_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
       
public int Class_ID
       
{
           
get
           
{
               
return this._Class_ID;
           
}
           
set
           
{
               
if ((this._Class_ID != value))
               
{
                   
this.OnClass_IDChanging(value);
                   
this.SendPropertyChanging();
                   
this._Class_ID = value;
                   
this.SendPropertyChanged("Class_ID");
                   
this.OnClass_IDChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Class_Name", DbType="NVarChar(50)")]
       
public string Class_Name
       
{
           
get
           
{
               
return this._Class_Name;
           
}
           
set
           
{
               
if ((this._Class_Name != value))
               
{
                   
this.OnClass_NameChanging(value);
                   
this.SendPropertyChanging();
                   
this._Class_Name = value;
                   
this.SendPropertyChanged("Class_Name");
                   
this.OnClass_NameChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_IsActive", DbType="Bit")]
       
public System.Nullable<bool> IsActive
       
{
           
get
           
{
               
return this._IsActive;
           
}
           
set
           
{
               
if ((this._IsActive != value))
               
{
                   
this.OnIsActiveChanging(value);
                   
this.SendPropertyChanging();
                   
this._IsActive = value;
                   
this.SendPropertyChanged("IsActive");
                   
this.OnIsActiveChanged();
               
}
           
}
       
}
        
       
public event PropertyChangingEventHandler PropertyChanging;
        
       
public event PropertyChangedEventHandler PropertyChanged;
        
       
protected virtual void SendPropertyChanging()
       
{
           
if ((this.PropertyChanging != null))
           
{
               
this.PropertyChanging(this, emptyChangingEventArgs);
           
}
       
}
        
       
protected virtual void SendPropertyChanged(String propertyName)
       
{
           
if ((this.PropertyChanged != null))
           
{
               
this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
           
}
       
}
   
}
    
   
[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.tblDivision")]
   
public partial class tblDivision : INotifyPropertyChanging, INotifyPropertyChanged
   
{
        
       
private static PropertyChangingEventArgs emptyChangingEventArgs 
= new PropertyChangingEventArgs(String.Empty);
        
       
private int _Division_ID;
        
       
private string _Division;
        
       
private System.Nullable<bool> _IsActive;
        
   
#region Extensibility Method Definitions
   
partial void OnLoaded();
   
partial void OnValidate(System.Data.Linq.ChangeAction action);
   
partial void OnCreated();
   
partial void OnDivision_IDChanging(int value);
   
partial void OnDivision_IDChanged();
   
partial void OnDivisionChanging(string value);
   
partial void OnDivisionChanged();
   
partial void OnIsActiveChanging(System.Nullable<bool> value);
   
partial void OnIsActiveChanged();
   
#endregion
        
       
public tblDivision()
       
{
           
OnCreated();
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Division_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
       
public int Division_ID
       
{
           
get
           
{
               
return this._Division_ID;
           
}
           
set
           
{
               
if ((this._Division_ID != value))
               
{
                   
this.OnDivision_IDChanging(value);
                   
this.SendPropertyChanging();
                   
this._Division_ID = value;
                   
this.SendPropertyChanged("Division_ID");
                   
this.OnDivision_IDChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Division", DbType="NVarChar(50)")]
       
public string Division
       
{
           
get
           
{
               
return this._Division;
           
}
           
set
           
{
               
if ((this._Division != value))
               
{
                   
this.OnDivisionChanging(value);
                   
this.SendPropertyChanging();
                   
this._Division = value;
                   
this.SendPropertyChanged("Division");
                   
this.OnDivisionChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_IsActive", DbType="Bit")]
       
public System.Nullable<bool> IsActive
       
{
           
get
           
{
               
return this._IsActive;
           
}
           
set
           
{
               
if ((this._IsActive != value))
               
{
                   
this.OnIsActiveChanging(value);
                   
this.SendPropertyChanging();
                   
this._IsActive = value;
                   
this.SendPropertyChanged("IsActive");
                   
this.OnIsActiveChanged();
               
}
           
}
       
}
        
       
public event PropertyChangingEventHandler PropertyChanging;
        
       
public event PropertyChangedEventHandler PropertyChanged;
        
       
protected virtual void SendPropertyChanging()
       
{
           
if ((this.PropertyChanging != null))
           
{
               
this.PropertyChanging(this, emptyChangingEventArgs);
           
}
       
}
        
       
protected virtual void SendPropertyChanged(String propertyName)
       
{
           
if ((this.PropertyChanged != null))
           
{
               
this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
           
}
       
}
   
}
    
   
[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.tblStudent")]
   
public partial class tblStudent : INotifyPropertyChanging, INotifyPropertyChanged
   
{
        
      
private static PropertyChangingEventArgs emptyChangingEventArgs
 = new PropertyChangingEventArgs(String.Empty);
        
       
private int _Student_Id;
        
       
private string _Student_Name;
        
       
private System.Nullable<int> _Student_Age;
        
       
private System.Nullable<int> _Student_Class;
        
       
private System.Nullable<int> _Student_Division;
        
       
private System.Nullable<bool> _IsCurrent;
        
   
#region Extensibility Method Definitions
   
partial void OnLoaded();
   
partial void OnValidate(System.Data.Linq.ChangeAction action);
   
partial void OnCreated();
   
partial void OnStudent_IdChanging(int value);
   
partial void OnStudent_IdChanged();
   
partial void OnStudent_NameChanging(string value);
   
partial void OnStudent_NameChanged();
   
partial void OnStudent_AgeChanging(System.Nullable<int> value);
   
partial void OnStudent_AgeChanged();
   
partial void OnStudent_ClassChanging(System.Nullable<int> value);
   
partial void OnStudent_ClassChanged();
   
partial void OnStudent_DivisionChanging(System.Nullable<int> value);
   
partial void OnStudent_DivisionChanged();
   
partial void OnIsCurrentChanging(System.Nullable<bool> value);
   
partial void OnIsCurrentChanged();
   
#endregion
        
       
public tblStudent()
       
{
           
OnCreated();
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Student_Id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
       
public int Student_Id
       
{
           
get
           
{
               
return this._Student_Id;
           
}
           
set
           
{
               
if ((this._Student_Id != value))
               
{
                   
this.OnStudent_IdChanging(value);
                   
this.SendPropertyChanging();
                   
this._Student_Id = value;
                   
this.SendPropertyChanged("Student_Id");
                   
this.OnStudent_IdChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Student_Name", DbType="NVarChar(50)")]
       
public string Student_Name
       
{
           
get
           
{
               
return this._Student_Name;
           
}
           
set
           
{
               
if ((this._Student_Name != value))
               
{
                   
this.OnStudent_NameChanging(value);
                   
this.SendPropertyChanging();
                   
this._Student_Name = value;
                   
this.SendPropertyChanged("Student_Name");
                   
this.OnStudent_NameChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Student_Age", DbType="Int")]
       
public System.Nullable<int> Student_Age
       
{
           
get
           
{
               
return this._Student_Age;
           
}
           
set
           
{
               
if ((this._Student_Age != value))
               
{
                   
this.OnStudent_AgeChanging(value);
                   
this.SendPropertyChanging();
                   
this._Student_Age = value;
                   
this.SendPropertyChanged("Student_Age");
                   
this.OnStudent_AgeChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Student_Class", DbType="Int")]
       
public System.Nullable<int> Student_Class
       
{
           
get
           
{
               
return this._Student_Class;
           
}
           
set
           
{
               
if ((this._Student_Class != value))
               
{
                   
this.OnStudent_ClassChanging(value);
                   
this.SendPropertyChanging();
                   
this._Student_Class = value;
                   
this.SendPropertyChanged("Student_Class");
                   
this.OnStudent_ClassChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Student_Division", DbType="Int")]
       
public System.Nullable<int> Student_Division
       
{
           
get
           
{
               
return this._Student_Division;
           
}
           
set
           
{
               
if ((this._Student_Division != value))
               
{
                   
this.OnStudent_DivisionChanging(value);
                   
this.SendPropertyChanging();
                   
this._Student_Division = value;
                   
this.SendPropertyChanged("Student_Division");
                   
this.OnStudent_DivisionChanged();
               
}
           
}
       
}
        
       
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_IsCurrent", DbType="Bit")]
       
public System.Nullable<bool> IsCurrent
       
{
           
get
           
{
               
return this._IsCurrent;
           
}
           
set
           
{
               
if ((this._IsCurrent != value))
               
{
                   
this.OnIsCurrentChanging(value);
                   
this.SendPropertyChanging();
                   
this._IsCurrent = value;
                   
this.SendPropertyChanged("IsCurrent");
                   
this.OnIsCurrentChanged();
               
}
           
}
       
}
        
       
public event PropertyChangingEventHandler PropertyChanging;
        
       
public event PropertyChangedEventHandler PropertyChanged;
        
       
protected virtual void SendPropertyChanging()
       
{
           
if ((this.PropertyChanging != null))
           
{
               
this.PropertyChanging(this, emptyChangingEventArgs);
           
}
       
}
        
       
protected virtual void SendPropertyChanged(String propertyName)
       
{
           
if ((this.PropertyChanged != null))
           
{
               
this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
           
}
       
}
   
}
}

In tomorrow’s blog we will see how to fire SELECT,INSERT,UPDATE queries on the tables using LINQ for the above classes.

Thanks.

Advertisements
Categories: Linq
  1. August 3, 2011 at 5:00 am

    Its really easy and understable !!!
    Nice to have your blog !!!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: