none
Alternate for union or repetitive joins - Linq to SQL RRS feed

  • Question

  • I have a database with 2 tables.

    Employee (Employee ID, Employee Name, Employee Address)
    EmployeeSalary(SalaryID, Employee ID, MonthlySalary, Bonus, 401K, SalaryYear, PercentageID)
    PercentageGrowth(PercentageID, PercentageValue)

    My query requirement is to show salary, bonus and 401K as rows (val) against percentageValue.  My entry point is always through Employee table so self join gets too costly.
    The only way I could do it in Linq to SQL was by writing the same query thrice and union the results. 

    SQL interpretation for LINQ to SQL is like-

    (select  Employee ID, Bonus as Val, PercentageValue
    from
    Employee e LEFT JOIN EmployeeSAlary es
    ON e.EmployeeID = es.EmployeeID
    LEFTJOIN PERCENTAGE p on
    es.PercentageID = p.PercentageID)
    UNION
    (select  Employee ID, Salary as Val, PercentageValue
    from
    Employee e LEFT JOIN EmployeeSAlary es
    ON e.EmployeeID = es.EmployeeID
    LEFTJOIN PERCENTAGE p on
    es.PercentageID = p.PercentageID)
    UNION
    (select  Employee ID, 401K as Val, PercentageValue
    from
    Employee e LEFT JOIN EmployeeSAlary es
    ON e.EmployeeID = es.EmployeeID
    LEFTJOIN PERCENTAGE p on
    es.PercentageID = p.PercentageID)

    Since I need to create a tree in my RDL file with employee Name on rows and (Val, Percentage) on columns, there is no other alternate at this point.
    I do not like the union and redundant join part. Is there any better way??

    Developer
    Thursday, April 30, 2009 11:10 AM

Answers

  • You can do something like this:
    from e in Employees
    join es in 
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es.Bonus, pg = es.PercentageGrowth.PercentageValue })
    .Concat(
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es.MonthlySalary, pg = es.PercentageGrowth.PercentageValue }))
    .Concat(
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es._401K, pg = es.PercentageGrowth.PercentageValue }))
    on e.EmployeeID equals es.EmployeeID into eSal
    select new 
    { 
      e.EmployeeID, eSal
    }
    Thursday, April 30, 2009 12:50 PM

All replies

  • You can do something like this:
    from e in Employees
    join es in 
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es.Bonus, pg = es.PercentageGrowth.PercentageValue })
    .Concat(
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es.MonthlySalary, pg = es.PercentageGrowth.PercentageValue }))
    .Concat(
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es._401K, pg = es.PercentageGrowth.PercentageValue }))
    on e.EmployeeID equals es.EmployeeID into eSal
    select new 
    { 
      e.EmployeeID, eSal
    }
    Thursday, April 30, 2009 12:50 PM
  • You can do something like this:
    from e in Employees
    
    join es in 
    
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es.Bonus, pg = es.PercentageGrowth.PercentageValue })
    
    .Concat(
    
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es.MonthlySalary, pg = es.PercentageGrowth.PercentageValue }))
    
    .Concat(
    
    EmployeeSalaries.Select( es => new {es.EmployeeID, Val = es._401K, pg = es.PercentageGrowth.PercentageValue }))
    
    on e.EmployeeID equals es.EmployeeID into eSal
    
    select new 
    
    { 
    
      e.EmployeeID, eSal
    
    }

    Nice and neat implementation...
    Developer
    Friday, May 1, 2009 12:50 PM