One common argument for O/RM adn also for LINQ is that developers shouldn't need to learn SQL and just be able to work with the objects. After working on a project where we use LINQ to SQL every day since January, I don't always feel that everything became so much easier. This could of course still be that I have 10+ year experience from SQL, but only 1 year with LINQ. Anyway I will in this post give two of examples of issues we have had (and maybe someone could teach me a better/correct way :).

1. I find the syntax to get an LEFT OUTER JOIN to be more difficult both to write and read in LINQ to SQL. Example in LINQ to SQL:

from p in DataContext.Persons
join ewl in DataContext.EmploymentWorkLocations
      on p.Id equals ewl.EmployeeId into temp
from worklocations in temp.DefaultIfEmpty()

2. My next example is also related to getting a LEFT OUTER JOIN. The picture below is from our LINQ to SLQ diagram. The CompanyId is not nullable for Employee because it is required for employees.

When getting employees we also want to get the Company that the Employee belongs to so we have a DataLoadOptions with a LoadWith that uses the relation between Employee and Company. The problem though is, if we have this relation and makes queries (for examples searches) which can result in both Employees and Persons, we only get Employees. The reason is that with the load option described the resulting SQL is an INNER JOIN between the person table and company table. LINQ to SQL only creates LEFT OUTER JOIN for child objects and for parent objects that are nullable. This behavior for parent objects is understandable, but the effect is not so good when working with inheritance.

This is partly discussed in this forum thread (not covering inheritance case though). The only solution we have managed to do is allowing CompanyId to be nullable. From an OO perspective we would never had done this, but it seems to be a limitation in LINQ to SQL. We have tried to use AssociateWith and AssociateWith in combination with LoadWith, but we always get NotSupportedException, when going down this road.