Freekshow

Anko has written about what he perceives as problems with LINQ to SQL in this post. Bob has previously spoken in similar terms. I’m putting in my two cents below.

#1 LINQ to SQL is designed for a two tier scenario. LINQ to SQL does not fits natural with a multi layer architecture.

This statement does not really specify what part of the design would prevent fitting LINQ to SQL in with a multi layer architecture. It’s true that the party line from the Microsoft camp seems to be that a 2-layer design would fit best with LINQ to SQL: you roll the data access layer and the business logic layer into one, with LINQ to SQL functioning as a 1:1 abstraction layer between your model and the database. I would use this model only for small-scale, data-driven applications. Problem with these applications usually is that they tend to evolve, bits get added on left right and center, and soon a fairly large codebase sprouts without a well thought out design.

When you need an enterprise-level application, you usually do not want to rely on the 1:1 mapping that LINQ to SQL offers. The database is will be normalized (or, in fact, denormalized) in a way that will not be in line with your domain model. (I think this is what Paul is referring to). In these cases, using an OR Mapper that would allow you to map entities to tables using the data mapper pattern is the more obvious choice.

Using LINQ to SQL in this kind of scenario, you could write your business object layer to encapsulate your data classes. Using LINQ, this kind of transformation is very easy to do.

For example, suppose that you have a data table for customer and a separate one for addresses. The address includes a country code, which is a foreign key to yet another table Countries. Do you want to worry about all this normalization when building the UI? Me thinks not..

You could create a data transfer object using LINQ like so

public class CustomerDTO

{

int CustomerID { get; set; }

string Name { get; set; }

string Street { get; set; }

string ZipCode { get; set; }

string City { get; set; }

string Country { get; set; }

} 

You can then use LINQ to transform the data like so:

CustomerDTO result = from c in context.Customers

where c.CustomerId == customerId

select new CustomerDTO

{

CustomerID = c.CustomerId,

Name = c.Name,

Street = c.Address.Street,

Zipcode = c.Address.ZipCode,

City = c.Address.City,

Country = c.Address.Country.Name

};

Note the use of Object Initializing and Automatic Implemented Properties that make it so much easier to write this sort of stuff.

When using this approach you keep normalization out of the entities, and you don’t lose any functionality since the data classes are POCO. You can’t obviously use the Attach and replay functionality, but to be honest it’s not a great loss.

Used this way I don’t see why LINQ to SQL would not fit in an enterprise scenario. I don’t think it’s the best solution. Other OR Mappers offer more functionality, and personally I think you’re still writing too much code that the choice of the right framework could handle for you.

#2 It is not possible to tune the SQL Server queries generated by LINQ to SQL.

This in itself is true, in my experience 90% of the queries you write off the cuff are translated to adequeate  SQL, and for the remaining queries you can decide to use stored procedures if the problem really is in the SQL that’s being generated.

 #3 LINQ to SQL generates dynamic SQL. The technology strongly motivates to use dynamic SQL instead of stored procedures.

Why are people so fond of stored procedures? In most stored procedures that I review there’s way too much business logic, and no kids, that’s not the right place for it. The only reasons for introducing stored procedures are IMHO if performance is not otherwise achievable or if the dba demands it (gotta love ‘em, the bastards).  The problem I do see is that if you do want to use a stored procedure, then you can’t use temp tables or dynamic SQL in the stored procedure, because LINQ to SQL does not allow you to map these into it’s model.

#4 LINQ to SQL encourages to a “SELECT *” query

Ehm, I don’t think LINQ to SQL handles in any other way then other OR-Mappers. So you could rephrase the statement as ‘using an OR-Mapper encourages “Select *” queries’. I don’t see it. With the introduction of anonymous types it’s now real easy to retrieve exactly the columns you’re after without having to write too much code.

#5 The cache mismatch problem. LINQ to SQL materialize your objects “once” in the cache, this can give unexpected query results.

 This is a feature, not a problem. The LINQ to SQL datacontext uses an identity map to keep track of instances of your data classes. This means that on any datacontext your data class will get instanciated only once. Where would this lead to unexpected results? Let’s take the following example

Country countryBefore = ctx.Countries.First(c => c.Iso2 == “NL”);

ctx.ExecuteCommand(“update countries set population = population + 1″);

Country countryAfter = ctx.Countries.First(c => c.Iso2 == “NL”);

Assert.IsTrue( countryAfter.Population == countryBefore.Population – 1);

 

LINQ to SQL does not support set-based operations, so for updating a whole set of records I’ve used the get-out-of-jail card here by using the ExecuteCommand method of the DataContext. (I’m hopeful that we will see some improvements in this area…).

The update statement successfully updates the population of all countries, but the Assert fails. This is because the country, as specified by the primary key ‘NL’ is already instantiated, and so countryAfter does not result in another query but gets a reference to countryBefore. This is an example where it’s maybe not immediately obvious when you’re new to the area, but the identity mapping pattern is well established and in use in similar frameworks.

So in all, if you’re a shop where it’s hard to introduce frameworks that are not labeled ’made in Redmond’ then I’d wait for the Entity Framework: it will allow for flexible mapping of your tables onto entities. If you want to start with what’s out today, make sure you’re aware of the limited support LINQ to SQL has for eager loading  See my earlier post for more details on the differences between the Entity Framework and LINQ to SQL.

The term deferred loading, sometimes also described as lazy loading. is used for the deferred loading of child objects. Suppose you are loading a customer, and the customer has a navigational property Orders. LINQ to SQL applies deferred loading by default, which means that the orders are not retrieved immediately when the customer is retrieved, but rather when you actually request the orders by enumerating over them (or bind them to a grid). LINQ to SQL will then automatically fire of a query to load the orders. 

However, in most cases this is not what you want because you of the excessive round-tripping this can cause to and from the database server. In most cases, it would be far more efficient to execute a single query and eager-load the parent object and it’s child objects with the results or that one query. As in all OR/M frameworks, LINQ to SQL offers you the possibility to specify how to load the child objects. This is done by specifying the dataload options.

First of all, you can set the default lazy loading mechanism off by specifying

ctx.DeferredLoadingEnabled = false; 

 where ctx is the instance of your DataContext class. At this point, no loading of child objects takes place at all. Next, you can specify which child objects you want to eager load 
 

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Customer>(c => c.Orders);
ctx.LoadOptions = dlo; 

This means that the customers and orders will be loaded by a single query, where the customers and orders table are queried using an inner join.

Note that this works well with the first level of child objects, but people have pointed out that LINQ to SQL will not join a third level of child objects in the same single query. Serious limitation, don’t you think?

Ofcourse, you can also execute the loading per object. Suppose you want to pass the object through a service, then it would make sense to make sure the child objects are loaded. If you have not set eager loading on the DataLoadOptions of the context, you can use the Customer.Orders.Load() method.

On a final note, you can also specify for specific attributes that you want to defer loading until such a time that you really use the value. Suppose for example you have a blob column for the customer with a picture of that customer. You will want to avoid retrieving the field unless it is really used in your UI. In the LINQ to SQL designer, specify ‘False’ for the ‘Delay Loaded’ property of that field. LINQ to SQL will then automatically fire off a separate query to retrieve the content at the last possible moment.

Here’s a few pointers on how to start learning how to use LINQ and LINQ to SQL:

• Read the relevant entries on Scott Guthries blog, right up to here.
•  There are some good introductory videos on the asp.net website.
• Download Linqpad. This is a great tool that enables you to start playing with the sample queries to familiarize yourself with the different keywords en operators.
• If you have a preference for VB over C#, check out Beth Massi’s blog. 
• If you have mastered the basics, make sure you understand how deferred execution works. Charlie Calvert has blogged about it here. 
• Find out about deferred loading, which I’ll talk about next.