The Drawbacks of Linq-to-SQL for Object Relational Mapping

Prove you're a Dev Guru! Take the test now!

Object-Relational Mapping (ORM) is a programming methodology used for converting data between object-oriented programming languages and relational databases.

Every object has an identity, state and behaviour in addition to data, whereas relational databases stores only data. Hence the role of object relational mapper, a software tool used to map data between the two. ORM techniques come in very handy when developing applications.

We were involved in a recent project that used Linq-to-SQL as an OR Mapper. We found some drawbacks that we thought we should share with other developers. Here is the list of the five major problems we faced during the development.

1. Linq-to-SQL does not have the ability to handle 'many-to-many' relationships. This is a necessary pre-requisite for an OR Mapper.

2. Batch fetching, insertion and updating features are not available in Linq-to-Sql. We required a batch process, when we need to update/insert multiple rows at a time.

3. Linq-to-Sql does not have the ability to map your own value objects. So instead of a reusable class like this:

public class MyClass

{ ...

public NewClass NewClass { get; set; }

... }

We need to do this:

public partial class MyClass

{ ...

partial void OnNewClassChanging(string value)

{

_myValidator.ValidateValue(value);

} ...

}

4. The generated T-SQLs (Transact-SQLs) created by the Linq-to-SQL OR-Mapper are very complex. For instance, the query generated for fetching a user having the state name ‘%%’ looks like this:

SELECT [y0].[emp_id], [y0].[name], [y0].[address], [y0].[state]

FROM [dbo].[User] AS [y0]

WHERE (

(CASE

WHEN (DATALENGTH(@p0) / 2) = 0 THEN 0

ELSE CHARINDEX(@p0, [y0].[firstname]) - 1

END)) > @p1

5. Linq-to-SQL is table-driven, so it imposes database design onto your entity model. It doesn't give you the option to model your entity in code, in order to get features that are not available in relational table models.

Linq-to-SQL wasn't the right tool for the ORM requirements of that project, but we'd still recommend giving it a try, as the shortcomings we faced won't necessarily apply to your projects.