none
How to convert this SQL to Linq RRS feed

  • Question

  • Hi people,

    I need the following converted to Linq:

    SELECT DISTINCT Application_Master.Licensed, Application_Master.Site, 
    COUNT([RIC_MASTER].RIC) AS RIC, Application_Master.BusinessActivity, RIC_MASTER.Application
    FROM Application_Master, RIC_MASTER
    WHERE Application_Master.Application=RIC_MASTER.Application 
    AND RIC_MASTER.AuditReport_ID = Application_Master.AuditReport_ID 
    AND RIC_MASTER.AuditReport_ID = @AuditReportID
    GROUP BY Application_Master.Licensed, Application_Master.Site,
    Application_Master.BusinessActivity, RIC_MASTER.Application
    
    

    Actually, I need it to be converted into something like this:

    var results = from order in ds.Tables[0].AsEnumerable()
                  join prod in ds.Tables[1].AsEnumerable() on order.Field<int>("ProdID") equals prod.Field<int>("ProdID")
                  group prod by order.Field<int>("CustID") into custGroup
                  select new
                  {
                      CustID = custGroup.Key,
                      Products = custGroup.Select( n => n.Field<string>("ProdName" ))
                  };
    
    

     

     

    Sunday, October 23, 2011 12:41 AM

Answers

  • Hi BlueMarkwe;

    I am not totally sure but try the following and see if it returns the wanted results.

     

    var ctx = new MyDataContext();
    
    var results = from am in ctx.Application_Master
    		from rm in ctx.RIC_MASTER
    		where am.Application == rm.Application 
    			&& rm.AuditReport_ID == am.AuditReport_ID 
    			&& rm.AuditReport_ID == AuditReportID
    		group new { am , rm } by new { am.Licensed, am.Site, am.BusinessActivity, rm.Application } into arGroup
    		from ar in arGroup
    		select new {
    			ar.am.Licensed, 
    			ar.am.Site, 
    			RIC = ar.rm.RIC.Count(), 
    			ar.am.BusinessActivity, 
    			ar.rm.Application
    		};
    

     
    Note that the variable in the query AuditReportID is a variable defined in your program.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, October 23, 2011 2:22 PM
  • Hi Fernandoo,

    Thanks for responding.

    I think I am missing the:

    select new
                  {
                      CustID = custGroup.Key,
                      Products = custGroup.Select( n => n.Field<string>("ProdName" ))
                  };
    

    I'll work it out in the meantime.





    Monday, October 24, 2011 11:02 AM

All replies

  • Hi BlueMarkwe;

    I am not totally sure but try the following and see if it returns the wanted results.

     

    var ctx = new MyDataContext();
    
    var results = from am in ctx.Application_Master
    		from rm in ctx.RIC_MASTER
    		where am.Application == rm.Application 
    			&& rm.AuditReport_ID == am.AuditReport_ID 
    			&& rm.AuditReport_ID == AuditReportID
    		group new { am , rm } by new { am.Licensed, am.Site, am.BusinessActivity, rm.Application } into arGroup
    		from ar in arGroup
    		select new {
    			ar.am.Licensed, 
    			ar.am.Site, 
    			RIC = ar.rm.RIC.Count(), 
    			ar.am.BusinessActivity, 
    			ar.rm.Application
    		};
    

     
    Note that the variable in the query AuditReportID is a variable defined in your program.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, October 23, 2011 2:22 PM
  • Hi Fernandoo,

    Thanks for responding.

    I think I am missing the:

    select new
                  {
                      CustID = custGroup.Key,
                      Products = custGroup.Select( n => n.Field<string>("ProdName" ))
                  };
    

    I'll work it out in the meantime.





    Monday, October 24, 2011 11:02 AM
  • Hi BlueMarkwe;

    If this is Linq to SQL then this will NOT work in the query:

    Products = custGroup.Select( n => n.Field<string>("ProdName" ))

    Because the Linq to SQL does not have a Field<...>() method. If this is Linq to DataSet then you would be able to use Field<...>() because it does implement the method.

    Please post the query as you implemented it and any code around it and what Linq technology you are trying top use.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 24, 2011 2:06 PM
  • Hi Fernandoo,

    Currently, I have stored procedures to query SQLServer and return data in dataset. I would break down the dataset in the code and bind it to the excel app. The query works on testing small tables but takes forever in a real table. So I am trying to switch to Linq.

    So now I have Linq that query data directly to SQLServer, I assume you mean its Linq to SQL. I am not sure what Linq to Dataset is at the moment.


    BlueMarker
    • Edited by BlueMarker Monday, October 24, 2011 2:32 PM
    Monday, October 24, 2011 2:32 PM
  • Hi Fernandoo,

    Let me try the following that you've mentioned in the other tread as Linq to Dataset:

    var results = from order in ds.Tables[0].AsEnumerable()

    Assuming ds contain data from stored procedure which query 2 tables.

    BlueMarker


    • Edited by BlueMarker Monday, October 24, 2011 2:37 PM
    Monday, October 24, 2011 2:35 PM
  • Hi Fernandoo,

    I am trying to convert this:

    SELECT DISTINCT Application_Master.Licensed, Application_Master.Site, 
    COUNT([RIC_MASTER].RIC) AS RIC, Application_Master.BusinessActivity, RIC_MASTER.Application
    FROM Application_Master, RIC_MASTER
    WHERE Application_Master.Application=RIC_MASTER.Application 
    AND RIC_MASTER.AuditReport_ID = Application_Master.AuditReport_ID 
    AND RIC_MASTER.AuditReport_ID = @AuditReportID
    GROUP BY Application_Master.Licensed, Application_Master.Site,
    Application_Master.BusinessActivity, RIC_MASTER.Application
    
    
    

    into the linq you provided from the other tread:

    var results = from order in ds.Tables[0].AsEnumerable()
                  join prod in ds.Tables[1].AsEnumerable() on order.Field<int>("ProdID") equals prod.Field<int>("ProdID")
                  group prod by order.Field<int>("CustID") into custGroup
                  select new
                  {
                      CustID = custGroup.Key,
                      Products = custGroup.Select( n => n.Field<string>("ProdName" ))
                  };
    
    foreach( var cust in results )
    {
        string products = cust.Products.Aggregate( "", ( buildStr, nextName ) => buildStr + ", " + nextName ).Substring( 1 );
        Console.WriteLine( cust.CustID + "\t" + products );
    } 
    
    

    Of course I am not going to use Console.WriteLine(.....) as it is a webpage.

    So what am I doing wrong with the following query?:

    var results = from am in appMasterRICMaster.Tables[0].AsEnumerable() from rm in appMasterRICMaster.Tables[1].AsEnumerable()
          where am.Field<string>("Application") == rm.Field<string>("Application")
          && rm.Field<string>("AuditReport_ID") == am.Field<string>("AuditReport_ID")
          && rm.Field<string>("AuditReport_ID") == auditReportNum
          group am by am.Field<string>("Licensed"), am.Field<string>("Site"), am.Field<string>("BusinessActivity"), rm.Field<string>("Application") into arGroup
          select new
          {
              BusinessActivity = arGroup.Key,
              Application = arGroup.Select(n => n.Application)
          };
    

     

     

     

     


    • Edited by BlueMarker Monday, October 24, 2011 3:35 PM
    Monday, October 24, 2011 3:32 PM
  • Hi BlueMarkwe;

    When posting questions about Linq to XXX please post in the following Forums:

    For Linq to DataSet where the source of the data comes from a DataSet/DataTable post to:
    ADO.NET DataSet http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/threads

    For Linq to Entity Framework
    ADO.NET Entity Framework and LINQ to Entities http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads

    For Linq to a technology not in this list post here:
    LINQ Project General http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/threads

    For Linq to SQL where the datasource comes directly from MS SQL Server
    LINQ to SQL http://social.msdn.microsoft.com/Forums/en-US/linqtosql/threads

    For Linq to XML where datasource comes from an XML document.
    XML, System.Xml, MSXML and XmlLite http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/threads


    To the current question, when you are grouping with multiple columns you need to wrap the key in a new { ... } block as shown in the code snippet below.

     

    var results = from am in appMasterRICMaster.Tables[0].AsEnumerable() 
    		from rm in appMasterRICMaster.Tables[1].AsEnumerable()
    		where am.Field<string>("Application") == rm.Field<string>("Application")
    		  && rm.Field<string>("AuditReport_ID") == am.Field<string>("AuditReport_ID")
    		  && rm.Field<string>("AuditReport_ID") == auditReportNum
    		group new { am, rm } by new { am by am.Field<string>("Licensed"), 
    			am.Field<string>("Site"), 
    			am.Field<string>("BusinessActivity"), 
    			rm.Field<string>("Application") } into arGroup
    		select new
    		{
    		    BusinessActivity = arGroup.Key,
    		    Application = arGroup.rm.Select(n => n.Application)
    		};

    The BusinessActivity is a structure of all the keys.

     

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 24, 2011 4:37 PM
  • Hi Fernandoo,

    I got an error "Invalid annoymous type member declarator. Annoymous type member declarator must be declared with a member assignment, simple name or member access." below:

    var results = from am in appMasterRICMaster.Tables[0].AsEnumerable() 
    	from rm in appMasterRICMaster.Tables[1].AsEnumerable()
                    where am.Field<string>("Application") == rm.Field<string>("Application")
    	&& rm.Field<string>("AuditReport_ID") == am.Field<string>("AuditReport_ID")
    	&& rm.Field<string>("AuditReport_ID") == auditReportNum
    	group new { am, rm } by new { am by am.Field<string>("Licensed"), 
    	am.Field<string>("Site"), 
    	am.Field<string>("BusinessActivity"), 
    	rm.Field<string>("Application") } into arGroup
    	select new
    	{
    	  BusinessActivity = arGroup.Key,
    	  Application = arGroup.rm.Select(n => n.Application)
    	};

    errors with:
    am.Field<string>("Licensed"),
    am.Field<string>("Site"),
    am.Field<string>("BusinessActivity"),
    rm.Field<string>("Application")

     sorry, I am new at these.



    • Edited by BlueMarker Monday, October 24, 2011 5:31 PM
    Monday, October 24, 2011 5:25 PM
  • Looks like the following will work...

    var results = from am in appMasterRICMaster.Tables[0].AsEnumerable() 
    	from rm in appMasterRICMaster.Tables[1].AsEnumerable()
    	where am.Field<string>("Application") == rm.Field<string>("Application")
    	&& rm.Field<string>("AuditReport_ID") == am.Field<string>("AuditReport_ID")
    	&& rm.Field<string>("AuditReport_ID") == auditReportNum
    	group new { am, rm } by new { Licensed = am.Field<string>("Licensed"),  
    	Site = am.Field<string>("Site"), 
    	BusinessActivity = am.Field<string>("BusinessActivity"), 
    	Application = rm.Field<string>("Application") } into arGroup
    	select new
    	{
    	  BusinessActivity = arGroup.Key,
    	  Application = arGroup.Select(n => n.rm.Field<string>("Application"))
                     };
    


     

     

    Monday, October 24, 2011 5:46 PM
  • Hi BlueMarker;

    Because you are using DataTable that are not strongly typed the multiple columns need to be given names. Replace the group by clause by the following group by clause:

    	group new { am, rm } by new { Licensed = am.Field<string>("Licensed"), 
    	Site = am.Field<string>("Site"), 
    	BusinessActivity = am.Field<string>("BusinessActivity"), 
    	Application = rm.Field<string>("Application") } into arGroup
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 24, 2011 5:47 PM
  • Hi Fernandoo,

    Any idea why I get an error at:

    var results = from am in appMasterRICMaster.Tables[0].AsEnumerable() 
    		                                  from rm in appMasterRICMaster.Tables[1].AsEnumerable()
    		                                  where am.Field<string>("Application") == rm.Field<string>("Application")
    		                                  && rm.Field<string>("AuditReport_ID") == am.Field<string>("AuditReport_ID")
    		                                  && rm.Field<string>("AuditReport_ID") == auditReportNum
    		                                  group new { am, rm } by new { Licensed = am.Field<string>("Licensed"), //am by am.Field<string>("Licensed"), 
    			                              Site = am.Field<string>("Site"), 
    			                              BusinessActivity = am.Field<string>("BusinessActivity"), 
    			                              Application = rm.Field<string>("Application") } into arGroup
    		                                  select new
    		                                  {
                                                  //Licensed = arGroup.Key,
                                                  //Site = arGroup.Key,
    		                                      BusinessActivity = arGroup.Key,
    		                                      Application = arGroup.Select(n => n.rm.Field<string>("Application"))
                                                  //Application = arGroup.rm.Select(n => n.Application)
    		                                  };
    
    // Create new DataTable and DataSource objects.
                                DataTable table = new DataTable();
    
                                // Declare DataColumn and DataRow variables.
                                DataColumn column;
                                DataRow row;
                                DataView view;
    
    // Create third column.
                                column = new DataColumn();
                                column.DataType = System.Type.GetType("System.String");
                                column.ColumnName = "BusinessActivity";
                                table.Columns.Add(column);
    
                                // Create fourth column.
                                column = new DataColumn();
                                column.DataType = System.Type.GetType("System.String");
                                column.ColumnName = "Application";
                                table.Columns.Add(column);
    
                                foreach (var cust in results)
                                {
                                    row = table.NewRow();
    
                                    //row["Application"] = cust.Application;
                                    
                                    //row["Licensed"] = cust.Licensed;
                                    //row["Site"] = cust.Site;
                                    row["BusinessActivity"] = cust.BusinessActivity;
                                    row["Application"] = cust.Application.Aggregate("", (buildStr, nextName) => buildStr + ", " + nextName).Substring(1);
                                    table.Rows.Add(row);
                                }
    

    Error at foreach(var cust in results)

    Error: "Column 'Application' does not belong to table Table.



    • Edited by BlueMarker Monday, October 24, 2011 7:09 PM
    Monday, October 24, 2011 7:09 PM
  • Ahh silly me, first table of the dataset doesn't have "Application" selected in the query.

    I'll fix.

    Monday, October 24, 2011 7:43 PM
  • Hi BlueMarker;

    As I stated in a eearlierpost that the group key arGroup.Key is a structure and needs the dot operator to access the individual parts of the key. So in the select clause to get the parts of the key do it like this:

    select new
    {
        Licensed = arGroup.Key.Licensed,
        Site = arGroup.Key.Site,
        BusinessActivity = arGroup.Key.BusinessActivity,
        // Because the group now contains two collection of table rows access rm
        // table as follows
        Application = arGroup.rm.Select(n => n.Field<string>("Application"))
    };
    

    Then the foreach loop should be something like this.

    foreach (var cust in results)
    {
        row = table.NewRow();
    
        row["Licensed"] = cust.Licensed;
        row["Site"] = cust.Site;
        row["BusinessActivity"] = cust.BusinessActivity;
        row["Application"] = cust.Application.Aggregate("", (buildStr, nextName) => buildStr + ", " + nextName).Substring(1);
        table.Rows.Add(row);
    }
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 24, 2011 8:11 PM
  • Hi Fernandoo,

    Again, thanks for the info.

    I will have to debug at home tonight...running out of time and had to do other task first. So far, the code above took forever to finish, I will have to isolate it on another page to execute it and hopes it won't take so long.

    Monday, October 24, 2011 10:50 PM
  • Hi,

    I tried converting the following SQL to LINQ but with errors.

    SQL:

     SELECT
     sum(s.totpresent) as totpresent,
     sum(s.notanswer) as Totaben ,
     (sum(s.totpresent) - sum(s.notanswer))
      as totans FROM 
     (SELECT t1.resourceID, t1.resourceName, t1.assignedTeamID 
      FROM Resource AS t1 WHERE t1.dateInactive is null) 
      AS x INNER JOIN (select resourceid, 
      count(resourceid) as totpresent, 
      sum(case when talktime=0 THEN 1 ELSE 0 END) as notanswer,
       AVG(talktime) as avtalk, MAX(talktime) as maxtalk 
       from agentconnectiondetail A INNER JOIN contactcalldetail C 
       ON A.sessionid = C.sessionid AND 
       A.sessionseqnum = C.sessionseqnum AND 
       A.nodeid = C.nodeid AND C.contactdisposition = '2' AND 
       C.contacttype <> '6' AND C.startDateTime >= @startdate
       GROUP BY resourceid) AS s ON s.resourceid = x.resourceID

    LINQ:

     public IQueryable chrt2(DateTime startDate)
           {
               return from x in
                          (from x in
                               (
                                   (from t1 in db.Resources
                                    where
                                      t1.dateInactive == null
                                    select new
                                    {
                                        t1.resourceID,
                                        t1.resourceName,
                                        t1.assignedTeamID
                                    }))
                           join s in
                               (
                                   (from a in db.AgentConnectionDetails
                                    join c in db.ContactCallDetails
                                          on new { a.sessionID, a.sessionSeqNum, a.nodeID, contactDisposition = "2", contactType != "6", startDateTime >= startDate }
                                      equals new { c.sessionID, c.sessionSeqNum, c.nodeID, c.contactDisposition, c.contactType, c.startDateTime }
                                    group a by new
                                    {
                                        a.resourceID
                                    } into g
                                    select new
                                    {
                                        resourceID = (System.Int32?)g.Key.resourceID,
                                        totpresent = (Int64?)g.Count(p => p.resourceID != null),
                                        notanswer = (System.Int64?)g.Sum(p => (
                                        p.talkTime == 0 ? 1 : 0)),
                                        avtalk = (System.Double?)g.Average(p => p.talkTime),
                                        maxtalk = (System.Int16?)g.Max(p => p.talkTime)
                                    })) on x.resourceID equals s.resourceID
                           select new
                           {
                               s.totpresent,
                               s.notanswer,
                               Dummy = "x"
                           })
                      group x by new { x.Dummy } into g
                      select new
                      {
                          totpresent = (Int64?)g.Sum(p => p.totpresent),
                          Totaben = (System.Int64?)g.Sum(p => p.notanswer),
                          totans = (System.Int64?)(g.Sum(p => p.totpresent) - g.Sum(p => p.notanswer))
                      };
           }


    I receive following errors:

    Error 1 The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.
    Error 2 The name 'contactType' is not in scope on the left side of 'equals'.  Consider swapping the expressions on either side of 'equals'.

    Error 3 The name 'startDateTime' is not in scope on the left side of 'equals'.  Consider swapping the expressions on either side of 'equals'.

    Error 4 An anonymous type cannot have multiple properties with the same name

    Error 5 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.

    Error 7 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.

    Saturday, March 23, 2013 4:25 PM
  • Hi hussain.f;

    Please kindly open a new question thread for this question.

    Thank you and have a nice day.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, March 24, 2013 2:26 PM