none
Trying to do a multi-join sql statement using Linq to Sql RRS feed

  • Question

  • I am trying to return all appointment records that have an fk relationship to three tables (DEVICE, USER, APPOINTMENT). The sql statement for this is something like:

    SELECT a.USER_ID, a.ID, a.START_TIME, a.END_TIME, a.PROVIDER
       FROM APPOINTMENT a
       JOIN USER u ON a.USER_ID = u.USER_ID
       JOIN DEVICE_ASSIGNMENT d ON d.USER_ID = u.USER_ID
       WHERE DEVICE_ID = '12345965edbd9555d1cd9sm34eae3d1bc71a40a5'

    I would like to convert this into a LINQ TO SQL statement.

    I am able to do a simple test on a single Appointment table, but having problems with DateTime columns from the db and the multiple joins. In addition, the original table is legacy and has two datetime columns for each appointment... one for date and one for time. I would like to combine these into a single datetime column for the return to the client (i.e., AppointmentDateTime = StartDate + StartTime). START_DATE and START_TIME from the db will not be returned through the service, but merely used to create a derived datetime column.

    My test code, so far is:

        [DataContract]
        [Table(Name = "APPOINTMENT")]
        public class Appointment
        {
            [DataMember]
            [Column(Name = "APPOINTMENT_ID", IsPrimaryKey = true)]
            public int AppointmentID { get; set; }

            [DataMember]
            [Column(Name = "USER_ID"]
            public string UserId{ get; set; }

            [DataMember]
            [Column(Name = "PROVIDER")]
            public string Provider { get; set; }

            [DataMember]
            public DateTime AppointmentDateTime { get; set; }

            [IgnoreDataMember]
            [Column(Name = "START_DATE")]
            public DateTime StartDate { get; set; }

            [IgnoreDataMember]
            [Column(Name = "START_TIME")]
            public DateTime StartTime { get; set; }

            [IgnoreDataMember]
            [Column(Name = "USER_ID")]
            public string UserId { get; set; }
        }

        [DataContract]
        [Table(Name = "USER")]
        public class User
        {
            [DataMember]
            [Column(Name = "USER_ID", IsPrimaryKey = true)]
            public string UserId { get; set; }

            [DataMember]
            [Column(Name = "LAST_NAME")]
            public string LastName { get; set; }

            [DataMember]
            [Column(Name = "FIRST_NAME")]
            public string FirstName { get; set; }

            [DataMember]
            [Column(Name = "MIDL_INIT")]
            public string MiddleInitial { get; set; }

            [DataMember]
            [Column(Name = "SSN4")]
            public string SSN4 { get; set; }
        }

        [DataContract]
        [Table(Name = "DEVICE")]
        public class Device
        {
            [DataMember]
            [Column(Name = "DEVICE_ID", IsPrimaryKey = true)]
            public string DeviceId{ get; set; }

            [DataMember]
            [Column(Name = "USER_ID")]
            public string UserId{ get; set; }
        }

    public IList<Appointment> GetAppointmentsList()
    {
        var AppointmentTable = new DataContext(ConnectionString).GetTable<Appointment>();

        IQueryable<Appointment> appointments =
           from a in AppointmentTable
           where a.UserId == "123456"
           select a;

        foreach (Appointment a in appointments)
       {
           a.AppointmentDateTime = new DateTime(
           a.StartDate.Year,
           a.StartDate.Month,
           a.StartDate.Day,
           a.StartTime.Hour,
           a.StartTime.Minute,
           0);
       }

        return apps.ToList();
    }

     

    • Edited by Bill2010 Monday, August 30, 2010 8:18 PM
    Monday, August 30, 2010 6:04 PM

Answers

  • Hi Bill,

    You can just use one context instance, and use that one context to get the different tables, something like:

    DataContext db = new DataContext(ConnectionString);
    var AppointmentTable = db.GetTable<Appointment>();
    var DeviceAssignmentTable = db.GetTable<DeviceAssignment>();


    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Bill2010 Tuesday, August 31, 2010 10:16 PM
    Tuesday, August 31, 2010 6:18 AM
    Moderator
  • Isn't using GetTable<> over complicating the code?

    public IList<Appointment> GetAppointmentsList()
    {
      DataContext db = new DataContext(ConnectionString);
    
    
      IQueryable<Appointment> appointments =
        from a in db.Appointments
        join d in db.DeviceAssignments on a.UserId equals d.AdvocacyUserId
        where a.UserId == "27f7d965edbd9555d1cb9ad44eae3d1bc71a40a5"  //Helper.GetHttpAuthDeviceId
        select a;
    
        foreach (Appointment a in appointments)
          a.AppointmentDateTime = Helper.CombineDateTime(a.StartDate, a.StartTime);
    
        return appointments.ToList();
    }
    
    

     

    • Marked as answer by Bill2010 Tuesday, August 31, 2010 10:16 PM
    Tuesday, August 31, 2010 9:24 AM

All replies

  • public partial class Appointment
    {
    
    public DateTime AppointmentDateTime
    {
      get
      {
        return new DateTime(StartDate.Year, .... );
      }
    }
    
    }
    
    You can add partial classes that Extend the generated Table Classes.
    Monday, August 30, 2010 7:51 PM
  • Ok, I tried simplifying to a single join as follows:

    SQL Statement I am trying to duplicate:

    SELECT a.USER_ID, a.APPOINTMENT_ID, a.START_TIME, a.END_TIME, a.PROVIDER
       FROM APPOINTMENT a
       JOIN DEVICE d ON d.USER_ID = a.USER_ID
       WHERE DEVICE_ID = '12345675edbd9555d1cb9ad44eae3d1bc71a40a5'

    IMPLEMENTATION IN LINK TO SQL:

        [Table(Name = "DEVICE")]
        public class Device
        {
            [Column(Name = "DEVICE_ID", IsPrimaryKey = true, IsDbGenerated = false, AutoSync = AutoSync.OnInsert)]
            public string DeviceId { get; set; }

            [Column(Name = "USER_ID")]
            public string UserId { get; set; }
        }

        [DataContract]
        [Table(Name = "APPOINTMENT")]
        public class Appointment
        {
            [DataMember]
            [Column(Name = "APPOINTMENT_ID", IsPrimaryKey = true)]
            public int AppointmentID { get; set; }

            [DataMember]
            [Column(Name = "USER_ID")]
            public string UserId{ get; set; }

            [DataMember]
            [Column(Name = "PROVIDER")]
            public string Provider { get; set; }

            [DataMember]
            public DateTime AppointmentDateTime { get; set; }

            [IgnoreDataMember]
            [Column(Name = "START_DATE")]
            public DateTime StartDate { get; set; }

            [IgnoreDataMember]
            [Column(Name = "START_TIME")]
            public DateTime StartTime { get; set; }

            [IgnoreDataMember]
            [Column(Name = "USER_ID")]
            public string UserId { get; set; }
        }

             public IList<Appointment> GetAppointmentsList()
            {
                var AppointmentTable = new DataContext(ConnectionString).GetTable<Appointment>();
                var DeviceAssignmentTable = new DataContext(ConnectionString).GetTable<DeviceAssignment>();

                IQueryable<Appointment> appointments =
                    from a in AppointmentTable
                    join d in DeviceAssignmentTable on a.UserId equals d.AdvocacyUserId
                    where a.UserId == "27f7d965edbd9555d1cb9ad44eae3d1bc71a40a5"   //Helper.GetHttpAuthDeviceId
                    select a;

                foreach (Appointment a in appointments)
                    a.AppointmentDateTime = Helper.CombineDateTime(a.StartDate, a.StartTime);

                return appointments.ToList();
            }

    The foreach statement throws an error stating it is in a different context. How do I get both of the tables to be in the same context?

    (BTW, I did not use the O/R designer, so the datacontext is not being auto-generated. Can I do this manually from the table definitions I provided above?)

    Monday, August 30, 2010 8:43 PM
  • Hi Bill,

    You can just use one context instance, and use that one context to get the different tables, something like:

    DataContext db = new DataContext(ConnectionString);
    var AppointmentTable = db.GetTable<Appointment>();
    var DeviceAssignmentTable = db.GetTable<DeviceAssignment>();


    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Bill2010 Tuesday, August 31, 2010 10:16 PM
    Tuesday, August 31, 2010 6:18 AM
    Moderator
  • Isn't using GetTable<> over complicating the code?

    public IList<Appointment> GetAppointmentsList()
    {
      DataContext db = new DataContext(ConnectionString);
    
    
      IQueryable<Appointment> appointments =
        from a in db.Appointments
        join d in db.DeviceAssignments on a.UserId equals d.AdvocacyUserId
        where a.UserId == "27f7d965edbd9555d1cb9ad44eae3d1bc71a40a5"  //Helper.GetHttpAuthDeviceId
        select a;
    
        foreach (Appointment a in appointments)
          a.AppointmentDateTime = Helper.CombineDateTime(a.StartDate, a.StartTime);
    
        return appointments.ToList();
    }
    
    

     

    • Marked as answer by Bill2010 Tuesday, August 31, 2010 10:16 PM
    Tuesday, August 31, 2010 9:24 AM