locked
Help with LINQ query RRS feed

  • Question

  • User-1842880510 posted

    Hello All;

    It appears that I am too stupid to understand LINQ at all.  I built a set of LINQ queries to populate a model object.  I followed examples that were working perfectly on the site or that were in the C# reference on Microsoft's https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/  .  However when I insert it into the action it throws all sorts of errors, and I can't figure out why.

    First some background.  I have a table called Requirements, which is related to another called PRSubLines by ReqID.  That table is related to another called ContractSubLines by PRSubLineID.  Both PRSubLines and ContractSubLines have a quantity field which I am trying to sum before joining them in my query Result.  In SQL this is very straight forward.  I am doing like this:

    	SELECT Distinct	Req.ReqID
    			,Req.ReqNum
    			,Req.DoDIC
    			,CC.CC_Description
    			,'' AMS
    			,Req.Quantity
    			,Isnull(PRQ.PRQTY, 0) [PRQTY]
    			,ISNULL(CtrQ.CtrQTY, 0) [CtrQty]
    			,Req.TargetPRSendDate
    	FROM prc_Requirements Req
    		LEFT JOIN prc_CCList CC ON Req.CC = CC.CCListID
    		LEFT JOIN (Select prsl.ReqID, Sum(IsNull(prsl.PRSubLineQty, 0)) PRQTY 
    					FROM prc_PRSubLines prsl
    					Group BY prsl.ReqID) PRQ ON Req.ReqID = prq.ReqID
    		LEFT JOIN  (SELECT prsl.ReqID, SUM(IsNull(csl.ContractSubLineQty, 0)) CtrQTY
    					FROM prc_PRSubLines prsl
    						JOIN prc_ContractSubLines csl on prsl.PRSubLineID = csl.PRSubLineID
    						join prc_ContractLines cl on csl.ContractLineID = cl.ContractLineID
    						join prc_Contracts c on cl.ContractID = c.ContractID
    						where c.IsLatestMod = 1
    					GROUP BY prsl.ReqID) CtrQ ON Req.ReqID = CtrQ.ReqID

    (I left out the Joins that I am not concerned with at this time so there will be some orphan aliases.)

    I tried to accomplish the same thing in LINQ like this, based on my research on how this should be constructed.

                var reqs = from req in db.prc_Requirements
                                   .Where(w => status == 0 || w.StatusID == status);
    
                var PrslQtys = (from prsl in db.prc_PRSubLines.Where(w => reqs.Select(s => s.reqID).Contains(w.ReqID))
                               group prsl by prsl.ReqID into j1
                               select new { ReqID = j1.Key, prslqty = j1.Sum(q1 => q1.PRSubLineQty) }).ToList();
    
                var CtrslQtys = (from ctsl in db.prc_PRSubLines.Select(s => s.prc_ContractSubLines.Where(w => w.prc_ContractLines.prc_Contracts.IsLatestMod).Where(w => reqs.Select(s1 => s1.reqID).Contains(w.prc_PRSubLines.ReqID ))) 
                                group ctsl by ctsl.prc_PRSubLines.ReqID into j2
                                select new { ReqID = j2.Key, ctslqty = j2.Sum(q1 => q1.ContractSubLineQty) }).toList();
    
    
                var listData = new RequirementIndexItem();
    
                            var poplistData = (from userReqs in reqs
                                               join PrslQtys on userReqs.ReqID equals PrslQtys.ReqID into j1
                                               join CtrslQtys on userReqs.ReqID equals CtrslQtys.ReqID into j2
                                                select new
                                                {
                                                    ReqID = userReqs.Req
                           
                            ReqNum = userReqs.ReqNum,
                                                    DoDIC = userReqs.DoDIC,
                                                    CC = userReqs.CC
                           
                            CC_Description = userReqs.prc_CCList.CC_Description,
                                                    Quantity = userReqs.Quantity,
                                                    PRQTY = PrslQtys.prslqty,
                                                    CtrQty = CtrslQty.ctslqty,
                                                    TargetPRSendDate = userReqs.TargetPRSendDate
                                                });
    
    
                listData = poplistData.ToList().Where(w => id == 0 || (id > 0 && dodics.Contains(w.DoDIC) || ccListIDs.Contains(w.CC.Value)))
                .Select(s => new RequirementIndexItem()
                {
                    ReqID = s.ReqID,
                    ReqNum = s.ReqNum,
                    DoDIC = s.DoDIC,
                    CC_Description = s.CC_Description,
                    AMS = "",
                    Quantity = s.Quantity,
                    PRQTY = s.PRQTY.GetValueOrDefault(0),
                    CtrQty = s.CtrQty.GetValueOrDefault(0),
                    TargetPRSendDate = s.TargetPRSendDate
                }).ToList();

    But this is not working.  The errors I am getting are showing:

    • In the query to create var CtrslQtys it says that ctsl.prc_PRSubLines does not exists yet I just used it successfully the line above. 
    • in the query to create var PopListData, it says it cannot see both of the created lists PrslQtys and CtrslQtys are variables but are used as types.  Then is says that there is no field prslqty in the List PrslQtys when I created it in the group by.

    Can someone see where I am going wrong.  As far as I can tell I doing exactly what was done in a different action elsewhere that is working just fine.

    Friday, November 3, 2017 8:15 PM

Answers

  • User-1842880510 posted

    I have an idea of using the Query that was originally used to derive out data (in the query above), and then apply the new constraints against that returned resultset in LINQ.

    So I tweaked the resultset of the original Stored Procedure then applied qualifiers against that List. Like this

                RightsMaskingHelper rmHelper = new RightsMaskingHelper();
                int ptUserRecID = _sessionHelper.Get<int>("UserRecID");
                var parentIDs = rmHelper.GetParentAMSGeneralIDs(ptUserRecID, db);
                parentIDs.Add(ptUserRecID);
                var dodics = rmHelper.GetDodicOrPPNForPTUsers(parentIDs, db);
                var ccListIDs = rmHelper.GetCCForPRUser(ptUserRecID, db);
    
                var poplistData = db.RequirementList_Get(id, status).ToList(); //stored Procedure results
    
    
                var listData = poplistData.Where(w => id == 0 || (id > 0 && dodics.Contains(w.DoDIC) || ccListIDs.Contains(w.CC.Value)))
                .Select(s => new RequirementIndexItem()
                {
                    ReqID = s.ReqID,
                    ReqNum = s.ReqNum,
                    DoDIC = s.DoDIC,
                    CC_Description = s.CC_Description,
                    AMS = "",
                    Quantity = s.Quantity,
                    PRQTY = s.PRQTY,
                    CtrQty = s.CtrQty,
                    TargetPRSendDate = s.TargetPRSendDate
                }).ToList();
    
                return listData;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 15, 2017 2:50 PM

All replies

  • User-707554951 posted

    Hi joeller,

    I suggest you could try online converter:

    Have you tried Linqer http://www.sqltolinq.com

    https://weblogs.asp.net/hajan/linqer-a-nice-tool-for-sql-to-linq-transition

    An SQL-> LINQ converter..

    Or LINQPad http://www.linqpad.net/

    Best regards

    Cathy

    Monday, November 6, 2017 9:36 AM
  • User-1842880510 posted

    Hi Cathy;

    We can't get to Linqer (www.sqltolinq.com) because it is blocked by the enterprise.

    The LinqPad seems to me to only be a means to test your LINQ.  which I am able to do just fine in Visual studio, but I wish I had better error messages.

    DA EntitySQL seems very interesting, but I am wondering whether it falls under our agency's prohibition against the  use of in-line SQL.

    I have an idea of using the Query that was originally used to derive out data (in the query above), and then apply the new constraints against that returned resultset in LINQ.

    I will try that.

    Monday, November 6, 2017 3:53 PM
  • User1120430333 posted

    DA EntitySQL seems very interesting, but I am wondering whether it falls under our agency's prohibition against the  use of in-line SQL

    I have an idea of using the Query that was originally used to derive out data (in the query above), and then apply the new constraints against that returned resultset in LINQ.

    Myself, I would just go to the EF backdoor, execute a sproc using a datareader  with the DTO pattern retuning the results in a generic collection a List<T>, which is no more than EF is really doing with the EF entities.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    With EF 6, you have to get the ObjectContext differently. I am not using the ObjectContext connection in the example  and just seeing if I could get the connection. Yes, I could get the connection

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data.Entity;
    using System.Data.Entity.Core.EntityClient;
    using System.Data.Entity.Infrastructure;
    using System.Data.SqlClient;
    using Entities;
    using DAL.Model;
    
    public List<DTOStudent> GetStudents()
            {
               
                var dtos = new List<DTOStudent>();
    
                using (var context = new CUDataEntities())
                {
                    var adapter = (IObjectContextAdapter)context;
                    var objectContext = adapter.ObjectContext;
    
                    var entityConn = objectContext.Connection as EntityConnection;
                    var dbConn = entityConn.StoreConnection as SqlConnection;
    
                    dbConn.Open();
    
                    var students = context.Students.ToList();
    
                    foreach(var stud in students)
                    {
                        var dto = new DTOStudent
                        {
                            StudentID = stud.StudentID,
                            FirstName = stud.FirstName,
                            LastName = stud.LastName,
                            EnrollmentDate = stud.EnrollmentDate
                        };
    
                        dtos.Add(dto);
                    }
                }
    
                return dtos;
            }

     

    Tuesday, November 7, 2017 3:27 PM
  • User-1842880510 posted

    I have an idea of using the Query that was originally used to derive out data (in the query above), and then apply the new constraints against that returned resultset in LINQ.

    So I tweaked the resultset of the original Stored Procedure then applied qualifiers against that List. Like this

                RightsMaskingHelper rmHelper = new RightsMaskingHelper();
                int ptUserRecID = _sessionHelper.Get<int>("UserRecID");
                var parentIDs = rmHelper.GetParentAMSGeneralIDs(ptUserRecID, db);
                parentIDs.Add(ptUserRecID);
                var dodics = rmHelper.GetDodicOrPPNForPTUsers(parentIDs, db);
                var ccListIDs = rmHelper.GetCCForPRUser(ptUserRecID, db);
    
                var poplistData = db.RequirementList_Get(id, status).ToList(); //stored Procedure results
    
    
                var listData = poplistData.Where(w => id == 0 || (id > 0 && dodics.Contains(w.DoDIC) || ccListIDs.Contains(w.CC.Value)))
                .Select(s => new RequirementIndexItem()
                {
                    ReqID = s.ReqID,
                    ReqNum = s.ReqNum,
                    DoDIC = s.DoDIC,
                    CC_Description = s.CC_Description,
                    AMS = "",
                    Quantity = s.Quantity,
                    PRQTY = s.PRQTY,
                    CtrQty = s.CtrQty,
                    TargetPRSendDate = s.TargetPRSendDate
                }).ToList();
    
                return listData;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 15, 2017 2:50 PM