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

  • Question

  • User-540818677 posted

    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

    Friday, March 26, 2021 11:20 AM

All replies

  • User475983607 posted

    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. 

    Friday, March 26, 2021 11:30 AM
  • User-540818677 posted

    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?

    Friday, March 26, 2021 11:43 AM
  • User475983607 posted

    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

    Friday, March 26, 2021 11:54 AM
  • User-540818677 posted

    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?

    Friday, March 26, 2021 12:25 PM
  • User475983607 posted

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

    Friday, March 26, 2021 1:23 PM
  • User-474980206 posted

    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

    Friday, March 26, 2021 3:18 PM