“Real World” LINQ to SQL

I found a pretty nice webcast on LINQ to SQL from Teched. It’s a 400 level session that goes into some of the challenges in creating “real world” applications with LINQ to SQL.

http://www.microsoft.com/events/teched2008/webcasts/TLA402/#9

Tony discusses a few different “real world” scenarios:

1. Using LINQ to SQL with a distributed, service-based system.

2. Handling change tracking (what was updated / inserted / deleted) by the client code.

3. Concurrency

4. Injecting business logic

5. Using stored procs

He does a very nice job of showing how things work and covers most of the topics in sufficient details to get the point across. He also makes his source code available via his blog.

My take-aways…

Good things:

  • LINQ to SQL is an option for real world, non-trivial systems and applications. It has the necessary features and extensibility points to make it do what you need it to do.
  • LINQ to SQL has support for passing it’s entity classes over the wire via WCF. You can set a property on the data context, telling it to put the WCF DataContact and DataMember attributes on the entities.
  • LINQ to SQL can do stored procs if needed.
  • LINQ to SQL has a place to intercept changes before they make it to the database. This makes it possible to inject custom business logic in the pipeline.
  • LINQ to SQL supports a couple options for concurrency checks. One is based on TIMESTAMP columns. The other is a “compare all values”.
  • LINQ to SQL has a built in logging API that spits out the SQL statements that it is sending to the database. This makes it very easy to log and/or diagnose exactly what’s going on under the hood. In the demos, Tony redirects this output to the console window.

Not so great:

  • The architecture that Tony shows for his web service is ok, but not great. I feel that it exposes a little too much of the internals on the surface of the service. It’s also set up as a standard “CRUD” style service. We’ve worked hard to get away from this style in our services. We create custom datatypes to model the “messages” that we pass in and out of the service. Typically, these messages contain only the data needed to perform the operation.
    He implements a custom “Change Tracking” implementation. This uses an enum value on each entity, which the caller can set to indicate whether the item was update, inserted, or deleted. This is slick, but it proliferates the CRUD pattern.
  • While stored procs are supported, they require a significant amount of additional code to make them work.
  • The concurrency support is good, but it should probably stay inside the service. I’m still not sure that passing TIMESTAMPs and using optimistic concurrency across service boundaries is a good approach. It has it’s use cases (mostly for internal, small scale service), but it’s not scalable and gives the end user a pretty bad experience.
    A better concurrency option requires designing the service interface so that methods don’t conflict with each other.

Overall:

LINQ to SQL looks like a very nice tool. It’s simple to use, but has good extensibility points. It seems like a good fit for straight forward web services and possibly for very simple applications that hit a database directly (which we shouldn’t really be doing ;-).

I would argue that the Entity Framework is probably a better option for enterprise systems. It offers the same core features, and adds some very nice layers of indirection. EF is relatively simple to use in basic scenarios and it can grow with the application as things get incrementally more complicated.

EF doesn’t have a “checkbox” to make the entities serializable, but this isn’t necessarily a bad thing. A proper architecture probably dictates that the entities flowing on the wire should be different than those used for data access. I’ll know more as I continue to explore the multitude of new data access offerings from MS.

One thought on ““Real World” LINQ to SQL

Add yours

  1. The LINQ to SQL has a place to intercept changes before they make it to the database. This makes it possible to inject custom business logic in the pipeline.It has a built in logging API that spits out the SQL statements that it is sending to the database. This makes it very easy to log and/or diagnose exactly what’s going on under the hood..It is nice ..Thanks..

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: