Entity Framework and large queries. What's practical?

Question

I'm from old school where DB had all data access encapsulated into views, procedures, etc. Now I'm forcing myself into using LINQ for most of the obvious queries.

What I'm wondering though, is when to stop and what practical? Today I needed to run query like this:

SELECT D.DeviceKey, D.DeviceId, DR.DriverId, TR.TruckId, LP.Description
FROM dbo.MBLDevice D
LEFT OUTER JOIN dbo.DSPDriver DR ON D.DeviceKey = DR.DeviceKey
LEFT OUTER JOIN dbo.DSPTruck TR ON D.DeviceKey = TR.DeviceKey
LEFT OUTER JOIN 
    (
    SELECT LastPositions.DeviceKey, P.Description, P.Latitude, P.Longitude, P.Speed, P.DeviceTime 
    FROM dbo.MBLPosition P
    INNER JOIN 
    (
        SELECT D.DeviceKey, MAX(P.PositionKey) LastPositionKey 
        FROM dbo.MBLPosition P
        INNER JOIN dbo.MBLDevice D ON P.DeviceKey = D.DeviceKey
        GROUP BY D.DeviceKey
    ) LastPositions ON P.PositionKey = LastPositions.LastPositionKey 
    ) LP ON D.DeviceKey = LP.DeviceKey
WHERE D.IsActive = 1

Personally, I'm not able to write corresponing LINQ. So, I found tool online and got back 2 page long LINQ. It works properly-I can see it in profiler but it's not maintainable IMO. Another problem is that I'm doing projection and getting Anonymous object back. Or, I can manually create class and project into that custom class.

At this point I wonder if it is better to create View on SQL Server and add it to my model? It will break my "all SQL on cliens side" mantra but will be easier to read and maintain. No?

I wonder where you stop with T-SQL vs LINQ ?

EDIT

• Model description.
• I have DSPTrucks, DSPDrivers and MBLDevices.
• Device can be attached to Truck or to Driver or to both.
• I also have MBLPositions which is basically pings from device (timestamp and GPS position)

What this query does - in one shot it returns all device-truck-driver information so I know what this device attached to and it also get's me last GPS position for those devices. Response may look like so:

There is some redundant stuff but it's OK. I need to get it in one query.

Answer 1

In general, I would also default to LINQ for most simple queries.

However, when you get at a point where the corresponding LINQ query becomes harder to write and maintain, then what's the point really? So I would simply leave that query in place. It works, after all. To make it easier to use it's pretty straight-forward to map a view or cough stored procedure in your EF model. Nothing wrong with that, really (IMO).