Using lambda expressions to return value from 1table while it compares value from 2 tables

Question

I didn't know what to put in the Title to be more precise but I will explain my question/problem.

I'm doing an API and writing a method that gets me all data inside a table.

Example:

Table1 = Department
ID_Department
Name_Department

Table 2 = Employees Id_Employee Name_Employee ID_Department ...

I'm trying to write a lambda expression that gets me all the data from employee through the Name_Department I'm passing in the program to the API. where ID_department in table 1 equals ID_Department in table 2 without knowing the ID_department itself.

To make it more understandable here is an example query (made from head since the attributes and tables are not these).

        SELECT EMPLOYEE.ID_EMPLOYEE, EMPLOYEE.NAME_EMPLOYEE, (OTHER EMPLOYEE ATTRIBUTES), DEPARTMENT.NAME_DEPARTMENT
        FROM EMPLOYEE INNER JOIN DEPARTMENT ON 
        EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID_DEPARTMENT
        WHERE DEPARTMENT.NAME_DEPARTMENT LIKE '%FINANCE%'

Thanks in advance

Not EntityFramework? If you have no ORM in place then you cannot efficiently use lambda expressions to query a database, unless you bring back all the records, populate some sort of dataset and then use lambda expression on that dataset... pretty nasty though I think... better to do the lamda functionality in the sql itself. — Paul Zahra, Jul 24 '14 at 11:21
Yes I have a model of the Database using entity Framework, dataset is already filled by using a query (Select * from Employees) — Jorge, Jul 24 '14 at 11:25

score 0 · Answer 1 · answered Jul 24 '14 at 12:06

Not sure why you want to use lambda expressions on a dataset... something like the following is generally better... (By the way this is called Linq To Entities or L2E, no lambda expressions required)

using (DBContext ctx = new DBContext())
{
    ctx.Connection.Open();

    List<EMPLOYEE> employees = (from e in ctx.EMPLOYEE 
                                join d in ctx.Department on e.IdDepartment equals d.IdDepartment
                                where d.NameDepartment.Contains("FINANCE")
                                select e).ToList();

    ctx.Connection.Close();
}

score 0 · Answer 2 · answered Jul 24 '14 at 12:40

I've written you a LinqPad Example that will show how this will work (LinqPad is a free download, you just need to paste the code into the query window and change the language dropdown to 'C# Program')

public class Department
{
    public int DepartmentId {get; set;}
    public string Name {get; set;}
    public List<Employee> Employees {get; set;}
    public Department()
    {
        Employees = new List<Employee>();
    }
}

public class Employee
{
    public int EmployeeId {get; set;}
    public string Name {get; set;}
    public Department Department {get; set;}
}

void Main()
{
    // set up replica data (much like any ORM would return)  
    List<Department> Departments = new List<Department>();
    List<Employee> iTEmployees = new List<Employee>();
    List<Employee> salesEmployees = new List<Employee>();

    Department iT = new Department(){ Name = "IT", DepartmentId = 1 };
    iTEmployees.Add(new Employee(){EmployeeId = 1, Name = "Jo",  Department = iT});
    iTEmployees.Add(new Employee(){EmployeeId = 2, Name = "Jim",  Department = iT });
    iTEmployees.Add(new Employee(){EmployeeId = 3, Name = "James",  Department = iT });
    iT.Employees = iTEmployees;
    Departments.Add(iT);

    Department sales = new Department(){ Name = "Sales", DepartmentId = 1 };
    salesEmployees.Add(new Employee(){EmployeeId = 1, Name = "Jo",  Department = sales});
    salesEmployees.Add(new Employee(){EmployeeId = 2, Name = "Jan",  Department = sales });
    salesEmployees.Add(new Employee(){EmployeeId = 3, Name = "Jane",  Department = sales });
    sales.Employees = salesEmployees;
    Departments.Add(sales);

    // query objects
    string searchParam = "IT";

    List<Employee> employees = /*dataContext.*/Departments.SelectMany(x=>x.Employees.Where(y=>y.Department.Name.ToLowerInvariant() == searchParam.ToLowerInvariant())).ToList();
    employees.Dump();
}

Hope that helps you

Hi, thanks for this complete post, but I'm having a problem, I can't seem to use my context, since I'm using vs2010 i had to use ef5 dbcontext to make one and when i try to call its name it doesn't show. Am I missing something? should i use some "using" reference? Thanks — Jorge, Jul 24 '14 at 13:45
dbContext isn't called dbContext by default, you must new one up (in a sensible place) doing something like 'var dbContext = new MyDatabaseEntities();'. The MyDatabaseEntities is a name you set up when creating your EDMX (EF mapping file). If you can't remember what you named it then look in your Web.Config/App.Config or the designer file under your EDMX — Humble Rumble, Jul 25 '14 at 07:33

Using lambda expressions to return value from 1table while it compares value from 2 tables

2 Answers2