Simple Linq Join

Question

Hello

I have 3 related tables in my employee.dbml

Emp        dept        Zone 
empId      deptID      ZoneID 
empName    deptName    ZoneType
empage                 deptID 
empzone
deptID

Now how do I get ZoneType by passing empid in a query in linQ

Please help

Answer 1

I think this should do it if I am understanding your tables correctly:

var result = (
    from e in dbContext.Emp
    join zone_join in dbContext.Zone
        on e.deptID equals zone_join.ZoneID
    where e.empId == YourEmpID
    select zone_join.ZoneType).SingleOrDefault();

Answer 2

Based on your description, you shouldn't need to join in the Dept table.

Also, this will return an IEnumerable, so if you want a single value, you will need to appropriately handle the case where this returns no results, or multiple results.

int empid = GetEmpId();

var query =
   from e in db.Emp
   join z in db.Zone on e.deptId equals z.deptId
   where e.empid == empid
   select z.ZoneType;

Answer 3

It's not clear to me the role that Dept is playing here; as described it appears that Emps can be joined to Zones without using Dept as an intermediary.

// these could be Tables in a DataContext
IEnumerable<Emp> emps;
IEnumerable<Zone> zones

var result = (from e in emps
              join z in zones on e.deptId equals z.DeptId
              where e.empId == employeeID
              select z.ZoneType).Single();

Perhaps you meant for Dept to have a column named ZoneID? Then you would say:

// these could be Tables in a DataContext
IEnumerable<Emp> emps;
IEnumerable<Dept> depts;
IEnumerable<Zone> zones;

var result = (from e in emps
              join d in depts on e.deptId equals d.deptId
              join z in zones on d.ZoneID equals z.ZoneID
              where e.empId == employeeId
              select z.ZoneType).Single();

Because of the ambiguity in your question I can not give you a definitive answer but the above should be enough to point you in the right direction. If not, please feel free to post a comment requesting clarification.