Filters in LINQ

LINQ plays an important role in application development using Entity Framework. In the past posts, we saw how to make use of Entity Framework to make the basic insert, update, delete and display operations and about some frequent errors.

 

LINQ, Language Integrated Query, uses similar querying syntax like SQL with some minor differences. The LINQs can be written in two ways, as queries and as Lambda expressions. Major difference between LINQ and SQL is, the former is used to query the objects(xml, SQL, entities) whereas the latter is used to query the relational database(tables).With this small introduction to LINQ, let us see how to do LINQ querying.

 

In this post, I will be covering various filters in LINQ that during displaying Records.

 

Filters ———- Description

 

Contains —— “Where in” in SQL

StartsWith—– “Like a%” in SQL

Single——— “Select Top 1” in SQL

Skip(n)——– Skips first n records from the list

Take(n)——– Takes first n records from the list

Distinct——- Removes duplicate records from the list

First ——— First element in the List

Last ———- Last element in the list

FirstOrDefault- First element or the default value from the list

LastOrDefault– Last element or the default value from the list

 

Find below the sample code implementing the above filters on the AdventureWorks db(This can be downloaded from the link http://msftdbprodsamples.codeplex.com/releases/view/93587 ).

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace EntityFrameworksFilters

{

    class Program

    {

        static void Main(string[] args)

        {

            using (AdventureWorksModelContainer context = new AdventureWorksModelContainer())

            {

                        //using Contains

                Console.WriteLine(“The currencies that contains the name Dollar in it”);

                var Records = from e in context.Currencies

                              where e.Name.Contains(“Dollar”)

                              select e.Name;

                foreach (string currencyName in Records)

                {

                    Console.WriteLine(currencyName);

                }

                Console.WriteLine(“Enter a key to view the first 10 currencies in the list”);

                Console.ReadKey();

//using Take

                var First10 = (from e in context.Currencies

                              select e.Name).Take(10);

                foreach (string currencyName in First10)

                {

                    Console.WriteLine(currencyName);

                }

                        //Using Skip

                Console.WriteLine(“Enter a key to view the second 10 currencies in the list”);

                Console.ReadKey();

                var Second10 = (from e in context.Currencies

                               select e).OrderBy(e=>e.Name).Skip(10).Take(10);

                foreach (var currencyName in Second10)

                {

                    Console.WriteLine(currencyName.Name);

                }

                        //Using StartsWith

                Console.WriteLine(“Enter a key to view the currencies starting with the letter ‘A’ in the list”);

                Console.ReadKey();

                var StartsWith = (from e in context.Currencies

                                  where e.Name.StartsWith(“A”)

                                  select e);

                foreach (var currencyName in StartsWith)

                {

                    Console.WriteLine(currencyName.Name);

                }

                        //Using Distinct

                Console.WriteLine(“Enter a key to view the Distinct list of currencies in the List”);

                Console.ReadKey();

                var DistinctList = (from e in context.Currencies

                                    select e).Distinct();

                foreach (var currencyName in DistinctList)

                {

                    Console.WriteLine(currencyName.Name);

                }

                Console.ReadKey();

            }

        }

    }

}

 

The project is available for download in the link:  https://docs.google.com/file/d/0B915XE_zbo18Rlpaa2R6ejlQTzA/edit?usp=sharing

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