EF Core completely ignores my selected properties in select

Question

As I understand it, the following code should generate a query containing only the RouteId, RouteNo, and ShipId

        var tow = (from t in _context.AllTowData
                   where t.RouteId == id
                   orderby t.RouteNo descending
                   select new TowDefaults {
                       Id = t.RouteId,
                       TowNo = t.RouteNo,
                       ShipId = t.ShipId,
                       LastTow = t.RouteNo
                   })
            .FirstOrDefault();

However, I get:

SELECT v.route_id, v.route_no, v.tow_id, v.analysis_complete, v.checks_complete, v.cpr_id, v.date_created, v.date_last_modified, v.factor, v.fromportname, v.instrument_data_file, v.instrument_id, v.internal_number, v.mastername, v.message, v.miles_per_division, v.month, v.number_of_samples, v.number_of_samples_analysed_fully, v.prop_setting, v.route_status, v.sampled_mileage, v.serial_no_per_calendar_month, v.ship_speed, v.silk_reading_end, v.silk_reading_start, v.toportname, v.tow_mileage, v.validity, v.year
FROM view_all_tow_data AS v
WHERE v.route_id = '@__id_0'
ORDER BY v.route_no DESC
LIMIT 1

That's every column except the explicitly requested ShipId! What am I doing wrong?

This happens using both a SQL Server and a PostGres database

Answer 1

The property ShipIdis not mapped, either by a [NotMapped] annotation or a mapping instruction. As far as EF is concerned, the property doesn't exist. This has two effects:

1. EF "notices" that there's an unknown part the final Select and it switches to client-side evaluation (because it's a final Select). Which means: it translates the query before the Select into SQL which doesn't contain the ShipId column, executes it, and materializes full AllTowData entities.
2. It evaluates the Select client-side and returns the requested TowDefaults objects in which ShipId has its default value, or any value you initialize in C# code, but nothing from the database.

You can verify this by checking _context.AllTowData.Local after the query: it will contain all AllTowData entities that pass the filter.

From your question it's impossible to tell what you should do. Maybe you can map the property to a column in the view. If not, you should remove it from the LINQ query. Using it in LINQ anywhere but in a final Select will cause a runtime exception.