How can I do this LINQ query on a condition that something not exist in a table?

Question

In our database, a file is "Processing" if the column "Pending" is set to false on the FileTable and that files FileID doesn't exist in the Transaction table.

How can I construct a LINQ query that basically says,

where f.Pending == False && (f.ID !=exist in db.Transactions.FileID)

The portion in the parenthesis is what I'm not sure how to do. I could do that in a seperate query but I know there is a way to elegantly do this in one query.

Here is what I have so far:

 public IEnumerable<PlacementFile> getProcessingPlacementFiles()
        {
            using (LinqModelDataContext db = new LinqModelDataContext())
            {
                var placementFiles = from f in db.Placement_Files
                                     where (f.Pending == false && (f.ID does not exist in db.Transactions.FileID))
                                     orderby f.Import_Date descending
                                     select f;

                return placementFiles;

            }

Is this a case where I need to do a join on the FileTable and the Transactions table? Can someone guide me a little through this?

Answer 1

Use:

where !f.Pending && !db.Transactions.Any(trans => trans.ID == f.ID)

That should work at least logically - you'll have to see whether it generated the right SQL, of course :)

Answer 2

This is a case where you want to use the Any() extension method, like so:

public IEnumerable<PlacementFile> getProcessingPlacementFiles()
{
    using (LinqModelDataContext db = new LinqModelDataContext())
    {
         return  from f in db.Placement_Files
                 where (!f.Pending && !db.Transactions
                                         .Any( t => t.FileID == f.ID)
                 orderby f.Import_Date descending                        
                 select f;
    }
}