RestFul service & SQL Query

  • (4 Pages)
  • +
  • « First
  • 2
  • 3
  • 4

52 Replies - 3776 Views - Last Post: 10 November 2012 - 08:10 AM Rate Topic: -----

#46 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,460
  • Joined: 07-April 08

Re: RestFul service & SQL Query

Posted 08 November 2012 - 12:18 PM

you will want to use a join(http://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9) in your linq statement like so:
var output = (from i in table1
				join b in table2 on i.col = b.col into joinedTable
				select new { FirstName = i.firstName, LastName = i.lastName, Groups = joinedTable });


Was This Post Helpful? 0
  • +
  • -

#47 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 08 November 2012 - 03:50 PM

Okay, I see how that works but the jobs has to be populated by 3 tables.
Was This Post Helpful? 0
  • +
  • -

#48 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,460
  • Joined: 07-April 08

Re: RestFul service & SQL Query

Posted 09 November 2012 - 04:55 AM

Show me your table structure and the object you want them stored in. It could be as simple as using another join statement.
Was This Post Helpful? 0
  • +
  • -

#49 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 09 November 2012 - 07:49 AM

The site is having issues with the replies. It keeps clearing my reply before I can post it. WOOHOO! I finally got it posted. Anyways...

I'm needing to read the employee db and the read the positions for each employee in the positions table. 1 employee can have multiple or no jobs.

Position.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;

namespace hello_services.Models {
    public class Position 
    {
        public int id { get; set; }
        public string room { get; set; }
        public string off_hours { get; set; }
        public string title { get; set; }
        //public string primarydept { get; set; }
        public string phone { get; set; }
        public string fax { get; set; }
        public string location { get; set; }
        public string college { get; set; }
    }
}



Employee.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;

namespace hello_services.Models {
    public class Employee {

        public int Id { get; set; }
        public string First { get; set; }
        public string Middle { get; set; }
        public string Last { get; set; }
        public string Title { get; set; }
        public string Suffix { get; set; }
        public string email { get; set; }
        public string dept { get; set; }
        public string building { get; set; }
        public string room { get; set; }
        public List<string> Job { get; set; }
        public string photo { get; set; }
        public List<string> Websites { get; set; }
        // public string phone { get; set; }
        // public string fax { get; set; }

    }
}



LINQ statement
var employees = from e in _context.tbl_peoples
                            join pos in _context.tbl_positions on e.ID equals pos.people_ID
                            join dep in _context.tbl_departments on pos.dept_ID equals dep.ID
                            join col in _context.tbl_colleges on dep.college_ID equals col.id
                            select new Models.Employee
                            {
                                Id = e.ID,
                                Title = e.Name_Title,
                                First = e.Name_First,
                                Middle = e.Name_Middle,
                                Last = e.Name_Last,
                                Suffix = e.Name_Suffix,
                                email = e.email_1,
                                Job = from d in _context.tbl_positions
                                      join depart in _context.tbl_departments on d.dept_ID equals depart.ID
                                      join college in _context.tbl_colleges on d.college_ID equals college.id
                                      join location in _context.tbl_locations on d.location_ID equals location.id
                                      where d.id == e.ID
                                      select new
                                      {
                                          //id = d.id,
                                          //room = d.room,
                                          //off_hours = d.office_hours,
                                          //title = d.title,
                                          //phone = d.public3 == null ? string.Empty : "111-222-" + d.public3,
                                          //fax = d.fax3 == null ? string.Empty : "111-222-" + d.fax3, 
                                          //location = location.Name,
                                          //college = college.college
                                          d.id,
                                          d.room,
                                          d.office_hours,
                                          d.title,
                                          //d.public3 == null ? string.Empty : "111-222-" + d.public3,
                                          //d.fax3 == null ? string.Empty : "111-222-" + d.fax3, 
                                          location.Name,
                                          college.college
                                      }.ToString(),
                                photo = "https://hi.mywebsite.com/profiles/uploads/photos/" + e.PhotoFileName
                            };


This post has been edited by maddtechwf: 09 November 2012 - 07:53 AM

Was This Post Helpful? 0
  • +
  • -

#50 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1181
  • View blog
  • Posts: 1,673
  • Joined: 24-August 11

Re: RestFul service & SQL Query

Posted 09 November 2012 - 07:54 AM

I posted an example of a SELECT with multiple JOINs in one of your other threads, here. Is that not still applicable to what you're trying to do?

Also, I've been having a bit of trouble with DIC in FireFox, and the Fast Reply window has been sketchy in FireFox and Chrome, but Full Reply works fine in Chrome.

EDIT: realized you're trying to do this with LINQ now, not SQL.

This post has been edited by h4nnib4l: 09 November 2012 - 07:56 AM

Was This Post Helpful? 0
  • +
  • -

#51 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 09 November 2012 - 08:10 AM

View Posth4nnib4l, on 09 November 2012 - 08:54 AM, said:

I posted an example of a SELECT with multiple JOINs in one of your other threads, here. Is that not still applicable to what you're trying to do?

Also, I've been having a bit of trouble with DIC in FireFox, and the Fast Reply window has been sketchy in FireFox and Chrome, but Full Reply works fine in Chrome.

EDIT: realized you're trying to do this with LINQ now, not SQL.


I have the joins setup like you said before, they just return on position. I need it to return multiple positions if an employee has one.

This is the XML structure that I want it to follow.
<ArrayOfEmployees>
   <employee>
      <id></id>
      <title></title>
      <first></first>
      <middle></middle>
      <last></last>
      <suffix></suffix>
      <email></email>
      <positions>
         <position>
            <id></id>
            <room></room>
            <hours></hours>
            <title></title>
            <location></location>
            <college></college>
         </position>
         <position>
            <id></id>
            <room></room>
            <hours></hours>
            <title></title>
            <location></location>
            <college></college>
         </position>
      </positions>
      <picture></picture>
   </employee>
</ArrayOfEmployees>


Was This Post Helpful? 0
  • +
  • -

#52 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,460
  • Joined: 07-April 08

Re: RestFul service & SQL Query

Posted 09 November 2012 - 07:44 PM

Have you tried changing the linq statement to use the join table?
Was This Post Helpful? 0
  • +
  • -

#53 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Re: RestFul service & SQL Query

Posted 10 November 2012 - 08:10 AM

View Postrgfirefly24, on 09 November 2012 - 08:44 PM, said:

Have you tried changing the linq statement to use the join table?


I'm a little loss at what you mean.
Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • « First
  • 2
  • 3
  • 4