Can LINQ To SQL generate invalid SQL?

Question

I have two tables with which I use Linq to SQL. Tables have an association of 1 to many. An 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 get 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 work the same way (even if compared it is rather cruel ...)

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 to the database, I get an SQL exception that says: "Unable to call methods on nvarchar." When I look at the generated SQL, it seems pretty clear why the exception is thrown:

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

, SQL t1.serialNumber.Length? , LEN (t1.serialNumber), .

- ? , / LINQ to SQL?

, , ( - LINQ).

, ( , where), LINQ to SQL?

Answer 1

, , Linq. SerialNumber .SerialNumber.Length. - (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;