Introduction to Linq To Stored Procedure

LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query. Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically.It is possible to migrate current ADO.NET solutions to LINQ to SQL in a piecemeal fashion (sharing the same connections and transactions) since LINQ to SQL is simply another component in the ADO.NET family. LINQ to SQL also has extensive support for stored procedures, allowing reuse of the existing enterprise assets. In addition it can filter data in .NET end which are coming from the stored proc.
Following are the steps describe you how LINQ to Stored proc works together.
Step 1-First create a table in your SQL server database-

I have created a table MyTable in the Northwind database.The table contains three feilds(MyID int,MyName Varchar,MyAddress varchar) MyID is a autogenerate number and primary key in this table.
Put some data in yout table

Step 2.Write the following stored proc in your database
-----------------------------------------------------------------------------------------------------------------------
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[spMyTableDelete] Script Date: 12/07/2009 00:08:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMyTableDelete]

(
@MyID int
-- @MyName varchar(50),
-- @MyAddress varchar(50)
)

AS
BEGIN
SET NOCOUNT ON;
DELETE from dbo.MyTable where MyID=@MyID
--SELECT MyID,MyName,MyAddress from dbo.MyTable
END
--select * from MyTable

-------------------------------------------------------------------------------------------------------------------------
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[spMyTableInsert] Script Date: 12/07/2009 00:09:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMyTableInsert]

(
@MyName varchar(50),
@MyAddress varchar(50)
)

AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.MyTable(MyName,MyAddress)VALUES(@MyName,@MyAddress)
END
------------------------------------------------------------------------------------------------------------------------
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[spMyTableSelect] Script Date: 12/07/2009 00:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMyTableSelect]

-- (
-- @MyID int,
-- @MyName varchar(50),
-- @MyAddress varchar(50)
-- )

AS
BEGIN
SET NOCOUNT ON;
SELECT MyID,MyName,MyAddress from dbo.MyTable
END
--select * from MyTable
----------------------------------------------------------------------------------------------------------------------------
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[spMyTableSelectMulti] Script Date: 12/07/2009 00:10:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMyTableSelectMulti]

-- (
-- @MyID int
-- @MyName varchar(50),
-- @MyAddress varchar(50)
-- )

AS

BEGIN
SET NOCOUNT ON;

SELECT MyID,MyName,MyAddress from dbo.MyTable where MyID=2
SELECT MyID,MyName,MyAddress from dbo.MyTable where MyID=1
END
--select * from MyTable
----------------------------------------------------------------------------------------------------------------------
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[spMyTableSelectMultiWithOutputParam] Script Date: 12/07/2009 00:11:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMyTableSelectMultiWithOutputParam]

(
@MyID int,
@MyName varchar(50) output
-- @MyAddress varchar(50)
)

AS

BEGIN
SET NOCOUNT ON;

SELECT MyID,MyName,MyAddress from dbo.MyTable
SELECT @MyName=(SELECT MyName from dbo.MyTable where MyID=@MyID)
END
--select * from MyTable
------------------------------------------------------------------------------------------------------------------------------

USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[spMyTableUpdate] Script Date: 12/07/2009 00:12:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMyTableUpdate]

(
@MyID int,
@MyName varchar(50),
@MyAddress varchar(50)
)

AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.MyTable SET MyName=@MyName,MyAddress=@MyAddress
WHERE MyID=@MyID
END

Step 3.Create a new web site in .NET IDE


Step 4.Choose "Add New Item" on your App_Code folder


Step 5.Choose "LINQ to SQL Classes" in yourAdd New Item Dialog and put the file name "MyDataClasses.dbml".


Step 6.Open the MyDataClasses.dbml and modify the name "MyDataContext".Click on "Server Explorer" in MyDataClasses.dbml


Step7.In the Server Explorer click on "Connect to database" icon


Step 8.In "Add Connection" dialog put the server name,database name and required credentials

Step 9.Drag the requird Stored proc in your Method Pane

Step 10.If the Method Pane is not visible,right click on the designer and click on "Show Methods Pane".

Step 11.Now go to your Default.aspx page and create few buttons ,event and a gridview
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="grdData" runat="server">
</asp:GridView>
</div>
<asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" Text="Insert" />
<asp:Button ID="btnUpdate" runat="server" OnClick="btnUpdate_Click" Text="Update" />
<asp:Button ID="btnSelect" runat="server" OnClick="btnSelect_Click" Text="Select" />
<asp:Button ID="btnDelete" runat="server" OnClick="btnDelete_Click" Text="Delete" />
<asp:Button ID="btnSelectMulti" runat="server" OnClick="btnSelectMulti_Click" Text="SelectMultipleDatatable" />
<asp:Button ID="btnSelectMultiWithOutputParam" runat="server" OnClick="btnSelectMultiWithOutputParam_Click"
Text="SelectMultipleWithOutputParameter" />
</form>
</body>
</html>

Step 12.In the Default.aspx.cs call the required stored procedure for Insert ,update ,select and Delete
MyDataContext db = new MyDataContext();

protected void btnInsert_Click(object sender, EventArgs e)
{
var q3 = db.spMyTableInsert("Indra", "Jadavpur");
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
var update = db.spMyTableUpdate(2,"Indra", "Jadavpur");

}
protected void btnSelect_Click(object sender, EventArgs e)
{

var Select2 = from o in db.spMyTableSelect()
where o.MyID <= 12 select new { o.MyID, o.MyName, o.MyAddress };
grdData.DataSource = Select2;
grdData.DataBind();
} protected void btnDelete_Click(object sender, EventArgs e)
{
var delete = db.spMyTableDelete(3);
}
Step 13.Now if you like to call a stored proc like dbo.spMyTableSelectMultiWithOutputParam which has a output param write teh follwoing code in your btnSelectMultiWithOutputParam click event
protected void btnSelectMultiWithOutputParam_Click(object sender, EventArgs e)
{
string strCustName = "";
var q2 = db.spMyTableSelectMultiWithOutputParam(1, ref strCustName);
Response.Write(strCustName.ToString());
grdData.DataSource = q2; grdData.DataBind();
}
The spMyTableSelectMultiWithOutputParam is like this
ALTER PROCEDURE dbo.spMyTableSelectMultiWithOutputParam
(
@MyID int, @MyName varchar(50) output
) AS
BEGIN
SET NOCOUNT ON;
SELECT MyID,MyName,MyAddress from dbo.MyTable
SELECT @MyName=(SELECT MyName from dbo.MyTable where MyID=@MyID)
END Step
14.Now if you want to call a storedproc which is returning multiple select statement like spMyTableSelectMulti,do the following steps---
The stored proc is like this
ALTER PROCEDURE dbo.spMyTableSelectMulti
AS
BEGIN
SET NOCOUNT ON;
SELECT MyID,MyName,MyAddress from dbo.MyTable where MyID=2
SELECT MyID,MyName,MyAddress from dbo.MyTable where MyID=1
END
Open the MyDataClassesDesigner.cs and comment the class spMyTableSelectMultiResult .

Then create a new class file clsMultiRecords.cs in App_code and create two partial class spMyTableSelectMultiResult and spMyTableSelectMultiResult2 First Class return the first SQL query and the second one return the second sql query.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

/// <summary>
/// Summary description for clsMultiRecords
/// </summary>
public class clsMultiRecords
{
public clsMultiRecords()
{
//
// TODO: Add constructor logic here
//
}
//[Function(Name = "dbo.spMyTableSelectMulti"), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult)), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult2))]
//public IMultipleResults spMyTableSelectMulti()
//{
// IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
// return ((IMultipleResults)(result.ReturnValue));
//}

public partial class spMyTableSelectMultiResult
{

private int _MyID;

private string _MyName;

private string _MyAddress;

public spMyTableSelectMultiResult()
{
}

[Column(Storage = "_MyID", DbType = "Int NOT NULL")]
public int MyID
{
get
{
return this._MyID;
}
set
{
if ((this._MyID != value))
{
this._MyID = value;
}
}
}

[Column(Storage = "_MyName", DbType = "VarChar(50)")]
public string MyName
{
get
{
return this._MyName;
}
set
{
if ((this._MyName != value))
{
this._MyName = value;
}
}
}

[Column(Storage = "_MyAddress", DbType = "VarChar(50)")]
public string MyAddress
{
get
{
return this._MyAddress;
}
set
{
if ((this._MyAddress != value))
{
this._MyAddress = value;
}
}
}
}
public partial class spMyTableSelectMultiResult2
{

private int _MyID;

private string _MyName;

private string _MyAddress;

public spMyTableSelectMultiResult2()
{
}

[Column(Storage = "_MyID", DbType = "Int NOT NULL")]
public int MyID
{
get
{
return this._MyID;
}
set
{
if ((this._MyID != value))
{
this._MyID = value;
}
}
}

[Column(Storage = "_MyName", DbType = "VarChar(50)")]
public string MyName
{
get
{
return this._MyName;
}
set
{
if ((this._MyName != value))
{
this._MyName = value;
}
}
}

[Column(Storage = "_MyAddress", DbType = "VarChar(50)")]
public string MyAddress
{
get
{
return this._MyAddress;
}
set
{
if ((this._MyAddress != value))
{
this._MyAddress = value;
}
}
}
}
}



Again open the DataClassesDesigner.cs and delete the follwoing portion

[Function(Name = "dbo.spMyTableSelectMulti")]
public ISingleResult spMyTableSelectMulti()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult)(result.ReturnValue));
}

and write the following code in place of that

[Function(Name = "dbo.spMyTableSelectMulti"), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult)), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult2))]
public IMultipleResults spMyTableSelectMulti()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((IMultipleResults)(result.ReturnValue));
}

This method will return the IMultipleResults instead of ISingleResult. Mind it,if you open the dbml(MyDataClasses.dbml not MyDataClasses.designer.cs) file and save/modify it, this above portion will be overridden by ISingleResult. Now go to the click event of btnSelectMulti and write the following code
protected void btnSelectMulti_Click(object sender, EventArgs e)
{
IMultipleResults results = new MyDataContext().spMyTableSelectMulti(); List result1 = results.GetResult().ToList();

List result2 = results.GetResult().ToList();


grdData.DataSource = result2;
grdData.DataBind();
}

Step 15.Now run your application ,you can now see the following output,click on different button to obeserve the operation



I am attaching the complete code for Default.aspx.cs ,MyDataContext class and clsMultiRecords.cs

Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data.Linq;

public partial class _Default : System.Web.UI.Page
{
MyDataContext db = new MyDataContext();
protected void Page_Load(object sender, EventArgs e)
{




}
protected void btnInsert_Click(object sender, EventArgs e)
{
var q3 = db.spMyTableInsert("Indra", "Jadavpur");
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
var update = db.spMyTableUpdate(2,"Indra", "Jadavpur");

}
protected void btnSelect_Click(object sender, EventArgs e)
{


var Select2 = from o in db.spMyTableSelect()
where o.MyID <= 12
select new { o.MyID, o.MyName, o.MyAddress };

grdData.DataSource = Select2;
grdData.DataBind();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
var delete = db.spMyTableDelete(3);
}
protected void btnSelectMulti_Click(object sender, EventArgs e)
{
IMultipleResults results = new MyDataContext().spMyTableSelectMulti();
List<clsMultiRecords.spMyTableSelectMultiResult> result1 = results.GetResult<clsMultiRecords.spMyTableSelectMultiResult>().ToList();

List<clsMultiRecords.spMyTableSelectMultiResult2> result2 = results.GetResult<clsMultiRecords.spMyTableSelectMultiResult2>().ToList();



grdData.DataSource = result2;
grdData.DataBind();
}
protected void btnSelectMultiWithOutputParam_Click(object sender, EventArgs e)
{
string strCustName = "";
var q2 = db.spMyTableSelectMultiWithOutputParam(1, ref strCustName);
Response.Write(strCustName.ToString());
grdData.DataSource = q2;
grdData.DataBind();
}
}

MyDataContext class
#pragma warning disable 1591
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a tool.
// Runtime Version:2.0.50727.3053
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;



[System.Data.Linq.Mapping.DatabaseAttribute(Name="Northwind")]
public partial class MyDataContext : System.Data.Linq.DataContext
{

private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();

#region Extensibility Method Definitions
partial void OnCreated();
#endregion

public MyDataContext() :
base(global::System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString, mappingSource)
{
OnCreated();
}

public MyDataContext(string connection) :
base(connection, mappingSource)
{
OnCreated();
}

public MyDataContext(System.Data.IDbConnection connection) :
base(connection, mappingSource)
{
OnCreated();
}

public MyDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}

public MyDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}

[Function(Name="dbo.spMyTableInsert")]
public int spMyTableInsert([Parameter(Name="MyName", DbType="VarChar(50)")] string myName, [Parameter(Name="MyAddress", DbType="VarChar(50)")] string myAddress)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), myName, myAddress);
return ((int)(result.ReturnValue));
}

[Function(Name="dbo.spMyTableUpdate")]
public int spMyTableUpdate([Parameter(Name="MyID", DbType="Int")] System.Nullable<int> myID, [Parameter(Name="MyName", DbType="VarChar(50)")] string myName, [Parameter(Name="MyAddress", DbType="VarChar(50)")] string myAddress)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), myID, myName, myAddress);
return ((int)(result.ReturnValue));
}

[Function(Name="dbo.spMyTableSelect")]
public ISingleResult<spMyTableSelectResult> spMyTableSelect()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<spMyTableSelectResult>)(result.ReturnValue));
}

[Function(Name="dbo.spMyTableDelete")]
public int spMyTableDelete([Parameter(Name="MyID", DbType="Int")] System.Nullable<int> myID)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), myID);
return ((int)(result.ReturnValue));
}

//[Function(Name = "dbo.spMyTableSelectMulti")]
//public ISingleResult<spMyTableSelectMultiResult> spMyTableSelectMulti()
//{
// IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
// return ((ISingleResult<spMyTableSelectMultiResult>)(result.ReturnValue));
//}
[Function(Name = "dbo.spMyTableSelectMulti"), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult)), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult2))]
public IMultipleResults spMyTableSelectMulti()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((IMultipleResults)(result.ReturnValue));
}
//[Function(Name = "dbo.spMyTableSelectMulti")]
//public IMultipleResults spMyTableSelectMulti()
//{
// IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
// return (IMultipleResults)(result.ReturnValue);
//}

[Function(Name="dbo.spMyTableSelectMultiWithOutputParam")]
public ISingleResult<spMyTableSelectMultiWithOutputParamResult> spMyTableSelectMultiWithOutputParam([Parameter(Name="MyID", DbType="Int")] System.Nullable<int> myID, [Parameter(Name="MyName", DbType="VarChar(50)")] ref string myName)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), myID, myName);
myName = ((string)(result.GetParameterValue(1)));
return ((ISingleResult<spMyTableSelectMultiWithOutputParamResult>)(result.ReturnValue));
}
}

public partial class spMyTableSelectResult
{

private int _MyID;

private string _MyName;

private string _MyAddress;

public spMyTableSelectResult()
{
}

[Column(Storage="_MyID", DbType="Int NOT NULL")]
public int MyID
{
get
{
return this._MyID;
}
set
{
if ((this._MyID != value))
{
this._MyID = value;
}
}
}

[Column(Storage="_MyName", DbType="VarChar(50)")]
public string MyName
{
get
{
return this._MyName;
}
set
{
if ((this._MyName != value))
{
this._MyName = value;
}
}
}

[Column(Storage="_MyAddress", DbType="VarChar(50)")]
public string MyAddress
{
get
{
return this._MyAddress;
}
set
{
if ((this._MyAddress != value))
{
this._MyAddress = value;
}
}
}
}

//public partial class spMyTableSelectMultiResult
//{

// private int _MyID;

// private string _MyName;

// private string _MyAddress;

// public spMyTableSelectMultiResult()
// {
// }

// [Column(Storage="_MyID", DbType="Int NOT NULL")]
// public int MyID
// {
// get
// {
// return this._MyID;
// }
// set
// {
// if ((this._MyID != value))
// {
// this._MyID = value;
// }
// }
// }

// [Column(Storage="_MyName", DbType="VarChar(50)")]
// public string MyName
// {
// get
// {
// return this._MyName;
// }
// set
// {
// if ((this._MyName != value))
// {
// this._MyName = value;
// }
// }
// }

// [Column(Storage="_MyAddress", DbType="VarChar(50)")]
// public string MyAddress
// {
// get
// {
// return this._MyAddress;
// }
// set
// {
// if ((this._MyAddress != value))
// {
// this._MyAddress = value;
// }
// }
// }
//}

public partial class spMyTableSelectMultiWithOutputParamResult
{

private int _MyID;

private string _MyName;

private string _MyAddress;

public spMyTableSelectMultiWithOutputParamResult()
{
}

[Column(Storage="_MyID", DbType="Int NOT NULL")]
public int MyID
{
get
{
return this._MyID;
}
set
{
if ((this._MyID != value))
{
this._MyID = value;
}
}
}

[Column(Storage="_MyName", DbType="VarChar(50)")]
public string MyName
{
get
{
return this._MyName;
}
set
{
if ((this._MyName != value))
{
this._MyName = value;
}
}
}

[Column(Storage="_MyAddress", DbType="VarChar(50)")]
public string MyAddress
{
get
{
return this._MyAddress;
}
set
{
if ((this._MyAddress != value))
{
this._MyAddress = value;
}
}
}
}
#pragma warning restore 1591

clsMultiRecords.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

/// <summary>
/// Summary description for clsMultiRecords
/// </summary>
public class clsMultiRecords
{
public clsMultiRecords()
{
//
// TODO: Add constructor logic here
//
}
//[Function(Name = "dbo.spMyTableSelectMulti"), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult)), ResultType(typeof(clsMultiRecords.spMyTableSelectMultiResult2))]
//public IMultipleResults spMyTableSelectMulti()
//{
// IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
// return ((IMultipleResults)(result.ReturnValue));
//}

public partial class spMyTableSelectMultiResult
{

private int _MyID;

private string _MyName;

private string _MyAddress;

public spMyTableSelectMultiResult()
{
}

[Column(Storage = "_MyID", DbType = "Int NOT NULL")]
public int MyID
{
get
{
return this._MyID;
}
set
{
if ((this._MyID != value))
{
this._MyID = value;
}
}
}

[Column(Storage = "_MyName", DbType = "VarChar(50)")]
public string MyName
{
get
{
return this._MyName;
}
set
{
if ((this._MyName != value))
{
this._MyName = value;
}
}
}

[Column(Storage = "_MyAddress", DbType = "VarChar(50)")]
public string MyAddress
{
get
{
return this._MyAddress;
}
set
{
if ((this._MyAddress != value))
{
this._MyAddress = value;
}
}
}
}
public partial class spMyTableSelectMultiResult2
{

private int _MyID;

private string _MyName;

private string _MyAddress;

public spMyTableSelectMultiResult2()
{
}

[Column(Storage = "_MyID", DbType = "Int NOT NULL")]
public int MyID
{
get
{
return this._MyID;
}
set
{
if ((this._MyID != value))
{
this._MyID = value;
}
}
}

[Column(Storage = "_MyName", DbType = "VarChar(50)")]
public string MyName
{
get
{
return this._MyName;
}
set
{
if ((this._MyName != value))
{
this._MyName = value;
}
}
}

[Column(Storage = "_MyAddress", DbType = "VarChar(50)")]
public string MyAddress
{
get
{
return this._MyAddress;
}
set
{
if ((this._MyAddress != value))
{
this._MyAddress = value;
}
}
}
}
}

Silverlight with LINQ and WCF

Microsoft Silverlight is a cross-browser, cross-platform, and cross-device plug-in for delivering the next generation of .NET based media experiences and rich interactive applications for the Web.Silverlight applications are delivered to a browser in a text-based markup language called XAML.
On the otherhand,LINQ is a programming model that introduces queries as a first-class concept into any Microsoft .NET language.
Additionaly,WCF is a technology by which pieces of software can communicate with one another.
These three technologies (Silverlight,LINQ,WCF) are the future of .NET.So you can't go further avoiding thess new technologies.I have tried to describe the interoperability between Silverlight,LINQ and WCF.
In the following example, data are coming from Sql server table with the help of LINQ,then the data are exposed by WCF for the client and atlast it cosumed by Silverlight application.

This can be achived by the following steps :

1.Create table in Sql Server database.
2.Create a silverlight application.
3.Linq To SQL to retrive the data from SQL server Database to .NET application.
4.Create WCF Service to receive the data for Silverlight application.
5.Consume data from WCF and display it in the datagrid of a silverligt application.

Create table in Sql Server database.

Step 1.Open SQL server Management Studio.
Step 2.Create a new database .

Step 3.write the database name "MyTestDb"

Step 4.create a new table "tblMyTest" as follows.

Step 5.Insert Some data in that table


Create a silverlight application

For this go to Start->Select Vs2008->Select Project In File Menu->select->SivlerLight. Then Select SilverLightAppliction in Templete


Put "SilverligtTest"

Create a Silver light Project :-


Choose "Asp.net web application project" under "new web project type"



Click OK

VS2008 create two project under solution:
SilverligtTest
SilverligtTest.Web

Linq To SQL to retrive the data from SQL server Database to .NET application.

LINQ is a very powerful addition to both VB 9 and C# 3 and is likely to be a central technique for data retrieval for Silverlight and other .NET technology going forward.

To begin right click on the SilverligtTest.Web project and choosing Add, and then choose the LinqToSql Classes template.Name it "MyDataClasses.dbml"



When the Object Relational Designer window opens, open the Server Explorer and navigate to the "MyTestDb" database and drag the tblMyTest Tables onto the MyDataClasses.dbml Designer workspace.
next click design surface of dbml (MyDataClasses.dbml) change the property of Serilaization mode from None to Unidirectional.



Create WCF Service to recevie the data for Silverlight application:

Right click on the SilverligtTest.Web project and choose Add New and from the templates choose WCF Service.Put "MyService.svc" in the name.



Open the file, IMyService.cs which contains the contract that was created by Visual Studio 2008.Write a method "GetAllEmployee"

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;


namespace SilverligtTest.Web
{
// NOTE: If you change the interface name "IMyService" here, you must also update the reference to "IMyService" in Web.config.
[ServiceContract]
public interface IMyService
{
[OperationContract]

List<tblMyTest> GetAllEmployee();

}
}


Having changed the contract in the interface, you must be sure to change the implementation in the .cs file.Open MyService.svc.cs.And implement the method GetAllEmployee
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace SilverligtTest.Web
{
// NOTE: If you change the class name "MyService" here, you must also update the reference to "MyService" in Web.config.
public class MyService : IMyService
{


#region IMyService Members

public List<tblMyTest> GetAllEmployee()
{
//First create instance of DataClasses1
MyDataClassesDataContext db = new MyDataClassesDataContext();
var emp=db.tblMyTests;
return emp.ToList();

}

#endregion
}
}

Changes are required in web.config of SilverligtTest.Web

WCF uses wsHttpBinding as its default binding, now change it basicHttpBinding.
<services>
<service behaviorConfiguration="SilverligtTest.Web.MyServiceBehavior" name="SilverligtTest.Web.MyService">
<endpoint address="" binding="basicHttpBinding" contract="SilverligtTest.Web.IMyService">
</endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
</service>
</services>

Build your SilverligtTest.Web application

Consume data from WCF and display it in the datagrid of a silverligt application.

Right click on SilverligtTest application and click on "Add Service Reference".

When the Add Service Reference,then click on Discover .The service you created will be found. Before clicking OK notice that by clicking on the Service, the operation you created is discovered.



Clicking OK button adds the service to your project. You will access the Web Service (and its method) through this reference.

Open MainPage.xaml of SilverligtTest and create a grid an button on this page.

<UserControl xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="SilverligtTest.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d" d:DesignWidth="640" d:DesignHeight="480">
<Grid x:Name="LayoutRoot">
<data:DataGrid x:Name="myDataGrid" AlternatingRowBackground="Beige" AutoGenerateColumns="True" Width="700" Height="500" Grid.Row="2" Grid.Column="1" CanUserResizeColumns="True"></data:DataGrid>
<Button x:Name="btnSubmit" Content="Submit" Height="30" Width="50" Click="btnSubmit_Click" ></Button>
</Grid>
</UserControl>

Now call the service Asynchronously.

Go to MainPage.xaml.cs and write the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

namespace SilverligtTest
{
public partial class MainPage : UserControl
{
public MainPage()
{
InitializeComponent();
}

private void btnSubmit_Click(object sender, RoutedEventArgs e)
{
ServiceReference1.MyServiceClient obj = new SilverligtTest.ServiceReference1.MyServiceClient();
obj.GetAllEmployeeCompleted +=new EventHandler(obj_GetAllEmployeeCompleted);
obj.GetAllEmployeeAsync();

}

void obj_GetAllEmployeeCompleted(object sender, SilverligtTest.ServiceReference1.GetAllEmployeeCompletedEventArgs e)
{
myDataGrid.ItemsSource = e.Result;
}
}
}

Now its time to run your application .Press F5 to run.



Clicking on the button,you can see the output as follows



Now you can realize the interoperability of Silverlight ,LINQ and WCF.

C# 3.0 Tutorial -9:Linq To Entities

Most applications are currently written on top of relational databases. At some point, these applications will have to interact with the data represented in a relational form. Database schemas are not always ideal for building applications, and the conceptual models of applications differ from the logical models of databases. The Entity Data Model (EDM) is a conceptual data model that can be used to model the data of a particular domain so that applications can interact with data as entities or objects.The following diagram shows the relationship between LINQ to Entities(L2E) and the Entity Framework, ADO.NET 2.0, and the data source.

It is basically the replacement of L2S(Linq to SQL).
This guide should be a good starting point for anyone whether or not they are familiar with L2S, and a quick 'jumping guide' to L2S developers. Here's how to get started:

Step 1.Create a Database in your SQL Server
Open the Sql Server ,Right Click On the Database and click on the new Database.

In the "New Database" modal window ,put the name "MyDb" in "Database Name" textbox



Step 2.Create Tables in your Database

Expend your "MyDb" Database ,Right Click on Tables and click on "New Table..."

Create 2 feilds :
a)CustomerID- the datatype is int and it is an identity column and make it is as Primary Key
b)CustomerName-the data type is varchar(50)

Save the table is "Cust_Master"

Create another table Cust_Dtls(Contain CustomerID(int) and CustLocation(varchar(50)) as follows


Make a foreign Key relation between two table with CustomerID


Step 3.Create a new Website in Visual Studio

Open Visual Studio 2008 IDE-Click on File-Click on New Website
In your New Web Site Dialog box select ASP.NET Web Site nad Choose "Visual C#" in your Language Dropdown.
Type "L2EDemo" as your Web Site name and click OK.

Step 4.Create App_Code Folder within your solution

Right Click on your Solution in the solution Explorer choose "App_Code" under the "Add Asp.NET Folder"

Step 5.Make your Ado.NET Entity Data Model
Right Click on your App_Code folder and click on "Add New Item..."
Choose "ADO.NET Entity Data Model" and name it (I left the default Model.edmx for the example)
Click on Add.



Click on Generate From Database



Click Next.
In the Entity Data Model Wizard Click on "New Connection"



Choose Microsoft SQL Server in your "Choose Data Source" Dialog Box



Choose The server Name and select your MyDb Database


Click OK and then Click on "Next"

Choose Cust_Dtls and Cust_Master tables


Then Click on Finish

Now your Model.edmx will be looked like as follows


Step 6.Access Your edmx

Go to your Default.aspx.cs Page
Include the namespace MyDbModel

using MyDbModel;

you will get the namespace from your ModelBrowser when the Model.edmx is opened


Step 7.Code for Save the record using L2E
protected void SaveRecord()
{
MyDbEntities db = new MyDbEntities();
Cust_Master cust = new Cust_Master();
cust.CustomerName = "Manab";
db.AddToCust_Master(cust);
db.SaveChanges();
}

Step 8.Code for modify records using L2E
MyDbEntities db = new MyDbEntities();
db.Cust_Master.First(c => c.CustomerID == 1).CustomerName = "Ranjan";
db.SaveChanges();

Step 9.Code to Display Records

MyDbEntities db = new MyDbEntities();
foreach (Cust_Master c in (from _c in db.Cust_Master select _c))
{
Response.Write("Customer ID : " + c.CustomerID);
Response.Write("<br/>");
Response.Write("Customer Name : " + c.CustomerName);
}

Step 10.Code To Delete Records
MyDbEntities db = new MyDbEntities();
db.DeleteObject(db.Cust_Master.First(c => c.CustomerID == 1));
db.SaveChanges();

Complete Code:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MyDbModel;

public partial class _Default : System.Web.UI.Page
{
MyDbEntities db;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void SaveRecord()
{
db = new MyDbEntities();
Cust_Master cust = new Cust_Master();
cust.CustomerName = "Manab";
db.AddToCust_Master(cust);
db.SaveChanges();
}
protected void ModifyRecord()
{
db = new MyDbEntities();
db.Cust_Master.First(c => c.CustomerID == 1).CustomerName = "Ranjan";
db.SaveChanges();
}
protected void showRecords()
{
db = new MyDbEntities();
foreach (Cust_Master c in (from _c in db.Cust_Master select _c))
{
Response.Write("Customer ID : " + c.CustomerID);
Response.Write("<br/>");
Response.Write("Customer Name : " + c.CustomerName);
}
}
protected void deleteRecord()
{
db = new MyDbEntities();
db.DeleteObject(db.Cust_Master.First(c => c.CustomerID == 1));
db.SaveChanges();
}
}

Now that should get you started. Like I said, if you are familiar with L2S, this transition should be no problem, and if you are new to this whole Linq arena, this should be simple to pick up. This new ADO is getting more and more impressive the more MS works on it. I can't even imagine going back to the old methods...

Related Resource:

C# 3.0 Tutorial -1:Var Keyword
C# 3.0 Tutorial -2:Extension Methods
C# 3.0 Tutorial -3:Lambda ExpressionsC# 3.0 Tutorial -4:Object Initializers
C# 3.0 Tutorial -5:Anonymous Types
C# 3.0 Tutorial -6:Type Equivalence
C# 3.0 Tutorial -7:Projections
C# 3.0 Tutorial -8:Linq

tags:Linq to Entities tutorials ,step by step Linq to Entities(L2E)

C# 3.0 Tutorial -8:Linq

LINQ

Introducing Linq
Linq is short for Language Integrated Query. If you are used to using SQL to query databases, you are going to have something of a head start with Linq, since they have many ideas in common. Before we dig into Linq itself, let's step back and look at what makes SQL different from C#.

Imagine we have a list of orders. For this example, we will imagine they are stored in memory, but they could be in a file on disk too. We want to get a list of the costs of all orders that were placed by the customer identified by the number 84. If we set about implementing this in C# before version 3 and a range of other popular languages, we would probably write something like (assuming C# syntax for familiarity):

List Found = new List();
foreach (Order o in Orders)
if (o.CustomerID == 84)
Found.Add(o.Cost);

Here we are describing how to achieve the result we want by breaking the task into a series of instructions. This approach, which is very familiar to us, is called imperative programming. It relies on us to pick a good algorithm and not make any mistakes in the implementation of it; for more complex tasks, the algorithm is more complex and our chances of implementing it correctly decrease.

If we had the orders stored in a table in a database and we used SQL to query it, we would write something like:

SELECT Cost FROM Orders WHERE CustomerID = 84

Here we have not specified an algorithm, or how to get the data. We have just declared what we want and left the computer to work out how to do it. This is known as declarative or logic programming.

Linq brings declarative programming features into imperative languages. It is not language specific, and has been implemented in the Orcas version of VB.Net amongst other languages. In this series we are focusing on C# 3.0, but the principles will carry over to other languages.

Understanding A Simple Linq Query

Let's jump straight into a code example. First, we'll create an Order class, then make a few instances of it in a List as our test data. With that done, we'll use Linq to get the costs of all orders for customer 84.

class Order
{
private int _OrderID;
private int _CustomerID;
private double _Cost;
public int OrderID
{
get { return _OrderID; }
set { _OrderID = value; }
}
public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public double Cost
{
get { return _Cost; }
set { _Cost = value; }
}
}
class Program
{
static void Main(string[] args)
{
// Set up some test orders.
var Orders = new List {
new Order {
OrderID = 1,
CustomerID = 84,
Cost = 159.12
},
new Order {
OrderID = 2,
CustomerID = 7,
Cost = 18.50
},
new Order {
OrderID = 3,
CustomerID = 84,
Cost = 2.89
}
};
// Linq query.
var Found = from o in Orders
where o.CustomerID == 84
select o.Cost;

// Display results.
foreach (var Result in Found)
Console.WriteLine("Cost: " + Result.ToString());
}
}

The output of running this program is:
Cost: 159.12
Cost: 2.89

Let's walk through the Main method. First, we use collection and object initializers to create a list of Order objects that we can run our query over. Next comes the query - the new bit. We declare the variable Found and request that its type be inferred for us by using the "var" keyword.

We then run across a new C# 3.0 keyword: "from".
from o in Orders

This is the keyword that always starts a query. You can read it a little bit like a "foreach": it takes a collection of some kind after the "in" keyword and makes what is to the left of the "in" keyword refer to a single element of the collection. Unlike "foreach", we do not have to write a type.

Following this is another new keyword: "where".
where o.CustomerID == 84

This introduces a filter, allowing us to pick only some of the objects from the Orders collection. The "from" made the identifier "o" refer to a single item from the collection, and we write the condition in terms of this. If you type this query into the IDE yourself, you will notice that it has worked out that "o" is an Order and intellisense works as expected.

The final new keyword is "select".
select o.CostThis comes at the end of the query and is a little like a "return" statement: it states what we want to appear in the collection holding the results of the query. As well as primitive types (such as int), you can instantiate any object you like here. In this case, we will end up with Found being a List, though.

You may be thinking at this point, "hey, this looks like SQL but kind of backwards and twisted about a bit". That is a pretty good summary. I suspect many who have written a lot of SQL will find the "select comes last" a little grating at first; the other important thing to remember is that all of the conditions are to be expressed in C# syntax, not SQL syntax. That means "==" for equality testing, rather than "=" in SQL. Thankfully, in most cases that mistake will lead to a compile time error anyway.

A Few More Simple Queries

We may wish our query to return not only the Cost, but also the OrderID for each result that it finds. To do this we take advantage of anonymous types.

var Found = from o in Orders
where o.CustomerID == 84
select new { OrderID = o.OrderID, Cost = o.Cost };

Here we have defined an anonymous type that holds an OrderID and a Cost. This is where we start to see the power and flexibility that they offer; without them we would need to write custom classes for every possible set of results we wanted. Remembering the projection syntax, we can shorten this to:

var Found = from o in Orders
where o.CustomerID == 84
select new { o.OrderID, o.Cost };

And obtain the same result. Note that you can perform whatever computation you wish inside the anonymous type initializer. For example, we may wish to return the Cost of the order with an additional sales tax of 10% added on to it.

var Found = from o in Orders
where o.CustomerID == 84
select new {
o.OrderID,
o.Cost,
CostWithTax = o.Cost * 1.1
};

Conditions can be more complex too, and are built up in the usual C# way, just as you would do in an "if" statement. Here we apply an extra condition that we only want to see orders valued over a hundred pounds.

var Found = from o in Orders
where o.CustomerID == 84 && o.Cost > 100
select new {
o.OrderID,
o.Cost,
CostWithTax = o.Cost * 1.1
};

Ordering :

It is possible to sort the results based upon a field or the result of a computation involving one or more fields. This is achieved by using the new "orderby" keyword.

var Found = from o in Orders
where o.CustomerID == 84
orderby o.Cost ascending
select new { o.OrderID, o.Cost };

After the "orderby" keyword, we write the expression that the objects will be sorted on. In this case, it is a single field. Notice this is different from SQL, where there are two words: "ORDER BY". I have added the keyword "ascending" at the end, though this is actually the default. The result is that we now get the orders in order of increasing cost, cheapest to most expensive. To get most expensive first, we would have used the "descending" keyword.

While I said earlier that the ordering condition is based on fields in the objects involved in the query, it actually doesn't have to be. Here's a way to get the results in a random order.

Random R = new Random();
var Found = from o in Orders
where o.CustomerID == 84
orderby R.Next()
select new { OrderID = o.OrderID, Cost = o.Cost };

Joins

So far we have just had one type of objects to run our query over. However, real life is usually more complex than this. For this example, let's introduce another class named Customer.

class Customer
{
private int _CustomerID;
private string _Name;
private string _Email;
public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public string Name
{
get { return _Name; }
set { _Name = value; }
}
public string Email
{
get { return _Email; }
set { _Email = value; }
}
}

In the Main method, we will also instantiate a handful of Customer objects and place them in a list.

var Customers = new List {
new Customer {
CustomerID = 7,
Name = "Emma",
Email = "emz0r@worreva.com"
},
new Customer {
CustomerID = 84,
Name = "Pedro",
Email = "pedro@cerveza.es"
},
new Customer {
CustomerID = 102,
Name = "Vladimir",
Email = "vladimir@pivo.ru"
}
};

We would like to produce a list featuring all orders, stating the ID and cost of the order along with the name of the customer. To do this we need to involve both the List of orders and the List of customers in our query. This is achieved using the "join" keyword. Let's replace our query and output code with the following.
// Query.
var Found = from o in Orders
join c in Customers on o.CustomerID equals c.CustomerID
select new { c.Name, o.OrderID, o.Cost };
// Display results.
foreach (var Result in Found)
Console.WriteLine(Result.Name + " spent " +
Result.Cost.ToString() + " in order " +
Result.OrderID.ToString());The output of running this program is:
Pedro spent 159.12 in order 1
Emma spent 18.5 in order 2
Pedro spent 2.89 in order 3

We use the "join" keyword to indicate that we want to refer to another collection in our query. We then once again use the "in" keyword to declare an identifier that will refer to a single item in the collection; in this case it has been named "c". Finally, we need to specify how the two collections are related. This is achieved using the "on ... equals ..." syntax, where we name a field from each of the collections. In this case, we have stated that the CustomerID of an Order maps to the CustomerID of a Customer.

When the query is evaluated, an object in the Customers collection is located to match each object in the Orders collection. Note that if there were many customers with the same ID, there may be more than one matching Customer object per Order object. In this case, we get extra results. For example, change Vladimir to also have an OrderID of 84. The output of the program would then be:

Pedro spent 159.12 in order 1
Vladimir spent 159.12 in order 1
Emma spent 18.5 in order 2
Pedro spent 2.89 in order 3
Vladimir spent 2.89 in order 3

Notice that Vladimir never featured in the results before, since he had not ordered anything.

Getting All Permutations With Multiple "from"s

It is possible to write a query that gets every combination of the objects from two collections. This is achieved by using the "from" keyword multiple times.

var Found = from o in Orders
from c in Customers
select new { c.Name, o.OrderID, o.Cost };

Earlier I suggested that you could think of "from" as being a little bit like a "foreach". You can also think of multiple uses of "from" a bit like nested "foreach" loops; we are going to get every possible combination of the objects from the two collections. Therefore, the output will be:
Emma spent 159.12 in order 1
Pedro spent 159.12 in order 1
Vladimir spent 159.12 in order 1
Emma spent 18.5 in order 2
Pedro spent 18.5 in order 2
Vladimir spent 18.5 in order 2
Emma spent 2.89 in order 3
Pedro spent 2.89 in order 3
Vladimir spent 2.89 in order 3

Which is not especially useful. You may have spotted that you could have used "where" in conjunction with the two "from"s to get the same result as the join:

var Found = from o in Orders
from c in Customers
where o.CustomerID == c.CustomerID
select new { c.Name, o.OrderID, o.Cost };

However, don't do this, since it computes all of the possible combinations before the "where" clause, which goes on to throw most of them away. This is a waste of memory and computation. A join, on the other hand, never produces them in the first place.

Grouping

Another operations that you may wish to perform is categorizing objects that have the same value in a given field. For example, we might want to categorize orders by CustomerID. The result we expect back is a list of groups, where each group has a key (in this case, the CustomerID) and a list of matching objects. Here's the code to do the query and output the results.

// Group orders by customer.
var OrdersByCustomer = from o in Orders
group o by o.CustomerID;
// Iterate over the groups.
foreach (var Cust in OrdersByCustomer)
{
// About the customer...
Console.WriteLine("Customer with ID " + Cust.Key.ToString() +
" ordered " + Cust.Count().ToString() + " items.");
// And what they ordered.
foreach (var Item in Cust)
Console.WriteLine(" ID: " + Item.OrderID.ToString() +
" Cost: " + Item.Cost.ToString());
}

The output that it produces is as follows:

Customer with ID 84 ordered 2 items.
ID: 1 Cost: 159.12
ID: 3 Cost: 2.89
Customer with ID 7 ordered 1 items.
ID: 2 Cost: 18.5

This query looks somewhat different to the others that we have seen so far in that it does not end with a "select". The first line is the same as we're used to. The second introduces the new "group" and "by" keywords. After the "by" we name the field that we are going to group the objects by. Before the "by" we put what we would like to see in the resulting per-group collections. In this case, we write "o" so as to get the entire object. If we had only been interested in the Cost field, however, we could have written:

// Group orders by customer.
var OrdersByCustomer = from o in Orders
group o.Cost by o.CustomerID;
// Iterate over the groups.
foreach (var Cust in OrdersByCustomer)
{
// About the customer...
Console.WriteLine("Customer with ID " + Cust.Key.ToString() +
" ordered " + Cust.Count().ToString() + " items.");
// And the costs of what they ordered.
foreach (var Cost in Cust)
Console.WriteLine(" Cost: " + Cost.ToString());
}Which produces the output:
Customer with ID 84 ordered 2 items.
Cost: 159.12
Cost: 2.89
Customer with ID 7 ordered 1 items.
Cost: 18.5

You are not restricted to just a single field or the object itself; you could, for example, instantiate an anonymous type there instead.

Query Continuations

At this point you might be wondering if you can follow a "group ... by ..." with a "select". The answer is yes, but not directly. Both "group ... by ..." and "select" are special in so far as they produce a result. You must terminate a Linq query with one or the other. If you try to do something like:

var CheapOrders = from o in Orders
where o.Cost < ordercounts =" from" customerid =" g.Key," totalorders =" g.Count()">

Code Snippet (in ASPX Page)

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
public partial class CSharp3_Linq : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
var Orders = new List<Order>
{
new Order{OrderID=1,CustomerID=84,Cost=159.12},
new Order{OrderID=2,CustomerID=7,Cost=18.50},
new Order{OrderID=3,CustomerID=84,Cost=2.89},
};
var Customers = new List<CustomerDescription>
{
new CustomerDescription{CustomerID=7,Name="Manab",Email="manab@wipro.com"},
new CustomerDescription{CustomerID=84,Name="Indra",Email="indra@wipro.com"},
new CustomerDescription{CustomerID=102,Name="Arun",Email="arun@wipro.com"}
};

//Simple LINQ Query-to get the costs of all orders for customer 84.
var Found1 = from o in Orders
where o.CustomerID == 84
select o.Cost;

foreach (var Result in Found1)
Response.Write("Found 1->Cost: " + Result.ToString()+"<br/>");


//query to return not only the Cost, but also the OrderID for each result that it finds
var Found2 = from o in Orders
where o.CustomerID == 84
select new { MyOrderID = o.OrderID, MyCost = o.Cost };
foreach (var Result in Found2)
Response.Write("Found 2->Cost: " + Result.MyCost.ToString() +" Order ID:" + Result.MyOrderID.ToString() + "<br/>");


//with the projection syntax, we can shorten this to:
var Found3 = from o in Orders
where o.CustomerID == 84
select new { o.OrderID, o.Cost };
foreach (var Result in Found3)
Response.Write("Found 3->Cost: " + Result.Cost.ToString() + " Order ID:" + Result.OrderID.ToString() + "<br/>");

//return the Cost of the order with an additional sales tax of 10% added on to it.
var Found4 = from o in Orders
where o.CustomerID == 84
select new
{
o.OrderID,
o.Cost,
CostWithTax = o.Cost * 1.1
};
foreach (var Result in Found4)
Response.Write("Found 4->Cost: " + Result.Cost.ToString() + " Order ID:" + Result.OrderID.ToString() +" Cost with Tax:"+ Result.CostWithTax.ToString()+ "<br/>");


//apply an extra condition that we only want to see orders valued over a hundred pounds.
var Found5 = from o in Orders
where o.CustomerID == 84 && o.Cost > 100
select new
{
o.OrderID,
o.Cost,
CostWithTax = o.Cost * 1.1
};
foreach (var Result in Found5)
Response.Write("Found 5->Cost: " + Result.Cost.ToString() + " Order ID:" + Result.OrderID.ToString() + " Cost with Tax:" + Result.CostWithTax.ToString() + "<br/>");


//Order By Clause
var Found6 = from o in Orders
where o.CustomerID == 84
orderby o.Cost ascending
select new { o.OrderID, o.Cost };
foreach (var Result in Found6)
Response.Write("Found 6->Cost: " + Result.Cost.ToString() + " Order ID:" + Result.OrderID.ToString() + "<br/>");

//get the results in a random order
Random R = new Random();
var Found7 = from o in Orders
where o.CustomerID == 84
orderby R.Next()
select new { OrderID = o.OrderID, Cost = o.Cost };
foreach (var Result in Found7)
Response.Write("Found 7->Cost: " + Result.Cost.ToString() + " Order ID:" + Result.OrderID.ToString() + "<br/>");


//Joining
var Found8 = from o in Orders
join c in Customers on o.CustomerID equals c.CustomerID
select new { c.Name, o.OrderID, o.Cost };
foreach (var Result in Found8)
Response.Write("Found 8->" +Result.Name + " spent " + Result.Cost.ToString() + " in order " +Result.OrderID.ToString()+"<br/>");

//Joining with multiple from
var Found9 = from o in Orders
from c in Customers
where o.CustomerID==c.CustomerID
select new { c.Name, o.OrderID, o.Cost };
foreach (var Result in Found9)
Response.Write("Found 9->" + Result.Name + " spent " + Result.Cost.ToString() + " in order " + Result.OrderID.ToString() + "<br/>");

//Group By
var OrdersByCustomer = from o in Orders
group o by o.CustomerID;
foreach (var Cust in OrdersByCustomer)
{
// About the customer...
Response.Write("Customer with ID " + Cust.Key.ToString() +
" ordered " + Cust.Count().ToString() + " items.<br/>");
// And what they ordered.
foreach (var Item in Cust)
Response.Write(" ID: " + Item.OrderID.ToString() +
" Cost: " + Item.Cost.ToString()+"<br/>");
}
}
}
class Order
{
private int _OrderID;
private int _CustomerID;
private double _Cost;
public int OrderID
{
get { return _OrderID; }
set { _OrderID = value; }
}
public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public double Cost
{
get { return _Cost; }
set { _Cost = value; }
}
}
class CustomerDescription
{
private int _CustomerID;
private string _Name;
private string _Email;
public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public string Name
{
get { return _Name; }
set { _Name = value; }
}
public string Email
{
get { return _Email; }
set { _Email = value; }
}
}

Output :

Found 1->Cost: 159.12
Found 1->Cost: 2.89
Found 2->Cost: 159.12 Order ID:1
Found 2->Cost: 2.89 Order ID:3
Found 3->Cost: 159.12 Order ID:1
Found 3->Cost: 2.89 Order ID:3
Found 4->Cost: 159.12 Order ID:1 Cost with Tax:175.032
Found 4->Cost: 2.89 Order ID:3 Cost with Tax:3.179
Found 5->Cost: 159.12 Order ID:1 Cost with Tax:175.032
Found 6->Cost: 2.89 Order ID:3
Found 6->Cost: 159.12 Order ID:1
Found 7->Cost: 159.12 Order ID:1
Found 7->Cost: 2.89 Order ID:3
Found 8->Indra spent 159.12 in order 1
Found 8->Manab spent 18.5 in order 2
Found 8->Indra spent 2.89 in order 3
Found 9->Indra spent 159.12 in order 1
Found 9->Manab spent 18.5 in order 2
Found 9->Indra spent 2.89 in order 3
Customer with ID 84 ordered 2 items.
ID: 1 Cost: 159.12
ID: 3 Cost: 2.89
Customer with ID 7 ordered 1 items.
ID: 2 Cost: 18.5

Under The Hood

Now we have looked at the practicalities of using Linq, I am going to spend a little time taking a look at how it works. Don't worry if you don't understand everything in this section, it's here for those who like to dig a little deeper.

Throughout the series I have talked about how all of the language features introduced in C# 3.0 somehow help to make Linq possible. While anonymous types have shown up pretty explicitly and you can see from the lack of type annotations we have been writing that there is some type inference going on, where are the extension methods and lambda expressions?

There's a principle in language design and implementation called "syntactic sugar". We use this to describe cases where certain syntax isn't directly compiled, but is first transformed into some other more primitive syntax and then passed to the compiler. This is exactly what happens with Linq: your queries are transformed into a sequence of method calls and lambda expressions.

The C# 3.0 specification goes into great detail about these transformations. In practice, you probably don't need to know about this, but let's look at one example to help us understand what is going on. Our simple query from earlier:

var Found = from o in Orders
where o.CustomerID == 84
select o.Cost;After transformation by the compiler, becomes:
var Found = Orders.Where(o => o.CustomerID == 84)
.Select(o => o.Cost);

And this is what actually gets compiled. Here the use of lambda expressions becomes clear. The lambda passed to the Where method is called on each element of Orders to determine whether it should be in the result or not. This produces another intermediate collection, which we then call the Select method on. This calls the lambda it is passed on each object and builds up a final collection of the results, which is then assigned to Found. Beautiful, huh?

Finally, a note on extension methods. Both Where and Select, along with a range of other methods, have been implemented as extension methods. The type they use for "this" is IEnumerable, meaning that any collection that implements that interface can be used with Linq. Without extension methods, it would not have been possible to achieve this level of code re-use.

DLinq and XLinq

In this article I have demonstrated Linq working with objects instantiated from classes that we implemented ourselves and stored in built-in collection classes that implement IEnumerable. However, the query syntax compiles down to calls on extension methods. This means that it is possible to write alternative implementations of Linq that follow the same syntax but perform different operations.

Two examples of this, which will ship with C# 3.0, are DLinq and XLinq. DLinq enables the same language integrated query syntax to do queries on databases by translating the Linq into SQL. XLinq enables queries on XML documents.

Conclusion

Linq brings declarative programming to the C# language and will refine and unify the way that we work with objects, databases, XML and whatever anyone else writes the appropriate extension methods for. It builds upon the language features that we have already seen in the previous parts of the series, but hiding some of them away under syntactic sugar. While the query language has a range of differences to SQL, there are enough similarities to make knowledge of SQL useful to those who know it. However, its utility is far beyond providing yet another way to work with databases.

Closing Thoughts On C# 3.0

This brings us to the end of this four part series on C# 3.0. Here is a quick recap on all that we have seen.
Type inference removes much of the tedium of writing out type annotations again and again.
Lambda expressions make higher order programming syntactically light.
Extension methods provide another path to better code re-use when correctly applied.
Object and collection initializers along with anonymous types make building up large data structures much less effort.
Linq gives us declarative programming abilities over objects, databases and XML documents.
When I saw C# 1.0 I highly doubted that C# was going to be a language I would ever be excited about. I have been pleasantly surprised, and writing this series has been a lot of fun. I hope that it has been informative and enjoyable to read, and that it will help you to make powerful use of the new language features. I greatly look forward to being able to use them in my own day-to-day development and seeing how other people use them.

Of course, knowing about something and doing it yourself are two entirely different things; if you haven't already done so, grab yourself the Visual Studio 2008 trial or the free Express Edition. Only then will you become comfortable with the new features and be able to use them effectively in your own development. Happy hacking, and have fun!

Reference:www.programmersheaven.com

tags:what is LINQ,step by step tutorials of Linq,what is new in c# 3.0/3.5,beginner tutorials on Linq