Transcript Slide deck - EricPhan.net

LINQ to SQL & WCF
Using LINQ to SQL in n-Tiered
architectures
Eric Phan

Solution Architect @ SSW

Used LINQ to SQL since the early CTPs and Betas

Used LINQ to SQL in a few large scale client applications


Windows App
Web Apps

In charge of Developer training and Architecture/Code reviews at
SSW

http://ericphan.info
Agenda

Defining the tiers

3 tiered architecture with LINQ to SQL

Working with WCF

Some gotchas

Some Tips
But before I start…

What’s the worst application you have worked on?

I was working on a Client project earlier…
They had a great architecture…
Database
ASP
Website
It had…

60 tables with no relationships and keys

It was crashing a lot

1000 ASP pages that managed their whole business
There was a lot of ad hoc
customizations

JobList.asp

JobListA.asp

JobListB.asp

JobListForGregInAccounting.asp
They wanted…

SQL Database to be cleaned up

ASP to be rewritten with new functionality in ASP.NET

ASP pages to be simplified

Clearly defined workflow for their business processes

Potentially later down the track they will have mobile PDA or
ruggedized laptops connecting to the system to access a
subset of the functionality
This is what we proposed

Clean up the database

Setup a good architecture with:





LINQ to SQL for data access
Windows Workflow
WCF for the business logic
ASP.NET 3.5 Web Application
Windows App [future release]
Data
Data Access
Business
UI
WebUI
Northwind
Data Access
WCF
Services
WinUI
Common Objects
LINQ to SQL DBML
How did we do it?

Isn’t LINQ to SQL a 2 tiered technology?
Where does LINQ to SQL fit in?

The LINQ to SQL DBML consists of two main parts

DataContext – Data Access
• e.g. NorthwindDataContext

Entities – objects representing data in your database
• e.g. Customer, Order, Employee

It’s like your Data Adapters and Data Sets.

The DataContext talks to the database and the Entities just
hold the data
2 Tiered?

By default it is 2 tiered

I can call my data access from my Web

using (var db = new NorthwindDataContext())
{
return db.Customers.ToList();
}
Data
Northwind
Data Access/Classes
Northwind.Common.Objects
DataContext
Entities
Data
UI
WebUI
WinUI
Northwind
Data Access/Classes
Northwind.Common.Objects
DataContext
Entities
Data
UI
WebUI
WinUI
Northwind
Data Access/Classes
Northwind.Common.Objects
DataContext
Entities
Data
Business
UI
WebUI
Services
WinUI
Northwind
Data Access/Classes
Northwind.Common.Objects
DataContext
Entities
Where does LINQ to SQL fit in?

The entities should be shared across all the projects




UI needs to know how to present the customer
Business logic needs to know what to do with a customer
Data access needs to know how to get and update a
customer
What about the DataContext?


It’s currently bundled with the entities
Can we split it?
Can we split it?

Has anyone tried?
So how do we separate our Data
Access layer?

Create our own generic DataContext class in a new
DataAccess project.

Create some methods in NorthwindData.cs to get and save
data.

Make the generated DataContext class internal
If I was the God of LINQ to SQL…

I would automatically separate the DataContext and Entities into
two different projects or at least two different class files

You can achieve this through various techniques and code
generators (e.g. generating XML using SQLMetal then using XSLT
to create your Entity classes), but it’s not nice. This stuff really
should be supported out of the box.

There are several projects out there that attempt to create this
separation.

I find it easier just to create this generic DataContext
So how does our architecture look
now?
Data
Northwind.DataAccess
Northwind.Services
UI
WebUI
Northwind
NorthwindData
Services
WinUI
Northwind.Common.Objects
Northwind.dbml
Internal DataContext
Entities
Let’s talk business over WCF

Has anyone tried to use LINQ over WCF?

Any problems?

Any success stories?
WCF

Service Contracts





Defines a set of contracts between the client and the
server so they know how to communicate
[ServiceContract] – marks a class as visible to WCF clients
[OperationContract] - marks a method as visible to WCF
clients
[DataContract] – marks a class as transportable over WCF
[DataMember] – marks a property on the class to serialize
WCF

Can easily configure how the service behaves





E.g. listen over HTTP, TCP IP, UDP
Enable reliable messages
Enable encryption
Enable security
Hosting via



IIS
Windows Service
Console
Let’s talk business over WCF

Get a list of customers

Delete some customers

Update customer details

View the orders a customer has made
Our WCF service is running
WCF Service Configurator
Data
Northwind
Data
Northwind
Northwind.Common.Objects
Northwind.dbml
with Internal DataContext
Data
Northwind
Northwind.DataAccess
NorthwindData
Northwind.Common.Objects
Northwind.dbml
with Internal DataContext
Data
Northwind
Northwind.DataAccess
Northwind.Services
NorthwindData
Northwind.
Services
Northwind.Common.Objects
Northwind.dbml
with Internal DataContext
Data
Northwind.DataAccess
Northwind.Services
Northwind.WebUI
WebUI
Northwind
NorthwindData
Northwind.
Services
WinUI
Northwind.Common.Objects
Northwind.dbml
with Internal DataContext
Connecting the Client

Make our client talk to the WCF services
Error #1 – Connection String
Error #1 – Connection String

Where should I put it?




A) Northwind.Common.Objects
B) Northwind.DataAccess
C) Northwind.Services
D) Northwind.WebUI
Error #1 – Connection String

A:\





A) Northwind.Common.Objects
B) Northwind.DataAccess
C) Northwind.Services
D) Northwind.WebUI
Let’s fix this and continue
Error #2 – Underlying connection was
closed
Error #2 – Underlying connection was
closed

Has anyone come across this one?

There’s not much details if you actually debug through it
Error #2 – Underlying connection was
closed

This is a WCF serialization issue

Whenever you come across this error, 90% of the time it’s
because one of the objects/classes you are passing back from
WCF can’t be serialized.

We need to make our LINQ classes serializable.
Serializable LINQ to SQL classes
Serializable LINQ to SQL Classes

[Table(Name="dbo.Customers")]
[DataContract()]
public partial class Customer :
INotifyPropertyChanging, INotifyPropertyChanged
{
[Column(Storage="_CustomerID", DbType="NChar(5)
NOT NULL", CanBeNull=false, IsPrimaryKey=true,
UpdateCheck=UpdateCheck.Never)]
[DataMember(Order=1)]
public string CustomerID
Error #3 – Maximum Message Size
Error #3 – Message Size

Who wants to give up?

Who’s hit this before?

What did you do?
Error #3 – Message Size

Who wants to:


A) Change the default size to the maximum possible size
B) Return less than 64K of data
Error #3 – Message Size

Who wants to:


A) Change the default size to the maximum possible size
B) Return less than 64K of data
Error #3 – Message Size

This one is actually a very good issue to hit early

Essentially what is happening here is that we’re doing a
SELECT * FROM Customers

Does anyone have a problem with this?

What if there were 100000 records?
Error #3 – Message Size

WCF is smart and doesn’t attempt to send any messages that
are over a certain limit (by default it’s 64K)

Saves your end users from waiting a long time to load a page
Error #3 – Message Size

The right thing to do here is to change our query to use paging
Message Size
public List<Customer> GetCustomers(int pageIndex, int pageSize, out int totalCustomers)
{
using (var db = new NorthwindData())
{
var customers = db.GetTable<Customer>();
totalCustomers = customers.Count();
var results = customers
.Skip(pageIndex*pageSize)
.Take(pageSize);
return results.ToList();
}
}
Message Size
What we saw

Creating a WCF service and hooking it up and these common
issues



Connection strings
Serialization
Message Size
Lets take a break

After this we will cover



Deletes
Updates
Eager Loading
Lets add some more functionality

Delete

Customer Details

Update
Delete
Delete

We solved it by deleting the references also

You can also set Cascade deletes on in the database 
Customer Details
Customer Details
Customer Details

Databinding through the UI against an object data source and
our WCF service client

0 code in the UI
Updates

Should be easy…
Update Error #1
Update Error #1

Fixed by adding Timestamp columns
Update Error #2
Update Error #2

Caused by the attach method just connecting the disconnected
entity but not actually checking to see if there were any
changes

As Modified parameter

Fixed by using Context.Refresh()
Some other errors

Cannot add an entity with a key that is already in use.

Value of member 'TimeStamp' of an object of type 'Customer'
changed.

A member that is computed or generated by the database
cannot be changed.
LINQ to SQL - Updates

A couple of strategies




Timestamp column
Reflection to replay changes
Keep a copy of the original
Use the Attach & Refresh method (recommended)
Updates - Attach and Refresh


public void UpdateCustomer(Customer customer)
{

using (var db = new NorthwindData())

{

db.GetTable<Customer>().Attach(customer, true);

db.GetTable<Customer>().Context.Refresh(RefreshMode.KeepCurrentValues, customer);

db.Save();
}


}
It works!!!
Lets do one more thing and load
some orders

Show the orders for a customer when you show the customer
details
Eager Loading with DataLoadOptions
and LoadsWith

Use this when you need to bring along child objects with you

Saves you from doing another round trip

Only works in one direction
Eager Loading with DataLoadOptions
and LoadsWith

E.g. When viewing an order you can’t get it to eagerly load a
Customer.


order.Customer will be null
order.CustomerID will have the CustomerID
Why does it only work in
one direction?
•
Remember the unidirectional
serialization?
•
Uni means one

One way serialization
What if I needed to access a parent
object as well?

Requery for it as you will have access to the foreign keys

Create an aggregate class that will return what you need

class OrderResult
{
Customer customer;
Order order;
}
Recap

Make the generated DataContext internal

Create your own generic one in DataAccess

WCF




Serialization
Gets – always page
Updates – Attach & Refresh
Eager Loading with DataLoadOptions and LoadsWith<T>
Is this ready?

We have used LINQ to SQL on several client projects

Our developers love using it

If you setup the architecture correctly then it is fine for use

Performance is faster than LINQ-to-Entities

http://www.thedatafarm.com/blog/2008/07/10/LookingAtEF
PerformanceSomeSurprises.aspx
With our client from before

We had a hard time looking at their existing system and
couldn’t get a hold of the previous developer…

We eventually figured things out



It was a 6 month project
We had up to 8 developers working on the project
It is currently in testing by the client
Resources

Rules to Better LINQ


Julie Lerman – Microsoft MVP, LINQ, EF guru


http://www.thedatafarm.com/blog/
Hooked on LINQ


www.ssw.com.au/ssw/Standards/Rules/RulesToBetterLinq.aspx
http://www.hookedonlinq.com
WCF Samples

http://msdn.microsoft.com/en-us/library/ms751514.aspx
Two things

[email protected]

http://ericphan.info
Thank You!
Gateway Court Suite 10
81 - 91 Military Road
Neutral Bay, Sydney NSW 2089
AUSTRALIA
ABN: 21 069 371 900
Phone: + 61 2 9953 3000
Fax: + 61 2 9953 3105
[email protected]
www.ssw.com.au