none
A relational query builder RRS feed

  • Question

  •  

    I start with this code:

          var RemitQuery = from r in ClaimContext.RemitBundles
                   select r;
    

     

    Then I call other function to build the query for me:

    <br/>
                if (!string.IsNullOrEmpty(JsonParams.txtPayerID))
            remitQuery = remitQuery.Where(c => c.PayerID == JsonParams.txtPayerID);
    
          if (!string.IsNullOrEmpty(JsonParams.txtPayerName))
            remitQuery = remitQuery.Where(c => c.CheckNumber.Contains(JsonParams.txtPayerName));
    
          if (JsonParams.isValid)
          {
            if (!string.IsNullOrEmpty(JsonParams.txtPatientLastName))
              remitQuery = from r in ClaimContext.RemitBundles
                     join a in ClaimContext.ClaimAdjudications on r.RemitBundlesKey equals a.RemitBundlesKey
                     where a.PatientLastName.Contains(JsonParams.txtPatientLastName)
                     select r;
    
          }
    

    One of the requirement is that I need to search in a child table as seen in the third if statement. But that will just overwrite what's on the remitQuery. Any clue how to search in a child table and still keep adding the remitQuery?

     

    Thanks

     

    Tuesday, October 5, 2010 9:30 PM

Answers

  • Hello Fahd,

    You could try to add the where statements at the end of the query. Something like this:
     if (JsonParams.isValid)
    {
         if (!string.IsNullOrEmpty(JsonParams.txtPatientLastName))
         remitQuery = from r in ClaimContext.RemitBundles
                      join a in ClaimContext.ClaimAdjudications
        on r.RemitBundlesKey equals a.RemitBundlesKey
                      where a.PatientLastName.Contains(JsonParams.txtPatientLastName)
        && a.PayerID==JsonParams.txtPayerID
         && r.CheckNumber.Contains(JsonParams.txtPayerName)
                      select r;

    }

    Please let me know if this is what you want.
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:51 AM
    Tuesday, October 12, 2010 6:54 AM

All replies

  • Use another query variable to hold the result, and then union remitQuery.
    Tuesday, October 5, 2010 9:45 PM
  • When you do a union on:

      var
     RemitQuery = from
     r in
     ClaimContext.RemitBundles
            select
     r;
    

    You will get all records no matter what!

     

     

     

     

     

    Wednesday, October 6, 2010 8:56 PM
  • Hello Fahd,

    You could try to add the where statements at the end of the query. Something like this:
     if (JsonParams.isValid)
    {
         if (!string.IsNullOrEmpty(JsonParams.txtPatientLastName))
         remitQuery = from r in ClaimContext.RemitBundles
                      join a in ClaimContext.ClaimAdjudications
        on r.RemitBundlesKey equals a.RemitBundlesKey
                      where a.PatientLastName.Contains(JsonParams.txtPatientLastName)
        && a.PayerID==JsonParams.txtPayerID
         && r.CheckNumber.Contains(JsonParams.txtPayerName)
                      select r;

    }

    Please let me know if this is what you want.
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:51 AM
    Tuesday, October 12, 2010 6:54 AM
  • it doesn't work that way, the parameters in the where clause depends on the data input by the user. They may enter the payer id and not the payer name. In this case, with you query it will return 0 because the payer name will be empty.
    Thursday, October 14, 2010 9:40 PM
  • I had to refine the top query as follow:

    var RemitQuery = (from Remit in ClaimContext.RemitBundles join Claim in ClaimContext.ClaimAdjudications on Remit.RemitBundlesKey equals Claim.RemitBundlesKey where Remit.Cin == JsonParams.CIN select new { Remit, Claim });

     

    Now if we need to search in the child table, we do this:

    if (!string.IsNullOrEmpty(JsonParams.txtPatientLastName))

    RemitQuery = RemitQuery.Where(c => c.Claim.PatientLastName.Contains(JsonParams.txtPatientLastName));

     

    and/or search in the parent, we do this:

    if (!string.IsNullOrEmpty(JsonParams.txtCheckNumber))

    RemitQuery = RemitQuery.Where(c => c.Remit.CheckNumber.Contains(JsonParams.txtCheckNumber));

     

    And it works, It took me quite some time to figure it out.

    Fahd

    Thursday, February 17, 2011 9:15 PM