Query multiple datatables

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 )

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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