locked
.NET Core C# Table Joins and Query Strings RRS feed

  • Question

  • User1391845369 posted

    I am fairly new to .NET Core and have been working with some Microsoft YouTube videos to create a new API .NET Core project.

    In the process I discovered Scaffold-DbContext.  I was successful in getting this to process through the NuGet Package Manager Console.  

    It created all the models for my database, but the issue is figuring out how to write code to pass a Id through the URI then perform a Database Query.  

    All the examples online that I have found use DBContext and DBSet.  I am able to pull all the data from one table SQL Table but can not figure out how to go about passing the URI Id and query my database using Table Joins.  

    I need good full blown examples if possible.

    Any help would be greatly appreciated.

    As I am new at this, please pardon my stupidly.  :)

    Wednesday, March 27, 2019 10:16 PM

Answers

  • User1391845369 posted

    This helped out a lot, thank you.

    [HttpGet("{id}")]
            //[HttpGet]
            public async Task<ActionResult<SearchData>> GetSearchData(string id) 
            {
                 
    
                var query = from dg in _context.EDispositionDestinationGroup
                                   join et in _context.ETimes on dg.DocumentId equals et.DocumentId
                                   join ep in _context.EPatientPatientNameGroup on dg.DocumentId equals ep.DocumentId
                                   join ea in _context.EResponseAgencyGroup on dg.DocumentId equals ea.DocumentId
                                   join pc in _context.PatientCareReport on dg.DocumentId equals pc.DocumentId
                                   where dg.EDisposition02 == id
                                   select new FacilityMetaData
                                   {
                                       FacilityOrgId = OrganizationId,
                                       FirstName = ep.EPatient02,
                                       LastName = ep.EPatient03,
                                       MiddleName = ep.EPatient04,
                                       PatientTransferTime = et.ETimes12,
                                       DocumentId = pc.DocumentId,
                                   }
                     
                    ;
    
    
    
                //foreach (var resultEntry in query )
                //{
    
                    //    foreach (var pcr in resultEntry)
                    //    {
                    //        // get each pcr property 
                    //    }
                    //}
    
                      var searchData = await query.ToListAsync();
    
    
                    return new SearchData();
                
    
    
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 3, 2019 2:45 PM

All replies

  • User1724605321 posted

    Hi steven0608,

    I am able to pull all the data from one table SQL Table but can not figure out how to go about passing the URI Id and query my database using Table Joins.  

    I'm not sure about your current scenario ,  do you want to use Raw SQL Queries in EF Core :

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

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

    var orderHeaders = db.OrderHeaders.FromSql(
                        @"select c.Name as CustomerName, o.DateCreated, sum(oi.Price) as TotalPrice, 
                        count(oi.Price) as TotalItems
                        from  OrderItems  oi 
                        inner join Orders o on oi.OrderId = o.OrderId
                        inner join Customers c on o.CustomerId = c.CustomerId
                        group by oi.OrderId, c.Name, o.DateCreated");

    If i misunderstand your requirement, please feel free to let me know .

    Best Regards,

    Nan Yu

    Thursday, March 28, 2019 5:18 AM
  • User1391845369 posted

    This is my code:

    I have the 3 basic elements for a .NET Core API:

    1. SearchController - used for capturing the URI
    2. SearchContext - Processes the URI and the database call
    3. PatientCareReport - is the Entity data model for just one table call PatientCareReport

    SearchController.cs

    namespace Rescue.Exchange.API.Controllers
    {
        //This controller will Post all data driven by a SQL query 
        [Route("api/[controller]")]
        [ApiController]
        public class SearchController : ControllerBase
        {
    
            private readonly SearchContext _context;
    
            public SearchController(SearchContext context)
            {
                _context = context;
    
    
                if (_context.SearchData.Count() == 0)
                {
                    // If SearchData from PatientCareReport Entity Model  collection is empty, then throw a flag               
    
                }
    
            }
    
    
            public async Task<ActionResult<IEnumerable<PatientCareReport>>> GetSearchData()
    
            {
    
                return await _context.SearchData.ToListAsync();
            }
    
            
        }
    }

    SearchContext.cs

    namespace Rescue.Exchange.API.Models
    {
    
        
        public class SearchContext : DbContext
        {
    
            public SearchContext(DbContextOptions<SearchContext> options)
                : base(options)
            {
            }
    
            //Pulls DB Model from SearchData.cs model and from SearchData Class and pulls the table profile name from:[Table("PatientCareReport")] 
            public DbSet<PatientCareReport> SearchData { get; set; }
        }
    
    }

    PatientCareReports.cs

    [Table("PatientCareReport")]
        public partial class PatientCareReport
        {
            
            public PatientCareReport()
            {
                ETimes = new HashSet<ETimes>();
            }
    
            [Key]
            [Column("pk_PatientCareReport")]
            public long PkPatientCareReport { get; set; }
            [Column("fk_Header")]
            public long FkHeader { get; set; }
            public Guid DocumentId { get; set; }
    
            [InverseProperty("FkPatientCareReportNavigation")]
            public ICollection<ETimes> ETimes { get; set; }
        }

    This a sample of a query I want to pass using the above method,   and ed.[eDisposition.02]) = '781' will by my key from the URI.

    SELECT p.[pk_PatientCareReport], p.[DocumentId], e.[eResponse.02],
    ep.[ePatient.02] AS LastName, ep.[ePatient.03] as FirstName, [ePatient.04] as MiddleInitial,
    et.[eTimes.12],
    ed.[eDisposition.02] 
    
    FROM [DATABASENAME].[dbo].[PatientCareReport] p
    join [eResponse.AgencyGroup] e on p.[DocumentId] = e.[DocumentId]
    join [ePatient.PatientNameGroup] ep on p.[DocumentId] = ep.[DocumentId]
    join [eTimes] et on p.[DocumentId] = et.[DocumentId]
    join [eDisposition.DestinationGroup] ed on p.[DocumentId] = ed.[DocumentId]
    where convert(varchar(255), ed.[eDisposition.02]) = '781'  

    So how do I incorporate the your above same query string in to what I currently have implemented?  
    I am still a bit green with C#  and the .NET Core world. 

    Thursday, March 28, 2019 2:29 PM
  • User1724605321 posted

    Hi steven0608,

    If using raw sql , you can use parameterized queries  to pass parameters in sql query :

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

    but i would suggest you could learn querying related data using Navigational Properties in EF :

    https://www.learnentityframeworkcore.com/relationships 

    https://www.tektutorialshub.com/entity-framework-tutorial/#Loading-Related-Data 

    and use  LINQ to Entities :

    https://www.tektutorialshub.com/linq-to-entities/join-query-entity-framework/ 

    Best Regards,

    Nan Yu

    Friday, March 29, 2019 2:45 AM
  • User1391845369 posted

    This helped out a lot, thank you.

    [HttpGet("{id}")]
            //[HttpGet]
            public async Task<ActionResult<SearchData>> GetSearchData(string id) 
            {
                 
    
                var query = from dg in _context.EDispositionDestinationGroup
                                   join et in _context.ETimes on dg.DocumentId equals et.DocumentId
                                   join ep in _context.EPatientPatientNameGroup on dg.DocumentId equals ep.DocumentId
                                   join ea in _context.EResponseAgencyGroup on dg.DocumentId equals ea.DocumentId
                                   join pc in _context.PatientCareReport on dg.DocumentId equals pc.DocumentId
                                   where dg.EDisposition02 == id
                                   select new FacilityMetaData
                                   {
                                       FacilityOrgId = OrganizationId,
                                       FirstName = ep.EPatient02,
                                       LastName = ep.EPatient03,
                                       MiddleName = ep.EPatient04,
                                       PatientTransferTime = et.ETimes12,
                                       DocumentId = pc.DocumentId,
                                   }
                     
                    ;
    
    
    
                //foreach (var resultEntry in query )
                //{
    
                    //    foreach (var pcr in resultEntry)
                    //    {
                    //        // get each pcr property 
                    //    }
                    //}
    
                      var searchData = await query.ToListAsync();
    
    
                    return new SearchData();
                
    
    
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 3, 2019 2:45 PM