Using lookup values in linq to entity queries

Question

I'm a total LINQ noob so I guess you'll probably have a good laugh reading this question. I'm learning LINQ to create queries in LightSwitch and what I don't seem to understand is to select an entity based on a value in a lookup table. Say I want to select all employees in a table that have a job title that is picked from a related lookup table. I want the descriptive value in the lookup table for the user to pick from a list to use as a parameter in a query, not the non-descriptive id's.

Can someone point me to an article or tutorial that quickly explains this, or give me a quick answer? I AM reading books and have a Pluralsight account but since this is probably the most extensive knowledge I will need for now a simple tutorial would help me more that watching hours of videos and read thousands of pages of books.

Thanks in advance!

Edit: this is the code. As far as I know this should but won't work (red squigly line under EmployeeTitle, error says that EmployeeContract does not contain a definition for EmployeeTitle even though there is a relationship between the two).

partial void ActiveEngineers_PreprocessQuery(ref IQueryable<Employee> query)
    {
        query = from Employee e in query
                where e.EmployeeContract.EmployeeTitle.Description == "Engineer"
                select e;
    }

Edit 2: This works! But why this one and not the other?

    partial void ActiveContracts_PreprocessQuery(ref IQueryable<EmployeeContract> query)
    {
        query = from EmployeeContract e in query
                where e.EmployeeTitle.Description == "Engineer"
                select e;
    }

Previously you were returning an `Employee`, you are now returning an `EmployeeContract`. If you want to return an employee, see my answer below. — Smudge202, Sep 26 '11 at 13:31

score 0 · Answer 1 · answered Sep 26 '11 at 03:43

Try something like this:

partial void RetrieveCustomer_Execute()
{
    Order order = this.DataWorkspace.NorthwindData.Orders_Single
        (Orders.SelectedItem.OrderID);

    Customer cust = order.Customer;
    //Perform some task on the customer entity.

}

(http://msdn.microsoft.com/en-us/library/ff851990.aspx#ReadingData)

score 0 · Answer 2 · answered Sep 26 '11 at 04:35

Assuming you have navigation properties in place for the foreign key over to the lookup table, it should be something like:

var allMonkies = from employee in context.Employees
                 where employee.EmployeeTitle.FullTitle == "Code Monkey"
                 select employee;

If you don't have a navigation property, you can still get the same via 'manual' join:

var allMonkies = from employee in context.Employees
                 join title in context.EmployeeTitles
                     on employee.EmployeeTitleID equals title.ID
                 where title.FullTitle == "Code Monkey"
                 select employee;

score 0 · Accepted Answer · answered Sep 26 '11 at 13:23

0

The red squiggly line you've described is likely because each Employee can have 1-to-many EmployeeContracts. Therefore, Employee.EmployeeContracts is actually an IEnumerable<EmployeeContract>, which in turn does not have a "EmployeeTitle" property.

I think what you're looking for might be:

partial void ActiveEngineers_PreprocessQuery(ref IQueryable<Employee> query) 
{ 
    query = from Employee e in query 
            where e.EmployeeContract.Any(x => x.EmployeeTitle.Description == "Engineer") 
            select e; 
}

What this is saying is that at least one of the Employee's EmployeeContracts must have an EmployeeTitle.Description == "Engineer"

answered Sep 26 '11 at 13:23

Smudge202

4,689
2
26
44

That did it. Lamda expressions freak me out but I see it's mandatory to know how to work with them. I'll keep studying. Thanks very much for your help. – Willem Sep 26 '11 at 14:33
Lambda is amazingly powerful so it's definitely worth practicing/learning. Glad I could help. Good luck. – Smudge202 Sep 26 '11 at 14:37

Using lookup values in linq to entity queries

3 Answers3