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