LINQ to SQL & WCF

1. LINQ to SQL & WCF Using LINQ to SQL in n-Tiered architectures

2. 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

3. Agenda • Defining the tiers • 3 tiered architecture with LINQ to SQL • Working with WCF • Some gotchas • Some Tips

4. But before I start… • What’s the worst application you have worked on? • I was working on a Client project earlier…

5. They had a great architecture…

6. It had… • 60 tables with no relationships and keys • It was crashing a lot • 1000 ASP pages that managed their whole business

7. There was a lot of ad hoc customizations • JobList.asp • JobListA.asp • JobListB.asp • JobListForGregInAccounting.asp

8. 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

9. 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]

10. UI • Business • Data • Data Access • Common Objects WebUI WCF Services Data Access Northwind WinUI LINQ to SQL DBML

11. How did we do it? • Isn’t LINQ to SQL a 2 tiered technology?

12. 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

13. 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();}

14. Data • Data Access/Classes Northwind Northwind.Common.Objects DataContext Entities

15. UI • Data • Data Access/Classes WebUI WinUI Northwind Northwind.Common.Objects DataContext Entities

16. UI • Data • Data Access/Classes WebUI WinUI Northwind Northwind.Common.Objects DataContext Entities

17. UI • Data • Data Access/Classes • Business WebUI Services WinUI Northwind Northwind.Common.Objects DataContext Entities

18. 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?

19. Can we split it? • Has anyone tried?

20. 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

21. 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

22. So how does our architecture look now?

23. UI • Northwind.Services • Data • Northwind.DataAccess • Northwind.Common.Objects WebUI Services NorthwindData Northwind WinUI Northwind.dbmlInternal DataContext Entities

24. Let’s talk business over WCF • Has anyone tried to use LINQ over WCF? • Any problems? • Any success stories?

25. 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

26. 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

27. Let’s talk business over WCF • Get a list of customers • Delete some customers • Update customer details • View the orders a customer has made

28. Our WCF service is running

29. WCF Service Configurator

30. Data Northwind

31. Data • Northwind.Common.Objects Northwind Northwind.dbmlwith Internal DataContext

32. Data • Northwind.DataAccess • Northwind.Common.Objects NorthwindData Northwind Northwind.dbmlwith Internal DataContext

33. Northwind.Services • Data • Northwind.DataAccess • Northwind.Common.Objects Northwind.Services NorthwindData Northwind Northwind.dbmlwith Internal DataContext

34. Northwind.WebUI • Northwind.Services • Data • Northwind.DataAccess • Northwind.Common.Objects WebUI Northwind.Services NorthwindData Northwind WinUI Northwind.dbmlwith Internal DataContext

35. Connecting the Client • Make our client talk to the WCF services

36. Error #1 – Connection String

37. Error #1 – Connection String • Where should I put it? • A) Northwind.Common.Objects • B) Northwind.DataAccess • C) Northwind.Services • D) Northwind.WebUI

38. Error #1 – Connection String • A:\ • A) Northwind.Common.Objects • B) Northwind.DataAccess • C) Northwind.Services • D) Northwind.WebUI • Let’s fix this and continue

39. Error #2 – Underlying connection was closed

40. Error #2 – Underlying connection was closed • Has anyone come across this one? • There’s not much details if you actually debug through it

41. 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.

42. Serializable LINQ to SQL classes

43. 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

44. Error #3 – Maximum Message Size

45. Error #3 – Message Size • Who wants to give up? • Who’s hit this before? • What did you do?

46. Error #3 – Message Size • Who wants to: • A) Change the default size to the maximum possible size • B) Return less than 64K of data

47. Error #3 – Message Size • Who wants to: • A) Change the default size to the maximum possible size • B) Return less than 64K of data

48. 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?

49. 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

50. Error #3 – Message Size • The right thing to do here is to change our query to use paging