Transcript Introducing WCF

LINQ – Language Integrated Query
Gyan Deo Singh
Why LINQ?
•
It is very common for applications to use data in SQL databases or
XML documents. Traditionally, developers had to learn a primary
programming language, such as C#, and a secondary language,
such as SQL or XQuery. Language-Integrated Query (LINQ) brings
query capabilities into the C# language itself. Now, instead of
learning a separate query language, you can use your knowledge
of C#, together with some additional keywords and concepts, to
query SQL databases, ADO.NET datasets, XML documents, and
any .NET collection class that implements the IEnumerable
interface.
•
A developer wants to make a SQL query in an application requires
explicitly programming with API Classes like Sqlconnection,
Sqlcommand, Sqlreader etc. This process is time consuming and
requires extra lines of code. This could be now reduces using
LINQ.
Why LINQ?
Continue…
Normal way to write SQL query
using (SqlConnection connection = new SqlConnection("..."))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText =
@"SELECT Name, Country
FROM Customers
WHERE City = @City";
command.Parameters.AddWithValue("@City", "Paris");
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(0);
string country = reader.GetString(1);
…
}
}
}
|#1
|#1
|#1
|#1
|#2
|#3
|#3
Why LINQ?
Continue…
LINQ approach
// First we will declare instance of DataContext class (It is responsible for the translation
// of LINQ to T-SQL, and the mapping of the results (rows) of that query to objects. The
// DataContext can be equated to a database, in that it contains a series of tables and
// stored procedures and views.).
CustomersDataContext dbCust = new CustomersDataContext();
var query = from p in dbCust.Customers
where p.City == “paris”
select p.Name, p.Country;
foreach (var cust in query)
{
Response.Write(cust.Name);
Response.Write(cust.Country);
}
Why LINQ?
•
•
•
•
•
•
•
Continue…
LINQ has features targeting "Rapid Development" against a
Microsoft SQL Server database. Think of LINQ to SQL as allowing
you to have a strongly-typed view of your existing database
schema. LINQ to SQL supports a direct, 1:1 mapping of your
existing database schema to classes; a single table can be
mapped to a single inheritance hierarchy (i.e., a table can contain
persons, customers, and employees) and foreign keys can be
exposed as strongly-typed relationships.
Familiar syntax for writing queries.
Compile-time checking for syntax errors and type safety.
Improved debugger support and IntelliSense support.
In-memory XML document modification that is powerful, yet simpler
to use than XPath or XQuery.
Powerful filtering, ordering, and grouping capabilities.
Consistent model for working with data across various kinds of data
sources and formats.
What is LINQ (Language Integrated Query)?
•
LINQ is a language which allows to make query to the database,
XML and in-memory objects.
In other words we can say that LINQ is a common query
programming model and syntax that can be used across all types
of data (Databases, XML files and In-memory objects).
With LINQ, Microsoft’s intention is to provide a solution for the
problem of object-relational mapping, as well as simplify the
interaction between objects and data sources.
What Microsoft says about LINQ
“Microsoft original motivation behind LINQ was to address the
impedance mismatch between programming languages and
database.”
Three major LINQ Providers
LINQ Providers
LINQ To In-memory Objects
LINQ to XML
LINQ
LINQ To ADO.NET
LINQ To DataSet
LINQ To SQL
LINQ Architecture
New features in .NET 3.5 for LINQ
New features in .NET 3.5 for LINQ
Extension Methods
Anonymous Types
Standard Query Operators
Lambda Expressions
Query expressions
Implicitly typed local variables
Object Initialization Syntax
Collection Initialization Syntax
Extension Methods
Extension methods enable us to "add" methods to existing
types without creating a new derived type, recompiling, or
otherwise modifying the original type.
Extension methods are a special kind of static method, but
they are called as if they were instance methods on the
extended type. For client code written in C# and Visual
Basic, there is no apparent difference between calling an
extension method and the methods that are actually defined
in a type.
The most common extension methods are the
LINQ standard query operators.
Extension Methods continue…
Example:
public static class ExtensionMethodTest {
//Extension Method
public static bool IsvalidEmail(this string strEmail){
Regex regex = new Regex(@"^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$");
return regex.IsMatch(strEmail);
}
}
//How to use extension method.
String strEmailID = [email protected];
if (strEmail.IsvalidEmail())
Response.Write(“Valid Email ID”);
else
Response.Write("Invalid Email ID“);
Anonymous Types
Anonymous types provide a convenient way to encapsulate
a set of read-only properties into a single object without
having to first explicitly define a type. The type name is
generated by the compiler and is not available at the source
code level. The type of the properties is inferred by the
compiler. The following example shows an anonymous type
being initialized with two properties called Price and
Message.
var v = new { Price = 105, Message = “XYZ” };
Anonymous types are typically used in the select clause of a
query expression to return a subset of the properties from
each object in the source sequence. Anonymous types are
created by using the new operator with an object initializer.
Standard Query Operators
The methods that form the LINQ pattern. They can be
used to query any data source for which a LINQ
provider is available.
The Standard Query Operators is enables querying of
any .NET array or collection. The Standard Query
Operators consists of the extension methods.
The Standard Query Operators operate on sequences.
Any object that implements the interface
IEnumerable<T> for some type T is considered a
sequence of that type.
Standard Query Operators continue…
Types of Standard Query Operators:
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Restriction Operators - Where
Projection Operators - Select, SelectMany
Partitioning Operators - Skip, SkipWhile, Take, TakeWhile
Join Operators - Join, GroupJoin
Concatenation Operators - Concat
Ordering Operators - OrderBy, OrderByDescending, Reverse, etc.
Grouping Operators - GroupBy
Set Operators - Distinct, Except, Intersect, Union
Conversion Operators - Cast, OfType, ToArray, ToList, ToDictionary, etc.
Equality Operators - SequenceEqual
Element Operators - DefaultEmpty, First, Last, Single, ElementAt, etc.
Generation Operators - Empty, Range, Repeat
Aggregate Operators - Average, Count, Max, Min, Sum, etc.
Quantifiers – Any, All, Contains
Standard Query Operators continue…
Standard Query Operators continue…
Lambda Expressions
A lambda expression is an anonymous function that can contain
expressions and statements, and can be used to create delegates or
expression tree types. Lambda Expressions are super useful when writing
LINQ query expressions - since they provide a very compact and typesafe way to write functions that can be passed as arguments for
subsequent evaluation. All lambda expressions use the lambda operator
=>, which is read as "goes to". The left side of the lambda operator
specifies the input parameters (if any) and the right side holds the
expression or statement block.
Example: var maxAge = objPerson.Max(p => p.Age).ToString();
One of the things that make Lambda expressions particularly powerful
from a framework developer's perspective is that they can be compiled as
either a code delegate (in the form of an IL based method) or as an
expression tree object which can be used at runtime to analyze, transform
or optimize the expression.
Query expressions
A query expression is a query expressed in query syntax. A query
expression is a first-class language construct. It is just like any
other expression and can be used in any context in which a C#
expression is valid. A query expression consists of a set of clauses
written in a declarative syntax similar to SQL or XQuery. Each
clause in turn contains one or more C# expressions, and these
expressions may themselves be either a query expression or
contain a query expression.
A query expression must begin with a from clause and must end
with a select or group clause. Between the first from clause and
the last select or group clause, it can contain one or more of
these optional clauses: where, orderby, join and additional from
clauses. You can also use the into keyword to enable the result of
a join or group clause to serve as the source for additional query
clauses in the same query expression.
Query expressions continue…
Example:
// Query Expression.
IEnumerable<int> aQuery = from p in objPeople
//required
where p.Age > 20
// optional
orderby p.FirstName descending // optional
select p;
//must end with select or group
Implicitly typed local variables
C# 3.5 introduces a new var keyword that can be used in place of the type
name when performing local variable declarations. The var keyword always
generates a strongly typed variable reference. Rather than require the
developer to explicitly define the variable type, the var keyword instead tells
the compiler to infer the type of the variable from the expression used to
initialize the variable when it is first declared. The var keyword can be used
to reference any type in C#. Example:
var name = "ABC XYZ";
var age = 33;
var male = true;
The compiler will infer the type of the "name", "age" and "male" variables
based on the type of their initial assignment value. This means it will
generate IL that is absolutely identical to the code below:
string name = "ABC XYZ";
int age = 33;
bool male = true;
The CLR actually never knows that the var keyword is being used - from its
perspective there is absolutely no difference between the above two code
examples.
Object Initialization Syntax
We can use object initializers to initialize type objects in a declarative
manner without having to invoke the type's constructor.
Consider the following previous version code:
Person objPerson = new Person(“A”, ”X”, 33);
With the C# and VB compilers we can now take advantage of a great
"syntactic sugar" language feature called "object Initializers" that allows
us to-do this and re-write code as:
Person objPerson = new Person
{
FirstName=“A", LastName=“B“, Age=33
};
The compiler will then automatically generate the appropriate
property setter code that preserves the same semantic meaning as the
previous.
Object Initialization Syntax
Continues..
•
•
This approach will help the developer to initialize as
many as properties at the run time without declaring
multiple overloaded constructors.
This approach also helps in reducing the extra lines of
code.
Collection Initialization Syntax
The C# and VB compilers now support "collection initializers" that allows
us to avoid having multiple Add statements, and save even further
keystrokes.
Example:
List<Person> lstPeople = new List<Person> {
new Person { FirstName = “A", LastName = “X" },
new Person { FirstName = "B", LastName = “Y" },
new Person { FirstName = “C", LastName = “Z" }
};
When the compiler encounters the above syntax, it will automatically
generate the collection insert code like below:
List<Person> lstPeople = new List<Person>();
people.Add( new Person { FirstName = “A", LastName = “X”} );
people.Add( new Person { FirstName = "B", LastName = “Y" } );
people.Add( new Person { FirstName = “C", LastName = “Z" } );
Various provider of LINQ
•
Now we will discuss about various provider of LINQ
•
It will have three ways
•
•
•
LINQ with In-Memory Objects
LINQ with XML
LINQ with SQL.
LINQ To In-Memory Objects
LINQ To In-Memory Objects is used to write queries against in-memory
collections and other queryable sources in any .NET language using
LINQ.
Example:
Let us consider a class Person as below:
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age
{ get; set; }
}
LINQ To In-Memory Objects continue…
Now create and populate a collection of persons using Person class we
have defined in previous slide:
//Create a list of person
System.Collections.Generic.List<Person> lstPerson =
new System.Collections.Generic.List<Person>
{
new Person { FirstName = "A", LastName = "X", Age = 15 },
new Person { FirstName = "B", LastName = "Y", Age = 17 },
new Person { FirstName = "C", LastName = "Z", Age = 24 }
};
LINQ To In-Memory Objects continue…
We could use the standard "Where()" extension method provided by
System.Linq to retrieve a sequence of those "Person" objects within this
collection whose FirstName starts with the letter "A" as:
IEnumerable<Person> queryResult;
queryResult = lstPerson.Where(p => p.FirstName.StartsWith("A"));
Response.Write(queryResult.Count().ToString());
//Total result count
Response.Write(queryResult.First().FirstName);
//Display First Name
Response.Write(queryResult.First().LastName);
//Display Last Name
//Compute average age of person in list and display.
Response.Write(person.Average(p => p.Age).ToString());
//Compute max age of person in list and display.
Response.Write(person.Max(p => p.Age).ToString());
LINQ To XML
LINQ to XML is a built-in LINQ data provider that is implemented within
the "System.Xml.Linq" namespace in .NET 3.5.
LINQ to XML provides a clean programming model that enables us to
read, construct and write XML data. We can use LINQ to XML to perform
LINQ queries over XML that we retrieve from the file-system, from a
remote HTTP URL or web-service, or from any in-memory XML content.
LINQ to XML provides much richer (and easier) querying and data
shaping support than the low-level XmlReader/XmlWriter API in .NET
today. It also ends up being much more efficient (and uses much less
memory) than the DOM API that XmlDocument provides.
LINQ To XML
continue…
Example: Consider an XML file on disk that contains the data below:
<?xml version="1.0" encoding="utf-8" ?>
<people>
<person age="15">
<firstname>AAA</firstname> <lastname>XXX</lastname>
</person>
<person age="17">
<firstname>ABB</firstname> <lastname>YYY</lastname>
</person>
<person age="24">
<firstname>CCC</firstname> <lastname>ZZZ</lastname>
</person>
</people>
LINQ To XML
continue…
//Using LINQ Extension Methods against an XML File
XDocument people = XDocument.Load(@"C:\LINQToXML.xml");
//Casting to XElement
System.Collections.Generic.IEnumerable<XElement> xmlResult;
xmlResult = people.Descendants("person")
.Where(p=>p.Element("firstname").Value.StartsWith("A"));
//Total count of records.
txtResultCount.Text = xmlResult.Count().ToString();
//Person First Name.
txtPersonFirstName.Text = xmlResult.First().FirstNode;
//Person Last Name.
txtPersonLastName.text = xmlResult.First().LastNode;
txtAvgAge.Text = people.Descendants("person").Average(p =>
Convert.ToInt32(p.Attribute("age").Value));
LINQ To ADO.NET
LINQ to ADO.NET consists of two separate technologies:
• LINQ to DataSet and
• LINQ to SQL
LINQ to DataSet provides richer, optimized querying over the
DataSet and LINQ to SQL enables us to directly query SQL
Server database schemas.
Transferring data from SQL tables into objects in memory is often
tedious and error-prone. The LINQ provider implemented by
LINQ to DataSet and LINQ to SQL converts the source data into
IEnumerable-based object collections. The programmer always
views the data as an IEnumerable collection, both when you
query and when you update. Full IntelliSense support is
provided for writing queries against those collections.
LINQ To DataSet
The DataSet is a key element of the disconnected programming model that
ADO.NET is built on, and is widely used. LINQ to DataSet enables
developers to build richer query capabilities into DataSet by using the
same query formulation mechanism that is available for many other
data sources.
LINQ to DataSet makes it easier and faster to query over data cached in a
DataSet object. Specifically, LINQ to DataSet simplifies querying by
enabling developers to write queries from the programming language
itself, instead of by using a separate query language. This is especially
useful for Visual Studio developers, who can now take advantage of the
compile-time syntax checking, static typing, and IntelliSense support
provided by the Visual Studio in their queries.
LINQ to DataSet can also be used to query over data that has been
consolidated from one or more data sources. This enables many
scenarios that require flexibility in how data is represented and handled,
such as querying locally aggregated data and middle-tier caching in
Web applications. In particular, generic reporting, analysis, and
business intelligence applications require this method of manipulation.
LINQ To DataSet
continue…
The LINQ to DataSet functionality is exposed primarily through the
extension methods in the DataRowExtensions and
DataTableExtensions classes. LINQ to DataSet builds on and uses the
existing ADO.NET 2.0 architecture, and is not meant to replace
ADO.NET 2.0 in application code. Existing ADO.NET 2.0 code will
continue to function in a LINQ to DataSet application. The relationship
of LINQ to DataSet to ADO.NET 2.0 and the data store is illustrated in
the following diagram:
LINQ To DataSet
continue…
The DataSet is one of the more widely used components of ADO.NET.
It is a key element of the disconnected programming model that
ADO.NET is based on, and it enables you to explicitly cache data from
different data sources. A common technique used to lower the number
of requests on a database is to use the DataSet for caching in the
middle-tier. For example, consider a data-driven ASP.NET Web
application. Often, a significant portion of the application data does not
change frequently and is common across sessions or users. This data
can be kept in memory on the Web server, which reduces the number of
requests against the database and speeds up the user’s interactions.
But at the same time DataSet has limited query capabilities. The Select
method can be used for filtering and sorting, and the GetChildRows and
GetParentRow methods can be used for hierarchy navigation. For
anything more complex, however, the developer must write a custom
query. This can result in applications that perform poorly and are difficult
to maintain.
LINQ To DataSet
continue…
LINQ to DataSet makes it easier and faster to query over data cached in a
DataSet object. These queries are expressed in the programming
language itself, rather than as string literals embedded in the application
code. This means that developers do not have to learn a separate query
language. Additionally, LINQ to DataSet enables Visual Studio
developers to work more productively, because the Visual Studio IDE
provides compile-time syntax checking, static typing, and IntelliSense
support for LINQ. LINQ to DataSet can also be used to query over data
that has been consolidated from one or more data sources. This
enables many scenarios that require flexibility in how data is
represented and handled.
Querying DataSets Using LINQ to DataSet
Before querying a DataSet object using LINQ to DataSet, must populate
the DataSet. Formulating queries using LINQ to DataSet is similar to
using Language-Integrated Query (LINQ) against other LINQ-enabled
data sources. LINQ queries can be performed against single tables in a
DataSet or against more than one table by using the Join and
GroupJoin standard query operators.
LINQ To DataSet
continue…
LINQ queries are supported against both typed and untyped DataSet
objects. If the schema of the DataSet is known at application design
time, a typed DataSet is recommended. In a typed DataSet, the tables
and rows have typed members for each of the columns, which makes
queries simpler and more readable.
In addition to the standard query operators implemented in
System.Core.dll, LINQ to DataSet adds several DataSet-specific
extensions that make it easier to query over a set of DataRow objects.
These DataSet-specific extensions include operators for comparing
sequences of rows, as well as methods that provide access to the
column values of a DataRow.
Data sources that implement the IEnumerable<(Of <(T>)>) generic
interface can be queried through LINQ. Calling AsEnumerable on a
DataTable returns an object which implements the generic
IEnumerable<(Of <(T>)>) interface, which serves as the data source for
LINQ to DataSet queries. LINQ to DataSet queries can be formulated in
two different syntaxes: query expression syntax and method-based
query syntax.
LINQ To DataSet
continue…
Query Expression Syntax
Query expressions are a declarative query syntax. This syntax enables
a developer to write queries in C# or Visual Basic in a format similar to
SQL. By using query expression syntax, you can perform even complex
filtering, ordering, and grouping operations on data sources with
minimal code. The .NET Framework common language runtime (CLR)
cannot read the query expression syntax itself. Therefore, at compile
time, query expressions are translated to something that the CLR does
understand: method calls. These methods are referred to as the
standard query operators. Example:
DataSet ds = new DataSet();
FillTheDataSet(ds);
//Fill the DataSet.
DataTable dtPeople = ds.Tables["People"];
IEnumerable<DataRow> query = from people In
dtPeople.AsEnumerable() select people;
foreach (DataRow p in query)
Response.Write(p.Field<string>(“FirstName"));
LINQ To DataSet
continue…
Method-Based Query Syntax
The other way to formulate LINQ to DataSet queries is by using methodbased queries. The method-based query syntax is a sequence of direct
method calls to LINQ operator methods, passing lambda expressions
as the parameters. Example:
DataSet ds = new DataSet();
FillTheDataSet(ds);
//Fill the DataSet.
DataTable dtPeople = ds.Tables["People"];
var query = dtPeople.AsEnumerable().
Select(people => new {
FirstName = people.Field<string>(“FirstName"),
LastName = people.Field<string>(" LastName"),
Age = people.Field<int>(“Age")
};
foreach (var personInfo in query)
{
Response.Write(personInfo.FirstName );
}
LINQ To SQL
LINQ to SQL is an O/RM (object relational mapping) implementation that
ships in the .NET Framework 3.5 release, and which allows us to model a
relational database using .NET classes. We can then query the database
using LINQ, as well as update/insert/delete data from it. LINQ to SQL fully
supports transactions, views, and stored procedures. It also provides an
easy way to integrate data validation and business logic rules into your
data model.
In LINQ to SQL, the data model of a relational database is mapped to an
object model expressed in the programming language of the developer.
When the application runs, LINQ to SQL translates into SQL the
language-integrated queries in the object model and sends them to the
database for execution. When the database returns the results, LINQ to
SQL translates them back to objects that you can work with in your own
programming language.
LINQ To SQL
•
•
•
•
•
•
continue…
ORM
Entity Classes
Creating Entity Classes
DataContext Class
Submitting Queries to Relational Databases
Invoking Stored Procedures
LINQ To SQL
continue…
ORM
•
Object-Relational Mapping or Object Role Modeling (ORM, O/RM,
and O/R mapping) is a programming technique for converting data
between incompatible type systems in relational databases and
object-oriented programming languages. This creates a "virtual
object database" which can be used from within the programming
language.
•
ORM is a method for designing and querying database models at
the conceptual level, where the application is described in terms
readily understood by users, rather than being recast in terms of
implementation data structures.
•
Object role modeling is a conceptual database design methodology
that allows the user to express information as an object and explore
how it relates to other information objects.
LINQ To SQL
continue…
Role of Entity Classes
LINQ to SQL enable us to model classes that map to/from a
database. These classes are referred to as "Entity Classes" and
instances of them are called "Entities". Entity classes map to tables
within a database. The properties of entity classes map to the table's
columns. Each instance of an entity class then represents a row within
the database table.
Entity classes defined with LINQ to SQL do not have to derive from a
specific base class, which means that we can have them inherit from
any object we want. All classes created using the LINQ to SQL
designer are defined as "partial classes" - which means we can
optionally drop into code and add additional properties, methods and
events to them. Unlike the DataSet/DataAdapter feature provided in VS
2005, when using the LINQ to SQL designer we do not have to specify
the SQL queries to use when creating our data model and access
layer.
The LINQ to SQL OR/M implementation will then take care of
generating the appropriate SQL execution logic for us at runtime when
we interact and use the data entities.
LINQ To SQL
continue…
Creating Entity Classes
In LINQ to SQL, a database table is represented by an entity class.
There are three tools available in Visual Studio 2008 to create
object model:
• The Object Relational Designer
• The SQLMetal code-generation tool
• A code editor
• SQLMetal.exe – a command line tool that gives a tonne of
options when creating DAL. Things of note are pluralisation of
entities, extracting stored procedures from database schema.
We will use LINQ to SQL classes by using the Visual Studio
2008 designer tool. This feature is simple to use.
LINQ To SQL
continue…
Object Relational Designer Tool
This designer provides a rich user interface for creating an object model
from an existing database. This tool is part of the Visual Studio IDE.
LINQ to SQL Class –This is a file template that lives in Visual Studio
2008. We can create and edit LINQ to SQL classes by using the Visual
Studio 2008 designer tool.
To add a LINQ to SQL Class file to a project:
• From within a C# or VB application, on the Project menu, click Add
New Item.
• Click the LINQ to SQL Classes template.
• Either provide an alternative name or keep the default name of
DataClasses1.dbml.
• Click Add.
LINQ To SQL
continue…
Visual Studio 2008 designer to create LINQ to SQL Class
LINQ To SQL
continue…
LINQ to SQL Class
The .dbml file is added to the project and the O/R Designer opens. The
name provided will be the name of the generated DataContext. E.g.,
using the default name will cause the designer to name the
DataContextDataClasses1DataContext.
After you add a LINQ to SQL file to your project, the empty design
surface represents a DataContext ready to be configured. Drag
database items from Server Explorer/Database Explorer onto the O/R
Designer to create data classes and DataContext methods. The data
connection of a DataContext is created based on the first item added to
the designer form Server Explorer/Database Explorer.
The created DataContext class is derived from
System.Data.Linq.DataContext.
LINQ To SQL
continue…
ORM Designer with a table “People”
LINQ To SQL
continue…
SQLMetal code-generation tool
The SqlMetal command-line tool generates code and mapping for the
LINQ to SQL component of the .NET Framework. We can instruct
SqlMetal to perform several different actions that include the following:
• From a database, generate source code and mapping attributes or a
mapping file.
• From a database, generate an intermediate database markup
language (.dbml) file for customization.
• From a .dbml file, generate code and mapping attributes or a
mapping file.
By default, the SQLMetal file is located at drive:\Program Files\Microsoft
SDKs\Windows\vn.nn\bin
Syntax: sqlmetal [options] [<input file>]
SqlMetal functionality actually involves two steps:
• Extracting the metadata of the database into a .dbml file.
• Generating a code output file.
LINQ To SQL
continue…
SQLMetal code-generation tool
By using the appropriate command-line options, you can produce Visual
Basic or C# source code, or you can produce an XML mapping file.
To extract the metadata from an .mdf file, you must specify the name of
the .mdf file after all other options.
If no /server is specified, localhost/sqlexpress is assumed.
Microsoft SQL Server 2005 throws an exception if one or more of the
following conditions are true:
• SqlMetal tries to extract a stored procedure that calls itself.
• The nesting level of a stored procedure, function, or view exceeds 32.
SqlMetal catches this exception and reports it as a warning.
To specify an input file name, add the name to the command line as the
input file. Including the file name in the connection string (using the /conn
option) is not supported.
LINQ To SQL
continue…
SQLMetal code-generation tool Example:
•
•
•
•
•
Generate a .dbml file that includes extracted SQL metadata:
sqlmetal /server:myserver /database:northwind /dbml:mymeta.dbml
Generate a .dbml file that includes extracted SQL metadata from an .mdf
file by using SQL Server Express:
sqlmetal /dbml:mymeta.dbml mydbfile.mdf
Generate a .dbml file that includes extracted SQL metadata from SQL
Server Express:
sqlmetal /server:.\sqlexpress /dbml:mymeta.dbml /database:People
Generate source code from a .dbml metadata file:
sqlmetal /namespace:people /code:people.cs /language:csharp
mymetal.dbml
Generate source code from SQL metadata directly:
sqlmetal /server:myserver /database:People /namespace:pple
/code:pple.cs /language:csharp
LINQ To SQL
continue…
Code Editor
You can write your own code by using either the Visual Studio code
editor or another editor. We do not recommend this approach, which can
be prone to errors, when you have an existing database and can use
either the O/R Designer or the SQLMetal tool. However, the code editor
can be valuable for refining or modifying code you have already
generated by using other tools.
LINQ To SQL
continue…
DataContext Class
DataContext class represents the main entry point for the LINQ to SQL
framework. The DataContext class is the most important class when
using LINQ to SQL. The DataContext is the source of all entities
mapped over a database connection. It tracks changes that you made
to all retrieved entities and maintains an "identity cache" that
guarantees that entities retrieved more than one time are represented
by using the same object instance.
The “LINQ to SQL Class” file allow us to model classes that represent a
relational database. It will also create a strongly-typed "DataContext"
class that will have properties that represent each Table we modeled
within the database, as well as methods for each Stored Procedure we
modeled. DataContext class is the main conduit by which we'll query
entities from the database as well as apply changes back to it.
The created DataContext class is derived from
System.Data.Linq.DataContext.
LINQ To SQL
continue…
Submitting Queries to Relational Databases using LINQ To SQL
LINQ to SQL supports all the key capabilities we would expect as
a SQL developer.
Once we've modeled our database using the LINQ to SQL
designer, we can then easily write code to work against it.
We can query for
Selecting information,
Insert,
Update,
and Delete information from tables.
LINQ To SQL
continue…
Submitting Queries to Relational Databases using LINQ To SQL
Select Query:
Selecting rows from table is achieved by just writing a LINQ query in our
own programming language, and then executing that query to retrieve
the results. LINQ to SQL itself translates all the necessary operations
into the necessary SQL operations. Example:
PersonDataClassesDataContext dbPeople = new
PersonDataClassesDataContext();
var query = from p in dbPeople.Peoples
where p.Age > 18
select p;
foreach (var ppl in query)
{
Response.Write(ppl.FirstName);
}
LINQ To SQL
continue…
Submitting Queries to Relational Databases using LINQ To SQL
cont…
Insert Query: To execute a SQL Insert, just add objects to the object model
we have created, and call SubmitChanges on the created DataContext.
PersonDataClassesDataContext dbPeople = new
PersonDataClassesDataContext();
People objPeople = new People();
objPeople.FirstName = "Gyan";
objPeople.LastName = "Singh";
objPeople.Age = 33;
dbPeople.Peoples.InsertOnSubmit(objPeople);
// At this point, the new People object is added in the object model.
// In LINQ to SQL, the change is not sent to the database until
SubmitChanges is called.
dbPeople.SubmitChanges();
LINQ To SQL
continue…
Submitting Queries to Relational Databases using LINQ To SQL
cont…
Update Query: To Update a database entry, first retrieve the item and edit it
directly in the object model. After you have modified the object, call
SubmitChanges on the DataContext to update the database. Example:
PersonDataClassesDataContext dbPeople = new
PersonDataClassesDataContext();
var query = from p in dbPeople.Peoples
select p;
var intAge = 18;
foreach (var ppl in query)
{
ppl.Age = intAge;
intAge++;
}
dbPeople.SubmitChanges();
LINQ To SQL
continue…
Submitting Queries to Relational Databases using LINQ To SQL
cont…
Delete Query: To Delete an item, remove the item from the collection to
which it belongs, and then call SubmitChanges on the DataContext to
commit the change. Example: the person whose PeronID is 1 retrieved
from the database. Then, after confirming that the people row was
retrieved, DeleteOnSubmit is called to remove that object from the
collection. Finally, SubmitChanges is called to forward the deletion to
the database.
PersonDataClassesDataContext dbPeople = new
PersonDataClassesDataContext();
var query = from p in dbPeople.Peoples
where p.PersonID == 1
select p;
if (query.Count() > 0) {
dbPeople.Peoples.DeleteOnSubmit(query.First());
dbPeople.SubmitChanges();
}
More About LINQ
Language-Integrated Query (LINQ) defines a set of general
purpose standard query operators that you can use in .NET
Framework 3.5 programming languages. These standard query
operators enable us to select, filter, and traverse in-memory
collections or tables in a database. Note that the LINQ queries
are expressed in the programming language itself, and not as
string literals embedded in the application code. This is a
significant change from the way most applications have been
written on earlier versions of the .NET Framework. Writing
queries from within our programming language offers several key
advantages. It simplifies querying by eliminating the need to use
a separate query language. And if we use the Visual Studio 2008
IDE, LINQ also lets us take advantage of compile-time checking,
static typing, and IntelliSense.
More About LINQ
continue…
Language-Integrated Query (LINQ) enables developers to form
set-based queries in their application code, without having to use
a separate query language. We can write LINQ queries against
various enumerable data sources (that is, a data source that
implements the IEnumerable interface), such as in-memory data
structures, XML documents, SQL databases, and DataSet
objects. Although these enumerable data sources are
implemented in various ways, they all expose the same syntax
and language constructs. Because queries can be formed in the
programming language itself, we do not have to use another
query language that is embedded as string literals that cannot be
understood or verified by the compiler. Integrating queries into
the programming language also enables Visual Studio
programmers to be more productive by providing compile-time
type and syntax checking, and IntelliSense. These features
reduce the need for query debugging and error fixing.
Advantages of LINQ
•
Simplicity
Simplify data access code and enhance the maintainability of the
applications using LINQ. Express traversal, filter, and projection
operations declaratively using any .NET-based programming language
with LINQ’s set of general purpose query operators.
• Query Data in any .NET Programming Language: Write queries by
using LINQ in the programming language of your choice instead of
learning the SQL dialect native to your data source.
var customers = from c in Peoples
where c.FirstName == “Gyan“
select c;
• Use Strongly-typed Objects in Your Data Access Code: Query
against strongly typed data objects easily. Write data access code in
the same object oriented manor used for the rest of your application.
Advantages of LINQ
•
continue…
Productivity
Increase productivity and reduce runtime errors in the applications by
using strongly-typed objects instead of embedded SQL query syntax.
• Optimize Development Efforts: You can become more productive
and optimize the overall application development effort by using a
single consistent query language for all aspects of the application.
• Reduce Bugs and Errors: You can work with strongly typed CLR
objects that reduce runtime errors in applications. Identify queryrelated coding errors at compile time and reduce the debugging
effort by using strongly typed variables.
• Be More Productive with Microsoft Visual Studio: Developers can
maximize their efficiency when writing code that includes stronglytyped data objects and take full advantage of the productivity
enhancing capabilities of Visual Studio, such as the object browser
and IntelliSense.
Advantages of LINQ
•
continue…
Flexibility
Access data in a wide range of data stores while using consistent LINQ
syntax. Easily adapt to the needs of a particular application scenario by
taking advantage of several data source-specific implementations of
LINQ to query various types of data.
•
Use LINQ with any Data Source: You can use whichever
implementation of LINQ is designed for your scenario:
• LINQ to SQL for objects mapped directly to Microsoft SQL Server
database schemas.
• LINQ to XML for XML data.
• LINQ to In-memory Objects for class objects.
• LINQ to DataSet to work with existing DataSet functionality.