Linq IN statement to exclude from a list

I have two tables

Contracts

Id | StartDate | EndDate

ExcludedContracts

Id | ContractId

I am using the following statements to get both sets of data:

            var excludedContracts = from Excluded in 
            DataContext.ExcludedTransportContracts
                                    select Excluded;开发者_JAVA技巧

            // Get a collection of all live sites first
            var liveContracts = from Contracts in DataContext.Contracts
                                where Contracts.EndDate > DateTime.Now
                                select Contracts;

I need to select all contracts that don't have a record in the ExcludedContracts table. I've been battling with WHERE queries for a while but had no luck.

How do I do a query similar as I would an instatement IN(1,2,3) SQL?

Thanks!

I don't know which query provider you're using, but if that query provider supports the Contains method you can use this:

//get all contracts that haven't been excluded
var nonExcludedContacts = liveContracts.Where(l => !excludedContracts.Contains(l));

If that method is not supported, you can use the Any method instead. It's not as readable but it gets the job done:

//get all contracts that haven't been excluded
var nonExcludedContacts = liveContracts.Where(l => !excludedContracts.Any(e => e.ContractId == l.Id));

Are the two tables not related at the data level? Ie a Contract object having a list of ExcludedTransportContracts as a property?

From the look of the data this is probably how they should be related, then you can do something like var liveContract = DataContext.Contracts.Where(c => c.ExcludedTransportContracts.Count() == 0 && c.EndDate > DateTime.Now )

Was off the top of my head so the code may need slight alterations.

the equivilent of in in LINQ is contains http://msdn.microsoft.com/en-us/library/system.linq.enumerable.contains.aspx however you should also look at linq except method http://msdn.microsoft.com/en-us/library/system.linq.enumerable.except.aspx

Update: as mentioned those are the IEnumerable ones so should be http://msdn.microsoft.com/en-us/library/bb341422.aspx for contains and http://msdn.microsoft.com/en-us/library/bb348938.aspx for except which are for Queryable

Try something like this. Obviously, if you use the "Contains" method, you should compare similar datatypes/ objects.

The compiler probably won't compile code when using implicit typed variables in a contains function, but I'm not sure about that.

List<Contracts> excludedContracts = 
            (from Excluded in DataContext.ExcludedTransportContracts
             select Excluded).toList();

List<Contracts> liveContracts = 
    (from Contracts in DataContext.Contracts
     where Contracts.EndDate > DateTime.Now &&
            !excludedContracts.Contains(Contracts)
     select Contracts).toList();

you can use the Enumerable.Contains() function for this task.