ASP.NET language integration query

Language integration queries

Most applications are data-centric, but most data warehouses are related databases. Over the years, designers and developers have designed applications based on object models.

The component that the object is responsible for connecting to access the data -- called the Data Access Layer (DAL). H ere are three things to consider:

• All the data required by an application can not be stored in one resource. This resource can be a relationship database, a business object, an XML file, or a WEB server.
• Accessing objects in memory is easier and cheaper than accessing data in databases and XML files.
• The data accessed is not used directly, but is transferred, sorted, grouped, modified, and so on.

So it's useful to have tools that can easily consolidate a wide variety of data with just a few lines of code -- data from different sources, and the ability to perform basic data operations.

Language Integration Query (LINQ) is one of these tools. L INQ is an extension set of .NET Framework 3.5 and its management language makes queries more like an object. I t defines a common syntax and program model that allows us to use a common syntax to find different types of data.

Related operations such as lookups, engineering, links, grouping, partitioning, collection operations, and so on can be used in LINQ, and the Syntax of LINQ is supported by the C# and VB compilers in .NET Framework 3.5, which allows it to be stored by configuring data without resorting to ADO.NET.

For example, querying the Constomers table in the Northwind database, using LINQ in C#, the code should look like this:

var data = from c in dataContext.Customers
where c.Country == "Spain"
select c;

Among them:

• The from keyword logically passes through each collection in turn.
• Expressions that contain the keyword where compare each object in the collection.
• The select declaration selects the objects that are compared to be added to the list and returned.
• The keyword var is used for variable declarations. B ecause the exact type of object returned is unclear, it indicates that the information needs to be dynamically speculative.

LinQ query statements can be applied to any data-supported class inherited from IEnumerable, where T can be any data type, such as List.lt; 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;
    }
}

Using this class in a web page requires simple label control to display the title of the book. Page_Load a list of books using the method and returns the title by 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 page is run, the label displays the results of the query:

LinQ expression above:

var booktitles = 
from b in books 
select b.Title;

Equivalent to the following SQL statement:

SELECT Title from Books

LinQ operator

In addition to the operators used so far, there are many other operators that execute query clauses. L et's look at some operators and clauses.

Join clause

'join clause' in SQL is used to connect two data tables and display a collection of data in columns that appear in both data tables. L INQ can also support this functionality. T o detect this, another class named Saledetails was added to the previous .cs:

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 Page_Load the join function to complete the query in two tables with the join clause:

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 appears as follows:

Where clause

The where clause allows filters to be added to the query. For example, if you want to get a book with more than 500 pages, you can change Page_Load handles in the method to look like the following:

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 returns only those columns with more than 500 pages:

Sort clauses in reverse order

These clauses allow you to sort the results of a query. T o query the price of the title, pages, and book, and sort by price, write the following code in the handle in 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 metagroups returned are:

Let clause

The let clause allows you to define a variable and assign a value to the data calculation. For example, to calculate the total sales value from the two sales values above, you need to calculate this:

TotalSale = Price of the Book * Sales

To complete this calculation, add the following snippet in Page_Load handle to the method:

The let clause allows you to define a variable and assign a value to the data calculation. For example, to calculate the total sales value from the two sales values above, you need to calculate 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 result of the query is shown in the following image: