Post
Ask Question

LINQ Basic To Advanced - MVC Demo Application

Introduction

Here, in this post, we are going to a see some basic and advanced LINQ queries. LINQ queries were introduced few years back to offer a consistent way for working with data across many datasources and formats. In LINQ queries, you will always work with objects, which makes it simple to write. I hope you would have already written lots of LINQ queries already, if you haven’t, I strongly reccommend you to read this blog where you can find the answer for, why do we need to use LINQ?. Here, I am going to create an MVC demo application. I hope you will like this. Now, let’s begin. You can always see this article in my blog here.

Download Source Code

Background

Whenever I got chance to write some Server side codes in C#, I always prefer using LINQ. A few weeks back, I was assigned to a training programme where my job was to teach LINQ, hence this post covers the the queries I have written for the training programme. Hope you will find these useful.

Using the code

A LINQ query can be written in two ways.

  • Query Syntax
  • Method Chain or Using dot(.) operator

There are so many LiNQ related articles available on the Internet but most of them don't cover the differences of writing the queries in two possible ways. The motif of this article is to write the queries in both ways, so that you can understand the differnces.

As I mentioned, we are going to create an MVC application, we need to create it first and then configure the entity. Let’s go and do that.

Create a database

To get started with, we need to configure our database first. To do so, either you can download the Wild World Importers from here or you can run the script file included in the downlaod link above.

Once after you have created the database, you can create your MVC application and Entity Data Model in it.

Configuring MVC application with Entity Data Model

By this time, I hope you would have configured your MVC application with Entity Data Model. Now it is time to create a controller and Entity object.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data.Entity;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Web.Mvc;  
  7. using System.Windows.Forms;  
  8. using LINQ_B_to_A.Models;  
  9. namespace LINQ_B_to_A.Controllers {  
  10.     public class HomeController: Controller {  
  11.         // GET: Home  
  12.         public MyDataModel DtContext {  
  13.             get;  
  14.             set;  
  15.         } = new MyDataModel();  
  16.     }  
  17. }  

Now, we can write some LINQ queries as everything is set to get started.

Setting up the Index view with possible actions

This is just to call the actions we are going write. Let’s see the Index page now.

  1. @ {  
  2.     ViewBag.Title = "Index";  
  3.     Layout = "~/Views/Shared/_Layout.cshtml";  
  4. } < h2 > Index < /h2> < input type = "button"  
  5. class = "btn-info"  
  6. onclick = "location.href='@Url.Action("  
  7. LoadAll ","  
  8. Home ")'"  
  9. value = "Load All - Query Expression" / > < input type = "button"  
  10. class = "btn-info"  
  11. onclick = "location.href='@Url.Action("  
  12. JoinWithWhere ","  
  13. Home ")'"  
  14. value = "Join With Where" / > < input type = "button"  
  15. class = "btn-info"  
  16. onclick = "location.href='@Url.Action("  
  17. LeftJoin ","  
  18. Home ")'"  
  19. value = "Left Join" / > < input type = "button"  
  20. class = "btn-info"  
  21. onclick = "location.href='@Url.Action("  
  22. DistinctSample ","  
  23. Home ")'"  
  24. value = "Distinct Sample" / > < input type = "button"  
  25. class = "btn-info"  
  26. onclick = "location.href='@Url.Action("  
  27. EqualsSamples ","  
  28. Home ")'"  
  29. value = "Equals Sample" / > < input type = "button"  
  30. class = "btn-info"  
  31. onclick = "location.href='@Url.Action("  
  32. NotEqualsSamples ","  
  33. Home ")'"  
  34. value = "Not Equals" / > < input type = "button"  
  35. class = "btn-info"  
  36. onclick = "location.href='@Url.Action("  
  37. PagingQueries ","  
  38. Home ")'"  
  39. value = "Paging Queries" / > < input type = "button"  
  40. class = "btn-info"  
  41. onclick = "location.href='@Url.Action("  
  42. MathQueries ","  
  43. Home ")'"  
  44. value = "Math Queries" / > < input type = "button"  
  45. class = "btn-info"  
  46. onclick = "location.href='@Url.Action("  
  47. StringQueries ","  
  48. Home ")'"  
  49. value = "String Queries" / > < input type = "button"  
  50. class = "btn-info"  
  51. onclick = "location.href='@Url.Action("  
  52. SelectMany ","  
  53. Home ")'"  
  54. value = "Select Many" / >  
LINQ
LINQ Basic to Advanced Index View

Normal Select Query With StartWith and Take

Let’s have a look at the preceding query.

  1. /// <summary>  
  2. /// Linq query with Select  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult LoadAll() {  
  6.     var loadAllData = (from d in DtContext.Cities where d.CityName.StartsWith("C") select d).Take(10);  
  7.     //Method Chain Format  
  8.     var loadAllData1 = DtContext.Cities.Where(d => d.CityName.StartsWith("C")).Take(10);  
  9.     return PartialView(loadAllData);  
  10. }  

As you can see, we are just fetching the data from Cities in both ways. And, we are using StartWith as a filter which actually looks for the city names starting with the letter C; and finally, we are taking 10 elements from it. The first query is the “Query syntax” and the second one is “Method chain”. Personally, I like the second way.

  1. @model IQueryable < LINQ_B_to_A.Models.City > < style > dd, dt, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. dd {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < dd > City Names < /dd>  
  14. @foreach(var ctyObj in Model) { < dt > @ctyObj.CityName < /dt>  
  15. } < pre > public ActionResult LoadAll() {  
  16.     var loadAllData = (from d in DtContext.Cities where d.CityName.StartsWith("C") select d).Take(10);  
  17.     //Method Chain Format  
  18.     var loadAllData1 = DtContext.Cities.Where(d => d.CityName.StartsWith("C")).Take(10);  
  19.     return PartialView(loadAllData);  
  20. } < /pre>  

We are ready to see our first query in action now. Let’s creat a partial View now to show the data we fetched.


LINQ

JOIN Query

To write a join, the query would be as preceding.

  1. /// <summary>  
  2. /// Linq query with join and where  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult JoinWithWhere() {  
  6.     var loadAllData = (from oOrders in DtContext.Orders join oOrderLines in DtContext.OrderLines on oOrders.OrderID equals oOrderLines.OrderID orderby oOrders.OrderID select new OrderAndOrderLines() {  
  7.         OrderId = oOrders.OrderID,  
  8.             Description = oOrderLines.Description,  
  9.             Quantity = oOrderLines.Quantity  
  10.     }).Take(10);  
  11.     //Method Chain Format  
  12.     var asMethodChain = DtContext.Orders.Join(DtContext.OrderLines, oOrders => oOrders.OrderID, oOrderLines => oOrderLines.OrderID, (oOrders, oOrderLines) => new {  
  13.         oOrders,  
  14.         oOrderLines  
  15.     }).OrderBy(@o => @o.oOrders.OrderID).Select(@s => new OrderAndOrderLines() {  
  16.         OrderId = @s.oOrders.OrderID,  
  17.             Description = @s.oOrderLines.Description,  
  18.             Quantity = @s.oOrderLines.Quantity  
  19.     }).Take(10);  
  20.     return PartialView(loadAllData);  
  21. }  

In the above query, we are just joining the tables Order and OrderLines with OrderID and to select, we are using another custom model OrderAndOrderLines.

  1. namespace LINQ_B_to_A.Models {  
  2.     public partial class OrderAndOrderLines {  
  3.         public int OrderId {  
  4.             get;  
  5.             set;  
  6.         }  
  7.         public string Description {  
  8.             get;  
  9.             set;  
  10.         }  
  11.         public int ? Quantity {  
  12.             get;  
  13.             set;  
  14.         }  
  15.     }  
  16. }  

The View can be written as follows.

  1. @model IEnumerable < LINQ_B_to_A.Models.OrderAndOrderLines > < style > td, th, thead, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. caption {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < table > < caption > Order Details < /caption> < tr > < th > Order ID < /th> < th > Description < /th> < th > Quantity < /th> < /tr>  
  14. @foreach(var @item in Model) { < tr > < td > @item.OrderId < /td> < td > @item.Description < /td> < td > @item.Quantity < /td> < /tr>  
  15. } < /table> < pre > public ActionResult JoinWithWhere() {  
  16.     var loadAllData = (from oOrders in DtContext.Orders join oOrderLines in DtContext.OrderLines on oOrders.OrderID equals oOrderLines.OrderID orderby oOrders.OrderID select new OrderAndOrderLines() {  
  17.         OrderId = oOrders.OrderID,  
  18.             Description = oOrderLines.Description,  
  19.             Quantity = oOrderLines.Quantity  
  20.     }).Take(10);  
  21.     //Method Chain Format  
  22.     var asMethodChain = DtContext.Orders.Join(DtContext.OrderLines, oOrders => oOrders.OrderID, oOrderLines => oOrderLines.OrderID, (oOrders, oOrderLines) => new {  
  23.         oOrders,  
  24.         oOrderLines  
  25.     }).OrderBy(o => o.oOrders.OrderID).Select(s => new OrderAndOrderLines() {  
  26.         OrderId = s.oOrders.OrderID,  
  27.             Description = s.oOrderLines.Description,  
  28.             Quantity = s.oOrderLines.Quantity  
  29.     }).Take(10);  
  30.     return PartialView(loadAllData);  
  31. } < /pre>  

Once you run the action, you can see the result as follows.

LINQ

LINQ Basic to Advanced Join With Where

Left JOIN Query

To write a Left join, the query would be as preceding.

  1. /// <summary>  
  2. /// Linq query with Left Join  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult LeftJoin() {  
  6.     var loadAllData = (from oOrder in DtContext.Orders join oOrderLine in DtContext.OrderLines on oOrder.OrderID equals oOrderLine.OrderID into lftOrder from afterJoined in lftOrder.DefaultIfEmpty() orderby oOrder.OrderID descending select new OrderAndOrderLines() {  
  7.         OrderId = oOrder.OrderID,  
  8.             Description = afterJoined.Description  
  9.     }).Take(10).ToList();  
  10.     //Method Chain Format  
  11.     var lftJoinMethodChain = (DtContext.Orders.GroupJoin(DtContext.OrderLines, oOrder => oOrder.OrderID, oOrderLine => oOrderLine.OrderID, (oOrder, lftJoin) => new {  
  12.         oOrder,  
  13.         lftJoin  
  14.     }).SelectMany(@sm => @sm.lftJoin.DefaultIfEmpty(), (@sm, afterJoin) => new {  
  15.         @sm,  
  16.         afterJoin  
  17.     }).OrderByDescending(@o => @o.sm.oOrder.OrderID).Select(@s => new OrderAndOrderLines() {  
  18.         OrderId = @s.sm.oOrder.OrderID,  
  19.             Description = @s.afterJoin.Description  
  20.     })).Take(10).ToList();  
  21.     return PartialView(loadAllData);  
  22. }  

In the above query, we are just joining the tables Order and OrderLines with OrderID and to select we are using an another custom model OrderAndOrderLines as our previous query. The differences you could find here are, using an ‘into’ and ‘DefaultIfEmpty’ statements. The DefaultIfEmpty is making sure that it returns empty if there is no appropriate rows found in the second table.

The View can be written as follows.

  1. @model IEnumerable < LINQ_B_to_A.Models.OrderAndOrderLines > < style > td, th, thead, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. caption {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < table > < caption > Order Details < /caption> < tr > < th > Order ID < /th> < th > Description < /th> < th > Quantity < /th> < /tr>  
  14. @foreach(var @item in Model) { < tr > < td > @item.OrderId < /td> < td > @item.Description < /td> < td > @item.Quantity < /td> < /tr>  
  15. } < /table> < pre > public ActionResult LeftJoin() {  
  16.     var loadAllData = (from oOrder in DtContext.Orders join oOrderLine in DtContext.OrderLines on oOrder.OrderID equals oOrderLine.OrderID into lftOrder from afterJoined in lftOrder.DefaultIfEmpty() orderby oOrder.OrderID descending select new OrderAndOrderLines() {  
  17.         OrderId = oOrder.OrderID,  
  18.             Description = afterJoined.Description  
  19.     }).Take(10).ToList();  
  20.     //Method Chain Format  
  21.     var lftJoinMethodChain = (DtContext.Orders.GroupJoin(DtContext.OrderLines, oOrder => oOrder.OrderID, oOrderLine => oOrderLine.OrderID, (oOrder, lftJoin) => new {  
  22.         oOrder,  
  23.         lftJoin  
  24.     }).SelectMany(sm => sm.lftJoin.DefaultIfEmpty(), (sm, afterJoin) => new {  
  25.         sm,  
  26.         afterJoin  
  27.     }).OrderByDescending(o => o.sm.oOrder.OrderID).Select(s => new OrderAndOrderLines() {  
  28.         OrderId = s.sm.oOrder.OrderID,  
  29.             Description = s.afterJoin.Description  
  30.     })).Take(10).ToList();  
  31.     return PartialView(loadAllData);  
  32. } < /pre>  

Once you run the action, you can see the result as follows.

LINQ

LINQ Basic to Advanced Left Join

As you can see in the image, the order id 200000 doesn’t has any appropriate rows in the second table, so it shows as empty.

Distinct Query

The below query shows how we can write simple distinct query.

  1. /// <summary>  
  2. /// Linq query Distinct sample  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult DistinctSample() {  
  6.     var distictSample = (from oOrder in DtContext.OrderLines select oOrder.Description).Distinct().Take(10).ToList();  
  7.     //Method Chain Format  
  8.     var distictAsMethodChain = (DtContext.OrderLines.Select(oOrder => oOrder.Description)).Distinct().Take(10).ToList();  
  9.     return PartialView(distictSample);  
  10. }  

In the above query, we use Distinct to make sure that only distict items are selected from the result. The View can be written as follows.

  1. @model List < string > < style > dd, dt, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. dd {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < dd > Order Descriptions < /dd>  
  14. @foreach(var orderLinesyObj in Model) { < dt > @orderLinesyObj < /dt>  
  15. } < pre > public ActionResult DistinctSample() {  
  16.     var distictSample = (from oOrder in DtContext.OrderLines select oOrder.Description).Distinct().Take(10).ToList();  
  17.     //Method Chain Format  
  18.     var distictAsMethodChain = (DtContext.OrderLines.Select(oOrder => oOrder.Description)).Distinct().Take(10).ToList();  
  19.     return PartialView(distictSample);  
  20. } < /pre>  

Once you run the action, you can see the result as follows.

LINQ

LINQ Basic to Advanced Distinct Query

Equals and Not Equals Queries

We can write the equals and not equals query as preceding.

  1. /// <summary>  
  2. /// Linq query Equals sample  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult EqualsSamples() {  
  6.     var objEquals = (from objCity in DtContext.Cities where objCity.CityName.Equals("Troy") select objCity).Take(2);  
  7.     //Method Chain Format  
  8.     var objEquals1 = DtContext.Cities.Where(d => d.CityName.Equals("Troy")).Take(2);  
  9.     return PartialView("OperatorSamples", objEquals);  
  10. }  
  11. /// <summary>  
  12. /// Linq query Not Equals sample  
  13. /// </summary>  
  14. /// <returns></returns>  
  15. public ActionResult NotEqualsSamples() {  
  16.     var objNotEquals = (from objCity in DtContext.Cities where objCity.CityName != "Troy"  
  17.         select objCity).Take(5);  
  18.     var objNotEquals1 = (from objCity in DtContext.Cities where!objCity.CityName.Equals("Troy") select objCity).Take(5);  
  19.     //Method Chain Format  
  20.     var objNotEquals2 = DtContext.Cities.Where(d => d.CityName != "Troy").Take(2);  
  21.     var objNotEquals3 = DtContext.Cities.Where(d => !d.CityName.Equals("Troy")).Take(2);  
  22.     return PartialView("OperatorSamples", objNotEquals);  
  23. }  

The view can be written as follows.

  1. @model IQueryable < LINQ_B_to_A.Models.City > < style > td, th, thead, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. caption {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < table > < caption > City Details < /caption> < tr > < th > City ID < /th> < th > City Name < /th> < th > City Location < /th> < /tr>  
  14. @foreach(var @item in Model) { < tr > < td > @item.CityID < /td> < td > @item.CityName < /td> < td > @item.Location < /td> < /tr>  
  15. } < /table> < caption > Equals Oerator < /caption> < pre > public ActionResult EqualsSamples() {  
  16.     var objEquals = (from objCity in DtContext.Cities where objCity.CityName.Equals("Troy") select objCity).Take(2);  
  17.     //Method Chain Format  
  18.     var objEquals1 = DtContext.Cities.Where(d => d.CityName.Equals("Troy")).Take(2);  
  19.     return PartialView("OperatorSamples", objEquals);  
  20. }  
  21. public ActionResult NotEqualsSamples() {  
  22.     var objNotEquals = (from objCity in DtContext.Cities where objCity.CityName != "Troy"  
  23.         select objCity).Take(5);  
  24.     var objNotEquals1 = (from objCity in DtContext.Cities where!objCity.CityName.Equals("Troy") select objCity).Take(5);  
  25.     //Method Chain Format  
  26.     var objNotEquals2 = DtContext.Cities.Where(d => d.CityName != "Troy").Take(2);  
  27.     var objNotEquals3 = DtContext.Cities.Where(d => !d.CityName.Equals("Troy")).Take(2);  
  28.     return PartialView("OperatorSamples", objNotEquals);  
  29. }  
  30. public ActionResult PagingQueries() {  
  31.     var objNotEquals = (from objCity in DtContext.Cities where objCity.CityName != "Troy"  
  32.         orderby objCity.CityName ascending select objCity).Skip(5).Take(5);  
  33.     //Method Chain Format  
  34.     var objNotEquals2 = DtContext.Cities.Where(d => d.CityName != "Troy").Skip(5).Take(5);  
  35.     return PartialView("OperatorSamples", objNotEquals);  
  36. } < /pre>  

Once you run the action you can see the result as follows.

LINQ

LINQ Basic to Advanced Equals and Not Equals

LINQ Paging Queries

Paging queries are always important as we work in some grid controls, with LINQ those are very easy. Let’s see one of that query.

  1. /// <summary>  
  2. /// Linq Paging Queries  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult PagingQueries() {  
  6.     var objNotEquals = (from objCity in DtContext.Cities where objCity.CityName != "Troy"  
  7.         orderby objCity.CityName ascending select objCity).Skip(5).Take(5);  
  8.     //Method Chain Format  
  9.     var objNotEquals2 = DtContext.Cities.Where(d => d.CityName != "Troy").Skip(5).Take(5);  
  10.     return PartialView("OperatorSamples", objNotEquals);  
  11. }  

Once you run the action, you can see the result as follows.

LINQ

LINQ Basic to Advanced Paging Queries

LINQ Math Queries

Here, we are going to write the possible Math functions in our LINQ query.

  1. /// <summary>  
  2. /// Math Queries  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult MathQueries() {  
  6.     var objMath = (from objInv in DtContext.InvoiceLines where objInv.ExtendedPrice > 10 && objInv.Quantity < 15 orderby objInv.InvoiceLineID descending select new MathClass() {  
  7.         Actual = objInv.ExtendedPrice,  
  8.             Round = Math.Round(objInv.ExtendedPrice),  
  9.             Floor = Math.Floor(objInv.ExtendedPrice),  
  10.             Ceiling = Math.Ceiling(objInv.ExtendedPrice),  
  11.             Abs = Math.Abs(objInv.ExtendedPrice)  
  12.     }).Take(10);  
  13.     //Method Chain Format  
  14.     var objMath2 = DtContext.InvoiceLines.Where(objInv => objInv.ExtendedPrice > 10 && objInv.Quantity < 15).OrderByDescending(o => o.InvoiceLineID).Select(objInv => new MathClass() {  
  15.         Actual = objInv.ExtendedPrice,  
  16.             Round = Math.Round(objInv.ExtendedPrice),  
  17.             Floor = Math.Floor(objInv.ExtendedPrice),  
  18.             Ceiling = Math.Ceiling(objInv.ExtendedPrice),  
  19.             Abs = Math.Abs(objInv.ExtendedPrice)  
  20.     }).Take(10);  
  21.     return PartialView("MathQueries", objMath);  
  22. }  

As you can see, we have written most of the possible Math functions in our query and selecting with a custome model MathClass.

  1. namespace LINQ_B_to_A.Models {  
  2.     public partial class MathClass {  
  3.         public decimal Actual {  
  4.             get;  
  5.             set;  
  6.         }  
  7.         public decimal Round {  
  8.             get;  
  9.             set;  
  10.         }  
  11.         public decimal Floor {  
  12.             get;  
  13.             set;  
  14.         }  
  15.         public decimal Ceiling {  
  16.             get;  
  17.             set;  
  18.         }  
  19.         public decimal Abs {  
  20.             get;  
  21.             set;  
  22.         }  
  23.     }  
  24. }  

Let’s see the View now.

  1. @model IQueryable < LINQ_B_to_A.Models.MathClass > < style > td, th, thead, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. caption {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < table > < caption > Math Operators < /caption> < tr > < /tr>  
  14. @foreach(var @item in Model) { < tr > < td > Actual: @item.Actual < /td> < td > Round: @item.Round < /td> < td > Floor: @item.Floor < /td> < td > Ceiling: @item.Ceiling < /td> < td > Abs: @item.Abs < /td> < /tr>  
  15. } < /table> < pre > public ActionResult MathQueries() {  
  16.     var objMath = (from objInv in DtContext.InvoiceLines where objInv.ExtendedPrice > 10 && objInv.Quantity < 15 orderby objInv.InvoiceLineID descending select new MathClass() {  
  17.         Actual = objInv.ExtendedPrice,  
  18.             Round = Math.Round(objInv.ExtendedPrice),  
  19.             Floor = Math.Floor(objInv.ExtendedPrice),  
  20.             Ceiling = Math.Ceiling(objInv.ExtendedPrice),  
  21.             Abs = Math.Abs(objInv.ExtendedPrice)  
  22.     }).Take(10);  
  23.     //Method Chain Format  
  24.     var objMath2 = DtContext.InvoiceLines.Where(objInv => objInv.ExtendedPrice > 10 && objInv.Quantity < 15).OrderByDescending(o => o.InvoiceLineID).Select(objInv => new MathClass() {  
  25.         Actual = objInv.ExtendedPrice,  
  26.             Round = Math.Round(objInv.ExtendedPrice),  
  27.             Floor = Math.Floor(objInv.ExtendedPrice),  
  28.             Ceiling = Math.Ceiling(objInv.ExtendedPrice),  
  29.             Abs = Math.Abs(objInv.ExtendedPrice)  
  30.     }).Take(10);  
  31.     return PartialView("MathQueries", objMath);  
  32. } < /pre>  

See the result now.

LINQ

LINQ Basic to Advanced Math Queries

LINQ String Queries

As we saw the Math queries, here, we are going to write the possible String functions in our LINQ query.

  1. /// <summary>  
  2. /// String Queries  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult StringQueries() {  
  6.     var objString = (from objInv in DtContext.InvoiceLines where objInv.ExtendedPrice > 10 && objInv.Quantity < 15 orderby objInv.InvoiceLineID descending select new StringClass() {  
  7.         Actual = objInv.Description,  
  8.             Insert = objInv.Description.Insert(2, "SibeeshPassion"),  
  9.             Remove = objInv.Description.Remove(1, 1),  
  10.             Substring = objInv.Description.Substring(2, 3),  
  11.             ToLower = objInv.Description.ToLower(),  
  12.             ToUpper = objInv.Description.ToUpper(),  
  13.             TrimEnd = objInv.Description.TrimEnd(),  
  14.             TrimStart = objInv.Description.TrimStart()  
  15.     }).Take(2);  
  16.     //Method Chain Format  
  17.     var objString2 = DtContext.InvoiceLines.Where(objInv => objInv.ExtendedPrice > 10 && objInv.Quantity < 15).OrderByDescending(o => o.InvoiceLineID).Select(objInv => new StringClass() {  
  18.         Actual = objInv.Description,  
  19.             Insert = objInv.Description.Insert(2, "SibeeshPassion"),  
  20.             Remove = objInv.Description.Remove(1, 1),  
  21.             Substring = objInv.Description.Substring(2, 3),  
  22.             ToLower = objInv.Description.ToLower(),  
  23.             ToUpper = objInv.Description.ToUpper(),  
  24.             TrimEnd = objInv.Description.TrimEnd(),  
  25.             TrimStart = objInv.Description.TrimStart()  
  26.     }).Take(2);  
  27.     return PartialView("StringQueries", objString);  
  28. }  

As you can see, here we are using a custom model StringClass.

  1. namespace LINQ_B_to_A.Models {  
  2.     public partial class StringClass {  
  3.         public string Actual {  
  4.             get;  
  5.             set;  
  6.         }  
  7.         public string Insert {  
  8.             get;  
  9.             set;  
  10.         }  
  11.         public string Remove {  
  12.             get;  
  13.             set;  
  14.         }  
  15.         public string Substring {  
  16.             get;  
  17.             set;  
  18.         }  
  19.         public string ToUpper {  
  20.             get;  
  21.             set;  
  22.         }  
  23.         public string ToLower {  
  24.             get;  
  25.             set;  
  26.         }  
  27.         public string TrimStart {  
  28.             get;  
  29.             set;  
  30.         }  
  31.         public string TrimEnd {  
  32.             get;  
  33.             set;  
  34.         }  
  35.     }  
  36. }  

Let’s see the view now.

  1. @model IQueryable < LINQ_B_to_A.Models.StringClass > < style > td, th, thead, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. caption {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < table > < caption > String Operators < /caption> < tr > < /tr>  
  14. @foreach(var @item in Model) { < tr > < td > Actual: @item.Actual < /td> < td > Insert: @item.Insert < /td> < td > Remove: @item.Remove < /td> < td > Substring: @item.Substring < /td> < td > ToLower: @item.ToLower < /td> < td > ToUpper: @item.ToUpper < /td> < td > TrimEnd: @item.TrimEnd < /td> < td > TrimStart: @item.TrimStart < /td> < /tr>  
  15. } < /table> < pre > public ActionResult StringQueries() {  
  16.     var objString = (from objInv in DtContext.InvoiceLines where objInv.ExtendedPrice > 10 && objInv.Quantity < 15 orderby objInv.InvoiceLineID descending select new StringClass() {  
  17.         Actual = objInv.Description,  
  18.             Insert = objInv.Description.Insert(2, "SibeeshPassion"),  
  19.             Remove = objInv.Description.Remove(1, 1),  
  20.             Substring = objInv.Description.Substring(2, 3),  
  21.             ToLower = objInv.Description.ToLower(),  
  22.             ToUpper = objInv.Description.ToUpper(),  
  23.             TrimEnd = objInv.Description.TrimEnd(),  
  24.             TrimStart = objInv.Description.TrimStart()  
  25.     }).Take(2);  
  26.     //Method Chain Format  
  27.     var objString2 = DtContext.InvoiceLines.Where(objInv => objInv.ExtendedPrice > 10 && objInv.Quantity < 15).OrderByDescending(o => o.InvoiceLineID).Select(objInv => new StringClass() {  
  28.         Actual = objInv.Description,  
  29.             Insert = objInv.Description.Insert(2, "SibeeshPassion"),  
  30.             Remove = objInv.Description.Remove(1, 1),  
  31.             Substring = objInv.Description.Substring(2, 3),  
  32.             ToLower = objInv.Description.ToLower(),  
  33.             ToUpper = objInv.Description.ToUpper(),  
  34.             TrimEnd = objInv.Description.TrimEnd(),  
  35.             TrimStart = objInv.Description.TrimStart()  
  36.     }).Take(2);  
  37.     return PartialView("StringQueries", objString);  
  38. } < /pre>  

Now see the result.

LINQ

LINQ Basic to Advanced String Queries

SelectMany Query

A SelectMany query flattern the result to a single dimentional collection, so to loop through the result we just need only one loop.

LINQ

LINQ Basic to Advanced SelectMany Tooltip

If you use the normal Select, you will be getting a list of lists, thus you will have to use two loops to get the datas.

LINQ

LINQ Basic to Advanced Select Tooltip

  1. /// <summary>  
  2. /// Linq query with SelectMany  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public ActionResult SelectMany() {  
  6.     var selectMany = DtContext.Orders.SelectMany(order => order.Invoices).Take(10);  
  7.     var select = DtContext.Orders.Select(order => order.Invoices).Take(10);  
  8.     return PartialView("SelectMany", selectMany);  
  9. }  

Let’s see the view now.

  1. @model IQueryable < LINQ_B_to_A.Models.Invoice > < style > td, th, thead, pre {  
  2.     border: 1 px solid# ccc;  
  3.     padding: 5 px;  
  4.     margin: 5 px;  
  5.     width: auto;  
  6.     width: 20 % ;  
  7. }  
  8. caption {  
  9.     background - color: #a9a9a9  
  10. }  
  11. pre {  
  12.     width: auto;  
  13. } < /style> < table > < caption > Invoice Details < /caption> < tr > < th > Order ID < /th> < th > Invoice ID < /th> < th > Invoice Date < /th> < /tr>  
  14. @foreach(var @item in Model) { < tr > < td > @item.OrderID < /td> < td > @item.InvoiceID < /td> < td > @item.InvoiceDate < /td> < /tr>  
  15.     } < /table> < caption > Select Many < /caption> < pre >  
  16.     /// <summary>  
  17.     /// Linq query with SelectMany  
  18.     /// </summary>  
  19.     /// <returns></returns>  
  20.     public ActionResult SelectMany() {  
  21.         var selectMany = DtContext.Orders.SelectMany(order => order.Invoices).Take(10);  
  22.         var select = DtContext.Orders.Select(order => order.Invoices).Take(10);  
  23.         return PartialView("SelectMany", selectMany);  
  24.     } < /pre>  

Now, see the result.

That’s all for today. I will come with another set of LINQ queries in the next part. Till then, bye.

LINQ

LINQ Basic to Advanced SelectMany

Conclusion

Did I miss anything that you may think is needed? Did you find this post useful? I hope you liked this article. Please share with me your valuable suggestions and feedback.

Your turn. What do you think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.


Similar Articles