如何使用左外连接在linq中连接四个表。 [英] How to join four tables in linq using left outer join.

问题描述

我的数据库中有四个表，我使用linq查询从所有表中检索数据。我确实离开了联盟加入了所有表格，但它花费了太多时间。如何优化这个查询，



我尝试过：



我已经尝试了下面的查询，但对于30000条记录来说，它的时间太长，超过10分钟。

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.

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();

任何帮助都将不胜感激。

谢谢，

Mukesh

any help would be appreciated.
Thanks,
Mukesh

推荐答案

Hi,

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.

你可以尝试兄弟。

只需要必要的列和表。

You can try bro.
Just necessary columns and tables.

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()

下面是工作代码。忘了发帖。

below is the working code. forgot to post.

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 :-)

这篇关于如何使用左外连接在linq中连接四个表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！