What is LINQ?
LINQ is a Microsoft programming model and methodology that essentially adds formal query capabilities into Microsoft .Net –based programming languages.
It is a component released within the .Net 3.5 framework which is the most powerful feature of .Net.
Why LINQ?
The popular answer is that LINQ is Integrated with C# (or VB), thereby eliminating the impedance mismatch between programming languages and databases, as well as providing a single querying interface for a multitude of data sources like databases and XML.
Before LINQ we used various languages and technologies to access different data sources. Now with LINQ we can use the same syntax to access the different data sources from within the language itself.
Without LINQ, ADO.NET is the best choice to manipulate the data of a database. But ADO.NET uses tables, relations and other relational database constructs which needs to be mapped to the application objects. This is more error prone.
Advantages of Using LINQ
- “Familiar syntax for writing queries.
- Compile-time checking for syntax errors and type safety.
- Improved debugger support.
- IntelliSense support.
- Ability to work directly with XML elements instead of creating a container XML document, as required with W3C DOM.
- In-memory XML document modification that is powerful, yet simpler to use than XPath or XQuery.
- Powerful filtering, ordering, and grouping capabilities.
- Consistent model for working with data across various kinds of data sources and formats.
Disadvantages of LINQ
- LINQ is not good to write complex queries like SQL.
- LINQ doesn’t take the full advantage of SQL features like cached execution plan for stored procedure.
- Performance is degraded if you don’t write the LINQ query correctly.
- If you have done some changes in your query, you have to recompile it and redeploy its dll to the server.
LINQ Architecture
LINQ makes the concept of querying a first-class programming concept in .NET. The data to be queried can take the form of XML (LINQ to XML), databases (LINQ-enabled ADO.NET: LINQ to SQL, LINQ to Dataset and LINQ to Entities) and objects (LINQ to Objects). LINQ is also highly extensible and allows you to build custom LINQ enabled data providers (e.g.: LINQ to Amazon, LINQ to NHibernate, and LINQ to LDAP).
In the above diagram LINQ Enabled Data Sources(LINQ provider) acts as a bridge between LINQ and the data sources like SQL and XML.
In a LINQ query, you are always working with objects. You use the same basic coding patterns to query and transform data in XML documents, SQL databases, ADO.NET Datasets, .NET collections, and any other format for which a LINQ provider is available.
Types of LINQ:
LINQ Comes with 3 Basic Types type of objects :
1. LINQ to Object
2. LINQ to Database(I will explain on Linq to Entities framework)
3. LINQ to XML
LINQ to Object
The term “LINQ to Objects” refers to the use of LINQ queries with any IEnumerable or IEnumerable collection directly, without the use of an intermediate LINQ provider or API such as LINQ to SQL or LINQ to XML. You can use LINQ to query any enumerable collections such as List, Array, or Dictionary. The collection may be user-defined or may be returned by a .NET Framework API.
Without LINQ, we would have to go through the values one-by-one and then find the required details. However, using LINQ we can directly query collections and filter the required values without using any looping.\
What is a Query in LINQ?
A query is an expression that retrieves data from a data source. Queries are usually expressed in a specialized query language.
Three Parts of Query Operation
All LINQ query operations consist of three distinct actions:
1. Obtain the data source.
2. Create the query.
3. Execute the query.
The following LINQ to Object example shows how the three parts of a query operation are expressed in source code. The example uses an integer array as a data source for convenience; however, the same concepts apply to other data sources also.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LinqExample
{
class TestProgram
{
static void Main(string[] args)
{
//The three parts of Linq Query are:
//1.Data source
int[] numbers = new int[7] { 1, 2, 3, 4, 5,6,12 };
//2.Query Creation
var numberQuery = from number in numbers
where (number % 2 == 0)
select number;
//3.Query Execution
Console.WriteLine("Even Numbers are: ");
foreach (int num in numberQuery)
{
Console.WriteLine(num);
}
Console.ReadLine();
}
}
}
Here Output look like:
The following illustration shows the complete query operation. In LINQ the execution of the query is distinct from the query itself; in other words you have not retrieved any data just by creating a query variable.
The Data Source
In the previous example, because the data source is an array, it implicitly supports the generic IEnumerable interface. This fact means it can be queried with LINQ. A query is executed in a foreach statement, and foreach requires IEnumerable or IEnumerable. Types that support IEnumerable or a derived interface such as the generic IQueryable are called queryable types.
A queryable type requires no modification or special treatment to serve as a LINQ data source. If the source data is not already in memory as a queryable type, the LINQ provider must represent it as such. For example, LINQ to XML loads an XML document into a queryable XElement type:
XElement contacts = XElement.Load(@”c:\myContactList.xml”);
The Query
The query specifies what information to retrieve from the data source or sources. Optionally, a query also specifies how that information should be sorted, grouped, and shaped before it is returned. A query is stored in a query variable and initialized with a query expression. To make it easier to write queries, C# has introduced new query syntax.
The query in the previous example returns all the even numbers from the integer array. The query expression contains three clauses: from, where and select. The from clause specifies the data source, the where clause applies the filter, and the select clause specifies the type of the returned elements.
Query Execution
As stated previously, the query variable itself only stores the query commands. The actual execution of the query is deferred until you iterate over the query variable in a foreach statement.
LINQ TO Entities
The Entity Framework is an Object Relational Mapping (O/RM) tool that enables you to generate a data access layer from a database automatically. The Entity Framework enables you to avoid the tedious work of building your data access classes by hand. Here the LINQ to Entities example shows how to LINQ connect with database.
In order to use the Entity Framework, you need to create an Entity Data Model. You can take advantage of the Visual Studio Entity Data Model Wizard to generate an Entity Data Model from a database automatically.
This is the SQL table Where I have inserted these values.
Here I am using Entity model for connection between Linq and Entities.
Now I will explain the steps to configure and add Linq to Entity Data Model framework and also how to connect it with the database:
You will need to add LINQ to Entities classes to your project using Add New Item Dialog as shown below.
Now we need to connect to the database.
In the next dialog you will need to provide the details of the SQL Server i.e. Server Name and authentication details and then select the database i.e. Northwind.
Once above is done you can Test Connection and if it works then click OK.
Now you’ll be able to see the LINQ to Entities (edmx) class created in the App_Code folder of Solution explorer.
Now from here you will need to Drag and Drop the EmpDetails Table (we created earlier) to the LINQ to Entities Framework (edmx) class and the save it.
This is the UI of my program where data will show up.
Binding the GridView using LINQ to Entities Framework.
This is fairly simple, you will need to create an object of the Data Entity Model of the EDMX .
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace Linq.EmployeeDetails
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnShow_Click(object sender, EventArgs e)
{
Showdetails();
}
private void Showdetails()
{
LinqtoSqlEntities ent = new LinqtoSqlEntities();
var employee = from u in ent.EmpDetails
select u;
dataGridView1.DataSource = employee;
}
private void btnupdate_Click(object sender, EventArgs e)
{
LinqtoSqlEntities ent = new LinqtoSqlEntities();
EmpDetail emp = ent.EmpDetails.Where(i => i.Id == 1).First();
emp.City = "Noida";
ent.SaveChanges();
Showdetails();
}
private void butExit_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}
Now Output look like as when we will click on Show button.
And When we will click on Update button data will be updated in the database also.
LINQ TO XML
Similar to DLINQ, XLINQ means using LINQ to XML documents.
LINQ to XML is a LINQ-enabled, in-memory XML programming interface that enables you to work with XML from within the .NET Framework programming languages.
LINQ to XML is like the Document Object Model (DOM) in that it brings the XML document into memory. You can query and modify the document, and after you modify it you can save it to a file or serialize it and send it over the Internet. However, LINQ to XML differs from DOM: It provides a new object model that is lighter weight and easier to work with, and that takes advantage of language improvements in Visual C# 2008.
The most important advantage of LINQ to XML is its integration with Language-Integrated Query (LINQ). This integration enables you to write queries on the in-memory XML document to retrieve collections of elements and attributes. The query capability of LINQ to XML is comparable in functionality (although not in syntax) to XPath and XQuery. The integration of LINQ in Visual C# 2008 provides stronger typing, compile-time checking, and improved debugger support.
Another advantage of LINQ to XML is the ability to use query results as parameters to XElement and XAttribute object constructors enables a powerful approach to creating XML trees. This approach, called functional construction, enables developers to easily transform XML trees from one shape to another.
Now i will explain the steps to configure and add Linq to XML framework and also how to connect it with the database and XML file.
You will need to add LINQ to SQL classes connection for database to your project using Add New Item Dialog as shown below.
After making connection we need to Drag and Drop the Notes Table (we created earlier) to the LINQ to SQL Framework (dbml) class and the save it.
And here UI of my programming looks like this:
Binding the GridView using LINQ to XML Framework.
This is fairly simple, you will need to create an object of the LINQ to SQL of the DBML .
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Linq;
namespace LinqtoXmlSample
{
public partial class Form1 : Form
{
private string fileName;
public Form1()
{
InitializeComponent();
}
private void btnbrowse_Click(object sender, EventArgs e)
{
DialogResult dr = openFileDialog1.ShowDialog();
if (dr == DialogResult.OK)
{
fileName = openFileDialog1.FileName;
}
txtPath.Text = fileName;
}
private void btnLoadxml_Click(object sender, EventArgs e)
{
DataSet dataset = new DataSet();
dataset.ReadXml(fileName);
dataGridView1.DataSource = dataset;
dataGridView1.DataMember = "node";
}
private void btnAdd_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable("whereClause");
ds.Tables.Add(dt);
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
DataColumn column = new DataColumn(dataGridView1.Columns[i - 1].Name);
dt.Columns.Add(column);
}
int ColumnCount = dataGridView1.Columns.Count;
foreach (DataGridViewRow v in dataGridView1.Rows)
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < ColumnCount; i++)
{
dataRow[i] = v.Cells[i].Value;
}
dt.Rows.Add(dataRow);
}
ds.WriteXml("D:\\test.xml");
MessageBox.Show("Added successfully..");
}
private void btnEdit_Click(object sender, EventArgs e)
{
DataTable dataSet = (DataTable)dataGridView1.DataSource;
dataSet.WriteXml("D:\\test.xml");
MessageBox.Show("Data Updated Successfully");
}
private void btnDelete_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow item in dataGridView1.SelectedRows)
{
dataGridView1.Rows.RemoveAt(item.Index);
}
DataSet dataSet = (DataSet)dataGridView1.DataSource;
dataSet.WriteXml("D:\\test1.xml");
MessageBox.Show("Deleted sucessfully..");
}
private void btnExportdb_Click(object sender, EventArgs e)
{
int rowindex = dataGridView1.CurrentRow.Index;
DataclassXmlDataContext dcxml = new DataclassXmlDataContext();
List noteList = new List();
Note1 note;
for (rowindex = 0; rowindex new XElement("Messages",
new XElement("node", new XAttribute("id", x.NoteId),
new XElement("to", x.NoteTo),
new XElement("from", x.NoteFrom),
new XElement("heading", x.NoteHeading),
new XElement("body", x.NoteBody))
))));
xDoc.Save("D:\\Message.xml");
MessageBox.Show("Successfully Imported");
}
}
}
Now When we click on Load XML button the output looks like this:
And when we click on Add,Edit and Delete button data will be added,deleted on the XML file also.
When we click on Export to DB button data will be inserted on the table also and output look like as shown below:
When we click on Export to XML button data will show on the XML file.
And Output look like this:
References: