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)

Slava Utesinov · Accepted Answer · 2018-06-25 09:21:03Z

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

edited Jun 25, 2018 at 9:21

answered Jun 25, 2018 at 6:56

Slava Utesinov

13.5k2 gold badges22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hasan Gholamali · Accepted Answer · 2018-06-23 08:30:17Z

This is because of you can't have nested query in linq when one table is from dbContext and the other one is from in-memory enumerable, so if dbcontext.Cars and dbcontext.Trucks don't have a lot of rows, it's a good idea to load them in memory and use nested query like below:

var listCars = dbcontext.Cars.ToList();
var listTrucks = dbcontext.Trucks.ToList();

var cars = (from dto in dtos
            where !(from c in listCars
                    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 listTrucks
                    select new { t.Make, t.Model })
                    .Any(x => dto.VehicleType == 'Truck' && dto.Truck.Make == x.Make && dto.Truck.Model == x.Model)
            select dto).ToList();

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related