Mapping SQL Data Base Table with LINQ (Linq to Sql)

Here are Show step to mapping linq query and retrieve data from sql data base

Step 01 : mapping linq collection class with sql dataTabel

i.) import following namespace

using System.Data.Linq;

using System.Data.Linq.Mapping;

ex :- [Table(Name = "D01_Invoice")] //data table Name

public class Invoice

{

private int _invoiceID;

[Column(IsPrimaryKey = true, Storage = "_invoiceID", Name = "D01_id")] //data mapping column Name

public int InvoiceID

{

get

{ return this._invoiceID; }

set { this._invoiceID = value; }

}

private string _invoiceNumber;

[Column(Storage = "_invoiceNumber", Name = "D01_InvoiceNumber", DbType = "numeric(18, 0)")]

public string InvoiceNumber

{

get

{

return this._invoiceNumber;

}

set

{

this._invoiceNumber = value;

}

}

}

step 2:

Create "DataContex" object . so that u have to give connection string or file store path

//string ConnSQL = ConfigurationManager.ConnectionStrings["ConnSQL"].ToString();

// SqlConnection sqlConnection = new SqlConnection(ConnSQL);

//sqlConnection.Open();

DataContext dataContex = new DataContext(sqlConnection);

step 3:

call linq query

IEnumerable invoice = dataContex.GetTable(); //it can be list

those are the basic step to mapping data table and getting data table using linq

step 4 :

if u need select data from linq data collection use following way

select query

IEnumerable empQuery =

from emp in Invoice

select emp;

filter Query

IEnumerable empQuery =

from emp in employees

where emp.Department == "IT Department" &&

emp.City == "Redmond"

select emp;

Order Query

IEnumerable empQuery =

from emp in employees

orderby emp.Department

select emp;

Sample code for Group

Sample of C# Code

Sample Code for Join

IEnumerable invoice = dataContex.GetTable();

IEnumerable invoiceDetails = dataContex.GetTable();

var invo = from inv in invoice

join details in invoiceDetails on inv.InvoiceNumber equals details.InvoiceNumberDetails into invDetails

select new { id= inv.InvoiceID,

invoiceNum = inv.InvoiceNumber,

invoiceDetails = invDetails};

foreach (var inv in invo)

{

Console.WriteLine(inv.invoiceNum);

foreach (var indetails in inv.invoiceDetails)

{

Console.WriteLine(indetails.InvoiceNumberDetails + indetails.ItemCode);

}

}