Introduction
One of the reason of LINQ (Language Integrated Query) being so popular is that it offers so much flexibility to query through the in-memory collection. In this article, I shall explain frequenlty used extension methods of LINQ from System.Linq namespace.Prerequisite
In order to understand this article, I assume that reader has basic knowledge of generic list collections and lamda expressions.Lets get the datasource first
In order to demonstrate the use of extension methods, I have parepared a sample database table called "SampleTutorials" that has AutoId, Name, Address, City and Phone columns and then I have created a Sample class.I have following method that retrieves the data from database and add into the generic list collection and returns that collection.
/// <summary>Above method is called from Page_Load method like this
/// Get list of sample records
/// </summary>
/// <returns></returns>
private IList<Sample> GetSampleList()
{
IList<Sample> list = new List<Sample>();}
string sql = "SELECT * FROM SampleForTutorials ORDER BY AutoId DESC";
using (DataTable table = new DataTable())
{
// get the data now}
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))}
{
cmd.CommandType = CommandType.Text;}
conn.Open();
// get the data first
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(table);}
conn.Close();
foreach (DataRow row in table.Rows)
{
list.Add(new Sample() { Address = row["Address"].ToString(), AutoId = int.Parse(row["AutoId"].ToString()), City = row["City"].ToString(), Name = row["Name"].ToString(), Phone = row["Phone"].ToString() });}
return list;
IList<Sample> list = GetSampleList();I have used above list collection variable to show how the LINQ extension method works.
Get solutions of your .NET problems with video explanations, .pdf and source code in .NET How to's.
Frequently used LINQ Extension methods
SartsWith
StartsWith extension method is used to retrive all records from the collection whose a particular field starts with certain characters or string. In my case, I want to retrieve all records whose Phone field starts with "Phone" and I will get all 5 records as this condition is satisfied by all 5 records in my database table.// select all records whose Phone starts with "Phone"Output:
var query1 = from sample in list
where sample.Phone.StartsWith("Phone")
select sample;
Average
Average extension method is used to get the average of a particular column from the collection objets. In my case I want to get the average of AutoId, so I have specified it using lamda expressions (=>).// gets the average of the specified column
var average = query1.Average(s => s.AutoId);
Contains
Contains extension method is used to retrieve all objects from the collection whose field contains specified string. In my case I want to get all records whose Address contains "Nagar"var query2 = from smp in query1Output:
where smp.Address.Contains("Nagar")
select smp;
Concat
Concat is used to concatenate two collections. In my case I am concatenating query2 into query1 and storing into queryConcat variable.// Concates two collections of same type into onevar queryConcat = query1.Concat(query2);Output:
Distinct
Distinct extension method is used to get the distinct records from the collection (similar to sql server distinct keyword). In my case I will get all 5 records as all 5 are distinct as displayed in the first picture above.// Distinct - gives distinct record
var distinctRecords = queryConcat.Distinct();
ElementAt
ElementAt extension method is used to get the element (object) from the specified position in the collection. In my case I have specified 3 so this will give me the element at the 4th position (as it uses 0 based index), refer the 1st picture at the top and it will give me the record with AutoID = 7.In case the position you have specified doesn't exsits in the collection, it throws error. To avoid error use next method.
// ElementAt - gives the record that is at nth position in the collection, if nth position doesn't exsits then throw error
var samp = query1.ElementAt(3);
ElementAtOrDefault
ElementAtOrDefault extension method is used to retrieve the specified position element from the collection and if specified position doesn't exists then it returns null.// ElementAtOrDefault - gives the record that is at nth position in the collection if not found then returns null
var samp1 = query1.ElementAtOrDefault(4);
Except
Except extension method is used to retrieve all records from the collection that doesn't have in the second collection.// Except - Get the list of collection that doesn't contain records from specified collectionOutput:
var exceptColl = query1.Except(query2);
First
Gives first record from the collection, if collection doesn't have any object then throws error. To avoid error, use next method.// First - gives first record from the collection, if doesn't exsits then throws error
var first = query1.First();
FirstOrDefault
FirstOrDefault extension method is used to retrieve the first element from the collection, if not then gives null.// FirstOrDefault - if record exsits then gives first record from the collection else returns nullvar firstDefault = query1.FirstOrDefault();
GroupBy
GroupBy extension method is used to retrieve the records grouped by specified field. In this case, it will return records grouped by City field.// GroupBy - filters records grouping by a column valuevar groupBy = query1.GroupBy(s => s.City);
Intersect
Intersect extension method is used to retrieve the common records from two collections. In my case I have retrieved all records that is common in query1 and query2.// Intersect - gives common records from two same types of collectionsvar intersect = query1.Intersect(query2);
Join
Join extension method is used to join two collections, I found this post very useful in understanding the Join method of LINQ http://byatool.com/c/linq-join-method-and-how-to-use-it/Last
Last extension method gives last element from the collection. If there is no element in the collection, it throws error.// Last - gives last record from the collection, if not throws errorvar last = query1.Last();
LastOrDefault
LastOrDefault extension method gives the last element form the collection, if not gives null.// LastOrDefault - gives last or default record from collection, if no records then gives nullvar lastOrDefault = query1.LastOrDefault();
Count and LongCount
Count and LongCount gives the count of elements from the collection, if no parameter passed then gives total number of elements from the collection else givers count of elements that meet the filter criteria. In this case I am trying to count the elements whose City is "Kolkata".LongCount does the same work as Count, however gives System.Int64 value.
// Count - returns the total number of records in the collection, if passed the parameter then returns the count that satisfies the conditionvar kolCount = query1.Count(s => s.City == "Kolkata");
// LongCount does the same function that Count does however it is used to count large number of records
var kolLongCount = query1.LongCount(s => s.City == "Kolkata");
Max
Max gives the maximum value of the specified field from the collection. In this case I am trying to retrieve the maximum value of the AutoId column so I will get 16.// Max - returns the record that has maximum value in the specified column
var max = query1.Max(s => s.AutoId);
Min
Min gives the minimum value of the specified field from the collection. In this case I am trying to retrieve the minimum value of the AutoId so I will get 4.// Min - returns the record that has minimum value in the specified columnvar min = query1.Min(s => s.AutoId);
OrderBy
OrderBy extension method is used to sort the collection on specified field. In this case I am sorting the collection on Name. It sorts the collection in ascending order.// OrderBy - sort the collection in ascending order
var query1 = query1.OrderBy(sample => sample.Name);
OrderByDescending
OrderByDescending extension method is used to sort the collection in descending order on specified field. In this case I am sorting the collection in descending order on Name.// OrderByDescending - sort the collection in ascending ordervar query1 = query1.OrderByDescending(sample => sample.Name);
ThenBy
ThenBy is used with OrderBy or OrderByDescending. It is used to apply sorting on already sorted collection using OrderBy or OrderByDescending. For example, in my case I am first sorting the collection by Name and then by City.// OrderBy.ThenBy - sort the collection in ascending ordervar query1 = query1.OrderBy(sample => sample.Name).ThenBy(s => s.City);
Reverse
Reverse is used to reverse the order the objects in collection.// Reverse - Reverse the order of the elements in the collectionOutput:
var reverseList = query1.Reverse();
SequenceEqual
SequenceEqual extension method is used to determine if both collections sequence are equal. In this case the order of the elements will be different in query1 and reverseList so I will get isEqual = false.// SequenceEqual - determines if both the collections sequences are same
var isEqual = query1.SequenceEqual(reverseList);
Skip
Skip extension method is used to skip first nth elements from the collection. In this case, I am skipping first 2 elements from the collection and will get rest of them.// Skip - skips first nth records var skip = query1.Skip(2);Output:
SkipWhile
SkipWhile extension method is used to skip the elements that satisfies the given filter criteria till it doens't get any element that doesn't satisfy. For example, if you have 5 records where 1st and 2nd records City is "Hyd", 3rd record City is "New York" and remaining two records City is again "Hyd" again. It will give you last 3 records.// SkipWhile - skips records while a certain condition is met and returns remaining records// this also returns record that has met the condition but exists in the collection after the position the loop has brokenOutput:
var skipWhile = query1.SkipWhile(s => s.City.Contains("City"));
In this case, first two records had City contains "City" word so it skips first two records but kept the 4 record because 3rd record doesn't have "City" in the City field.
Sum
Sum extension method gives the sum of specified field. In my case I am getting sum of AutoId that will be 51.// Sum - sums up the specified column
var sum = query1.Sum(s => s.AutoId);
Take
Take extension method is used to take specified number of elements from the collection. In my case, I am getting top 3 elements from the collection.// Take - takes n number of recordsvar take = query1.Take(3);
Custom paging using Linq
You can use Skip and Take extension methods to do custom paging using LINQ that has been explained in this post http://www.dotnetfunda.com/codes/code874-custom-paging-using-linq-.aspxRepeater1.DataSource = products.Skip(startRowIndex).Take(pageSize); Repeater1.DataBind();In this case, Repeater control is getting the custom paged data from the products collection.
I have found another great collection of LINQ samples at MSND - http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx. You can download the source code of this article from the download link available at the top of this article.
