CODEWITHSUNDEEP
c#winformslinq-to-sqlc#-4.0
Sealer_05
Sealer_05

Reputation: 5566

Linq2sql join query to show list of names but only show ones that have record in another table

I have an employee table and I am currently filling the combo box with the names in that table. I also have related table called expenseHdr that contains the employee pk. My table structure looks like this:

employees     expenseHdr
empPk         expPk
name          expenseType
              empPk

Right now it is filtering the names but it is repeating the name for each record it has in the expenseHdr. How would I get it to only display each name once? Thanks!

var emps = (from emp in db.employees
                        join exp in db.expenseHdrs on emp.pk equals exp.empPk
                        where emp.active == true 
                        orderby emp.name
                        select emp.name.Substring(0, 20)).ToList();
            cboEmployee.DataSource = emps;

Upvotes: 0

Views: 79

Answers (1)

Magnus
Magnus

Reputation: 46977

You can use Any()

var emps = (from emp in db.employees
                        where emp.active && emp.expenseHdrs.Any()
                        orderby emp.name
                        select emp.name.Substring(0, 20)).ToList();
            cboEmployee.DataSource = emps;

If there is no db relation between the tables you would instead write:

where emp.active && db.expenseHdrs.Any(exp => emp.pk == exp.empPk)

Upvotes: 2

Related Questions