Week 09 LINQ - • LINQLINQ (Language-Integrated Query) is the query language that is tightly LINQ (Language-Integrated Query) is the query language that is tightly PDF

Preview

Summary

• LINQ
LINQ (Language-Integrated Query) is the query language that is tightly integrated with programming languages used in the .NET Framework.
LINQ comes in a few different implementations, enabling you to access and query a wide variety of sources, including in-memory data, XML files, ...

Description

•

LINQ

LINQ (Language-Integrated Query) is the query language that is tightly integrated with programming languages used in the .NET Framework. LINQ comes in a few different implementations, enabling you to access and query a wide variety of sources, including in-memory data, XML files, .NET DataSets, and databases. •

LINQ

To give you an idea of what a LINQ query looks like, here’s a quick example that shows a list of authors whose names contain the capital letter S: •

LINQ

Given an array of strings, containing author names, you can simply select all the authors whose names contain the capital letter S and order them in ascending order. Please see LINQtoInMemory.aspx •

LINQ

Given an array of strings, containing author names, you can simply select all the authors whose names contain the capital letter S and order them in ascending order. Please see LINQtoInMemory.aspx •

LINQ

Because LINQ is so powerful and has so much potential, it has been integrated into many different areas of the .NET Framework. The following list lists the different LINQ implementations: •

LINQ to Objects: With LINQ to Objects, you can query collections (such as arrays) in your .NET applications as you saw in the previous example.

•

LINQ to XML: LINQ to XML is the new .NET way to read and write XML. Instead of typical XML query languages like XSLT or Xpath, you can now write LINQ queries that target XML directly in your application.

•

LINQ to ADO.NET: With LINQ to ADO.NET, you can query database-related information sets, including LINQ to DataSet, LINQ to SQL, and LINQ to Entities.

•

LINQ to ADO.NET

ADO.NET is the part of the .NET Framework that enables you to access data and data services like SQL Server and many other different kinds of data sources. LINQ to ADO.NET includes •

LINQ to DataSet

•

LINQ to SQL

•

LINQ to Entities

LINQ to DataSet enables you to write queries against the DataSet, a class that represents an inmemory version of a database.

Please see example. •

LINQ to DataSet

LINQ to DataSet enables you to write queries against the DataSet, a class that represents an inmemory version of a database. •

LINQ to SQL

LINQ to SQL enables you to write object-oriented queries in your .NET projects that target Microsoft SQL Server database. The LINQ to SQL implementation translates your queries into SQL statements, which are then sent to the database to perform typical CRUD operations. However, Microsoft has indicated that it will no longer actively develop LINQ to SQL. The reason for this is the great overlap in functionality with the Entity Framework （EF). Almost anything you can do in LINQ to SQL can be done in LINQ to Entities. •

LINQ to Entities

With EF (Entity Framework), you can take a bunch of database objects like tables and turn them into .NET objects that you can access in your code. You can then use these objects in queries or use them directly in data-binding scenarios. EF also enables you to do the reverse: design an object model first and then let EF create the necessary database structure for you. •

Mapping Your Data Model to An Object Model

Assume that you have a database named Week9DB.mdf Step1: Add an ADO.NET Entity Data Model named Week9DBEntities.edmx as shown below: •

Mapping Your Data Model to An Object Model

Step 2: On the dialog box that follows, make sure that Generate from Database is selected and click Next •

Mapping Your Data Model to An Object Model

Step 3: In the Choose Your Data Connection step, make sure Week9DB.mdf is selected in the dropdown and that the check box to store the settings in Web.config is checked. •

Mapping Your Data Model to An Object Model

Step 4: Choose Entity Framework 6.0 Step 5: On the Choose Your Database Objects and Settings dialog, check Tables as below: •

Mapping Your Data Model to An Object Model

Step 6: Click Finish Visual Studio adds a file called Week9DBEntities.edmx and two files with a .tt extension (Week9DBEntities.Context.tt & Week9DBEntities.tt)

•

Mapping Your Data Model to An Object Model

Opens the Entity Designer by double clicking Week9DBEntities.edmx as shown below: •

Mapping Your Data Model to An Object Model

Visual Studio also installed the EntityFramework package, which reslted in an EF assembly (a .dff file) and XML documentation file in the Bin folder. Two classes (Member and Vehicle) are generated based on the tables in Week9DB. Also, a class called Week9DBEntities is generated which is used to access the data in Week9DB. •

Mapping Your Data Model to An Object Model

AccessDatainDB1.aspx shows an example of how to list members’ name by a Label control. •

Mapping Your Data Model to An Object Model

AccessDatainDB2.aspx shows an example of how to insert a new member into the database •

Mapping Your Data Model to An Object Model

AccessDatainDB2.aspx shows an example of how to insert a new member into the database •

Query Syntax

The query you saw in the previous example is quite simple. The querying capabilities of LINQ are much more powerful than this. LINQ supports a large number of query operators – keywords that enable you to select, order, or filter data that is to be returned from the query. •

Query Syntax

SELECT The Select keyword is used to retrieve objects from the source you are querying. var query = from m in db.Member select m; The m variable in this example is referred to as a range variable that is only available within the current query. You typically introduce the range variable in the From clause, and then use it again in the Where and Select clauses to filter the data and to indicate the data you want to select. You can choose any name you like. •

Query Syntax

FROM The From clause defines the collection or data source that the query must act upon. var query = from m in db.Member select m; The From clause indicates that the query must be executed against the Member collection that is exposed by the db object in EF. •

Query Syntax

ORDER BY With Order By, you can sort the items in the result collection. For example: var query = from v in db.Vehicle orderby v.Brand descending select v; •

Query Syntax

WHERE Just like the Where clause in SQL, the Where clause in LINQ enables you to filter the objects returned by the query. var query = from m in db.Member where m.Name == “Peter” select m; •

Query Syntax

SUM, MIN, MAX, AVERAGE, and COUNT These aggregation operators enable you to perform mathematical calculations on the objects in the result set. For example, to retrieve the number of reviews, you can execute this query: var numberOfMembers = (from m in db.Member select m).Count(); •

Query Syntax

FIRST, FIRSTORDEFAULT, LAST, and LASTORDEFAULT These operators enable you to return the first or the last element in a specific sequence of objects. First and Last throw an error when the collection is empty, whereas the other two operators return the default value for the relevant data types. The following code snippet shows how to retrieve the most recent member from the database: var query = (from m in db.Member orderby m.Id descending).FirstOrDefault();...