Transcript Chpt14Lecture

Chapter 14
LINQ and the ADO.NET
Entity Framework
Objectives
•
What LINQ is and what its syntax looks like
•
The different forms of LINQ that are available and when they are
appropriate to use
•
How to use the ADO.NET Entity Framework
•
How to use Model Binding to access the ADO.NET Entity Framework
•
How to use the ListView and DataPager controls
LINQ
•
LINQ is the Language-Integrated Query is the query language tightly
integrated with the programming languages in the .NET Framework.
•
LINQ syntax has been modeled partially after the SQL language, making it
easier for programmers familiar with SQL to get started with LINQ.
•
Can Access in-memory data, XML, .NET Data Sets, and databases.
•
Modelled after SQL
LINQ Example
•
Retrieve all Authors in an array
using System.Linq;
...
string[] authors = new string[] { "Hanselman, Scott", "Evjen, Bill",
"Haack, Phil", "Vieira, Robert", "Spaanjaars, Imar" };
var result = from author in authors
where author.Contains("S")
orderby author
select author;
foreach (var author in result)
{
Label1.Text += author + "<br />";
}
LINQ in Application
•
LINQ to Objects
•
•
LINQ to XML
•
•
This is the purest form of language integration. With LINQ to Objects, you can
query collections in your .NET applications.
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
•
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.
With LINQ to ADO.NET you can query database-related information sets,
including LINQ to DataSet, LINQ to SQL, and LINQ to Entities.
ADO.NET Entity Framework
•
Entity Framework is an Object Relational Mapper (ORM) that can take
database tables and turn them into .NET Objects .
•
EF also enables you to do the reverse: design an object model first and then
let EF create the necessary database structure for you.
•
When you drop more than one related database table on your diagram, the
designer detects the relationships between the tables and then replicates
these relationships in your object model.
•
EF creates a layer between your .NET application and your SQL Server
database. The Entity Designer takes care of most of the work for you,
providing access to a clean object model that you can use in your
application.
ADO.NET Entity Framework
•
Right-click the App_Code folder, choose Add ➪ Add New Item, and select
your programming language on the left. Then click ADO.NET Entity Data
Model.
•
Make sure the tool is generating from database and choose a connection
string.
•
The tables will then be available to you from your database. Select the
tables that you wish to bring into the project as Objects.
ADO.NET Entity Framework
•
Once the mapping is done Visual Studio adds an .edmx file and files with a
.tt extension.
•
This Entity Designer shows you .NET classes that have been generated
based on the tables in your database. VS draws a line connecting any tables
that have a relationship.
ADO.NET Entity Framework
•
Once this is done you drag a Gridview into a page and add the following
code:
protected void Page_Load(object sender, EventArgs e)
{
using (PlanetWroxEntities myEntities = new PlanetWroxEntities())
{
var authorizedReviews = from review in myEntities.Reviews
where review.Authorized == true
orderby review.CreateDateTime descending
select review;
GridView1.DataSource = authorizedReviews.ToList();
GridView1.DataBind();
}
}
ADO.NET Entity Framework
•
Under the hood, the run time converts this LINQ query into its SQL
counterpart and executes it against the underlying database.
•
The classes that are added to the designer are stored in the .edmx file and
its Code Behind files.
•
The .tt files — which are T4 Template Files used to generate code
dynamically — look at the data in the .edmx file and generate classes on
the fly.
•
The model defines the main object types which also have collection
counterparts which are referred to as the entity sets (the names are in
plural).
•
EF uses a mechanism called lazy loading, which means sub objects are not
loaded until you explicitly tell them to.
ADO.NET Entity Framework
•
By default subobjects (the other table objects joined by a FK relationship)
are loaded as null.
•
You can use the Include keyword to load the subject.
•
For example, suppose we want to the “Genre” object when obtaining
reviews:
var authorizedReviews = from review in
myEntities.Reviews.Include("Genre")
where review.Authorized == true
ADO.NET Entity Framework
•
Because the code didn’t state the type for authorizedReviews (the example
used Dim or var instead), .NET needs a different solution to determine the
type.
•
This is done by a concept called type inference, where the compiler is able
to infer the type for a variable by looking at the right side of the
assignment.
•
In this case, the compiler sees that a list of Review objects will be returned
from the query, and correctly types the authorizedReviews variable as a
generics type IQueryable<Review> in C#.
•
DataBind() on the GridView you instruct the control to display the individual
Review objects on the page.
Query Syntax and Operators
•
This is a syntax that can be used to obtain objects from your object model
collection.
•
Very similar to SQL syntax.
•
SELECT operator
•
•
FROM operator
•
•
used to retrieve objects from the source you are querying. The r variable in this
example is referred to as a range variable that is only available within the current
query. You can use any name for the range variable.
defines the collection or data source that the query must act upon.
Example:
var allReviews = from r in myEntities.Reviews
select r;
Query Syntax and Operators
•
ORDER BY Operator:
you can sort the items in the result collection. Order By is followed by an optional
Ascending or Descending
•
•
Example:
var allGenres = from g in myEntities.Genres
orderby g.SortOrder descending, g.Name
select g;
•
WHERE Operator:
•
Filters the collection of objects by a certain criteria.
var authorizedReviews = from r in myEntities.Reviews
where r.Authorized == true
select r;
Query Syntax and Operators
•
TAKE, SKIP, TAKEWHILE, SKIPWHILE Operators:
•
Take gets the requested number of elements from the result set and
then ignores the rest, whereas Skip ignores the requested number of
elements and then returns the rest. Should be used with ORDER BY.
•
Translated into SQL Statements so efficient paging is done at the
database query (only loads the data that it needs).
•
TakeWhile and SkipWhile are LINQ operators but do not work with EF.
var someReviews = (from r in myEntities.Reviews
orderby r.Title
select r).Skip(10).Take(10);
Query Syntax and Operators
•
SINGLE, SINGLEORDEFAULT Operators:
•
Return a single Object in a strongly typed instance. Single operator throws an exception if
item not found or more than one instance.
•
Use SingleorDefault to default to null or default value for the relevant type.
var review37 = (from r in myEntities.Reviews
where r.Id == 37
select r).Single();
•
FIRST, FIRSTORDEFAULT, LAST, LASTORDEFAULT Operators:
•
These operators enable you to return the first or the last element in a specific sequence of
objects. Last, Lastordefault not supported in EF, use Order By with First.
var firstReview = (from r in myEntities.Reviews
orderby r.Id
select r).First();
var lastReview = (from r in myEntities.Reviews
orderby r.Id descending
select r).First();
Shaping Data with Anonymous Data Types
•
Queries return full data types, not just partial data from the object.
•
An anonymous type is a type whose name and members you don’t define
up front as you do with other types. You construct the anonymous type by
selecting data and then letting the compiler infer the type for you using the
new keyword.
•
The anonymous type can only be accessed within the method that declared
it, and as such you cannot return an anonymous type from a method.
var authorizedReviews = from review in myEntities.Reviews
where review.Authorized == true
select new { review.Id, review.Title, review.Genre.Name };
Shaping Data with Anonymous Data Types
•
Since the type is inferred at runtime, use the var c# keyword to access
them.
•
Intellisense is supported. Example foreach loop:
Shaping Data with Anonymous Data Types
•
Renaming is supported when creating anonymous data types.
var allReviews = from myReview in myEntities.Reviews
select new
{
Number = myReview.Id,
Title = myReview.Title.Substring(0, 20),
myReview.Genre.Name,
HasBeenUpdated = (myReview.UpdateDateTime >
myReview.CreateDateTime)
};
The Repeater Control
•
The Repeater Controls follows the same principle as other controls, it uses
the <ItemTemplate> to define the markup for each item you want to be
repeated.
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<h3><asp:Literal ID="Literal1" runat="server"
Text='<%# Eval("Name") %>'></asp:Literal></h3>
<!-- BulletedList here -->
</ItemTemplate>
</asp:Repeater>
<asp:BulletedList ID="BulletedList1" runat="server" DisplayMode="Text"
DataSource='<%# Eval("Reviews")%>' DataTextField="Title" />
The Repeater Control
After you have set up the Repeater and defined the query,
you need to start the data-binding process. You do this by
assigning the results of the query to the DataSource
property of the Repeater.
•
Repeater1.DataSource = allGenres.ToList();
Repeater1.DataBind();
The Repeater Control
Model Binding
•
Model Binding is a capability built into the data-bound controls to execute
CRUD operations against a data source.
•
Model Binding relies on methods that you can define in your code to supply
the data and handle changes such as an insert, an update, or a delete
operation. These methods can be defined in the Code Behind of the Web
Forms that contains the data-bound controls, or in other classes in your
project.
•
A very powerful feature of Model Binding is the ability to dynamically fill
objects with data coming from the data-bound control when the insert,
update, or delete methods are fired.
Model Binding
•
Model Binding methods
Model Binding
•
If you create a DetailsView control, you would have the following markup
<asp:DetailsView AutoGenerateRows="false" ID="DetailsView1"
DefaultMode="Insert" runat="server">
<Fields>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:CommandField ShowInsertButton="True" ShowCancelButton="false" />
</Fields>
</asp:DetailsView>
•
One of the properties of DetailsView control is one of the Model Binding methods.
Model Binding
•
This property will be bound to a method and will be executed when the
event is fired.
InsertMethod="DetailsView1_InsertItem“
•
Will call the method:
public void DetailsView1_InsertItem()
{
//Add my code here to handle the insert
}
ListView Control
•
ListView is a “best of all worlds” control, combining the rich feature set of
the GridView with the control over the markup that the Repeater gives you
and adding the insert behavior of the DetailsView.
•
The ListView displays and manages its data through templates that enable
you to control many of the different views that the ListView gives you on its
underlying data.
•
You add the control to the page, define one or more templates (for
example, one to display an item in read-only mode and another that lets
you edit an item), and then use Model Binding to get data from the
database and send updates back.
•
ListView has templates to add the content. The templates supported are:
<LayoutTemplate>, <ItemTemplate>, <AlternatingItemTemplate>,
<SelectedItemTemplate>, etc..
ListView Control Templates
ListView Control Templates
Control Properties
Implementing ListView
<asp:ListView ID="ListView1" DataKeyNames="Id" runat="server"
InsertItemPosition="LastItem" SelectMethod="ListView1_GetData"
InsertMethod="ListView1_InsertItem" DeleteMethod="ListView1_DeleteItem">
<InsertItemTemplate>
<li>
Description: <asp:TextBox ID="Description" runat="server"
TextMode="MultiLine" Text='<%# Bind("Description") %>' /><br />
ToolTip: <asp:TextBox ID="ToolTip" runat="server"
Text='<%# Bind("ToolTip") %>' /><br />
ImageUrl: <asp:TextBox ID="ImageUrl" runat="server"
Text='<%# Bind("ImageUrl") %>' /><br />
<asp:Button ID="InsertButton" runat="server" Text="Insert" CommandName="Insert" />
</li>
</InsertItemTemplate>
•
We create out methods depending on the operations. Here is our SelectMethod
public IQueryable ListView1_GetData([QueryString("PhotoAlbumId")] int photoAlbumId)
{
var myEntities = new PlanetWroxEntities();
return from p in myEntities.Pictures
where p.PhotoAlbumId == photoAlbumId
select p;
}
Implementing ListView
•
In the Listview we also have our Layout template:
…inside List View
<LayoutTemplate>
<ul class="ItemContainer">
<li runat="server" id="itemPlaceholder" />
</ul>
</LayoutTemplate>
•
The LayoutTemplate you added to the ListView serves as the container for
the other templates. In between its tags you define the markup that
contains the individual items.
•
Note that this <li> has its ID set to itemPlaceholder. This tells the ListView
control where to add the individual items. At run time, this element will be
replaced by the actual items from the templates, like ItemTemplate.
Implementing ListView
•
Implement our Delete Method
public void ListView1_DeleteItem(int id)
{
using (var myEntities = new PlanetWroxEntities())
{
var picture = (from p in myEntities.Pictures
where p.Id == id
select p).Single();
myEntities.Pictures.Remove(picture);
myEntities.SaveChanges();
}
}
Strongly Typed Data-Bound Controls
•
Bind and Eval are used with Data Bound Controls.
<asp:TextBox ID="ToolTip" runat="server" Text='<%# Bind("ToolTip") %>' />
...
<asp:Label ID="ToolTip" runat="server" Text='<%# Eval("ToolTip") %>' />
•
Bind is for two-way data binding (for insert and edit scenarios) and Eval is
for read-only scenarios.
•
Using a String Literal to bind or eval properties (see the “ToolTip” above) is
error prone.
Strongly Typed Data-Bound Controls
•
A new property that exists is ItemType. With an ItemType property you can
point to the type of object you’re assigning to its data source.
•
Once you’ve set this property, the data-bound control gets two new
properties, Item and BindItem, that are of the type you assigned to the
ItemType property. The first one serves as a replacement for Eval and the
second one replaces Bind.
Strongly Typed Data-Bound Controls
•
Now that the control knows your Item Type, we can set its properties with
intellisense support.
<asp:TextBox ID="Description" runat="server" Text='<%# BindItem.Description %>' />
<asp:Label ID="Description" runat="server" Text='<%# Item.Description %>' />
•
With a strongly typed data bound control, we now have intellisense
available and in addition, we get compile time checking of the data item
that was bound.
DataPager Control
•
DataPager currently is only used with the ListView control.
•
You can hook up the DataPager to the ListView control in two ways: You can
either define itwithin the LayoutTemplate of the ListView control or you can
define it entirely outside the ListView.
•
In the first case, the DataPager knows to what control it should provide
paging capabilities automatically.
•
In the latter case, you need to set the PagedControlID property of the
DataPager to the ID of a valid ListView control.
DataPager Control
<div style="clear: both;">
<asp:DataPager ID="DataPager1" runat="server" PageSize="3">
<Fields>
<asp:NextPreviousPagerField ButtonType="Button"
ShowFirstPageButton="True" ShowLastPageButton="True" />
</Fields>
</asp:DataPager>
</div>
Summary
•
In this chapter we covered:
•
Learned what LINQ is and what its syntax looks like
•
Learned the different forms of LINQ that are available and when they are
appropriate to use
•
Learned how to use the ADO.NET Entity Framework
•
Learned how to use Model Binding to access the ADO.NET Entity Framework
•
Learned how to use the ListView and DataPager controls