none
Turn SQL to Linq and best way to pass results back RRS feed

  • Question

  • Hi,

    I am having trouble with some Linq to SQL.  I have a SQL statement, shown below and I have changed it partly into a LINQ statement (statement 2 below).  I am having trouble because in my sql statement I return a table that has two coloums, and I am unsure how best to return them.




    select
    (e.FirstName + ' ' + e.LastName) as employeedetails,
    (m.FirstName + ' ' + m.LastName) as mentordetails
    from employees e
    inner join EmployeeMentors em on em.EmployeeID = e.EmployeeID
    inner join employees m on m.EmployeeID = em.MentorID
    


    So far my Linq looks like:

    DataClassesMainDataContext db = new DataClassesMainDataContext();
    
    string result = (from e in db.Employees
    join em in db.EmployeeMentors on e.EmployeeID equals em.EmployeeID
    join m in db.Employees on em.EmployeeID equals m.EmployeeID
    select (e.FirstName + ' ' + e.LastName)).ToString();
    



    I need to to get the Employee Name and Mentor Name, and return them to the application so they can then be displayed in a datagrid.


    Thanks

    Marc
    Marc Garraway
    Thursday, January 7, 2010 11:05 AM

Answers


  • In your case, you might find it easier to start with the EmployeeMentor table.  For example, if you have set up the associations between the Employee and EmployeeMentor tables (you'll need two, one for Employee and one for Mentor), the following would work:


                    var result = from em in db.EmployeeMentors
                                 select new
                                 {
                                     EmployeeDetails = em.Employee.FirstName + " " + em.Employee.LastName,
                                     MentorDetails = em.Mentor.FirstName + " " + em.Mentor.LastName
                                 };
    

    This gives the following SQL:

    SELECT ([t1].[FirstName] + @p0) + [t1].[LastName] AS [EmployeeDetails], ([t2].[FirstName] + @p1) + [t2].[LastName] AS [MentorDetails]
    FROM [dbo].[EmployeeMentor] AS [t0]
    INNER JOIN [dbo].[Employee] AS [t1] ON [t1].[EmployeeID] = [t0].[EmployeeID]
    INNER JOIN [dbo].[Employee] AS [t2] ON [t2].[EmployeeID] = [t0].[MentorID]
    -- @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
    -- @p1: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
    

    I think this is equivalent to what you had.  If the associations are not in place, you can manually add the joins as you were doing:

                    var result = from em in db.EmployeeMentors
                                 join e in db.Employees
                                   on em.EmployeeID equals e.EmployeeID
                                 join m in db.Employees
                                   on em.MentorID equals m.EmployeeID
                                 select new
                                 {
                                     EmployeeDetails = e.FirstName + " " + e.LastName,
                                     MentorDetails = m.FirstName + " " + m.LastName
                                 };
    
    Thursday, January 7, 2010 2:39 PM
  • You can create a type such as:

        public class EmployeeInfo
        {
            public string EmployeeDetails { get; set; }
            public string MentorDetails { get; set; }
        }
    

    Then instead of creating an anonymous type, create these EmployeeInfo objects:

                    var result = from em in db.EmployeeMentors
                                 select new EmployeeInfo
                                 {
                                     EmployeeDetails = em.Employee.FirstName + " " + em.Employee.LastName,
                                     MentorDetails = em.Mentor.FirstName + " " + em.Mentor.LastName
                                 };
    

    The method can then either return this result as an IQueryable<EmployeeInfo>, or if you want to force it into memory, return result.ToList() as a List<EmployeeInfo>.
    Friday, January 8, 2010 7:31 PM

All replies

  • Salaam Aliakmon

    without doing all of this, you can create another Employee class  and do this

    public partial class Employee
    {
           public string EmployeeName
           {
                  get
                  {
                         // add some validation on the null values
                         return _FirstName + ' ' + _LastName;
                  }
           }
    }

    BR
    Best Regards
    Thursday, January 7, 2010 12:31 PM

  • In your case, you might find it easier to start with the EmployeeMentor table.  For example, if you have set up the associations between the Employee and EmployeeMentor tables (you'll need two, one for Employee and one for Mentor), the following would work:


                    var result = from em in db.EmployeeMentors
                                 select new
                                 {
                                     EmployeeDetails = em.Employee.FirstName + " " + em.Employee.LastName,
                                     MentorDetails = em.Mentor.FirstName + " " + em.Mentor.LastName
                                 };
    

    This gives the following SQL:

    SELECT ([t1].[FirstName] + @p0) + [t1].[LastName] AS [EmployeeDetails], ([t2].[FirstName] + @p1) + [t2].[LastName] AS [MentorDetails]
    FROM [dbo].[EmployeeMentor] AS [t0]
    INNER JOIN [dbo].[Employee] AS [t1] ON [t1].[EmployeeID] = [t0].[EmployeeID]
    INNER JOIN [dbo].[Employee] AS [t2] ON [t2].[EmployeeID] = [t0].[MentorID]
    -- @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
    -- @p1: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
    

    I think this is equivalent to what you had.  If the associations are not in place, you can manually add the joins as you were doing:

                    var result = from em in db.EmployeeMentors
                                 join e in db.Employees
                                   on em.EmployeeID equals e.EmployeeID
                                 join m in db.Employees
                                   on em.MentorID equals m.EmployeeID
                                 select new
                                 {
                                     EmployeeDetails = e.FirstName + " " + e.LastName,
                                     MentorDetails = m.FirstName + " " + m.LastName
                                 };
    
    Thursday, January 7, 2010 2:39 PM
  • Thanks thats great.

    Just wondering how I would pass this back.

    The SQL statement gives a list with two columns, mentor and employee.  How would I pass this back to the applicaition and what would the method type need to be??
    Marc Garraway
    Friday, January 8, 2010 5:11 PM
  • You can create a type such as:

        public class EmployeeInfo
        {
            public string EmployeeDetails { get; set; }
            public string MentorDetails { get; set; }
        }
    

    Then instead of creating an anonymous type, create these EmployeeInfo objects:

                    var result = from em in db.EmployeeMentors
                                 select new EmployeeInfo
                                 {
                                     EmployeeDetails = em.Employee.FirstName + " " + em.Employee.LastName,
                                     MentorDetails = em.Mentor.FirstName + " " + em.Mentor.LastName
                                 };
    

    The method can then either return this result as an IQueryable<EmployeeInfo>, or if you want to force it into memory, return result.ToList() as a List<EmployeeInfo>.
    Friday, January 8, 2010 7:31 PM