none
Can I populate a DTO class from multiple tables RRS feed

  • Question

  • I have a stored procedure that I would like to convert to a LINQ to SQL call that will automatically populate a DTO class with all of the paramaters specified in the SELECT statement, with data coming from several tables.

    An example of the stored procedure I have in mind is:

    select TOP(500) p.ID, p.LAST_NAME, p.FRST_NAME,
    r.RESERVATION_ID, r.OFFICE_ID, r.RESERVATION_STATUS_CODE,
    o.PVDR_ID, pr.M_ADDR1, pr.M_ADDR2, pr.M_CITY, pr.M_ST,
    r.APPT_DATE, r.STRT_TIME
    from PATIENT p
    join CLAIM c on c.pat_id = p.id
    join RESERVATION r on c.ID = r.CLM_ID
    join PROVIDER_OFFICE o on r.OFFICE_ID = o.OFFICE_ID
    join PROVIDER pr on o.PVDR_ID = pr.ID
    where P.ID = '-12488527'
    order by APPT_DATE, STRT_TIME

    Is this possible with LINQ TO SQL?

     

    Wednesday, September 1, 2010 10:34 PM

Answers

  • Hi Bill,

    The error become from the fact that the anonymous type cannot be converter to List<Appointment>, you can use Appointment class instead of anonymous type when you doing Select New. By the way, you mentioned in your post "That second foreach loop seems inefficient, so I tried the following", but the two GetAppointmentsList(string deviceId) methods seem doing the different things. The first GetAppointmentsList method is trying to modify each Appointment's ProviderId, whereas the second GetAppointmentsList method is just retrieving the values. Please check and make clear what you really want to achieve with GetAppointmentsList method.

    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 Wednesday, September 15, 2010 3:55 PM
    Wednesday, September 15, 2010 6:16 AM
    Moderator

All replies

  • Hi Bill,

     

    You can use multiple joins on these tables. For example ,you can do something like:

     

    Var result = (from p in db. PATIENT

                        join c in db. CLAIM on c.pat_id  Equals p.id

                        join r in db. RESERVATION on c.ID Equals r.CLM_ID

                        …other joins…

                        where P.ID = '-12488527'

                        order by r.APPT_DATE, r.STRT_TIME

                        select new

                        {

                            p.ID,

                            p.LAST_NAME,

                            p.FRST_NAME,

                            r.RESERVATION_ID,

                            r.OFFICE_ID,

                            …other fields

                            …

                            …

                        }) .Take(500)

     

    And you can find many examples about linq join in the folloiwng articles.

    http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

    http://weblogs.asp.net/rajbk/archive/2010/03/12/joins-in-linq-to-sql.aspx

     

    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 Thursday, September 2, 2010 3:14 PM
    • Unmarked as answer by Bill2010 Thursday, September 2, 2010 4:09 PM
    Thursday, September 2, 2010 7:01 AM
    Moderator
  • This is very close. Unfortunately, I should have provided more detail on what I am actually trying to accomplish. I have defined a class table (a subset of the actual db table) that is being returned in a WCF REST request.

    It is defined as:

        [DataContract]
        [Table(Name = "RESERVATION")]
        public class Appointment
        {
            [DataMember]
            [Column(Name = "RESERVATION_ID", IsPrimaryKey = true)]
            public int ReservationId { get; set; }

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

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

            [DataMember]
            public string ProviderId { get; set; }

            [IgnoreDataMember]
            [Column(Name = "OFFICE_ID")]
            public int OfficeId { get; set; }

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

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

    The following method works, but doesn't seem elegant to me (I was hoping the solution you provided could be made to work as it seems closer to what I am trying to accomplish):

            public IList<Appointment> GetAppointmentsList(string deviceId)
            {
                var db = new DataContext(ConnectionString);

                var DeviceAssignmentTable = db.GetTable<DeviceAssignment>();
                var ClaimTable = db.GetTable<Claim>();
                var AppointmentTable = db.GetTable<Appointment>();
                var ProviderOfficeTable = db.GetTable<ProviderOffice>();

                IQueryable<Appointment> reservations = from d in DeviceAssignmentTable
                    join c in ClaimTable on d.PatientId equals c.PatientId
                    join a in AppointmentTable on c.ClaimId equals a.ClaimId
                    where d.DeviceId == deviceId
                    select a;

                foreach (Appointment r in reservations)
                {
                    IQueryable<ProviderOffice> providers = from p in ProviderOfficeTable
                        where p.OfficeId == r.OfficeId
                        select p;
                    foreach (ProviderOffice p in providers)
                        r.ProviderId = p.ProviderId;
                }

                return reservations.ToList();
            }

    That second foreach loop seems inefficient, so I tried the following, which throws a runtime exception:

            public IList<Appointment> GetAppointmentsList(string deviceId)
            {
                var db = new DataContext(ConnectionString);

                var DeviceAssignmentTable = db.GetTable<DeviceAssignment>();
                var ClaimTable = db.GetTable<Claim>();
                var AppointmentTable = db.GetTable<Appointment>();
                var ProviderOfficeTable = db.GetTable<ProviderOffice>();

                var result = (from d in DeviceAssignmentTable
                    join c in ClaimTable on d.PatientId equals c.PatientId
                    join a in AppointmentTable on c.ClaimId equals a.ClaimId
                    join p in ProviderOfficeTable on a.OfficeId equals p.OfficeId
                    where d.DeviceId == deviceId
                    select new
                    {
                        a.ReservationId,
                        a.ClaimId,
                        p.ProviderId,
                        a.OfficeId,
                        a.AppointmentDate,
                        a.StartTime
                    });

                return (IList<Appointment>)result;
            }

    Thursday, September 2, 2010 4:18 PM
  • Hi Bill,

    Sorry for the late reply. You mentioned "That second foreach loop seems inefficient, so I tried the following, which throws a runtime exception:", could you please tell us what kind of exception it is and the whole error message ? Thanks.

    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.
    Monday, September 13, 2010 2:21 AM
    Moderator
  • It looks like it would be a casting exception in the return statement. 

    You could use load options if speed is the issue and not the result set.

     DataContext dc = new DataContext();
     DataLoadOptions options = new DataLoadOptions();
     options.LoadWith<Appointment>( x => x.ProviderOffices );
    
    

    Other than that you would need to create the type in your result set. Anoyomous types only have properties in there local scope. So if you create the type, you could return it to another scope. You could return a list of objects, but that probably isn't ideal. 

    You should probably also consider revising your data table.  Should that provider id be a foreign key in the appointment table?

    Tuesday, September 14, 2010 3:11 PM
  • The error is thrown on the return:

    Unable to cast object of type 'System.Data.Linq.DataQuery`1[<>f__AnonymousType4`6[System.Int32,System.String,System.String,System.Int32,System.DateTime,System.DateTime]]' to type 'System.Collections.Generic.IList`1[MobileApps.Domain.Entities.Appointment]'.
    Tuesday, September 14, 2010 4:06 PM
  • Hi Bill,

    The error become from the fact that the anonymous type cannot be converter to List<Appointment>, you can use Appointment class instead of anonymous type when you doing Select New. By the way, you mentioned in your post "That second foreach loop seems inefficient, so I tried the following", but the two GetAppointmentsList(string deviceId) methods seem doing the different things. The first GetAppointmentsList method is trying to modify each Appointment's ProviderId, whereas the second GetAppointmentsList method is just retrieving the values. Please check and make clear what you really want to achieve with GetAppointmentsList method.

    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 Wednesday, September 15, 2010 3:55 PM
    Wednesday, September 15, 2010 6:16 AM
    Moderator