Run raw SQL statement inside my .NET core console application and map the results to C# variables RSS

• • Reply
  johnjohn1231...

  Member

  492 Points

  2569 Posts

  Run raw SQL statement inside my .NET core console application and map the results to C# variables

  Mar 26, 2021 11:20 AM|johnjohn123123|LINK

  I have the following SQL statement to get data from multiple tables inside my SQL Server database:

  SELECT max(ad.ORG_NAME) AS "Account", wo.WORKORDERID AS "Request ID", max(aau.FIRST_NAME) AS "Requester", max(aci.emailid) "Email ID",max(wo.TITLE) AS "Request Title", max(ti.FIRST_NAME) AS "Technician", longtodate(max(srm.Responsetime)) AS "Survey responded Time", max(srcmt.COMMENTTEXT) AS "Comments",max(srm.result) "Over All Satisfaction Level" FROM SurveyResponseRequestMapping srrm 
  INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID 
  INNER JOIN Survey_Response_Answer sra ON srm.RESPONSEID=sra.RESPONSEID 
  LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID 
  LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID 
  LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
  LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
  LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
  LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
  LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
  INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid 
  INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id
  left join aaausercontactinfo auci on auci.user_id = sdu.userid 
  left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
  where srm.RESPONSETIME>=DATETOLONG('2021-03-22 00:00:00') and srm.RESPONSETIME<=DATETOLONG('2021-03-31 23:59:59')
  GROUP BY wo.WORKORDERID order by 7

  My question is if can I run this raw SQL statement inside my .NET core console application and map the returned columns into C# variables?

  Thanks

• • Reply
  mgebhard

  All-Star

  53711 Points

  24031 Posts

  Re: Run raw SQL statement inside my .NET core console application and map the results to C# varia...

  Mar 26, 2021 11:30 AM|mgebhard|LINK

  johnjohn123123

  I have the following SQL statement to get data from multiple tables inside my SQL Server database:

  SELECT max(ad.ORG_NAME) AS "Account", wo.WORKORDERID AS "Request ID", max(aau.FIRST_NAME) AS "Requester", max(aci.emailid) "Email ID",max(wo.TITLE) AS "Request Title", max(ti.FIRST_NAME) AS "Technician", longtodate(max(srm.Responsetime)) AS "Survey responded Time", max(srcmt.COMMENTTEXT) AS "Comments",max(srm.result) "Over All Satisfaction Level" FROM SurveyResponseRequestMapping srrm 
  INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID 
  INNER JOIN Survey_Response_Answer sra ON srm.RESPONSEID=sra.RESPONSEID 
  LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID 
  LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID 
  LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
  LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
  LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
  LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
  LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
  INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid 
  INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id
  left join aaausercontactinfo auci on auci.user_id = sdu.userid 
  left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
  where srm.RESPONSETIME>=DATETOLONG('2021-03-22 00:00:00') and srm.RESPONSETIME<=DATETOLONG('2021-03-31 23:59:59')
  GROUP BY wo.WORKORDERID order by 7

  My question is if can I run this raw SQL statement inside my .NET core console application and map the returned columns into C# variables?

  Thanks

  Take advantage of raw queries in EF Core.  The official docs cover the details. 

  https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

  https://www.learnentityframeworkcore.com/raw-sql

  I take advantage of raw queries often in EF Core.  Keep in mind it is better to use a stored procedure if you think the logic might change in the future.  It is usually easier to update a proc than deploy an application. 

• • Reply
  johnjohn1231...

  Member

  492 Points

  2569 Posts

  Re: Run raw SQL statement inside my .NET core console application and map the results to C# varia...

  Mar 26, 2021 11:43 AM|johnjohn123123|LINK

  mgebhard

  johnjohn123123

  I have the following SQL statement to get data from multiple tables inside my SQL Server database:

  SELECT max(ad.ORG_NAME) AS "Account", wo.WORKORDERID AS "Request ID", max(aau.FIRST_NAME) AS "Requester", max(aci.emailid) "Email ID",max(wo.TITLE) AS "Request Title", max(ti.FIRST_NAME) AS "Technician", longtodate(max(srm.Responsetime)) AS "Survey responded Time", max(srcmt.COMMENTTEXT) AS "Comments",max(srm.result) "Over All Satisfaction Level" FROM SurveyResponseRequestMapping srrm 
  INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID 
  INNER JOIN Survey_Response_Answer sra ON srm.RESPONSEID=sra.RESPONSEID 
  LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID 
  LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID 
  LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
  LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
  LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
  LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
  LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
  INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid 
  INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id
  left join aaausercontactinfo auci on auci.user_id = sdu.userid 
  left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
  where srm.RESPONSETIME>=DATETOLONG('2021-03-22 00:00:00') and srm.RESPONSETIME<=DATETOLONG('2021-03-31 23:59:59')
  GROUP BY wo.WORKORDERID order by 7

  My question is if can I run this raw SQL statement inside my .NET core console application and map the returned columns into C# variables?

  Thanks

  Take advantage of raw queries in EF Core.  The official docs cover the details. 

  https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

  https://www.learnentityframeworkcore.com/raw-sql

  I take advantage of raw queries often in EF Core.  Keep in mind it is better to use a stored procedure if you think the logic might change in the future.  It is usually easier to update a proc than deploy an application. 

  Thanks a lot for the reply. i actually read your great documentation before.. but in your documentation you always refer a single table, as follow:-

   db.Authors.

  then you write the raw sql on this single table, but in my case i have many tables involved,, so how i can use your approach? can you provide an example please?

• • Reply
  mgebhard

  All-Star

  53711 Points

  24031 Posts

  Re: Run raw SQL statement inside my .NET core console application and map the results to C# varia...

  Mar 26, 2021 11:54 AM|mgebhard|LINK

  johnjohn123123

  then you write the raw sql on this single table, but in my case i have many tables involved,, so how i can use your approach? can you provide an example please?

  await _dbContext.Database.ExecuteSqlInterpolatedAsync(sql: @$"
      IF NOT EXISTS(SELECT (1) FROM dbo.SomeTable 
          WHERE Id = {request.id} AND OtherId = {request.OtherId})
              BEGIN
                  INSERT INTO dbo.aTable(Id, OtherId)
                  VALUES({request.id}, {request.OtherId})
              END");

  https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlinterpolatedasync?view=efcore-5.0

  https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlrawasync?view=efcore-5.0

• • Reply
  johnjohn1231...

  Member

  492 Points

  2569 Posts

  Re: Run raw SQL statement inside my .NET core console application and map the results to C# varia...

  Mar 26, 2021 12:25 PM|johnjohn123123|LINK

  mgebhard

  johnjohn123123

  then you write the raw sql on this single table, but in my case i have many tables involved,, so how i can use your approach? can you provide an example please?

  await _dbContext.Database.ExecuteSqlInterpolatedAsync(sql: @$"
      IF NOT EXISTS(SELECT (1) FROM dbo.SomeTable 
          WHERE Id = {request.id} AND OtherId = {request.OtherId})
              BEGIN
                  INSERT INTO dbo.aTable(Id, OtherId)
                  VALUES({request.id}, {request.OtherId})
              END");

  https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlinterpolatedasync?view=efcore-5.0

  https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlrawasync?view=efcore-5.0

  but how i can get the columns from the SQL statement as c# variables?

• • Reply
  mgebhard

  All-Star

  53711 Points

  24031 Posts

  Re: Run raw SQL statement inside my .NET core console application and map the results to C# varia...

  Mar 26, 2021 01:23 PM|mgebhard|LINK

  johnjohn123123

  but how i can get the columns from the SQL statement as c# variables?

  My last example return an Task<int>.  The pattern is used to INSERT/UPDATE data.

  To query data use the following pattern.

      public class VendorModel
      {
          public int VendorId { get; set; }
          public string VendorName { get; set; }
          public string Address { get; set; }
          public string City { get; set; }
          public string State { get; set; }
          public string ZipCode { get; set; }
          public int? MetrosId { get; set; }
      }

  Register the model

          protected override void OnModelCreating(ModelBuilder modelBuilder)
          {
              modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");
  
              modelBuilder.Entity<VendorModel>().HasNoKey();

  Set the results to the model. 

  static async Task Main(string[] args)
  {
      int id = 1;
      using (var context = new DemoDbContext())
      {
  
          VendorModel result = await context.Set<VendorModel>().FromSqlRaw($@"SELECT [VendorId]
                          ,[VendorName]
                          ,[Address]
                          ,[City]
                          ,[State]
                          ,[ZipCode]
                          ,[MetrosId]
                      FROM[dbo].[Vendor]
                      WHERE[VendorId] = {id}").FirstOrDefaultAsync();
  
          Console.WriteLine(result.VendorId);
      }      
  }

• • Reply
  bruce (sqlwo...

  All-Star

  58464 Points

  15788 Posts

  Re: Run raw SQL statement inside my .NET core console application and map the results to C# varia...

  Mar 26, 2021 03:18 PM|bruce (sqlwork.com)|LINK

  EF raw sql queries require a data set be defined in the dbcontext for the query, so it always looks like a single table query, as the query is defined the dataset.

  if you are not tied to EF, dapper may be a better choice. it just maps result sets to poco objects.

  https://github.com/StackExchange/Dapper

  bruce (sqlwork.com)