How to join four tables in linq using left outer join.

Question

4.00/5 (1 vote)

See more:

I have four tables in my database , I am using linq query to retrieve data from all the tables. I did left join to join all the tables it is giving the correct result but it is taking too much time. how to optimize this query,

What I have tried:

I have tried the below query but it is too much time like more than 10 minutes for 30000 records.

C#

var searchedData = (from a in objContext.FileProgresses
     join pg in objContext.V01_PG on a.ProDocsId equals (int?)pg.ID into pgs
     from m in pgs.DefaultIfEmpty()
     join pr in objContext.V01_PR on m.ID equals pr.PAGE into prs
     from p in prs.DefaultIfEmpty()
     join ds in objContext.DOCSTATs on p.DOCSTAT equals ds.ID into docs
     from docst in docs.DefaultIfEmpty()
     where a.FullPath.Contains(txtSearchText.Text)
     select new
     {
          a.Id,
          a.FullPath,
          a.Filename,
          a.Extension,
          a.Received,
          a.Downloaded,
          a.Unsupported,
          a.ProcessByAbbyy,
          a.AvailableInProDocs,
          a.AvailableInDocs2Go,
          a.Done,
          p.BARCODE,
          m.DOCNO,
          p.REVISION,
          docst.NAME
     }).ToList();

any help would be appreciated.
Thanks,
Mukesh

Posted 11-May-16 0:59am

Mukesh Pr@sad

Updated 16-Jun-16 3:45am

Andy Lanng

v3

Add a Solution

Comments

Rob Philpott 11-May-16 12:01pm

How long does it take, if you execute an equivalent SQL query in SSMS or whatever?

Mukesh Pr@sad 12-May-16 2:20am

more than 10 minutes

Rishikesh_Singh 12-May-16 3:08am

Can you check what is the difference in your query when you run directly in ssms and query generated by linq in sql profiler

Mukesh Pr@sad 12-May-16 6:17am

Sorry But it is taking same time at both the places . means More than 10 minutes.

Mukesh Pr@sad 12-May-16 6:34am

Below is the equivalent sql query which is taking 13:03 minutes to retrieve the data.
is there any query better than this. Please help.

select FileProgress.Id, fullpath,Extension, Received, Downloaded,Unsupported,ProcessByAbbyy,AvailableInProDocs,Done,DOCNO,REVISION,DOCSTAT.NAME
From FileProgress
Left Join V01_PG on FileProgress.ProDocsId=V01_PG.ID
Left Join V01_PR on V01_PG.ID = V01_PR.PAGE
Left Join DOCSTAT on V01_PR.DOCSTAT = DOCSTAT.ID
where FileProgress.FullPath like '%2014AL0206%'

Mukesh Pr@sad 12-May-16 7:30am

It is taking time because of the last join . If I am removing the last join then it is working fine.

3 solutions

Solution 1

Hi,

C#

var searchedData = (from a in objContext.FileProgresses
     join pg in objContext.V01_PG on a.ProDocsId equals (int?)pg.ID into pgs
     from m in pgs.DefaultIfEmpty()
     join pr in objContext.V01_PR on m.ID equals pr.PAGE into prs
     from p in prs.DefaultIfEmpty()
     join ds in objContext.DOCSTATs on p.DOCSTAT equals ds.ID into docs
     from docst in docs.DefaultIfEmpty()
     select new
     {
          Id = a.Id,FullPath = a.FullPath etc.
}).AsQueryable().AsNoTracking().Where(x=>x.FullPath.Contains(Searchtext)).ToList();

Can you try this code bro ?
I'm wait result. Please,feedback.
Have a good day.

Posted 12-May-16 2:51am

Sinan Ergin

Updated 12-May-16 2:53am

v2

Comments

Mukesh Pr@sad 12-May-16 9:42am

AsNoTracking() is giving error.

Sinan Ergin 12-May-16 9:58am

Hmm. Look at I'm Solution 2.

Rishikesh_Singh 12-May-16 22:31pm

Instead of creating multiple solution , edit the existing solution.

Solution 2

You can try bro.
Just necessary columns and tables.

C#

var searchedData = 
  (from a in objContext.FileProgresses
     where a.FullPath.Contains(txtSearchText.Text)
     select new
     {
          ItemA = a,
          IEnumerable<otheritems> = 
                from m in objContext.V01_PG
                join pr in objContext.V01_PR on m.ID equals pr.PAGE 
                      into prs from p in prs.DefaultIfEmpty()
                join ds in objContext.DOCSTATs on p.DOCSTAT equals ds.ID 
                      into docs from docst in docs.DefaultIfEmpty() 
                where (int?)m.ID == a.ProDocsId 
                select new { p.BARCODE,  m.DOCNO, p.REVISION, docst.NAME }
  }).ToList()

Posted 12-May-16 4:10am

Sinan Ergin

Updated 12-May-16 16:34pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mukesh Pr@sad · Accepted Answer · 2016-06-16T03:45:00

below is the working code. forgot to post.

C#

var DistincItem = (from a in objContext.FileProgresses
                                    join pg in objContext.V01_PG on a.ProDocsId equals (int?)pg.ID into pgs
                                    from g in pgs.DefaultIfEmpty()
                                    join pr in objContext.V01_PR on g.ID equals pr.PAGE into prs
                                    from p in prs.DefaultIfEmpty()
                                    where a.FullPath.Contains(extension)
                                    select new
                                    {
                                        a.Id,
                                        a.FullPath,
                                        a.Filename,
                                        a.Extension,
                                        a.Received,
                                        a.Downloaded,
                                        a.Unsupported,
                                        a.ProcessByAbbyy,
                                        a.AvailableInProDocs,
                                        a.AvailableInDocs2Go,
                                        a.Done,
                                        p.BARCODE,
                                        g.DOCNO,
                                        p.REVISION,
                                        a.NotAllowed,
                                        p.DOCSTAT
                                    }).ToList();

Thanks for the help :-)