none
Can LINQ to SQL populate an imbedded complex type using the attached complex T-SQL RRS feed

  • Question

  • I am attempting to use LINQ to SQL to populate the following classes, however I am not sure how to get it to populate the LocationInfo and InjuredBodyPart list.

     

    [DataContract]
    public class EmpMedAuthDetail
    {
     [DataMember] public int CAID { get; set; }
     [DataMember] public string FormType { get; set; }
     [DataMember] public string PatientLastName { get; set; }
     [DataMember] public string PatientFirstName { get; set; }
     [DataMember] public string PatientMI { get; set; }
     [DataMember] public string SSN4 { get; set; }
     [DataMember] public string Email { get; set; }
     [DataMember] public DateTime InjuryDateTime { get; set; }
     [DataMember] public string JurisdictionState { get; set; }
     [DataMember] public string CustomerClaimNo { get; set; }
     [DataMember] public string EmployerName { get; set; }
     [DataMember] public LocationInfo EmployerLocation { get; set; }
     [DataMember] public List<InjuredBodyPart> InjuredBodyParts { get; set; }
     [DataMember] public string AdditionalNotes { get; set; }
     [DataMember] public string TreatingPhyFirstName { get; set; }
     [DataMember] public string TreatingPhyMiddleInitial { get; set; }
     [DataMember] public string TreatingPhyLastName { get; set; }
     [DataMember] public string TreatingClinic { get; set; }
     [DataMember] public string LevelOfSeverity { get; set; }
     [DataMember] public string TreatmentType { get; set; }
     [DataMember] public string FnolStatus { get; set; }
    }
    
    public class LocationInfo
    {
     public string Address1 { get; set; }
     public string Address2 { get; set; }
     public string City { get; set; }
     public string State { get; set; }
     public string Zip { get; set; }
     public string Phone { get; set; }
     public string Fax { get; set; }
     public string Email { get; set; }
    }
    
    public class InjuredBodyPart
    {
     public string BodyPartInjured { get; set; }
     public string BodyPartSide { get; set; }
    }
    
    I am using the following T-SQL with LINQ:

     

    string sql =
     string.Format(
      @"SELECT DISTINCT 
        R.CAID       AS CAID,
        R.ADDNL_NOTES     AS AdditionalNotes,
        R.FORM_ID      AS FormType, 
        R.SEVERITY_LEVEL    AS LevelOfSeverity,
        R.PHYN_FRST_NAME    AS TreatingPhyFirstName,
        R.PHYN_LAST_NAME    AS TreatingPhyLastName,
        R.PHYN_MIDL_INIT    AS TreatingPhyMiddleInitial,
        R.HOSP_NAME      AS TreatingClinic,
        R.P_LAST_NAME     AS PatientLastName,
        R.P_FRST_NAME     AS PatientFirstName,
        R.P_MIDL_INIT     AS PatientMI,
        CAST(R.I_INCD_DATE AS DATETIME) AS InjuryDateTime,
        R2.P_EMAIL      AS Email,   
        R2.P_SSN_LAST4     AS SSN4,
        R2.TYP_CARE      AS TreatmentType,
        CLAIM.CUST_CLM_NUM    AS CustomerClaimNo,
        JURISDICTION.CODE    AS JurisdictionState,
        COALESCE(lb1.DSCP, lb2.DSCP) AS BodyPartInjured,
        BODY_PART_SIDE.DSCP    AS BodyPartSide,
        NAME       AS EmployerName,
        M.DSCP       AS FnolStatus,
        M_ADDR1       AS Address1,
        M_ADDR2       AS Address2,
        M_CITY       AS City,
        M_ST       AS State,
        M_ZIP       AS Zip
      FROM REFERRAL (NOLOCK) R
        JOIN REFERRAL RR (NOLOCK) ON R.FNOL_ID = RR.FNOL_ID
        JOIN REFERRAL_2 (NOLOCK) R2 ON R2.REFERRAL_ID = R.REFERRAL_ID 
        JOIN CLAIM (NOLOCK) ON CLAIM.ID = R.CLM_ID
        JOIN USER_CLAIM (NOLOCK) ON USER_CLAIM.CLM_ID = CLAIM.ID 
        JOIN CUSTOMER (NOLOCK) ON CUSTOMER.ID = CLAIM.EMPR_ID
        JOIN MILESTONE_TRACKING_CURRENT MTC (NOLOCK) ON CONVERT (VARCHAR, RR.REFERRAL_ID) = MTC.WORKFLOW_SOURCE_KEY
         AND MTC.WORKFLOW_TYPE_ID IN ('FNOLDRAFT_WORKFLOW','FNOLEXTERNAL_WORKFLOW','FNOLINTERNAL_WORKFLOW')
        JOIN MILESTONE M (NOLOCK) ON MTC.Milestone_Result_ID = M.MILESTONE_ID 
        JOIN CUST_ASSOCIATION CA (NOLOCK) ON R.CAID = CA.ID
        LEFT JOIN JURISDICTION (NOLOCK) ON JURISDICTION.CODE = R.I_ST
        LEFT JOIN Lookup_Body lb1 (NOLOCK) ON R.Claim_Part_Inj = lb1.CODE AND lb1.CUST_ID = CA.CUST_ID
        LEFT JOIN Lookup_Body lb2 (NOLOCK) ON R.Claim_Part_Inj = lb2.CODE AND lb2.CUST_ID = 0
        LEFT JOIN BODY_PART_SIDE (NOLOCK) ON BODY_PART_SIDE.CODE = R2.PRT_SIDE
      WHERE R.REFERRAL_ID = '{0}'", referralId);
    
    
    using (var db = new CaremcDB(Database.Conn))
    {
     return db.ExecuteQuery<EmpMedAuthDetail>(sql).FirstOrDefault<EmpMedAuthDetail>();
    }
    

    For example, in the above code, LocationInfo is returning as null with no address data, etc.

    Tuesday, August 2, 2011 6:06 PM

Answers

  • The responses did not solve my issue. I am in Portland at the Microsoft office for a Sprint planning and will not be able to clarify my question until next week.

    I believe what Alan was trying to get you to do was create a new type to copy the results into first (not EmpMedAuthDetail).  The ExecuteQuery method does not have enough information to translate the results of your query into a nested object structure.  It only works with flat structures.  If you first project the results into a flat structure, you can then construct the EmpMedAuthDetail type using your own code/logic.
    Wayward LINQ Lacky
    • Marked as answer by Bill2010 Thursday, August 18, 2011 9:47 PM
    Tuesday, August 16, 2011 3:45 AM
    Moderator

All replies

  • Hi Bill,

    Welcome!

    I think you can create a new type which matches your T-sql, then you can loop the list to retrieve records to fill different types.

     

     class Program
     {
      static void Main(string[] args)
      {
       DataClasses1DataContext dd = new DataClasses1DataContext();
       var test = dd.ExecuteQuery<EV>("select e.EmployeeId, e.Name, v.VacationId, v.Days,v.EId from Employees e, Vacation v where e.EmployeeId=v.EId");
      var tt= test.AsEnumerable().First();
      }
     }
     public class EV
     {
      public int EmployeeId { get; set; }
      public int VacationId { get; set; }
      public string Name { get; set; }
      public int Days { get; set; }
      public int EId { get; set; }
     }
    

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Wednesday, August 3, 2011 10:00 AM
    Moderator
  • Alan,

    Simple types are not a problem for what I am trying to accomplish. The example in the OP does populate the non-embedded types such as FormType, PatientLastName, etc.. It is the embedded LocationInfo and InjuredBodyPart types that are not being populated in EmpMedAuthDetail. They render as <LocationInfo/> and <InjuredBodyPart/>, although there is data (for example Address1 returns "1 Rocket Dr."). I tried changing "M_ADDR1 AS Address1" to "M_ADDR1 AS LocationInfo.Address1", but that threw a run-time error.

    It just may not be possible to do what I am attempting, so I will need to flatten the structure, but that will effect the clients.

    -- Bill

    Wednesday, August 3, 2011 2:54 PM
  • H Bill,

    Thanks for your feedback.

    I'm not sure about your description, but we can get what columns will return from your T-SQL, right? We can use the columns to create a type (Complex type) then use ExcuteQuery<Type>() to return the list  of this type. The next step is Loop the return list to retrieve what you need.

    it is easier to handle records that in our memory  than database.

    Please clarify me, if I miss your point. Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 4, 2011 9:39 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance. 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 11, 2011 8:16 AM
    Moderator
  • The responses did not solve my issue. I am in Portland at the Microsoft office for a Sprint planning and will not be able to clarify my question until next week.
    Thursday, August 11, 2011 5:28 PM
  • The responses did not solve my issue. I am in Portland at the Microsoft office for a Sprint planning and will not be able to clarify my question until next week.

    I believe what Alan was trying to get you to do was create a new type to copy the results into first (not EmpMedAuthDetail).  The ExecuteQuery method does not have enough information to translate the results of your query into a nested object structure.  It only works with flat structures.  If you first project the results into a flat structure, you can then construct the EmpMedAuthDetail type using your own code/logic.
    Wayward LINQ Lacky
    • Marked as answer by Bill2010 Thursday, August 18, 2011 9:47 PM
    Tuesday, August 16, 2011 3:45 AM
    Moderator