Few Best Feature
Write query against in memory collection other quenchable sources using any . NET Language
Using LINQ to SQL we can directly write Db related queries in the code itself
We can write Data Access Code for XML using LINQ to XML
We can write common data access provider against different data source using LINQ to ENTITIES
We can write reach Queries against data using LINQ to DATASET
Simplicity:
simplify the data access code and maintainability of code
Query data in any .NET Programming Language:
we can write LINQ queries in the programming languages in stead of learning SQL dialect
ex – var employee = from emp in
tblemployee
where dept = 'sales'
select emp
Strongly Typed Object in our Data access code
Query against strongly typed data objects easily. Write data access code in object oriented manner as used in the rest of the application
Productivity
Increase productivity, reduce runtime errors by using strongly typed objects instead of traditional SQL queries
Optimize Development Efforts
Become more productive and optimize the overall development efforts by using the consistent query language for all aspect of the application
Reduce Bugs and Errors
Work with strongly typed CLR Objects that reduce runtime errors in the application, identify query related errors at the compile time and reduce the debugging efforts by using strongly typed objects
Be more productive with the visual studio
Flexibility
Use of consistent LINQ syntax across all data source. Use LINQ with any data source
LINQ to SQL, LINQ to DATASET, LINQ to XML and LINQ to Entities
LINQ Query
LINQ query consist of the Three part of the query operations
- Obtain the data source
- Create the query
- Execute query
// The Three Parts of a LINQ Query:
// 1. Data source.
int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };
// 2. Query creation.
// Array implicitly supports the generic IEnumerable<T> interface
// numQuery is an IEnumerable<int>
var numQuery =
from num in numbers
where (num % 2) == 0
select num;
// 3. Query execution.
foreach (int num in numQuery)
{
Console.Write("{0,1} ", num);
}
Developer who writes queries, most visible language integrated part of LINQ is the Query Expression, which is written in the declarative syntax, by using query syntax we can perform complex filtering, ordering and grouping operations on different data sources with minimum code.
Variable in query expression is strongly typed, although in many cases we don't need to provide type explicitly
LINQ queries can be written as Query Syntax and Method syntax
Most queries in the LINQ documentation are written in the query syntax however the CLR does not have any notion for query syntax itself therefore during compilation query expression are converted into something that CLR can understand These methods are called standard query operators, and they have names such as Where, Select, GroupBy, Join, Max, Average, and so on. You can call them directly by using method syntax instead of query syntax.
In general query syntax is preferred over the method syntax coz is is usually more simpler and can understand easily but in fact there is no semantic difference in between the query syntax and the method syntax there are some operation such as number of element that matched the specified condition, maximum value in the data source can be expressed only as the method syntax.
Query Expression syntax and Method syntax LINQ Query
int[] numbers = { 5, 10, 8, 3, 6, 12};
//Query syntax:
IEnumerable<int> numQuery1 =
from num in numbers
where num % 2 == 0
orderby num
select num;
//Method syntax:
IEnumerable<int> numQuery2 = numbers.Where(num => num % 2 == 0).OrderBy(n => n);
foreach (int i in numQuery1)
{
Console.Write(i + " ");
}
Console.WriteLine(System.Environment.NewLine);
foreach (int i in numQuery2)
{
Console.Write(i + " ");
}
// Keep the console open in debug mode.
Console.WriteLine(System.Environment.NewLine);
Console.WriteLine("Press any key to exit");
Console.ReadKey();
/*
Output:
6 8 10 12
6 8 10 12
*/
output from the above two example is same.
To understand the query based syntax is very easy.
Now we will poke up in the Method based syntax
on the right side of the query expression where clause is expressed as the instance method on the numbers object like numbers.Where, here we will find the the where which is not the default method found on array but infact this is the extension method The standard query operators(like Where/Select/SelectMany/Join / and Orderby) are implemented as a new kind of method called extension methods. Extensions methods "extend" an existing type; they can be called as if they were instance methods on the type.
Lambda Expressions
In method syntax conditional expression (num % 2 == 0) is passed as Where(num => num % 2 == 0) This inline expression is called as Lambda Expression
This is the conveient way of writing the code otherwise which will be written in the cumbersome manner as anonymous method, generic expression or expression tree.
The symbaol (=>) read as GOES TO
num on the left side of the operator is the input body of the lambda is just same as the query expression or it can include any other complex logic return value is just expression result.
Query Exceution
Deferred Execution
In LINQ the excutio of query is different than the query itself.we will not receive any data by just creating the query variable.
Each query is not executable until we iterate over the query variable over foreach loop
A query is executed in a foreach statement, and foreach requires IEnumerable or IEnumerable<(Of <(T>)>). Types that support IEnumerable<(Of <(T>)>) or a derived interface such as the generic IQueryable<(Of <(T>)>) are called queryable types.
Because the query variable itself never holds the query results, you can execute it as often as you like. For example, you may have a database that is being updated continually by a separate application. In your application, you could create one query that retrieves the latest data, and you could execute it repeatedly at some interval to retrieve different results every time.
Forcing Immediate Execution
Queries that perform aggregation functions over a range of source elements must first irate through the source elements Queries such as Count, Max, Average, First These execute without the explicit foreach statement bcoz the query itself must use the foreach to return the resulted single value like this
var evenNumQuery =
from num in numbers
where (num % 2) == 0
select num;
int evenNumCount = evenNumQuery.Count();
to force immediate execution of any query and cache its result we can use ToList(TSource) or ToArray(TSource) methods like below
List<int> numQuery2 =
(from num in numbers
where (num % 2) == 0
select num).ToList();
// or like this:
// numQuery3 is still an int[]
var numQuery3 =
(from num in numbers
where (num % 2) == 0
select num).ToArray();
We can also force execution by putting the foreach loop immediately after the query expression. However, by calling ToList or ToArray We can also cache all the data in a single collection object.
Some RnD with LINQ to Dataset. Following example which is the simple empty aspx page to test the normal functionality of the LINQ. This is done as a part of digging the practice carried out by me to test the various simple aspect of the LINQ coding.
Following is the code behind code of the empty aspx page which we can use to test LINQ functionality on the fly along with the few basics mentioned in the c
using System;
using System.Configuration;
using System.Data;
using System.Reflection;
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.Text;
using System.Collections.Generic;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
FewBasicsofLinq();
LessThanGreaterThanTest();
DataSet ds = CreateSampleDataset();
SimpleWhereCondition(ds);
LinqtoObjectTest(ds);
FieldTExample(ds);
DelegateLinqTest(ds);
DataRowComparerUniontest(ds);
ShapingQueries(ds);
GropuingResults(ds);
JoinExample(ds);
AsDataViewTestExample(ds);
CopyToDattableTestExample(ds);
}
protected void FewBasicsofLinq()
{
Response.Write("###################################################################################</br>");
Response.Write("1) LINQ queries target IEnumerable<T> sources. The DataSets DataTable and the DataTables DataRowCollection do not implement IEnumerable<DataRow> so these types cannot be used as sources for LINQ query expressions. To work around this issue, an extension method called <b>AsEnumerable</b> was added to the DataTable type. This extension method takes a source DataTable and wraps it in an object of the type EnumerableRowCollection which implements IEnumerable<DataRow>. This allows DataTables to be a source for LINQ query expressions.</br>");
Response.Write("2) In fact, the DataTable.AsEnumerable method turns a given DataTable into a sequence of objects where the element type is DataRow. (see linqtoobject</br>");
Response.Write("3) As a general rule, all access to DataRow column values should be done through the Field<T> method when developing LINQ to DataSet query expressions. This makes the query expression much less error prone and generally provides much cleaner code. </br>");
Response.Write("4) DataSet is an entirely in-memory cache, all LINQ queries against it are translated directly into .NET IL (intermediate language). This is different from LINQ technologies such as LINQ to SQL or LINQ to Entities which require the LINQ query to be translated to the target source's query language, and are therefore limited by what can be translated. As a result, LINQ to DataSet queries can be arbitrarily complex and can contain anything that can be expressed in the host language. </br>");
Response.Write("5) When working with LINQ to DataSet, you should also keep in mind how the LINQ set query operators (Union, Distinct, Intersection, Except) work. These operators all have two overloads, one which takes an IEqualityComparer<T> and one that does not. When calling one of the set query operators where the source(s) is IEnumerable<DataRow>, the set operator implementation will call DataRow.Equals() to compare elements when an IEqualityComparer<DataRow> comparer is not passed in. For developers familiar with the well known relational semantics of set operators, the results will be quite unexpected since the DataRow.Equals method will compare DataRow references and not the underlying values. <b>To support more traditional relational semantics LINQ to DataSet includes a type called DataRowComparer which includes a default DataRow comparer for comparing two DataRows.</b> </br>");
Response.Write("###################################################################################</br>");
}
protected void LessThanGreaterThanTest()
{
int[] no = { 10, 13, 1, 4, 12, 3, 4, 9, 8, 120, 1234 };
var nogreaterthan80 = from n in no
where n > 80
orderby n ascending
select n;
Response.Write("No Greater Than 80 : </br>");
foreach (var number in nogreaterthan80)
{
Response.Write(number + "</br>");
}
Response.Write("###################################################################################</br>");
var nolessthan120 = from n in no
where n < 120
orderby n descending
select n;
Response.Write("No Less Than 120 : </br>");
foreach (var number in nolessthan120)
{
Response.Write(number + "</br>");
}
Response.Write("###################################################################################</br>");
}
protected DataSet CreateSampleDataset()
{
Response.Write("###################################################################################</br>");
DataSet ds = new DataSet("Parks");
DataTable parksDataTable = ds.Tables.Add("National Parks");
parksDataTable.Columns.Add("ID", typeof(int));
parksDataTable.Columns.Add("Name", typeof(string));
parksDataTable.Columns.Add("YearEstablished", typeof(int));
parksDataTable.Columns.Add("Country", typeof(string));
parksDataTable.Columns.Add("Rating", typeof(int));
DataTable countriesDataTable = ds.Tables.Add("Countries");
countriesDataTable.Columns.Add("ID", typeof(int));
countriesDataTable.Columns.Add("Name", typeof(string));
countriesDataTable.Columns.Add("Population", typeof(long));
countriesDataTable.Columns.Add("ContinentID", typeof(int));
DataTable continentsDataTable = ds.Tables.Add("Continents");
continentsDataTable.Columns.Add("ID", typeof(int));
continentsDataTable.Columns.Add("Name", typeof(string));
DataRelation ContinentCountryDataRelation = ds.Relations.Add(
continentsDataTable.Columns["ID"],
countriesDataTable.Columns["ContinentID"]);
continentsDataTable.Rows.Add(
new object[] { 1, "North America" });
continentsDataTable.Rows.Add(
new object[] { 2, "South America" });
continentsDataTable.Rows.Add(
new object[] { 3, "Asia" });
countriesDataTable.Rows.Add(
new object[] { 1, "Canada", 31612000, 1 });
countriesDataTable.Rows.Add(
new object[] { 2, "USA", 302249000, 1 });
countriesDataTable.Rows.Add(
new object[] { 3, "Argentina", 39921833, 2 });
countriesDataTable.Rows.Add(
new object[] { 4, "Japan", 127433000, 3 });
countriesDataTable.Rows.Add(
new object[] { 5, "South Korea", 49024737, 3 });
parksDataTable.Rows.Add(
new object[] { 1, "Jasper", 1907, "Canada", 8 });
parksDataTable.Rows.Add(
new object[] { 2, "Yoho", 1886, "Canada", 7 });
parksDataTable.Rows.Add(
new object[] { 3, "North Cascade", 1968, "USA", 9 });
parksDataTable.Rows.Add(
new object[] { 4, "Lago Puelo", 1971, "Argentina", 8 });
parksDataTable.Rows.Add(
new object[] { 5, "Bandai-Asahi", 1950, "Japan", null });
parksDataTable.Rows.Add(
new object[] { 6, "Saikai", 1955, "Japan", 6 });
parksDataTable.Rows.Add(
new object[] { 7, "Jirisan", 1967, "South Korea", 8 });
Response.Write("<b>Dataset Created !!!!</b><br>");
Response.Write("###################################################################################</br>");
return ds;
}
protected void SimpleWhereCondition(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>Simple where condition</b><br>");
var wherecountryname = from park in ds.Tables["National Parks"].AsEnumerable()
where (string)park["Country"] == "Japan"
select park;
DumpResults(wherecountryname);
Response.Write("###################################################################################</br>");
}
protected void LinqtoObjectTest(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>Linq To Object</b><br>");
List<DataRow> list = new List<DataRow>(ds.Tables["Continents"].Rows.Cast<DataRow>());
var querylinqtoobject = from park in list
where (string)park["Name"] == "Asia"
select park;
DumpResults(querylinqtoobject);
Response.Write("###################################################################################</br>");
}
protected void FieldTExample(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>field<T> Example</b><br>");
var fldtex = from park in ds.Tables["National Parks"].AsEnumerable()
where park.Field<int?>("Rating") > 8
select park;
DumpResults(fldtex);
Response.Write("###################################################################################</br>");
}
//CALLING DELEGATE checkPopulation from linq to dataset queries
protected void DelegateLinqTest(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>Delgate Linq Example</b><br>");
Func<long, bool> checkPopulation = delegate(long i)
{return i < 100000000;};
var delgatelinqtest = from country in
ds.Tables["Countries"].AsEnumerable()
where country.Field<string>("Name").Length > 3 &&
!country.HasErrors &&
checkPopulation(
country.Field<long>("Population"))
select country;
DumpResults(delgatelinqtest);
Response.Write("###################################################################################</br>");
}
protected void DataRowComparerUniontest(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>Data Row comparer Union Test</b><br>");
var source1 = from park in ds.Tables["National Parks"].AsEnumerable()
where park.Field<int?>("Rating") < 8
select park;
var source2 = from park in ds.Tables["National Parks"].AsEnumerable()
where park.Field<int>("YearEstablished") > 1960
select park;
var unionResults = source1.Union(source2, DataRowComparer.Default);
DumpResults(unionResults);
Response.Write("###################################################################################</br>");
//use DataRowCompared for explanation view the head topic no 5)
}
protected void ShapingQueries(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>Shaping Queries</b><br>");
var shapingqueries = from park in ds.Tables["National Parks"].AsEnumerable()
orderby park.Field<int>("YearEstablished")
select new
{
ParkName = park.Field<string>("Name"),
Established =
park.Field<int>("YearEstablished")
};
DumpResults(shapingqueries);
Response.Write("###################################################################################</br>");
//use DataRowCompared for explanation view the head topic no 5)
}
protected void GropuingResults(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>Grouping Results</b><br>");
var groupingrslt = from park in ds.Tables["National Parks"].AsEnumerable()
group park by park.Field<string>("Country") into g
select new { Country = g.Key, Count = g.Count() };
DumpResults(groupingrslt);
Response.Write("###################################################################################</br>");
//use DataRowCompared for explanation view the head topic no 5)
}
protected void JoinExample(DataSet ds)
{
//Response.Write("###################################################################################</br>");
//Response.Write("<b>Join Example Results</b><br>");
//var joinexampl = from park in ds.Tables["National Parks"].AsEnumerable()
// join country in ds.Tables["Countries"].AsEnumerable()
// on park.Field<string>("Country") equals
// country.Field<string>("Name")
// select new
// {
// ParkName = park.Field<string>("Name"),
// Country = country.Field<string>("Name"),
// Continent = country.
// GetParentRow(
// ContinentCountryDataRelation)
// .Field<string>("Name")
// };
//DumpResults(joinexampl);
//Response.Write("###################################################################################</br>");
//use DataRowCompared for explanation view the head topic no 5)
}
protected void AsDataViewTestExample(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>AsDataViewTest Example Results</b><br>");
DataView dataView = (from park in
ds.Tables["National Parks"].AsEnumerable()
where park.Field<int?>("YearEstablished") < 1960
orderby park.Field<string>("Country")
select park).AsDataView();
DataRowView[] drv = dataView.FindRows("Canada");
foreach (object obj in drv.Cast<Object>())
{
Response.Write(obj);
}
Response.Write("###################################################################################</br>");
//use DataRowCompared for explanation view the head topic no 5)
}
protected void CopyToDattableTestExample(DataSet ds)
{
Response.Write("###################################################################################</br>");
Response.Write("<b>CopyToDattableTest Example Results</b><br>");
DataTable newParksTable = (from park in
ds.Tables["National Parks"].AsEnumerable()
where park.Field<int?>("YearEstablished") < 1960
orderby park.Field<int?>("YearEstablished")
select park).CopyToDataTable();
Response.Write("###################################################################################</br>");
//use DataRowCompared for explanation view the head topic no 5)
}
//Method to Display the query result
public void DumpResults<T>(IEnumerable<T> source)
{
StringBuilder sb = new StringBuilder();
if (typeof(T) == typeof(DataRow))
{
foreach (DataRow dr in source.Cast<DataRow>())
{
foreach (object o in dr.ItemArray)
{
sb.Append((o == DBNull.Value ? "DBNull" : o) + " ");
}
sb.Append("<br>");
}
}
else
{
foreach (T t in source)
{
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
object o = pi.GetValue(t, null);
sb.Append(o + " ");
}
sb.Append("<br>");
}
}
Response.Write(sb.ToString());
}
}
LAMBDA EXPRESSION
Lambda expression is the anonymous function which can contain the expression and statement can can be used to create delegate and expression tree
Lambda expression use the Lambda Operator(=>) read as GOES TO .
The left side of the lambda expression specifies the input if any and right side holds the expression or statement block. The lambda expression p => p * p read as "p goes to p time p" this expression can be assigned as delegate types as follows
delegate int del(int i);
static void Main(string[] args)
{
del myDelegate = p => p * p;
int j = myDelegate(5); //j = 25
}
Lambdas are not allowed on the left side of the is or as operator.
All restrictions that apply to anonymous methods also apply to lambda expressions.
Expression Lambadas
A lambda expression with an expression on the right hand side is called expression lambda. Expression lambda are used extensively in the construction of the expression trees
An expression lambda returns the expression result and takes the following basic form
(input parameter) => expression
parenthesis are optional if the input parameter is single otherwise it is compulsory. Two or more parameter enclosed in the parenthesis separated by comma
(x, y) => x == y
sometime it is difficult or impossible for compiler to infer the input data type, in that case we can specify the data type explicitly as follows
(int x, string s) => s.Length > x
even we can specify zero input parameter with empty parenthesis like as
() => SomeMethod()
Statement Lambda
statement lambda resembles the expression lambda except that the statement is enclosed in the parenthesis
(input parameter ) => (statement;)
body of the statement lambda can consist of any number of lines but practically they are not more than two three lines
delegate void TestDelegate(string s);
…
TestDelegate myDel = n => { string s = n + " " + "World"; Console.WriteLine(s); };
myDel("Hello");
Statement lambdas, like anonymous methods, cannot be used to create expression trees.
