Can LINQ To SQL generate invalid SQL?

Question

I have two tables that I am using Linq to SQL with. The tables have a 1 to many association. The important part of the database schema is as follows:

Camera:
  Id (int)
  SerialNumber (string)
  ...

CameraCalibration
  Id (int)
  CameraFk (int)
  ...

Using LINQ to SQL I can obtain a list of all Camera Calibrations for cameras with 10 character serial numbers as follows

var query = from n in db.CameraCalibrations
    where n.Camera.SerialNumber.Length == 10
    select n

From my understanding of LINQ the following query should equally work (even if by comparision it is rather brutal...)

var query = from n in db.CameraCalibrations
where db.Cameras.Where(c => c.Id == n.CameraFk).SingleOrDefault()
                .SerialNumber.Length == 10
select n

However when I execute this second query against the database I get a SQL exception stating "Cannot call methods on nvarchar". When I look at the generated SQL it seems fairly clear why the exception is being generated:

SELECT t0.*
FROM CameraCalibration AS t0
WHERE (
    SELECT t1.serialNumber.Length
    FROM Camera AS t1
    WHERE t1.id = t0.cameraFk
    ) = 10

Notice how the generated SQL uses the expression t1.serialNumber.Length? I would have expected this to be translated into LEN(t1.serialNumber), and indeed with this change the query works.

Am I doing something wrong here? Or is this an error in the way I've structured my query and/or a limitation of LINQ to SQL?

Although I can easily restructure the query I've used to demonstrate the problem, in my real scenario that will be much harder (in part due to dynamically generated LINQ being involved).

How can I write a query similiar to the second one (with a lookup performed within the where clause) that LINQ to SQL will be happy to execute?

Answer 1

In this particular instance I think you need to join your two tables in your Linq statement. This will allow you to get an instance of the SerialNumber without using the .SerialNumber.Length association. Something like this (untested):

var query = from n in db.CameraCalibrations 
            join c in db.Cameras on c.Id equals n.CameraFk
            where c.SerialNumber.Length == 10
            select n;