DotNetStories
An abundance of LINQ queries and expressions using both the query and method syntax.
In this post I will be writing LINQ queries against an array of strings, an array of integers.Moreover I will be using LINQ to query an SQL Server database.
I can use LINQ against arrays since the array of strings/integers implement the IENumerable interface.
I thought it would be a good idea to use both the method syntax and the query syntax.
There are other places on the net where you can find examples of LINQ queries but I decided to create a big post using as many LINQ examples as possible. We will start with very simple queries 1) Fire up Visual Studio 2008/2010. Express editions will work fine. 2) Create an empty web site. Give it the name of your choice.Choose C# as the development language. 3) Add a web form item to your site. Leave the default name. We will start off with a very simple example. 4) In the Page_Load event handling routine type (Do not comment out the array declaration. Very often in this post you will have to comment out bits of code but not this one)
string[] names = { "Hilary", "George", "Paul", "Mary", "Christine", "Fred",
"John", "Oliver", "Ken", "Jessy", "Joddie", "Helen", "Wesley", "Elvis" };
foreach (string name in names)
{
Response.Write(name);
Response.Write("<br/>");
}
We have our single array and just loop through the items.Run your application.Then comment out the foreach loop. 5) If we want to find all the names in the array that start with the letter "M", we can type in the Page_Load event handling routine,
foreach (string name in names)
{
if (name.StartsWith("M"))
Response.Write(name);
Response.Write("<br/>");
}
Run your application and see the results.6) Now let's have the same result by writing a LINQ query.Comment out the previous foreach loop and type
var namesStartWithM = from name in names
where name.StartsWith("M")
select name;
foreach (string item in namesStartWithM)
{
Response.Write(item);
Response.Write("<br/>");
}
Run your application and see the results.7) Now let's rewrite the previous query using lambdas Comment out the previous foreach loop and the var statement and typevar namesStartWithM = names.Where(n => n.StartsWith("M"));
foreach (string item in namesStartWithM)
{
Response.Write(item);
Response.Write("<br/>");
}
Run your application again and see the results.
8) Now let's rewrite the previous query in a much simpler way.Comment out the previous foreach loop and the var statement and type
foreach (string name in names.Where(n => n.StartsWith("M")))
{
Response.Write(name);
Response.Write("<br/>");
}
This is a much simple way. Run your application again and see the results. 9) Now let's say we want to find only the names that have more than 6 characters.
Comment out the previous foreach loop and in the Page_Load event handling routine
foreach (string name in names.Where(n => n.Length>6))
{
Response.Write(name);
Response.Write("<br/>");
}
Run your application again and see the results.We can rewrite the above query using the query syntax.
Comment out the previous foreach loop and type
var mynames = from name in names
where name.Length > 6
select name;
foreach (string item in mynames)
{
Response.Write(item);
Response.Write("<br/>");
}
Run your application again and see the results.
10) Now let's say we want to order our array using the query syntax.
Comment out the previous foreach loop and the var statement in the Page_Load event handling routine type
var query = from name in names
orderby name
select name;
foreach (string name in query)
{
Response.Write(name);
Response.Write("<br/>");
}
Run your application and see the results.11) Now let's say we want to order our array using the method syntax.
Comment out the previous foreach loop and the var statement. In the Page_Load event handling routine type
foreach (string name in names.OrderBy(n => n))
{
Response.Write(name);
Response.Write("<br/>");
}
Run your application and see the results.
We can also order our list descending. Change the first line of the loop with this lineforeach (string name in names.OrderByDescending(n => n))
12) But what if we wanted to get only some elements of the array and ignore the first 4.
Comment out the previous foreach loop. In the Page_Load event handling routine type
foreach (string name in names.Skip(4))
{
Response.Write(name);
Response.Write("<br/>");
}
Run your application and see the results. Comment out the previous foreach loop and type
var mynames = from name in names.Skip(4)
select name;
foreach (string name in mynames)
{
Response.Write(name);
Response.Write("<br/>");
}
13) But what if wanted to take the first four elements of the array only.
Comment out the previous foreach loop and var statement and type
foreach (string name in names.Take(4))
{
Response.Write(name);
Response.Write("<br/>");
}
Run your application and see the results.Now let's do the same using the query syntax.Comment out the previous foreach loop and type
var mynames = from name in names.Take(4)
select name;
foreach (string name in mynames)
{
Response.Write(name);
Response.Write("<br/>");
}
14) Let's move on and see a more complicated example where we want to have a logical operator (e.g &&). We want to find all names that start with "J" and are more than 4 characters.Comment out the previous foreach loop and var statement and in the Page_Load event handling routine type
var mynames = from name in names
where name.StartsWith("J") && name.Length > 4
select name;
foreach (string name in mynames)
{
Response.Write(name);
Response.Write("<br/>");
}
Run your application and see the results.
15) Let's rewrite the above query using the method syntax.Comment out the previous foreach loop and the var statement. In the Page_Load event handling routine type
var mynames = names.Where(n => n.StartsWith("J") && n.Length > 4);
foreach (string name in mynames)
{
Response.Write(name);
Response.Write("<br/>");
}
Run your application and see the results.16) Now let's see more examples on how to write LINQ queries. I will use LINQ to SQL to demonstrate that.
I assume that you have access to a version of SQL Server and Northwind database.
If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here
17) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.
18) From the Server Explorer / Data Explorer window drag the Customers,Orders,Order Details tables and drop them on the Northwind.dbml.
19) Have a look at the generated code from the Linq engine in Northwind.designer.cs
20) Add a new web form to your site.Name it LINQtoSQL.aspx. Drag a Gridview web server control on the LINQtoSQL.aspx page.Let's say we want to return all the customers.
21) In the Page_Load event handling routine of the LINQtoSQL.aspx page type
NorthwindDataContext ctx = new NorthwindDataContext();
var query = from mycust in ctx.Customers
select mycust;
GridView1.DataSource = query;
GridView1.DataBind();Note that we return all the fields,columns from the Customers table.If we want to use the method syntax, replace this line of codevar query = from mycust in ctx.Customers
select mycust;with this line of codevar query = ctx.Customers.Select(c => c);
Run your application and see the results.22) But if we wanted to return only one field,column of the underlying table(Customers) ? Change this line of codevar query = from mycust in ctx.Customers
select mycust;with this onevar query = from mycust in ctx.Customers
select mycust.ContactName;Let's rewrite the query with the method syntax.Replace this line of codevar query = from mycust in ctx.Customers
select mycust.ContactName;with this line of codevar query = ctx.Customers.Select(c => c.ContactName);
Run your application and see the results.23) But what if wanted to have 2 or more fields returned from the Customers table?Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then typeNorthwindDataContext ctx = new NorthwindDataContext();
var query = from mycust in ctx.Customers
select new { mycust.ContactName, mycust.CompanyName, mycust.Country };
GridView1.DataSource = query;
GridView1.DataBind();Run your application and see the results.Let's rewrite the query with the method syntax.Replace this line of codevar query = from mycust in ctx.Customers
select new { mycust.ContactName, mycust.CompanyName, mycust.Country };with this line of codevar query = ctx.Customers.Select(c => new { c.ContactName, c.CompanyName, c.Country });
What we did here was to create a new anonymous type.If you want to see what kind of anonymous type this is, after this lineRun your application and see the results.GridView1.DataBind();type
Response.Write(query.First().GetType());Run your application and see the results.In my case the anonymous type looks like this<>f__AnonymousType0`3[System.String,System.String,System.String]24) Let's imagine we have a custom named type e.g Employee and we wanted to select data into this named type from the anonymous type we just demonstrated before.Add another item in your site, a class file. Name it Employee.cs.Inside the .cs file typeclass Employee
{
public string Name { get; set; }
public string Company { get; set; }
public string Country { get; set; }
}Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then typeNorthwindDataContext ctx = new NorthwindDataContext();
var query = from mycust in ctx.Customers
select new Employee { Name = mycust.ContactName, Company= mycust.CompanyName,Country = mycust.Country };
foreach (Employee item in query)
{
Response.Write(item.Name);
Response.Write("<br/>");
Response.Write(item.Company);
Response.Write("<br/>");
Response.Write(item.Country);
Response.Write("<br/>");
}Have a look at the code above to see how we select data into a named type from an anonymous type.Run your application and see the data selected into the Employee type printed out in the screen.Let's rewrite the query with the method syntax.Replace this line of codewith this line of codevar query = from mycust in ctx.Customers
select new Employee { Name = mycust.ContactName, Company= mycust.CompanyName,Country = mycust.Country };var query = ctx.Customers.Select(mycust => new Employee
{ Name = mycust.ContactName, Company = mycust.CompanyName,Country = mycust.Country });Run your application and see the results.25) Let's move on to something else. We have orders that are associated with customers. Let's say we want to get only the order date for each order regardless of the customer it is associated with.Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then typeNorthwindDataContext ctx = new NorthwindDataContext();
var query = from mycust in ctx.Customers
from order in mycust.Orders
select order.OrderDate;
GridView1.DataSource = query;
GridView1.DataBind();Run your application and see the results.26) Let's move on with our example and see how we can filter our results and return a subset of the data. We want to get all the columns of the Customers table, only for them that they live in Germany.Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then typeNorthwindDataContext ctx = new NorthwindDataContext();
var query = from mycust in ctx.Customers
where mycust.Country == "Germany"
select mycust;
GridView1.DataSource = query;
GridView1.DataBind();Run your application and see the results.Now let's rewrite the query using the method syntax. Replace this line of codevar query = from mycust in ctx.Customers
where mycust.Country == "Germany"
select mycust;with this line of codevar query = ctx.Customers.Where(mycust => mycust.Country=="Germany");Run your application and see the results.27) We want to get the customers that have more than 17 orders.Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then typeNorthwindDataContext ctx = new NorthwindDataContext();
var query = from mycust in ctx.Customers
where mycust.Orders.Count > 17
select mycust;
GridView1.DataSource = query;
GridView1.DataBind();Run your application and see the results.Now let's rewrite the query using the method syntax. Replace this line of codevar query = from mycust in ctx.Customers
where mycust.Orders.Count > 17
select mycust;with this line of code
var query = ctx.Customers.Where(mycust => mycust.Orders.Count > 17);
Run your application and see the results.28) We want to get the customers that have more than 17 orders and live in Germany.Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then typeNorthwindDataContext ctx = new NorthwindDataContext();
var query = from mycust in ctx.Customers
where mycust.Orders.Count > 17 && mycust.Country=="Germany"
select mycust;
GridView1.DataSource = query;
GridView1.DataBind();Run your application and see the results.If you want to use OR operator just replace the && with ||Let's use the method syntax.Replace this line of codevar query = from mycust in ctx.Customers
where mycust.Orders.Count > 17 && mycust.Country=="Germany"
select mycust;with this line of codevar query = ctx.Customers.Where(mycust => mycust.Orders.Count > 17 && mycust.Country=="Germany");
Run your application and see the results.29) We want to get the customers and their respective orders. We need to perform some sort of join.Comment out everything in the Page_Load event handling routine and typeNorthwindDataContext ctx = new NorthwindDataContext();
var query =
from c in ctx.Customers
join o in ctx.Orders on c.CustomerID equals o.CustomerID
into orders
select new { c.ContactName, OrderCount = orders.Count() };
GridView1.DataSource = query;
GridView1.DataBind();Run your application and see the results.30) Add another web form to your site, name it ArraysOfInt.aspx. Let's investigate more extension methods.In the Page_Load event handling routine type
int[] mynums = { 12, 45, 23, 34, 67, 45, 78, 11, 74, 81, 190, 99, 102,109, 112, 116, 27, 121, 65, 134, 19, 154 };
var query = mynums.Any();
Response.Write(query);Run your application and see the results. You will get back True. The code above checks if the array has items in it or not.31) Let's say that we want to check if all the items in the array are greater than 5.Change the the previous var statement with this onevar query = mynums.All(p => p > 5);
Run your application and see the results. You will get back True.32) If we wanted to check if the value 45 is included in this array. Change the previous var statement with thisvar query = mynums.Contains(45);Run your application and see the results.33) What if wanted to find the number of the items in the array? What if wanted to find the sum, the minimum, the maximum, the average, the first, the last values in the array?Comment out the previous var statement and type the following. After you finish with the first var statement, comment it out and follow with the next one.Only one var statement should be uncommented at each time.var query = mynums.Count();
var query = mynums.Sum();
var query = mynums.Min();
var query = mynums.Max();
var query = mynums.Average();
var query = mynums.First();
var query = mynums.Last();
Run your application and see the results.34) What if wanted to get the first number in the sequence that is greater than 100?Comment out the previous var statement and typevar query = mynums.First(p => p > 100);Run your application and see the results.35) What if wanted to get the last item that is greater than 100? Comment out the previous var statement and typevar query = mynums.Last(p => p > 100);Run your application and see the results.I could go on and on but I think you have a good view now on how LINQ works are what are the main extension methods.Email me if you need the source code.Hope it helps!!!