locked
Converting SQL Query with SubQuery to LINQ RRS feed

  • Question

  • User-1842880510 posted

    Hi all;

    I have been using LINQ in my app in a very rudimentary fashion for the past 3 years because all of our data calls have been for the most part been done in Stored Procedures and we just had to do var resultset = db.SPName(param1, param2).toList() or ToFirstOrDefault().  All of the  complex joins were in the stored procedures.  Now I need to do a complex join in a LINQ statement for a sub-report so that I am not doing a database call for each record in the main report.  I am confused by all the examples I am finding on the web so I thoroughly baffled.

    Here is the situation.  I've got four tables:

    • prc_FundDoc (FD) has a parent to child 1 to many relationship to prc_FundDocLines  (FDL) (FundDocID is FK field in FDL)
    • prc_FundDocLinks relates FundDocs to each other so that structure is FundDocLinkID, int PK, FundDocID1 int, FundDocID2 int (Hey I didn't design it) so this table FD has two many to many foreign key relationships to the Links table.
    • Finally the prc_Requirements table has a foreign key 1 to many relationship with the prc_FundDocLines, reqID being the FK field.
    • Fund Docs do not always have Lines, but Fund Docs of type "Direct" or "Both" always have lines whereas Reimburse don't have to have lines but if they don't they are linked to a "Direct" Fund Doc which does.

    Here is the query that works in the stored Procedure

    Select reqID 
    FROM prc_Requirements reqInfo
       --Fund Doc Info
       Left JOIN (Select FD.FundDocNum, FD.Type, FDL.ReqID, Fdl.FundDocID, fdl.LineItem 
    			 FROM VFS_ProcurementTracking..prc_FundDocs FD 
    				LEFT JOIN VFS_ProcurementTracking..prc_FundDocLines FDL 
    				ON FD.FundDocID = FDL.FundDocID Where FD.Type = 'Direct') FundDocsDirect
        ON reqINFO.ReqID = FundDocsDirect.ReqID
      LEFT JOIN VFS_ProcurementTracking..prc_FundDocLinks FDL 
    	ON FundDocsDirect.FundDocID = FDL.FundDocID1 OR FundDocsDirect.FundDocID = FDL.FundDocID2
      Left JOIN (Select FDL.ReqID, FD.FundDocNum, FD.Type, FD.FundDocID
    			 FROM VFS_ProcurementTracking..prc_FundDocs FD
    			 LEFT JOIN VFS_ProcurementTracking..prc_FundDocLines FDL 
    				ON FD.FundDocID = FDL.FundDocID 
    			 Where FD.Type = 'Reimburse') FundDocsReim
        ON (FDL.FundDocID2 = FundDocsReim.FundDocID OR FDL.FundDocID1 = FundDocsReim.FundDocID) OR FundDocsReim.ReqID = reqInfo.ReqID

    So how does one convert this into LINQ?

    Thanks in advance for your assistance.
     

     

    Monday, July 31, 2017 9:03 PM

Answers

  • User1120430333 posted

    Are these Left Joins?  I don't see how they differ from inner joins. 

    I need to be sure that these are left joins since I've already been caught out when the Stored Procedure with the left JOINs got overwritten with one with inner joins and the users screamed bloody murder.

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

    do you know about Linqpad, because you formulate your Linq statement and execute it using Lingpad and getting the query to work before doing it in the .NET program.

    https://www.linqpad.net/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 2, 2017 1:19 PM

All replies

  • User-335504541 posted

    Hi joeller,

    Please try to use the following code:

                var reqID = from req in db.Requirements
                            join FundDocsDirect in (from FD in db.FundDoc
                                                    join FDL in db.FundDocLines
                                                    on FD.FundDocID equals FDL.FundDocID
                                                    where FD.Type == "Direct"
                                                    select new
                                                    {
                                                        FD.FundDocNum,
                                                        FD.Type,
                                                        FDL.ReqID,
                                                        FDL.FundDocID,
                                                        FDL.LineItem
                                                    }
                                  ) on req.reqID equals FundDocsDirect.ReqID
                            join FDL in db.FundDocLinks
                            on FundDocsDirect.FundDocID equals FDL.FundDocID1
                            join FDL2 in db.FundDocLinks
                             on FundDocsDirect.FundDocID equals FDL2.FundDocID2
                            join FundDocsReim in (from FD in db.FundDoc
                                                  join FDL in db.FundDocLines
                                                  on FD.FundDocID equals FDL.FundDocID
                                                  select new
                                                  {
                                                      FDL.ReqID,
                                                      FD.FundDocNum,
                                                      FD.Type,
                                                      FD.FundDocID
                                                  }
                            ) on FDL.FundDocID1 equals FundDocsReim.FundDocID
                            join FundDocsReim2 in (from FD in db.FundDoc
                                                   join FDL in db.FundDocLines
                                                   on FD.FundDocID equals FDL.FundDocID
                                                   select new
                                                   {
                                                       FDL.ReqID,
                                                       FD.FundDocNum,
                                                       FD.Type,
                                                       FD.FundDocID
                                                   }
                            ) on FDL.FundDocID2 equals FundDocsReim2.FundDocID
                            join FundDocsReim3 in (from FD in db.FundDoc
                                                   join FDL in db.FundDocLines
                                                   on FD.FundDocID equals FDL.FundDocID
                                                   select new
                                                   {
                                                       FDL.ReqID,
                                                       FD.FundDocNum,
                                                       FD.Type,
                                                       FD.FundDocID
                                                   }
                            )on req.reqID equals FundDocsReim3.ReqID
                            select req.reqID;

    Best Regards,

    Billy

    Tuesday, August 1, 2017 6:35 AM
  • User-1842880510 posted

    Are these Left Joins?  I don't see how they differ from inner joins. 

    I need to be sure that these are left joins since I've already been caught out when the Stored Procedure with the left JOINs got overwritten with one with inner joins and the users screamed bloody murder.

    Tuesday, August 1, 2017 1:12 PM
  • User475983607 posted

    If the query is working as expected, then I would look into submitting the SQL directly to SQL server rather than converting SQL to linq.  Linq just converts the expression to SQL anyway.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-directly-execute-sql-queries

    Tuesday, August 1, 2017 2:00 PM
  • User-1842880510 posted

    I enter this into the web app and ran into an issue.

    I just gave you an abbreviated version of the Query because I was more concerned about the JOINs.  However the resultset that really needs to be returned is something like:

    Select req.ReqID, req.RequirementNumber, req.RequisitionNumber, req.CC, req.Customer, FundDocReimburse.FundDocNumber as FundDocNumReim, FundDocDirect, FundDocNumDirect, .....

    So having a FundDocReim, FundDocReim2, and FundDocReim3 would not really work.

    Tuesday, August 1, 2017 2:05 PM
  • User1120430333 posted

    Are these Left Joins?  I don't see how they differ from inner joins. 

    I need to be sure that these are left joins since I've already been caught out when the Stored Procedure with the left JOINs got overwritten with one with inner joins and the users screamed bloody murder.

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

    do you know about Linqpad, because you formulate your Linq statement and execute it using Lingpad and getting the query to work before doing it in the .NET program.

    https://www.linqpad.net/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 2, 2017 1:19 PM
  • User-1842880510 posted

    OK I got some help designing the Left JOIN LINQ query from a developer here.  Problem was it took a godforsaken long time to run.  Point of Information; I was getting the initial result set of requirements to link my FundDocs queries to from else where in the class.  But the original resultset was the result complex object from the original stored procedure.  (We were trying to break out different parts of the resultset into subReports.)  So instead of trying to do another data call to try the rebuild that part of the original resultset, I just passed the original result set to the method building the Report Dataset Object for the subreport and used LINQ to select the needed properties of the resultset object into the Report Data Set object. Thus no need for a Left Join Fund Docs Query.  However, I did see a different way to do Left JOIN based on DA924's answer.  (The method I was using used two froms back to back, with the relationship set in the where clause.  I still don't understand how that was valid.  Looked just like a link version of the old ORACLE JOINs with the commas in the FROM clause and the equivalency set in the WHERE clause.  I am not sure I understand how the one referred on the page above works either, but I can see it is not just a "from x from y where x.field = y.field".

    Any way the point go to DA294 who answered my question about LEFT JOINs, so I can close  this thread although I resolved the issue on my own. (Fortunately unlike Experts Exchange I can award unlimited points.)

    Oh from what I can see LINQPad requires a download and install.  We can't do that here.

    Wednesday, August 2, 2017 10:02 PM