LINQ-to-SQL Joins

I have a table called Payroll. Payroll can have many PayStubs. In other words there is table called PayStub that is a child entity of Payroll. PayStub has a child Entity called PayrollTax. I want to write a LINQ-to-SQL query that gets all payrolls which have more than one Payroll Tax. I use the following query:

public IList<Payroll> GetPayrollsWithPayrollTaxes()
        {
            return (from payroll in ActiveContext.Payrolls
                    join payStub in ActiveContext.PayStubs on payroll.PayrollID equals payStub.PayrollID
                    where payStub.InternalPayrollTaxes.Count > 0
                    select payroll
                    ).ToList();
        }

The problem开发者_如何学JAVA is since there is a one to many relationship between Payroll and PayStub, I end up getting the same Payroll twice. I want a unique list of Payrolls.

Any ideas and suggestions are appreciated!

I think using Any (EXISTS in SQL) might help you here.

public IList<Payroll> GetPayrollsWithPayrollTaxes()
{
      return (from payroll in ActiveContext.Payrolls
              where ActiveContextPayStubs.Any(payStub => 
                 payroll.PayrollID == payStub.PayrollID && 
                 payStub.InternalPayrollTaxes.Any())
              select payroll
              ).ToList();
        }

Have you tried .Distinct().ToList() ?

Or you could add an into after the join like this:

(from payroll in ActiveContext.Payrolls
 join payStub in ActiveContext.PayStubs on payroll.PayrollID equals payStub.PayrollID into payStubGroup
 where payStubGroup.Any(p => p.InternalPayrollTaxes.Any())
 select payroll
 ).ToList();