Linq to SQL How to write "not-in" query

Question

I have the following 3 tables ( 1 base table and other 2 sattelite tables for each vehicle type.)

Vehicles

ID      VehicleType
-----------------------------
1       Car
2       Truck

Cars

ID  Make    Model   
-------------------------
1   Toyota  Camry   
2   Honda   Accord  

Trucks

ID  Make    Model   
--------------------
1   Ford    F150    
2   Dodge   Ram     

Then i have corresponding DTO

public class VehicleDTO
{
    public int ID {get;set;}
    public int VehicleType {get;set;}
    public IEnumerable<CarDTO> Cars {get;set;}
    public IEnumerable<TruckDTO> Trucks {get;set;}
}

public class CarDTO
{
    public int ID {get;set;}
    public string Make {get;set;}
    public string Model {get;set;}  
}

public class TruckDTO
{
    public int ID {get;set;}
    public string Make {get;set;}
    public string Model {get;set;}  
}

Then i have list of Vehicle DTO as an argument to the method. I want find vehicles from DTO list that does not exists in the database by matching Make and Model for that Vehicle Type. Idea is to then insert the missing vehicles into database.

I have the following query

public void FindMissingVehicles(IEnumerable<VehicleDTO> dtos)
{

         var cars = (from dto in dtos
                where !(from c in dbcontext.Cars
                        select new { c.Make, c.Model })
                        .Any(x => dto.VehicleType == 'Car' && dto.Car.Make == x.Make && dto.Car.Model == x.Model)
                select dto).ToList();

 var trucs = (from dto in dtos
                where !(from t in dbcontext.Trucks
                        select new { t.Make, t.Model })
                        .Any(x => dto.VehicleType == 'Truck' && dto.Truck.Make == x.Make && dto.Truck.Model == x.Model)
                select dto).ToList();

    //insert missing cars and trucks into db here

}

The query above throws exception

Message "Non-static method requires a target." string

Questions

1> How do i construct this query.

2> Can i make this query async by using AnyAsync and ToListAsync. (I know i have to make method async with Task, and use await inside howevere i could not figure out the async query syntax)

Answer 1

Moreover, your approach has performance issue - you perform N queries - one for each of dto, instead of doing only two queries: one for cars and one for trucks:

var allCars = dtos.Where(x => x.VehicleType == "Car").ToList()
             .SelectMany(x => x.Cars.Select(y => y.Make + "-" + y.Model).ToList()).ToList();

var existedCars = await dbcontext.Cars.Where(x => allCars.Contains(x.Make + "-" + x.Model))
             .Select(x => x.Make + "-" + x.Model).ToListAsync();

var newCars = allCars.Except(existedCars).Select(x => 
{
    var temp = x.Split('-');
    return new CarDTO 
    {
        Make = temp[0],
        Model = temp[1] 
    };
}).ToList(); 

//exactly same code for Trucks