LINQ to SQL Finding objects not listed in another list

Question

I am programming a data entry application for a vet and I've followed many of the principles used in the MVC Nerd Diner application. I'm 90% finished with my app, but trying to clean up an annoying issue:

Currently when a user adds a pet to an invoice, I have a dropdown that lists all the customers current pets - whether or not they are already on the invoice. I'd like it to only include the customer's pets NOT already on the invoice so they can't add the same pet more than once.

Following the Nerd Dinner tutorial application I have a CustomerRepository.cs file with the following query:

// Finds list of a customer's pets
    public IQueryable<Pet> FindCustomerPets(int id)
    {
        return from pet in db.Pets
               where pet.CustomerID == id
               select pet;
    }

If a pet is already on a visit, there will be a visit detail record for it. So... What I'm trying to write is a query that will only take the pet if there is no visit detail for it. Here's what I'm working with:

public IQueryable<Pet> FindPetsNotOnVisit(int id)
{

    Visit visit = GetVisit(id);
    var pets = FindCustomerPets(visit.CustomerId);

    var visitDetails = from visitDetail in db.VisitDetails
                       where visitDetail.VisitID == id
                       select visitDetail;

    //What do I need to do to remove where pet.PetID == visitDetail.PetID?    
}

Answer 1

pets.Where(p=>!visitDeails.Any(v=>v.PetID==p.PetID));

Notepad code.

Answer 2

Not sure if I got your domain right, but try something like this:

public IQueryable<Pet> FindPetsNotOnVisit(int id)
{
    Visit visit = GetVisit(id);
    var pets = FindCustomerPets(visit.CustomerId);

    var petIds = from visitDetail in db.VisitDetails
                 where visitDetail.VisitID == id
                 select visitDetail.PetID;

    return pets.Where(p => !petIds.Contains(p.PetID));
}

You could end up with not-so-pretty SQL statements. Use SQL Server Profiler to figure out exactly how to go about it.

Answer 3

from pet in pets
join visit in db.VisitDetails
on pet.id equals visit.petid into g
where !g.Any()
select pet