locked
Query multiple datatables RRS feed

  • Question

  • User-590375999 posted

    hi i have 4 data tables

    1.Files

    2.RFiles

    3.GFiles

    4.RestrictedFiles

    how to query

    (Files.Filename = RFiles.FileName  and  RFiles.Filename not in RectrictedFiles )  or  (Files.Filename = GFiles.FileName  and  GFiles.Filename not in RectrictedFiles )

    Wednesday, November 22, 2017 9:46 AM

All replies

  • User-707554951 posted

    Hi sivapooja,

    1.Files

    2.RFiles

    3.GFiles

    4.RestrictedFiles

    Just use the following code:

                Database1Entities context = new Database1Entities();
                //Files.Filename = RFiles.FileName  and  RFiles.Filename not in RectrictedFiles
                foreach(File file in  context.Files)
                {
                    foreach(RFile  rfile in context.RFiles)
                    {
                        if(file.Name == rfile.Name)
                        {
                            bool flag = false;
                            foreach(RestrictedFile refile in context.RestrictedFiles)
                            {
                                if(rfile.Name== refile.Name)
                                {
                                    flag = true;
                                }
                            }
                            if(flag == false)
                            {
                                Response.Write(rfile.Name+"|");
                            }
                        }
    
                    }
                }
                Response.Write("<br />");
                //Files.Filename = GFiles.FileName  and  GFiles.Filename not in RectrictedFiles 
                foreach (File file in context.Files)
                {
                    foreach (GFile gfile in context.GFiles)
                    {
                        if (file.Name == gfile.Name)
                        {
                            bool flag = false;
                            foreach (RestrictedFile refile in context.RestrictedFiles)
                            {
                                if (gfile.Name == refile.Name)
                                {
                                    flag = true;
                                }
                            }
                            if (flag == false)
                            {
                                Response.Write(gfile.Name + "|");
                            }
                        }
    
                    }
                }

    Output:

    Or use code below:

     Database1Entities context = new Database1Entities();
                //Files.Filename = RFiles.FileName  and  RFiles.Filename not in RectrictedFiles
                var result = (from file in context.Files join rfile in context.RFiles on file.Name equals rfile.Name select new { rfile.Name }).ToList();
                var result2  = (from refile in context.RestrictedFiles select refile.Name).ToList();
                List<string> names = new List<string>();
                foreach (var obj in result )
                {
                    bool flag = false;
                    foreach (string  refile in result2)
                    {
                        if (obj.Name == refile)
                        {
                            flag = true;
                        }
    
                    }
                    if (flag == false)
                    {
                        names.Add(obj.Name);
                    }
    
                }
                GridView1.DataSource = names;
                GridView1.DataBind();

    Output:

    Best regards

    Cathy

    Thursday, November 23, 2017 6:08 AM
  • User-590375999 posted

    Hi Cathy,

    i don't want to use for loop , if tables contains 10 hundred thousand rows then it will hangs, i want to use linq to filter the rows with above condition

    Friday, November 24, 2017 7:59 AM
  • User991499041 posted

    Hi Sivapooja,

    hi i have 4 data tables

    1.Files

    2.RFiles

    3.GFiles

    4.RestrictedFiles

    how to query

    (Files.Filename = RFiles.FileName  and  RFiles.Filename not in RectrictedFiles )  or  (Files.Filename = GFiles.FileName  and  GFiles.Filename not in RectrictedFiles )

    To convert query into LINQ, you can use tools like LINQPad.

    It helps learning LINQ and convert SQL statements.

    Regards,

    zxj

    Friday, November 24, 2017 8:28 AM
  • User-707554951 posted

    Hi sivapooja,

       Database1Entities context = new Database1Entities();
                var result3 = (from file in context.Files
                               from refile in context.RFiles
                               where file.Name == refile.Name && !context.RestrictedFiles.Select(c => c.Name).ToList().Contains(file.Name)
                               select new
                               {
                                   name = file.Name
                             }).Distinct().ToList();
            
                GridView1.DataSource = result3;
                GridView1.DataBind();
       
                var result4 = (from file in context.Files
                               from refile in context.GFiles
                               where file.Name == refile.Name && !context.RestrictedFiles.Select(c => c.Name).ToList().Contains(file.Name)
                               select new
                               {
                                   name = file.Name
                               }).Distinct().ToList();
    
                GridView2.DataSource = result4;
                GridView2.DataBind();

    Output:



    Best regards

    Cathy

    Friday, November 24, 2017 8:59 AM