CODEWITHSUNDEEP
entity-frameworklinqlinq-to-sqllinq-to-entities
LP13
LP13

Reputation: 34109

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

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)

Upvotes: 0

Views: 80

Answers (2)

Slava Utesinov
Slava Utesinov

Reputation: 13488

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

Upvotes: 1

Hasan Gholamali
Hasan Gholamali

Reputation: 633

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();

Upvotes: 0

Related Questions