Linq to entities syntax help, when having two tables

Expected result:

I would like to have a IQueryable<Vehicle>, which contains vehicles which is not assigned to anyone.

Data:

I have two tables:

Vehicle

int id
nvarchar name
DateTime deletedAt

AssignedToVehicle

int userId
int vehicleId
DateTime unassignedAt

What I've tried:

query = context.Vehicle.Where(v => v.DeletedAt == null && 
            !v.AssignedToVehicle.Where(a=>a.VehicleId == v.Id && a.UnassignedAt != null).Any());

My plan was to leave out all deleted vehicles with v.DeletedAt == null and then look in AssignedToVehicle to see if it had any rows with this vehicle. If not then I want it on my list.

What are my options here to get a list of unassigned vehicles?

Solution

So you have a table with Vehicles and VehicleAssignments, with a one-to-many relation between Vehicles and VehicleAssignments: every Vehicle has zero or more VehicleAssignments; every VehiclAssignment belongs to exactly one Vehicle, namely the one that the foreign key VehicleId refers to.

(I slightly changed your names, so I can use plural nouns and singular nouns to easily refer to tables and rows in the tables)

As you are using entity framework you'll have classes similar to the following:

class Vehicle
{
    public int Id {get; set;}
    public string Name {get; set;}
    public DateTime? DeletedAt {get; set;}  // null if not deleted yet

    // every Vehicle has zero or more VehicleAssignments (one-to-many)
    public virtual ICollection<VehicleAssignement> VehicleAssignements {get; set;}
}

class VehicleAssignment
{
    public int Id {get; set;}
    public string Name {get; set;}
    public DateTime? DeletedAt {get; set;}  // null if not deleted yet

    // every VehicleAssignement belongs to exactly one Vehicle, using foreign key
    public int VehicleId {get; set;}
    public virtual Vehicle Vehicle {get; set;}
}

This is enough for entity framework to detect your one-to-many relationship. If you want, you can add attributes or use fluent API, but that's only necessary if you want to deviate from the standards.

I would like to have a IQueryable, that contains vehicles that are not assigned to anyone.

Or to be more precise: you want a query that returns all Vehicles that are not deleted (= have a value for property DeletedAt equal to null) AND that have no not deleted VehicleAssignments.

Or in normal language: you want all Vehicles that have at least one Assignment. Both the Vehicle and Assignment may not be deleted.

There are two methods for this:

The easy one: use the virtual ICollection<...>. Entity framework knows your one-to-many relationship and will create the proper GroupJoin for you
Do the GroupJoin yourself.

Use virtual ICollection

IQueryable<Vehicle> assignedVehicles = dbContext.Vehicles
    .Where(vehicle => vehicle.DeletedAd == null
        && vehicle.VehicleAssignements
            .Where(vehicleAssignment => vehicleAssignment.DeletedAdd == null)
            .Any());

In words: from the collection of Vehicles, keep only those Vehicles that are not deleted yet, and that have at least one VehicleAssignment that is not deleted yet.

You could also start at VehicleAssignments:

IQueryable<Vehicle> assignedVehicles = dbContext.VehicleAssignments
    .Where(assignment => assignment.DeletedAt == null)
    .Select(assignment => assignment.Vehicle)
    .Where(vehicle => vehicle.DeletedAt == null)
    .Distinct();

In words: from the collection of VehicleAssignments, keep only those assignments that are not deleted yet. From each of the remaining VehicleAssignments select its one and only Vehicle. Result: a sequence of Vehicles. From this sequence keep only those that are not deleted and remove duplicates.

Do the (Group)Join yourself

In small steps:

IQueryable<Vehicle> nonDeletedVehicles = dbContext.Vehicles
    .Where(vehicle => vehicle.DeletedAd == null);
IQueryable<VehicleAssignments> nonDeletedAssignments = dbContext.VehiclAssignments
    .Where(vehicleAssignment => vehicleAssignment.DeletedAdd == null);

IQueryable<Vehicle> vehiclesWithAnyAssignment = nonDeletedVehicles
.GroupJoin(noneDeletedAssignments,

vehicle => vehicle.Id,                 // from each Vehicle take the primary key
assignment => assignment.VehicleId,    // from each Assignment take the foreign key

// parameter resultSelector: use each vehicle, with its zero or more assignments,
// to make one new:
(vehicle, assignmentsOfThisVehicle) => 

      // if there is at least one assignment: take the vehicle, otherwise take null
      assignmentsOfThisVehicle.Any() ? vehicle : (Vehicle)null)

// remove all nulls (= vehicles that didn't have an assignment)
.Where(vehicle => vehicle != null);

You can also do a simple join, which will automatically remove the Vehicles that are not assigned. You'll have to remove the duplicates:

var result = nonDeletedVehicls.Join(nonDeletedAssignments,

    vehicle => vehicle.Id,                    // primary key
    assignment => assignment.VehicleId,       // foreign key

    // parameter resultSelector: whenever you find a matching [vehicle, assignment]
    // combination, keep the Vehicle, because this Vehicle has at least one Assignment
    (vehicle, assignment) => vehicle)

    // remove Duplicates (= Vehicles with several assignments)
    .Distinct(),