LINQ Flashcards
LINQ
for the application to be able to retrieve the data from sql server
the developer needs to know ado.net and Transact- sql
LINQ stands for Language Integrated Query. LINQ enables us to query any type of data store (SQL Server, XML documents. Objects in memory like list of customers or array etc). it could be entities or dataset
Why should we use LINQ
If the .NET application that is being developed
a) Requires data from SQL Server - Then the developer has to understand ADO.NET code and SQL specific to SQL Server Database
b) Requires data from an XML document - Then the developer has to understand XSLT & XPATH queries
c) Need to query objects in memory (List, List etc) - Then the developer has to understand how to work with objects in memory
LINQ enables us to work with these different data sources using a similar coding style without having the need to know the syntax specific to the data source. In our upcoming videos we will discuss querying different data sources using LINQ.
Another benefit of using LINQ is that it provides intellisense and compile time error checking.
LINQ Architecture & LINQ Providers
- LINQ query can be written using any .NET supported programming language
- LINQ provider is a component between the LINQ query and the actual data source, which converts the LINQ query into a format that the underlying data source can understand. For example LINQ to SQL provider converts a LINQ query to T-SQL that SQL Server database can understand.
we also have LINQ to SQL, LINQ to objects, LINQ to Entities
using LINQ to SQL
Step 1: Create a new empty asp.net web application and name it Demo
Step 2: Click on “View” menu item and select “Server Explorer”
Step 3: In “Server Explorer” window, right click on “Data Connections” and select “Add Connection” option
Step 4: Specify your SQL Server name and the credentials to connect to SQL Server. At this point we should be connected to SQL Server from Visual Studio.
Step 5: Adding LINQ to SQL Classes
a) Right click on the “Demo” project in solution explorer and select “Add New Item” option
b) In the “Add New Item” dialog box, select “Data” under “Installed Templates”
c) Select “LINQ to SQL Classes”
d) Set Name = Sample.dbml
e) Finally click “Add” button
Step 6: From “Server Explorer” window drag and drop “Students” table onto “Sample.dbml” designer file.
Step 7: Add a webform. Drag and drop a gridview control.
Step 8: Copy and paste the following code in the code-behind file
using System;
using System.Linq;
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SampleDataContext dataContext = new SampleDataContext();
GridView1.DataSource = from student in dataContext.Students
where student.Gender == “Male”
select student;
GridView1.DataBind();
}
}
}
Notice that, with LINQ we are getting intellisense. If we misspell the table or column names we will get to know about them at compile time. Open SQL Profiler. Run the application, and notice the SQL Query that is generated.
Query syntax
int[] numbers ={1,2,3,4,5,6,7,8,9};
Gridview1.Datasource = from number in numbers
where (number % 2) ==0
select number;
//from sql
Gridview1.Datasource = from student in datacontext.Students
where student.gender==”Male”
select student;
from varaibleName in dataset
where condition
select that record with VariableName;
different ways of writing LINQ Queries
There are 2 ways to write LINQ queries using these Standard Query Operators
1. Using Lambda Expressions. We discussed Lambda Expressions in detail in Part 99 of C# Tutorial
- Using SQL like query expressions
LINQ Standard Query Operators
To write LINQ queries we use the LINQ Standard Query Operators. The following are a few Examples of Standard Query Operators select from where orderby join groupby
query expressions are translated into their lambda expressions
From a performance perspective there is no difference between the two. Which one to use depends on your personal preference. But keep in mind, behind the scene, LINQ queries written using SQL like query expressions are translated into their lambda expressions before they are compiled.
The Standard Query Operators are implemented as extension methods on IEnumerable
The Standard Query Operators are implemented as extension methods on IEnumerable interface. We will discuss, what extension methods are and how to implement them in a later video session.
on all the types that have implemented IEnumerable, we should be able to use these linq standard query operators
arrays implement Ienumerable
Non-generic collection classes that are present in systems.collection namespace like arraylist, hash table implement Ienumerable
Generic collection classes like list, dictionary, implement IEnumerable
SQL like query expression
the query body must either end with select clause or a group clause
LINQ query using using SQL like query expressions
IEnumerable students = from student in Student.GetAllStudents()
where student.Gender == “Male”
select student;
To bind the results of this LINQ query to a GridView
GridView1.DataSource = students;
GridView1.DataBind();
lambda expression
LINQ query using Lambda Expressions.
IEnumerable students = Student.GetAllStudents()
.Where(student => student.Gender == “Male”);
Where is an extension method on IEnumerable, as list implement Ienumearble, we could use on it
where return IEnumerable
To bind the results of this LINQ query to a GridView
GridView1.DataSource = students;
GridView1.DataBind();
extension Methods in C#
string strName= “pragim”;
strName.ChangeFirstLetterCase();
options 1:
add this method to string class, but we do not own it, it belongs to .net framework
option 2:
why dont we create a base class, define the method there and inherit the string class. but that is not possible
as we do not own the string class
option 3:
Wrapper class/Helper
Wrapper class/Helper
public class StringHelper
{
public static string ChangeFirstLetterCase(string inputString)
{
if (inputString.Length > 0)
{
char[] charArray = inputString.ToCharArray();
charArray[0] = char.IsUpper(charArray[0]) ?
char.ToLower(charArray[0]) : char.ToUpper(charArray[0]);
return new string(charArray);
}
return inputString; }
}
Wrapper class works, but the problem is, we cannot call ChangeFirstLetterCase() method using the following syntax. string result = strName.ChangeFirstLetterCase();
Instead we have to call it as shown below.
string result = StringHelper.ChangeFirstLetterCase(strName);
instead of wrapper, we want to call the method as an instance method defined in the string class
Convert ChangeFirstLetterCase() method to an extension method to be able to call it using the following syntax, as though it belongs to string class. string result = strName.ChangeFirstLetterCase();
To convert ChangeFirstLetterCase() method to an extension method, make the following 2 changes
- Make StringHelper static class
- The type the method extends should be passed as a first parameter with this keyword preceeding it.
With these 2 changes, we should be able to call this extension method in the same way we call an instance method. Notice that the extension method shows up in the intellisense as well, but with a different visual clue.
string result = strName.ChangeFirstLetterCase();
Please note that, we should still be able to call this extension method using wrapper class style syntax. In fact, behind the scene this is how the method actually gets called. Extension methods are just a syntactic sugar. string result = StringHelper.ChangeFirstLetterCase(strName);
So, this means we should also be able to call LINQ extension methods (select, where etc), using wrapper class style syntax. Since all LINQ extension methods are defined in Enumerable class, the syntax will be as shown below.
List Numbers = new List { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
IEnumerable EvenNumbers = Enumerable.Where(Numbers, n => n % 2 == 0);
public static class StringHelper
{
public static string ChangeFirstLetterCase(this string inputString)
{
if (inputString.Length > 0)
{
char[] charArray = inputString.ToCharArray();
charArray[0] = char.IsUpper(charArray[0]) ?
char.ToLower(charArray[0]) : char.ToUpper(charArray[0]);
return new string(charArray);
}
return inputString; }
}
extension method symbol
string res= strName.theextensionMethod name is shown as downward arrow
