by jesseliberty via Jesse Liberty - Silverlight Geek on 8/16/2009 2:48:00 PM
Turing Project Page: [Novice: 8 | Advanced: 5 ] FAQ | Table of Contents | Definitions
What is this and where do I start?
In a previous post I discussed the advantage of separating the Data Access Layer (retrieving data from your data store) from the Data Transport Layer (moving data from the server to your client. We then took a small but important detour into an introduction to Linq. This posting will expand upon Linq sufficient to allow a good understanding of how we’ll be using Linq in the rest of the project.
There are three strong candidate technologies for the Data Access Layer (DAL):
In the next posting we’ll move forward with Entity Framework as it provides an excellent high abstraction of your data that is object-oriented and that works very well with our Data Transport technology of choice: RIA Services.
Linq To Sql, while a powerful technology, is ruled out both because it is limited to Sql Server and because it is not well suited to Silverlight. That said, it is excellent for looking at Linq, which is what we’ll do now.
Let’s start by building and annotating a stripped down starter program that will retrieve data from the BlogEntries table and put it into a DataGrid.
For this and many postings, you’ll want the Turing DB. There are two ways to get it: you can create it or you can download a backup and restore it. The Database design is here, and I’ve prepopulated the three tables as follows:
Blogs:
Notice that BlogID 4 and BlogID 1 share a common BloggerID – that is, these are two different Blogs owned by the same person.
The Bloggers themselves are identified in the Bloggers table:
Finally, I filled the entries table with a number of rows (I wrote a simple program to grab strings) to have something to work with:
(click on picture to see full size image)
To retrieve this data with LinqToSql we’ll create a new Silverlight Application, let’s call it LinqToSqlSL and be sure to click the checkbox indicating that you do want a web application along with it.
Your solution will open with two projects, one for Silverlight and one a server / ASP.NET project. Let’s immediately add a third project by right clicking on the solution, choosing Add->New Project and choosing the Class Library (not the Silverlight Class library) template.
Call the new project Linq and click OK. When the project opens in the Solution explorer, delete Class1.cs that Visual Studio created. Next, right click on the new Linq project and choose Add –> New item and select the Linq to SQL Classes template and give your new item a name, e.g., Blog,
After you click Add notice that three items are added to the project: Blog.dbml and under that Blog.dbml.layout and Blog.dbml.cs. You are also placed in the Object Relational Designer which is a very powerful drag and drop tool. Open the DataConnections window and add or open a connection to the database.
Drag the three tables onto the design surface, where they will immediately and automatically create a database diagram.
The diagram represents a set of classes created for you by the designer; you can see the corresponding generated code in Blog.designer.cs. These classes will be the framework for your LinqToSql interactions. Before you leave this project, however, open the app.config file and find and copy the ConnectionStrings element:
<connectionStrings>
<add name="Linq.Properties.Settings.TuringDBConnectionString"
connectionString="Data Source=LIBERTYT60P\SQLSERVER2008;
Initial Catalog=TuringDB;
Persist Security Info=True;
User ID=sa;Password=????????"
providerName="System.Data.SqlClient" />
</connectionStrings>
[I chose to leave my connection string exposed in plain text; in a production application you’ll want to prompt for the password]
Open web.config in LinqToSqlSL.web and find the ConnectionStrings element, which probably looks like this:
<connectionStrings/>
and replace it with the connectionStrings section you just copied. Build the app to make sure all is well.
You now have a LinqToSql model on the server side, and you are ready to make calls through that model to the database. This, however, must also be done on the server side. To get the data to the client, you’ll create a web service. Right click on the LinqToSqlSL.web project and choose Add->New Item and in the dialog, select the Silverlight-enabled WCF Service template, and name it BlogsWS.svc
Visual Studio will open to BlogsWS.svc.cs – the code for your WCF web service. A pair of attributes will have been added to the class, and a starter method, DoWork, with its own attribute is also created and will serve as a great starting point, though you will change its name and return value.
using System.ServiceModel;
using System.ServiceModel.Activation;
namespace LinqToSqlSL.Web
{
[ServiceContract( Namespace = "" )]
[AspNetCompatibilityRequirements( RequirementsMode =
AspNetCompatibilityRequirementsMode.Allowed )]
public class BlogsWS
[OperationContract]
public void DoWork()
// Add your operation implementation here
return;
}
// Add more operations here and mark them with [OperationContract]
Being careful not to alter the attributes, change the name of DoWork to GetBlogListings, and change the return value from void to List<BlogEntry>, as shown in red.
1: using System.Linq;
2: using System.ServiceModel;
3: using System.ServiceModel.Activation;
4: using System.Collections.Generic;
5: using Linq;
6:
7: namespace SimpleLinqToSQL.Web
8: {
9: [ServiceContract( Namespace = "" )]
10: [AspNetCompatibilityRequirements( RequirementsMode =
11: AspNetCompatibilityRequirementsMode.Allowed )]
12: public class BlogDataWs
13: {
14:
15: private BlogDataContext db;
16:
17: [OperationContract]
18: public List<BlogEntry> GetBlogListings()
19: {
20: db = new BlogDataContext();
21: var result = db.BlogEntries.Where(be => be.Blogger == 1).ToList();
22: return result;
23: }
24: }
25: }
Notice that we also added three using statements. Line 1 includes the Linq library. You’ll need to add a reference to System.Data.Linq from the .NET tab in the Add References dialog.
Line 4 adds System.Collections.Generic to support the List<BlogEntry> and line 5 adds the Linq dll created by your Linq project (Open add reference and click on the projects tab where you can add the dll for the Linq project.)
On line 15 I’ve created a private member variable of a type that derives from System.Data.Linq.DataContext. The derived type you need was created for you, when you in Blog.designer.cs,
1: [System.Data.Linq.Mapping.DatabaseAttribute(Name="TuringDB")]
2: public partial class BlogDataContext : System.Data.Linq.DataContext
instantiate the data context in the GetBlogListings method. Intellisense will show that the new object has a member BlogEntries whose data type is Table<BlogEntry>,
The Table<BlogEntry> property has, in turn, a method, Where, that will take a Linq expression to find the subset of the rows that you want returned. The Linq statement to find every record where the Blogger ID is equal to 1 is:
be => be.Blogger == 1
var result = db.BlogEntries.Where(be => be.Blogger == 1).ToList();
When you build you’ll have created a web service that retrieves every BlogEntry where the Blogger’s ID is 1.
Let’s use this (admittedly hard-wired and limited) web service to populate a DataGrid back in Silverlight. To do so, right click on the LinqToSql Silverlight project and select Add Service Reference. When the Add Service Reference dialog opens, click on Discover. Your newly created web service will be found at localhost (your computer) and you can expand it in the Services window to see all one of its operations,
The Silverlight project can now call the web service’s GetBlogListings method and expect to get back a List<BlogEntry>. To keep this dead simple, we’ll add a DataGrid to MainPage and give it no attributes except a name (be sure to drag the DataGrid from the Toolbox onto the form so that Visual Studio will set up all the namespaces for you).
<Grid x:Name="LayoutRoot"> <data:DataGrid x:Name="myDataGrid" /></Grid>
In the code behind for MainPage we need to set the ItemsSource property for the DataGrid, and we want to set it to the results of invoking the method in the Web Service. We’ll do that in the event handler for the Loaded event, ensuring that the page is fully ready for the DataGrid to be populated.
The steps are to create an instance of the web service and then to invoke the method, but the method must be invoked asynchronously (you must never block the Browser!). Thus, you need to identify the call back method for when the method completes, and then you call the (automatically generated) asynchronous version of the method,
1: using System.Windows;
2: using System.Windows.Controls;
3: using LinqToSqlSL.BlogsWS;
4:
5: namespace LinqToSqlSL
6: {
7: public partial class MainPage : UserControl
9: public MainPage()
10: {
11: InitializeComponent();
12: Loaded += MainPage_Loaded;
13: }
15: void MainPage_Loaded( object sender, RoutedEventArgs e )
16: {
17: var ws = new BlogsWSClient();
18: ws.GetBlogListingsCompleted += (ws_GetBlogListingsCompleted);
19: ws.GetBlogListingsAsync();
20: }
21:
22: void ws_GetBlogListingsCompleted(
23: object sender,
24: GetBlogListingsCompletedEventArgs e )
25: {
26: myDataGrid.ItemsSource = e.Result;
27: }
28: }
29: }
In the Loaded event handler, on line 17, we instantiate the web service client proxy. On line 18 we register the call back method and on line 19 we invoke the asynchronous version of the web service method. When the method completes, the call back method is called; lines 22-27. We extract the Result property from the EventArgs and assign it to ItemsSource.
Unfortunately, when you run this example it will throw an exception and report the wonderfully useless error that it can’t find the data. The reason you get this error is explained very clearly by John Papa in this article where he also shows you how to get more useful error information. It turns out that LinqToSql can’t serialize the relational data it has retrieved from the database. The problem and the solution are found in this excellent article by Scott Lanford.
The simple fix is to return to the Linq project and double click on the .dbml file to bring up the designer. Right click anywhere on the design surface (not on one of the classes) and select properties. In the properties window change the Serialization Mode to unidirectional (yes, this means that you can read, but not update using LinqToSql objects).
After doing this, delete the service reference to the web service in LinqToSql and rebuild each project. Then reinstate the service reference and rebuild and run.
Notice that you are getting the entire BlogEntry row, but of course not the resolution of the Blogger or Blog information; just the IDs. One approach to solving all these problems is to create a business class on the server. Right click on the SimpleLinqToSqlWeb project and add a class; name it BlogEntryInfo.cs,
1: namespace SimpleLinqToSQL.Web
2: {
3: public class BlogEntryInfo
4: {
5: public string Title { get; set; }
6: public string TheUri { get; set; }
7: public string Description { get; set; }
8: public string First { get; set; }
9: public string Last { get; set; }
10: public string Alias { get; set; }
11: public string Phone { get; set; }
12: public string Blog { get; set; }
13: public string BlogUri { get; set; }
14: }
15: }
I’ve created a property for each value I want to convey to the client. I could, of course, add any business logic I’d like at this point (e.g., combine First and Last name into name, exclude records with a modified date before 8/1/2009, etc.). We’ll keep it simple, though I did take the Liberty™ of changing some of the identifiers from what is used i the database.
We’ll rewrite the WebService to create an instance of BlogEntryInfo for each row in the database, and while we’re at it we’ll get all the rows, so that Blog.svc.cs now looks like this:
10: [AspNetCompatibilityRequirements(
11: RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed )]
14: private BlogsDBDataContext db;
15:
16: [OperationContract]
17: public List<BlogEntryInfo> GetBlogListings()
18: {
19: db = new BlogsDBDataContext();
20: var result = (
21: from be in db.BlogEntries
22: join bl in db.Bloggers on be.Blogger equals bl.ID
23: join b in db.Blogs on be.Blog equals b.ID
24: select new BlogEntryInfo()
26: Description = be.Description,
27: Title = be.Title,
28: TheUri = be.URL,
29: First = bl.FirstName,
30: Last = bl.LastName,
31: Alias = bl.alias,
32: Phone = bl.Phone,
33: BlogUri = b.BlogURL,
34: Blog = b.BlogName
35: }
36: ).ToList();
37: return result;
38: } // end method
39: } // end class
40: } // end namespace
Once again we get the dataConext and use it to create the Linq statement. The Linq statement this time joins the three tables together and then the projection (select) is used to populate a BlogEntryInfo object. The method returns a generic list of these, and so we make the same call to ToList() as we did previously.
[Note that because you are now returning an object you no longer are trying to serialize the LinqToSql object and you can remove the setting of unidirectional]
You’ll need to rebuild SimpleLinqToSql.web, then refresh or replace the web service reference in SimpleLinqToSql and rebuild the entire solution and run it,
(Click on image for larger view)
We’ve Won Here And Now It’s On To Chicago Bobby Kennedy June, 1968
With that background in Linq, we’re ready to leave LinqToSql and go on to the EntityFramework and thence to RIA Services.
Original Post: LinqToSQL A Detailed Review
The content of the postings is owned by the respective author. Silverlight Feeds is not responsible for the contents of the postings. This site is automatically generated and cannot be reviewed for abusive content. If you find abusive content on Silverlight Feeds, please contact us. Designated trademarks and brands are the property of their respective owners. All rights reserved.
