none
Exists subquery for 2 DataTables RRS feed

  • Question

  • Hi everybody,

    I am working on a conversion of VFP application into C#. In VFP I have the following code

          IF empty(m.lcRequest)
              SELECT * FROM b_instr1 b WHERE exists ;
              (SELECT 1 FROM csrAllSched s WHERE s.resource1 = b.instr_id and s.layer = 1 and s.schdassign = .t.) ;
              ORDER BY instr_id INTO CURSOR b_instr readwrite
          ELSE
             SELECT * FROM b_instr1 b WHERE exists ;
              (SELECT 1 FROM csrAllSched s WHERE s.resource1 = b.instr_id and s.layer = 1 and s.schdassign = .t.) ;
             UNION ; 
             SELECT * FROM b_instr1 WHERE ALLTRIM(instr_id) == m.lcRequest ;  
             ORDER BY instr_id INTO CURSOR b_instr readwrite
          endif    

    In C# I have two data tables dtInstructors and dtAllScheduled. I want to get again dtInstructors but eliminate all rows that are not qualified.

    My first question is how to translate the query above into LINQ and my second question how can I get the same dtInstructors after query, e.g. later I am processing that same dtInstructors and so for my purpose I just need to somehow eliminate rows that don't have corresponding rows in dtAllSchedule with layer = 1 and schdassign = true.

    Thanks a lot in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 10, 2014 8:29 PM

Answers

  • Hi Naomi,

    >>Here is what I wrote so far but I don't think I am correct:

    I just made a small test with a simailr scenario however, it could work fine and return rows whose amount is reduced as:

    DataTable dtOrder = new DataTable("Order");
    
                    DataTable dtOrderDetail = new DataTable("OrderDetail");
    
                    SqlCommand cmd = new SqlCommand("select * from [Order]", connection);
    
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                    da.Fill(dtOrder);
    
                    cmd = new SqlCommand("select * from [OrderDetail]", connection);
    
                    da = new SqlDataAdapter(cmd);
    
                    da.Fill(dtOrderDetail);
    
                    DataTable newDTOrder = dtOrder.Clone();
    
                    dtOrder.AsEnumerable().Where(order => !dtOrderDetail.AsEnumerable().Any(od => od.Field<int>("OrderID") == order.Field<int>("OrderID"))).CopyToDataTable(newDTOrder, LoadOption.OverwriteChanges);

    I notice that the difference is that you use the original datatable while I clone a new one. You can have a try my way.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred BaoModerator Wednesday, June 11, 2014 2:54 AM
    • Marked as answer by Naomi N Wednesday, June 11, 2014 6:52 PM
    Wednesday, June 11, 2014 2:54 AM
    Moderator
  • Want to share the solution I went up with (thanks for your help and help of David Browne who I contacted by email):

       // Remove instructors who don't have anything planned
                          dtInstructors = dtInstructorsAll.Clone();                      
    
                          var allScheduledByInstructor = dtAllScheduled.AsEnumerable()
                                                              .Where(b => 1 == b.Field<Byte>("layer") && true == b.Field<Boolean>("schdassign"))
                                                              .ToLookup(c => c.Field<String>("resource1").TrimEnd());
    
                          var query = from i in dtInstructorsAll.AsEnumerable()
                                      where allScheduledByInstructor.Contains(i.Field<String>("instr_id").TrimEnd())
                                      || (!String.IsNullOrWhiteSpace(bRow.RequiredResource) && i.Field<String>("instr_id").TrimEnd() == bRow.RequiredResource)
                                      select i;
                          //var scheduledInstructors = allScheduledByInstructor.Select(g => g.Key).ToList();
                          //var instructors = dtInstructorsAll.AsEnumerable().Select(r => r.Field<String>("instr_id")).ToList();
    
                          foreach (var row in query)
                          {
                              dtInstructors.ImportRow(row);
                          }


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Naomi N Friday, June 13, 2014 1:44 AM
    Thursday, June 12, 2014 3:00 PM

All replies

  • Hello Naomi,

    I know little about the VPF, from the query, it is similar with TSQL, so I assume it is the same with TSQL. For TSQL, for the Exists, the translation in LINQ to SQL is the any operator and the ORDER BY is translated as:

    from s in context.shift
    
    where context.employeeshift.Any(es=>(es.shiftid==s.shiftid)&&(es.empid==57))
    
    orderby s.ID
    
    select s;

    For details, you can refer to below links:

    Basic LINQ Query Operations (C#)

    101 LINQ Samples(This contains most TSQL operations as exists, orderby and join).

    >> how can I get the same dtInstructors after query, e.g. later I am processing that same dtInstructors and so for my purpose I just need to somehow eliminate rows that don't have corresponding rows in dtAllSchedule with layer = 1 and schdassign = true.

    From your description, it seems that you are looking for the “Not Exists” syntax, is it right?

    If it is, you just need to add “!” symbol as:

    where !context.employeeshift.Any(es=>(es.shiftid==s.shiftid)&&(es.empid==57))

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, June 11, 2014 2:00 AM
    Moderator
  • Hi Fred,

    Thanks for your response. As I said, I have two data tables DataTable dtInstructors and dtAllScheduled. I need to process dtInstructors data table. From that table I need to eliminate rows that don't confirm my conditions, so I need to somehow get the same dtInstructors but with less rows. Do you know how can I get it?

    Here is what I wrote so far but I don't think I am correct:

      // Remove instructors who don't have anything planned
                          var query = dtInstructors.AsEnumerable().Where(c => dtAllScheduled.AsEnumerable().Any(b =>  1 == b.Field<Byte>("layer") 
                                              && true==b.Field<Boolean>("schassign") && c.Field<String>("instr_id")== b.Field<String>("resource_id")
                                              ));
                          query.CopyToDataTable(dtInstructors, LoadOption.OverwriteChanges);


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, June 11, 2014 2:18 AM
  • Hi Naomi,

    >>Here is what I wrote so far but I don't think I am correct:

    I just made a small test with a simailr scenario however, it could work fine and return rows whose amount is reduced as:

    DataTable dtOrder = new DataTable("Order");
    
                    DataTable dtOrderDetail = new DataTable("OrderDetail");
    
                    SqlCommand cmd = new SqlCommand("select * from [Order]", connection);
    
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                    da.Fill(dtOrder);
    
                    cmd = new SqlCommand("select * from [OrderDetail]", connection);
    
                    da = new SqlDataAdapter(cmd);
    
                    da.Fill(dtOrderDetail);
    
                    DataTable newDTOrder = dtOrder.Clone();
    
                    dtOrder.AsEnumerable().Where(order => !dtOrderDetail.AsEnumerable().Any(od => od.Field<int>("OrderID") == order.Field<int>("OrderID"))).CopyToDataTable(newDTOrder, LoadOption.OverwriteChanges);

    I notice that the difference is that you use the original datatable while I clone a new one. You can have a try my way.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred BaoModerator Wednesday, June 11, 2014 2:54 AM
    • Marked as answer by Naomi N Wednesday, June 11, 2014 6:52 PM
    Wednesday, June 11, 2014 2:54 AM
    Moderator
  • Thanks, Fred. Using clone seems to work well.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, June 11, 2014 6:53 PM
  • Want to share the solution I went up with (thanks for your help and help of David Browne who I contacted by email):

       // Remove instructors who don't have anything planned
                          dtInstructors = dtInstructorsAll.Clone();                      
    
                          var allScheduledByInstructor = dtAllScheduled.AsEnumerable()
                                                              .Where(b => 1 == b.Field<Byte>("layer") && true == b.Field<Boolean>("schdassign"))
                                                              .ToLookup(c => c.Field<String>("resource1").TrimEnd());
    
                          var query = from i in dtInstructorsAll.AsEnumerable()
                                      where allScheduledByInstructor.Contains(i.Field<String>("instr_id").TrimEnd())
                                      || (!String.IsNullOrWhiteSpace(bRow.RequiredResource) && i.Field<String>("instr_id").TrimEnd() == bRow.RequiredResource)
                                      select i;
                          //var scheduledInstructors = allScheduledByInstructor.Select(g => g.Key).ToList();
                          //var instructors = dtInstructorsAll.AsEnumerable().Select(r => r.Field<String>("instr_id")).ToList();
    
                          foreach (var row in query)
                          {
                              dtInstructors.ImportRow(row);
                          }


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Naomi N Friday, June 13, 2014 1:44 AM
    Thursday, June 12, 2014 3:00 PM