The title is a bit of a misnomer and not comparing apples to apples. To do it right would have been longer. The real comparison is

“Choosing LINQ to Entities vs. Entity SQL+Object Services vs. Entity SQL+EntityClient”

When doing an intro session on entity Framework last night for GUVSM in Montreal, even though it’s something like the 7th time I’ve done this session (and every single presentation has been totally unique from any of the others I have done –  shaped by the questions asked), I think I finally figured out how I want to express this option of which query method you want to use. Even within those three options, there are more variations happening because they return different payloads and this can additionally be affected by whether or not you do projections (e.g. select firstname, lastname, age  instead of just select the whole object).

1) For me, Linq to Entities will always be the first query method I go for. I’m getting more and more comfy with LINQ since I’ve been using it with objects, SQL, DataSets and XML. Having all of that intellisense support is dreamy. Whether I use operators (From c in context.Customers Where c.CompanyName=”Acme Builders” Select c) or method operators (context.Customers.Where(Function(c) c.CompanyName=”Acme Builders”) it just makes my life easy! I’ve been using LINQ to Entities almost exclusively unless I have a reason to go to the next level.

LINQ to Entities still depends on an ObjectContext (and therefore uses Object Services) so you get all of the goodies that go along with that such as ChangeTracking, etc.

LINQ to Entities returns objects.

2) The next level is using EntitySQL with Object Services. That would be:

objectcontext.CreateQuery(Of Customer)(“Select VALUE c from Customer as c WHERE c.CompanyName=’Acme Builders'”)

The main reason I would want to use this is because Entity SQL is a very robust query syntax and I can express queries that LINQ (which is strongly typed) may not enable me to write. I want to be clear about my use of the word “dynamic” since I just mean that I can build the strings. You can also use parameters and as in most cases, should be careful about where and when you use dynamic queries (“select value c from customer where c.CompanName=” & myvariable).

As an aside, I’ve discovered that writing custom operators in Astoria, while possible with LINQ to Entities, is better in teh long run if you write them with EntitySQL. That is a conclusion made using the prototype so this may change down the road.

This method can return objects or dbDataRecords, depending on the query.

3) The method I see myself using most infrequently is EntitySQL with EntityClient. EntityClient is a db provider, similar to SQLClient or OLEDBClient. You create connections (EntityConnections) and commands (EntityCommands), set the command’s string (an Entity SQL query) and execute as you would other clients. The big difference between this and the other Client Providers is that you are connecting to your entity data model, not to a database.

There are two reasons to use EntityClient.

The first is if you do not want to return objects, but just streamed data, because EntityClient always returns a dbDataReader. This also means it’s a great candidate for helping you use an Entity Data Model in existing applications.

The other scenario (which is currently theoretical in my mind and has not be tested yet) where I can see myself wanting entityClient is if I have a data layer that needs to implement the Provider Factory class so that I can select my provider at runtime.

I’m ignoring another possible reason which is that EntityClient provides a familiar way of accessing data because it feels similar to using the other client providers. But because you lose your objects and all of the benefits of the objectContext, I don’t think the benefit of “smaller learning curve” is a worthy trade-off.

“The proof is in the pudding,” as they say and we saw the pudding last night at the GUVSM presentation because I have already gotten out of the swing of composing Entity SQL queries, while I’m getting to be a champ with LINQ to Entities.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!