LINQ in .NET


 101 Samples of LINQ:
http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

LINQ TO SQL:

With .NET Framework 3.5 Microsoft released Language Integrated Query aka LINQ. LINQ enables developers to query data sources using a query like syntax with both C# and VB.NET. These data sources can be collections, SQL Server databases, XML, DataSets etc. Other than what is supplied by Microsoft, LINQ is also extensible. This means that you can query data sources beyond what Microsoft ships. Examples of such implementations are LINQ To Flickr, LINQ To Amazon, LINQ to Google etc. In this article I will show you how you can use LINQ To SQL to perform CRUD operations on a SQL Server database. I will use Northwind database and build an ASP.NET application to demonstrated the capabilities of LINQ To SQL. You can download Northwind database here.

Toolset for this article

  1. Visual Studio 2008
  2. .NET Framework 3.5 (This is already installed if you have Visual Studio 2008)
  3. SQL Server 2005 (You can also work with SQL Server Express)

Solution Structure

For this article we will need two projects. One is a data layer (created as a Class Library)which we will generate and the other is an ASP.NET Web Application. The solutions structure looks like this in Solution Explorer.

LINQ

<img style=”border-right-width: 0px; margin: 0px 10px 10px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px” border=”0″ alt=”LINQ” src=”http://www.deepakkapoor.net/wp-content/uploads/2008/07/image-thumb3.png&#8221; width=”297″ height=”280″ />

Creating Data Layer

Before we generate our data layer we must create a new connection in Server Explorer which points to Northwind database.

LINQ

<img style=”border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px” border=”0″ alt=”LINQ” src=”http://www.deepakkapoor.net/wp-content/uploads/2008/07/image-thumb8.png&#8221; width=”251″ height=”311″ />

We will now generate our data layer using LINQ To SQL. To do this you need to add a new item to the data layer project of type LINQ to SQL Classes. We will name it Northwind as shown below.

LINQ

<img style=”border-right-width: 0px; margin: 0px 10px 10px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px” border=”0″ alt=”LINQ” src=”http://www.deepakkapoor.net/wp-content/uploads/2008/07/image41.png&#8221; width=”540″ height=”381″ />

After adding a LINQ to SQL Class we are presented with a designer surface. Here we can simply drag the tables which will become part of our data layer. For this article we will drag all tables on the designer by selecting them all in one go. Our designer should look like this after dragging all tables on it.

LINQ

<img style=”border-right-width: 0px; margin: 0px 10px 10px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px” border=”0″ alt=”LINQ” src=”http://www.deepakkapoor.net/wp-content/uploads/2008/07/image30-thumb.png&#8221; width=”451″ height=”638″ />

We should now build our solution to make sure everything is okay. And that’s it. We have successfully generated our data layer. In Solution Explorer we can see that we have two new files namely Northwind.dbml.layout and Northwind.designer.cs. We can also see that references required to compile and run our code have been added by Visual Studio.

LINQ

<img style=”border-right-width: 0px; margin: 0px 10px 10px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px” border=”0″ alt=”LINQ” src=”http://www.deepakkapoor.net/wp-content/uploads/2008/07/image36.png&#8221; width=”282″ height=”509″ />

The .cs file contains the code for our data layer. Let’s examine the code that has been generated for us. We will look at the Region class.

[Table(Name=”dbo.Region”)] public partial class Region : INotifyPropertyChanging, INotifyPropertyChanged

The class itself is decorated with Table attribute and the Name property has been assigned the actual table name we have in our database. Region class also implements INotifyPropertyChanging and INotifyPropertyChanged interfaces. These interfaces are used for databinding. Region class also contains one property per column. Let’s look at the RegionDescription property.

[Column(Storage=”_RegionDescription”, DbType=”NChar(50) NOT NULL”, CanBeNull=false)] public string RegionDescription {   get   {     return this._RegionDescription;   }   set   {     if ((this._RegionDescription != value))     {       this.OnRegionDescriptionChanging(value);       this.SendPropertyChanging();       this._RegionDescription = value;       this.SendPropertyChanged(“RegionDescription”);       this.OnRegionDescriptionChanged();     }   } }

Columns are decorated with Column attribute and values are passed in for Storage, DbType and CanBeNull which indicates if the column can be null or not.

Using Data Layer

Now that we have generated our data layer. We will work on ASP.NET web application where we will use our data layer. To keep things simple we will create a web forms to search for customers and display search results. We will also create a web form to insert new customers. Let’s start by creating our web form for customer search. For this we will use the Default.aspx page. We will place few controls on the web form. These controls will give us search parameters and a button which will do the search and display results when clicked. This is what the form will look like after placing our controls.

LINQ

<img style=”border-right-width: 0px; margin: 0px 10px 10px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px” border=”0″ alt=”LINQ” src=”http://www.deepakkapoor.net/wp-content/uploads/2008/07/image-thumb13-thumb.png&#8221; width=”396″ height=”160″ />

We will also place a GridView control on our form to display search results. We will now put in some code in our button’s click event handler to do the search and display results in GridView. Make sure that we have a reference to Data Layer project, System.Data.Linq and appropriate using statement. Here is what our button click event handler will contain.

protected void buttonSearch_Click(object sender, EventArgs e) {   using (NorthwindDataContext context = new NorthwindDataContext())   {     var customers =       from c in context.Customers       select c;
gridViewCustomers.DataSource = customers;     gridViewCustomers.DataBind();   } }

This code will query the customers table in northwind database and will return all customers. We will now modify it slightly to accept customer name and company name as parameters for our query. After modification our event handler looks like this.

protected void buttonSearch_Click(object sender, EventArgs e) {   using (NorthwindDataContext context = new NorthwindDataContext())   {     var customers =       from c in context.Customers       where (c.ContactName.Contains(textBoxCustomerName.Text.Trim())       &amp;&amp;       c.CompanyName.Contains(textBoxCompanyName.Text.Trim()))       select c;
gridViewCustomers.DataSource = customers;     gridViewCustomers.DataBind();   } }

Our search results will now be filtered.

Let us now created a data entry form for customers.  We will insert a new web form in our ASP.NET project and call it CustomerEntry. To start with we will make sure that our form contains fields required to insert a customer. Our form after completion will look like this.

LINQ

<img style=”border-right-width: 0px; margin: 0px 10px 10px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px” border=”0″ alt=”LINQ” src=”http://www.deepakkapoor.net/wp-content/uploads/2008/07/image.png&#8221; width=”357″ height=”549″ />

We expect a new row to be inserted into customers table when Save Customer button is clicked. This code achieves data insertion into customers table for us.

protected void buttonSave_Click(object sender, EventArgs e) {   using (NorthwindDataContext context = new NorthwindDataContext())   {     Customer customer = new Customer     {       CustomerID = textBoxCustomerID.Text,       CompanyName = textBoxCompanyName.Text,       ContactName = textBoxCustomerName.Text,       ContactTitle = textBoxTitle.Text,       Address = textBoxAddress.Text,       City = textBoxCity.Text,       Region = textBoxRegion.Text,       PostalCode = textBoxPostalCode.Text,       Country = textBoxCountry.Text,       Phone = textBoxPhone.Text,       Fax = textBoxFax.Text     };
context.Customers.InsertOnSubmit(customer);     context.SubmitChanges();   } }

Similarly an existing row in database can be updated by first retrieving the data and then submitting it via DataContext.

Conclusion

In this tutorial we have not written a single SQL statement to retrieve or insert data into a database. This is the beauty of LINQ To SQL. Further our retrieval code while in C# looks a lot like a query. We can already appreciate the benefits of such a streamlined and unified approach in dealing with data.

LINQ or Language-Integrated Query is such a tool. LINQ is set of extensions to the .Net Framework 3.5 and its managed languages that sets the query as an object. It defines a common syntax and a programming model to query different types of data using a common language.

The relational operators like Select, Project, Join, Group, Partition, Set operations etc., are implemented in LINQ and the C# and VB compilers in the .Net framework 3.5, which support the LINQ syntax makes it possible to work with a configured data store without resorting to ADO.Net.

For example querying the Customers table in the Northwind database, using LINQ query in C#, the code would be:

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

Where:

  • The ‘from’ keyword logically loops through the contents of the collection.
  • The expression with the ‘where’ keyword is evaluated for each object in the collection.
  • The ‘select’ statement selects the evaluated object to add to the list being returned.
  • The ‘var’ keyword is for variable declaration. Since the exact type of the returned object is not known, it indicates that the information will be inferred dynamically.

LINQ query can be applied to any data-bearing class that inherits from IEnumerable<T>, here T is any data type, for example List<Book>.

Let us look at an example to understand the concept. The example uses the following class: Books.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;
  }

}

The web page using this class has a simple label control, which will display the titles of the books. The Page_Load event creates a list of books and returns the titles by using 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 will display the results of the query:

LINQ ResultThe above LINQ expression:

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

Is equivalent to the following SQL query:

SELECT Title from Books

LINQ Operators:

Apart from the operators used so far, there are several other operators, which implement all query clauses. Let us look at some of the operators and clauses.

The Join clause:

The ‘join clause’ in SQL is used for joining two data tables and displays a data set containing columns from both the tables. LINQ is also capable of that. To check this, add another class named Saledetails.cs in 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 the codes in the Page_Load event handler to query on both the tables using 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 resulted Page:

LINQ Result2

The Where clause:

The ‘where clause’ allows adding some conditional filters to the query. For example, if you want to see the books, where the number of pages are more than 500, change the Page_Load event handler to:

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 returns only those rows, where the number of pages is more than 500:

LINQ Result3

The Orderby and Orderbydescending clauses:

These clauses allow sorting the query results. To query the titles, number of pages and price of the book, sorted by the price, write the following code in the Page_Load event handler:

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 tuples are:

LINQ Result4

The Let clause:

The let clause allows defining a variable and assigning it a value calculated from the data values. For example, to calculate the total sale from the above two sales, you need to calculate:

TotalSale = Price of the Book * Sales

To achieve this, add the following code snippets in the Page_Load event handler:

The let clause allows defining a variable and assigning it a value calculated from the data values. For example, to calculate the total sale from the above two sales, you need to calculate:

var booktitles = from b in books
     join s in sales
     on b.ID equals s.ID
     let totalprofit = (b.Price * s.sales)
     select new { Name = b.Title, TotalSale = totalprofit};

The resultant query page looks like:

LINQ Result5

LINQ to Object:

string[] names = { "Sara", "Bill", "Alex", "Don", "Tom", "David", "Dana" };
// Find names starts with with character 'D' 
int namesStartsWithD = names.Count( name => name.StartsWith("d",StringComparison.CurrentCultureIgnoreCase ) ); 
Console.WriteLine("There are {0} names that start with character 'D' in the name list.",namesStartsWithD);
// Find the longest name
 int longestName = names.Max( name=>name.Length ); 
Console.WriteLine("The longest name is {0} characters long.", longestName);
 // Find the first name that starts with  character 'D' 
string firstStartsWithD = names.First(name => name.StartsWith("d",StringComparison.CurrentCultureIgnoreCase)  ); 
Console.WriteLine("The first name that start with 'D' - {0}.", firstStartsWithD);

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s