I have a simple SQL query like this (SQL Server)

update fcfv
set    fcfv.DateTriggerSatisfied = getdate()
from   ford.tblFordCompoundFlowVehicle fcfv
where  fcfv.CompoundVehicleID = 12836
and    fcfv.FordFlowTriggerID = 2

that I translated into this

 var updateVehicleIncoming = from fcfv in _context.GetTable<TblFordCompoundFlowVehicle>()
                             where fcfv.CompoundVehicleID == compoundVehicleID
                             && fcfv.FordFlowTriggerID == TRIGGER_OUTGOING
                             && fcfv.DateTriggerSatisfied == null
                             && fcfv.DateSend == null
 count = updateVehicleIncoming.Set(p => p.DateTriggerSatisfied, DateTime.Now).Update();

But now I need to add an extra check that in SQL I solved easy with a subquery like this

update fcfv
set    fcfv.DateTriggerSatisfied = getdate()
from   ford.tblFordCompoundFlowVehicle fcfv
where  fcfv.CompoundVehicleID = 12836
and    fcfv.FordFlowTriggerID = 2

and    0 = ( select count(1) 
             from   ford.tblFordCompoundFlowVehicle fcfv2
             where  fcfv2.CompoundVehicleID = fcfv.CompoundVehicleID
             and    fcfv2.SortOrder < fcfv.SortOrder
             and    fcfv2.DateTriggerSatisfied is null
           )

But I can’t figure out how to translate this into Linq2DB

I tried this

var updateVehicleIncoming = from fcfv in _context.GetTable<TblFordCompoundFlowVehicle>()
                            where fcfv.CompoundVehicleID == compoundVehicleID
                            && fcfv.FordFlowTriggerID == TRIGGER_OUTGOING
                            && fcfv.DateTriggerSatisfied == null
                            && fcfv.DateSend == null

                            && 0 == (from fcfv2 in _context.GetTable<TblFordCompoundFlowVehicle>()
                                   where fcfv2.CompoundVehicleID == fcfv.CompoundVehicleID
                                   && fcfv2.SortOrder < fcfv.SortOrder
                                   && fcfv2.DateTriggerSatisfied == null
                                   select fcfv2).ToList().Count
                               
                            select fcfv;
count = updateVehicleIncoming.Set(p => p.DateTriggerSatisfied, DateTime.Now).Update();

but it give this error at runtime

LinqToDB.Linq.LinqException: ”value(gttAPIServer.Repositories.Ford.HandleFordFlowRepository)._context.GetTable().Where(fcfv2 => (((fcfv2.CompoundVehicleID == fcfv.CompoundVehicleID) AndAlso (fcfv2.SortOrder < fcfv.SortOrder)) AndAlso (fcfv2.DateTriggerSatisfied == null))).ToList().Count()’ cannot be converted to SQL.’

I am struggling to find/understand the correct documentation but I am having troubles with that.

My question is what am I doing wrong, how can I make this Linq2DB to do the same as the SQL code does ?