none
Outer join with associations RRS feed

  • Question

  • Northwind database has Employees table containing 3 employees:

    EmployeeID FirstName  ReportToEmployee
    1          Andrew         NULL
    2          Nancy          1
    3          Bob            2


    I need to create list of employee and its manager first names *including*
    employee Andrew. For Andrew, first name can be null or empty string.:

    Andrew  NULL
    Nancy  Andrew
    Bob  Nancy


    I tried

    Code Snippet
    Northwind db = CreateDB();
    var query = from e in db.Employees
             select new        {
              Name = e.FirstName,
              ReportsTo = e.ReportsToEmployee.FirstName
             };
    var list = query.ToList();

     

     


    But this returns only 2 members, without Andrew.
    How to return 3 entities, including Andrew ?

    Andrus.

    Employee class is defined as

    Code Snippet
     [Table(Name = "employees")]
     public partial class Employee {

    [AutoGenId]
    private int employeeID;

    [Column(Storage = "employeeID", Name = "employeeid", DbType =
    "integer(32,0)", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull =
    false, Expression = "nextval('employees_employeeid_seq')")]
    public int EmployeeID  {
       get
       {
        return employeeID;
       }
       set
       {
        if (value != employeeID)
        {
         employeeID = value;
        }
       }
    }

      private string firstName;
      [Column(Storage = "firstName", Name = "firstname", DbType = "character
    varying(10)", CanBeNull = false, Expression = null)]
      public string FirstName
      {
       get
       {
        return firstName;
       }
       set
       {
        if (value != firstName)
        {
         firstName = value;
        }
       }
      }

      private int? reportsTo;
      [Column(Storage = "reportsTo", Name = "reportsto", DbType =
    "integer(32,0)", Expression = null)]
      public int? ReportsTo  {
       get
       {
        return reportsTo;
       }
       set
       {
        if (value != reportsTo)
        {
         reportsTo = value;
        }
       }
      }

      [Association(Storage = null, OtherKey = "EmployeeID", Name =
    "fk_emp_reportstoemp")]
      public EntityMSet Employees  {
       get
       {
        return null;
       }
      }

      [Association(Storage = null, OtherKey = "EmployeeID", Name =
    "employeeterritories_employeeid_fkey")]
      public EntitySet EmployeeTerritories  {
       get
       {
        return null;
       }
      }

      [Association(Storage = null, OtherKey = "EmployeeID", Name =
    "fk_order_product")]
      public EntitySet Orders
      {
       get
       {
        return null;
       }
      }

      private System.Data.Linq.EntityRef reportsToEmployee;
      [Association(Storage = "reportsToEmployee", ThisKey = "ReportsTo", Name =
    "fk_emp_reportstoemp")]
      public Employee ReportsToEmployee
      {
       get
       {
        return reportsToEmployee.Entity;
       }
       set
       {
        reportsToEmployee.Entity = value;
       }
      }
     }

     

     

     

    Friday, May 16, 2008 5:12 PM

Answers

  • I tried to reproduce the behavior you are observing, using the 'standard' Northwind database, but could not.

     

    I see nine entries in the resultset, one of which is {"Andrew", null }.

     

    In your employee class definition, you're using "integer(32,0)" as a data type that isn't recognized by SQL Server. I'm also not sure what 'nextval()' does - is it a function you've defined?

     

    Can you please run your code against the Northwind sample database (that ships with SQL Server) and confirm your observations?

     

    Thanks,

     

    --Samir

     

     

    Monday, May 19, 2008 8:30 PM

All replies

  • This may be due to a bug that is fixed in SP1.

     

    Friday, May 16, 2008 6:54 PM
    Moderator
  • I tried to reproduce the behavior you are observing, using the 'standard' Northwind database, but could not.

     

    I see nine entries in the resultset, one of which is {"Andrew", null }.

     

    In your employee class definition, you're using "integer(32,0)" as a data type that isn't recognized by SQL Server. I'm also not sure what 'nextval()' does - is it a function you've defined?

     

    Can you please run your code against the Northwind sample database (that ships with SQL Server) and confirm your observations?

     

    Thanks,

     

    --Samir

     

     

    Monday, May 19, 2008 8:30 PM
  • As there has been no discussion on this thread for some time I am going to mark it as closed but feel free to reopen it if you wish to continue.

     

    [)amien

     

    Tuesday, June 10, 2008 8:19 PM
    Moderator