LINQ Terminology 101 for DBAs and Others Puzzled

I was responding to a discussion in the SQL MVP newsgroups today about LINQ. A comment was made about understanding of the basic terminology and layers of software. I figured I should blog this as well to help someone.
 
1. LINQ per se is unrelated to databases. It’s a language extension that provides an easy to use query syntax for things that are enumerable. You can build a “LINQ to xxx” provider where xxx is almost anything. The thing I find weird about it is that they used SQL keywords as operators at all.
 
2. LINQ to XML is excellent to use compared to working with the XML-based objects that we had to use in .NET before. The latest version of VB is stunningly good at working with XML natively compared to any other language. Bill McCarthy (local excellent VB MVP) has written a really excellent article on this here: http://visualstudiomagazine.com/columns/article.aspx?editorialsid=2421
 
3. LINQ to SQL is what most people are talking about now. The way most people are using it is to build an object model out of their table layouts in a 1 to 1 fashion and only for SQL Server. Sadly, even though the audience is often Enterprise clients and ISVs, this is what most people are shown about LINQ at Microsoft events as it fits well in 7 minute demos. Apart from in the simplest cases, it tends to generate T-SQL that’s impenetrable for most humans to debug, particularly those that were trying to avoid T-SQL in the first place. I find great irony lately in every discussion I’ve seen where people that didn’t want to do T-SQL in the first place are pouring over pages and pages of incomprehensible machine-written T-SQL trying to locate performance issues.
 
4. The Entity Framework allows you to have an alternate mapping layer above SQL Server (and potentially other database engines) that you can program to.
 
In the example I’ve used in the SQL launch materials, I have a Flights table, a Passengers table and a FlightManifests table. FlightManifests is just a simple linking table (many to many) between flights and passengers. In LINQ to SQL, what you’d see is three objects, directly relating to Flights, Passengers and FlightManifests. While this makes perfect sense in the database, it isn’t really useful in the application layer. With the Entity Framework, the typical representation of this would only have a Flight object and a Passenger object. The Passenger object would have a Flights collection as one of its properties. The Flights object would have a Passengers collection as one of its properties. This much more closely models the business.
 
But you can go further with this. You can then inherit a new type of object, say “EmployeePassenger” that is a Passenger but has extra properties like an EmployeeNumber. You can also create constrained types such as a FrequentFlier which might be a Passenger that has a FrequentFlierNumber.
 
The Entity Framework then adds extensions to SQL (aka Entity SQL) that are only understood by its provider. So it lets you query the Passengers table where the row is of type FrequentFlier. The upside of this is that you only record the rules for what makes someone a FrequentFlier in one location and you do so declaratively, instead of having to remember to put the right WHERE clause all over the place.
 
“So why wouldn’t I do this with views” is the next normal response. Most large databases long outlive the applications that work with them. Often many many applications use the same databases. The traditional approach has been to litter the database with application-specific views and procs.
 
The Entity Framework allows the mappings to live with the application and to be completely different for different applications. For example, I might have a rule that says I don’t use abbreviations.I can have “EstimatedTimeOfArrival” in my model even if the DBA prefers “ETA”. I probably want singular object names. The DBA might prefer plural table names. etc etc
 
In addition, these mappings can be to/from stored procedures and views, not just to/from tables.
 
5. LINQ to Entities is just the provider to let you use LINQ with the Entity Framework instead of writing Entity SQL.
 
6. ADO.NET Data Services takes this another step further and exposes the Entities (from #4 above) via URLs. (URI’s actually) This is considered a logical replacement for native HTTP Endpoints that were in SQL Server 2005. It also provides a much more cross-platform approach.
 
Hope this helps someone. I discussed it all at some length with Pablo Castro in the interview I did a few weeks ago at www.sqldownunder.com. The discussion in the podcast was targetted more at SQL folk than developers.
 

SQL Server 2008 CTP6 hits the streets and you can win an XBox

SQL Server 2008 CTP6 hit the streets today. You can get it here:

http://www.microsoft.com/downloads/details.aspx?familyid=749bd760-f404-4d45-9ac0-d7f1b3ed1053&displaylang=en&tm

It’s getting pretty close to feature complete now. Microsoft is keen to have it tested like no other version before. To assist with this, they’ve introduced a bug bash competition with a bunch of XBox’s and other interesting prizes if you help find issues with the CTP. Details are here:

http://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=7734

It’s time to go bug-hunting !

TechEd Orlando – 4 sessions and lots of friends there too

I got the good news today that I’ve had 4 sessions accepted in the first round of approvals for TechEd Orlando. Two are in the developer week and two in the IT Pro week. I’m really looking forward to those two weeks as TechEd’s are always¬†great events.

Good to see so many of my colleagues also doing sessions. Peter Myers has two precons together with Douglas McDowell and Craig Utley. Peter also has two sessions in the dev week. Adam Machanic is really excited about his first TechEd session coming up. I just know it’ll be great so don’t miss it. Fernando Guerrero has an interesting session planned, Andrew Kelly has several as does Itzik Ben-Gan both of whom are always great value. Maciej Pilecki also has a session this year that looks excellent.

Our friends at SQL Skills are also doing a number of sessions. Bob Beauchemin has some really interesting looking ones along with a precon and Kim Tripp and Paul Randall are also doing a double-act at a precon.

And great to see another local Jeff Wharton from WardyIT doing a session, hot after winning his Speaker Idol spot at Barcelona! And recent SQL Down Under guest Matthew Roche is there as well.

And that’s just some of the info from the SQL tracks. All up, it should be a great event.

Crocodiles and curiosity

Late last night I watched the end of a wonderful documentary from the BBC called Dragons Alive. Although Mai didn’t really want to discuss it at 12:30am, it really got me thinking about the nature of curiosity and its importance in how we advance science and through it, society.

Most awesome advances in science seem to come through people asking seemingly child-like questions. Some of the ones presented at the end of the show were great examples.

One that really struck me was about crocodiles. We all know they live in stagnant water quite happily. How do they live there without getting sick? If you’d have asked me, I would have guessed it was the tough outer skin. But the question that should have been obvious to us all is why don’t they get infected and die when they get injured? If we exposed our injuries to stagnant water, it’d probably be the end of us.

There has been lots of work on that over the last few years and it looks like it’ll yield antibiotics stronger than anything we’ve ever seen before.

But the interesting question isn’t the specifics of this case, it’s why we haven’t asked that question before and what we need to do to encourage people (particularly children without preconceptions) to ask “dumb” questions.

 

SQL Down Under show 30 – Pablo Castro – LINQ, Entity Framework, ADO.NET Data Services

SQL Down Under show 30 with guest SQL Server product team member Pablo Castro discussing LINQ, Entity Framework and ADO.NET Data Services (Astoria) for the DBA and database developer is now available for download from www.sqldownunder.com.

 

Enjoy!