Friday 6 January 2012

Article on ASP.NET LINQ


Probably the most exciting new feature of LINQ is the ability to write in line SQL style queries, known as query expressions (using query syntax).  This tutorial introduces the basic concept

What is LINQ Query Syntax?
LINQ query syntax is a set of query keywords built into the .NET framework (3.5 and higher) that allow the developer to write SQL style commands in line straight in the code editor, without the use of quotes.

Introduction

The .NET framework 3.5 introduces us to the following query keywords;
  • from / in - Specifies the data source
  • where - Conditional boolean expression (e.g. i == 0)
  • orderby (ascending/descending) - Sorts the results into ascending or descending order
  • select - Adds the result to the return type
  • group / by - Groups the results based on a given key
There are more keywords that provide additional functionality, but they are outside the scope of this tutorial.

Writing your first query

The quickest easiest way to learn how to write query expressions is to go right ahead and just do it.
This tutorial assumes that we have the following Generic List, called "Characters", present in our project;
This tutorial assumes that we have the following Generic List, called "Characters", present in our project;

private readonly List<Character> Characters = new List<Character>
{
//Format: Character Name, Gender, Number of episodes
      new Character("Charlie",Gender.Male, 162),
      new Character("Alan",Gender.Male, 162),
      new Character("Berta",Gender.Female, 117),
      new Character("Jake",Gender.Male, 162),
      new Character("Evelyn",Gender.Female, 78),
      new Character("Judith",Gender.Female, 69)
 };

Note: The Character class above is just a trivial class containing a string, enum and integer.  You could do this same example with any data types.

Note: The Character class above is just a trivial class containing a string, enum and integer.  You could do this same example with any data types.
Here is our first example;

IEnumerable<Character> results = from character in Characters
                                 where character.Episodes > 120
                                 select character;

Ok, lets just take a closer look;

from character in Characters
where character.Episodes > 120
select character;

We have three things going on here;
  • Specify the data source (Characters) and a temporary variable for each element within the data source (in this case a Generic List)
  • Create a condition with a boolean result used to query the data source
  • If the condition is met, select the temporary variable and add it to our results collection (IEnumerable<Type>)
The above statement returns a list of all the "characters" whom have appeared in more than 120 episodes.  We can then use a foreach loop to iterate through the list of results and do whatever we need to do.

This is a good example of a basic LINQ query expression.  We can also do more interesting things like;

Make our conditions as complicated as we need;
where character.Episodes > 120 && character.Episodes < 200

Order our results list in Ascending or Descending keywords;

from character in Characters
where character.Episodes > 120
orderby character.Name ascending
select character;
And even group the results;

from character in Characters
where character.Episodes > 0
group character by character.Episodes;

The demo project/source code available at the top of the page shows these scenarios in action.

Mixing Query Syntax and Extension Methods

The System.Linq namespace also gives us the ability to to use LINQ's built in extension methods on the results of our query expressions.  For example, instead of storing the results of the query in a variable and then calling the Count extension method and storing the result in another variable, we can just do it all right on a single line;

int count = (from character in Characters
             where character.Episodes > 120
             select character).Count();

There is some example for filter option
Ex: 1

clsQualitySetupCollection dt = (clsQualitySetupCollection)Session["__QUALITY"];

IEnumerable<clsQualitySetup> query2 = from r in dt.AsEnumerable()
where r.DESCR.ToUpper().StartsWith(selItem.Text.ToUpper())
                                      select r;

Filter “DESCR” by the “selItem.Text” added in another clsQualitySetupCollection class object as shown in below.

clsQualitySetupCollection oColl = new clsQualitySetupCollection();
if (query2.Count() > 0)
    oColl.AddRange(query2);
Ex: 2

clsQualitySetupCollection dt = (clsQualitySetupCollection)Session["__QUALITY"];
clsQualitySetupCollection oCollToSave = new clsQualitySetupCollection();

IEnumerable<clsQualitySetup> obj = from r in dt.AsEnumerable()
where r.QCREQD.Equals("Y") || r.OLDQCREQD.Equals("Y")
                                   select new clsQualitySetup
                                   {
                                       LOCATIONCODE = ucQryLocType.Value,
                                       ITEMCD = r.ITEMCD,
                                       QCREQD = r.QCREQD,
                                       OLDQCREQD = r.OLDQCREQD
                                    };
oCollToSave.AddRange(obj);
Ex: 3

clsQcInspectionDetailsObserrvelCollection oObs = (clsQcInspectionDetailsObserrvelCollection)Session["__QUALITYDATA_PRM"];
clsQcInspectionDetailsCollection oDtl = (clsQcInspectionDetailsCollection)Session["__QUALITYDATA_TMP"];

To find the position of the perticular data by some where cluse

#region ItemIndex
int ItemIndex = oDtl.AsEnumerable().Select((row, index) => new
{
    row.ITEMCD,
    row.QCNO,
    row.GRNNO,
    row.SRLNO,
    Position = index
})
.Where(w => w.ITEMCD == hdnItemCD.Value
         && w.QCNO == hdnQcNo.Value
         && w.GRNNO == hdnDocNo.Value
         && w.SRLNO == Convert.ToInt32(hdnSrlno.Value))
.First()
.Position;
#endregion

Posted By: Mr. Palash Paul

1 comment:

  1. It is really interesting it is useful and informative thnks for sharing this information.


    dotnet training in chennai

    ReplyDelete