Most applications are data-centric, however most data warehouses are relational databases. Over the years, designers and developers have designed applications based on the object model.
Objects are responsible for connecting components that access data - called the Data Access Layer (DAL). Here we need to consider three points:
All data required by an application does not need to be stored in one resource. This resource can be a relational database, business object, XML file or a WEB server.
Accessing objects in memory is simpler and cheaper than accessing data in databases and XML files.
The accessed data is not used directly, but is dumped, sorted, grouped, modified, etc.
So it would be very useful if there were tools that could easily integrate all kinds of data with just a few lines of code - tools that could integrate data from different sources and be able to perform basic data operations.
Language Integrated Query (LINQ) is one such tool. LINQ is an extension to the .NET Framework 3.5 and its management language makes queries more like objects. It defines a common syntax and program model that allows us to use an idiomatic syntax to complete the search for different types of data.
Related operations like search, project, link, group, partition, set operations, etc. can be used in LINQ, and the C# and VB compilers in .NET Framework 3.5 support LINQ syntax, which allows it to store configuration data , without resorting to ADO.NET.
For example, to query the Consumers table in the Northwind database, using LINQ in C#, the code should be as follows:
var data = from c in dataContext.Customerswhere c.Country == "Spain"select c;
in:
The from keyword logically passes through each collection in sequence.
Expressions containing the where keyword compare each object in the collection.
The select statement will select the compared object and add it to the list and return it.
Keyword var is used for variable declaration. Because the exact type of the returned object is unclear, it indicates that the information needs to be inferred dynamically.
LINQ query statements can be applied to any data-supported class that inherits from IEnumerable, where T can be any data type, such as List<Book>.
Let's look at an example to understand the concept. The following class is used in the example: Book.cs
public class Books{ public string ID {get; set;} public string Title { get; set; } public decimal Price { get; set; } public DateTime DateOfRelease { get; set; } public static List<Books> GetBooks() { List<Books> list = new List<Books>(); list.Add(new Books { ID = "001", Title = "Programming in C#", Price = 634.76m, DateOfRelease = Convert.ToDateTime("2010-02-05") }); list.Add(new Books { ID = "002", Title = "Learn Jave in 30 days", Price = 250.76m, DateOfRelease = Convert.ToDateTime ("2011-08-15") }); list.Add(new Books { ID = "003", Title = "Programming in ASP.Net 4.0", Price = 700.00m, DateOfRelease = Convert.ToDateTime("2011-02-05") }); list.Add(new Books { ID = "004", Title = "VB.Net Made Easy", Price = 500.99m, DateOfRelease = Convert.ToDateTime("2011-12-31") }); list.Add(new Books { ID = "005", Title = "Programming in C", Price = 314.76m, DateOfRelease = Convert.ToDateTime("2010- 02-05") }); list.Add(new Books { ID = "006", Title = "Programming in C++", Price = 456.76m, DateOfRelease = Convert.ToDateTime("2010-02-05") }); list.Add(new Books { ID = "007", Title = "Datebase Developement", Price = 1000.76m, DateOfRelease = Convert.ToDateTime("2010-02-05") }); return list; }}Use this class in a web page to have a simple label control to display the title of the book. The Page_Load method creates a list of books and returns the titles using a LINQ query:
public partial class simplequery : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { List<Books> books = Books.GetBooks(); var booktitles = from b in books select b.Title; foreach ( var title in booktitles) lblbooks.Text += String.Format("{0} <br />", title); }}When the web page is run, the tag displays the query results:
The LINQ expression above:
var booktitles = from b in books select b.Title;
Equivalent to the following SQL statement:
SELECT Title from Books
In addition to the operators used so far, there are many other operators to execute query clauses. Let's look at some operators and clauses.
The 'join clause' in SQL is used to join two data tables and display a collection of data in columns that appear in both data tables. LINQ can also support this functionality. To check this, add another class named Saledetails.cs to the previous project:
public class Salesdetails{ public int sales { get; set; } public int pages { get; set; } public string ID {get; set;} public static IEnumerable<Salesdetails> getsalesdetails() { Salesdetails[] sd = { new Salesdetails { ID = "001", pages=678, sales = 110000}, new Salesdetails { ID = "002", pages=789, sales = 60000}, new Salesdetails { ID = "003", pages=456, sales = 40000}, new Salesdetails { ID = "004", pages=900, sales = 80000}, new Salesdetails { ID = "005", pages=456, sales = 90000}, new Salesdetails { ID = "006", pages=870, sales = 50000}, new Salesdetails { ID = "007", pages=675, sales = 40000}, }; return sd.OfType<Salesdetails>(); }}Add code in the Page_Load function to complete the query on the two tables using join clause processing:
protected void Page_Load(object sender, EventArgs e){ IEnumerable<Books> books = Books.GetBooks(); IEnumerable<Salesdetails> sales = Salesdetails.getsalesdetails(); var booktitles = from b in books join s in sales on b.ID equals s.ID select new { Name = b.Title, Pages = s.pages }; foreach (var title in booktitles) lblbooks.Text += String.Format("{0} <br />", title);}The results page displays as follows:
The where clause allows filtering conditions to be added to the query. For example, if you want to get bibliographies with more than 500 pages, you can change the handle in the Page_Load method to look like this:
var booktitles = from b in books join s in sales on b.ID equals s.ID where s.pages > 500 select new { Name = b.Title, Pages = s.pages };The query statement only returns those columns with a page count greater than 500:
These clauses allow query results to be sorted. In order to query the title, page number and book price, and sort them by price, write the following code in the handle of the Page_Load method:
var booktitles = from b in books join s in sales on b.ID equals s.ID orderby b.Price select new { Name = b.Title, Pages = s.pages, Price = b.Price};The returned tuple is:
The let clause allows defining a variable and assigning a value calculated from the data to it. For example, to calculate the total sales value from the above two sales values, you need to calculate it like this:
TotalSale = Price of the Book * Sales
To complete this equation, add the following code snippet to the handle of the Page_Load method:
The let clause allows defining a variable and assigning a value calculated from the data to it. For example, to calculate the total sales value from the above two sales values, you need to calculate it like this:
var booktitles = from b in book join s in sales on b.ID equals s.ID let totalprofit = (b.Price * s.sales) select new { Name = b.Title, TotalSale = totalprofit};The query results are as shown below:
