Reputation: 149
I am trying to create get request that when provided with a broker ID, it returns all the broker information from the Broker table and all of the brokers Applications from the Application table. The two tables are joined by broker ID.
I have tried a few methods found on stakoverflow but keep hitting errors.
public class BrokerApplications
{
public virtual ICollection<Broker> Broker { get; set; }
public virtual ICollection<Application> Applications { get; set; }
}
I tried
[HttpGet("{id}")]
public async Task<ActionResult<BrokerApplications>> GetBrokerApplicationsAsync(int brokerID)
{
var brokerApps = (from b in _context.Broker
join a in _context.Application on b.BrokerId equals a.BrokerId
where a.BrokerId == brokerID
// select new { b.BrokerForename, b.BrokerId, a.RollNumber, a.CustomerFullName }
//).ToList();
select a);
return brokerApps;
}
But got an error message saying
"cs0029:cannot implicitly covert type 'System.Linq.IQueryable<SMWEBAPI.Models.Application> to 'Microsoft.AspNetCore.Mvc.ActionResult<SMWebAPI.Controllers.BrokerAppsController.BrokerApplicattions>
This is when I tried
[HttpGet("{id}")] public async Task<ActionResult> GetBrokerApplicationsAsync(int brokerID) { var broker = await _context.Broker.FindAsync(brokerID);
BrokerApplications BrokerApps = new BrokerApplications();
List<Application> lstApps = new List<Application>();
lstApps = (List<Application>)_context.Application.Where(a => a.BrokerId == brokerID);
BrokerApps.Broker = (ICollection<Broker>)broker.ToList();
BrokerApps.Applications = lstApps;
return BrokerApps;
}
but this return the following error message when I tried to call from the URL
InvalidCastException: Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable
1[SMWebAPI.Models.Application]' to type 'System.Collections.Generic.List
1[SMWebAPI.Models.Application]'. SMWebAPI.Controllers.BrokerAppsController.GetBrokerApplications(int brokerID) in BrokerAppsController.cs + lstApps = (List)_context.Application.Where(a => a.BrokerId == brokerID);
Can someone please help me in how to return from this Get method when provided with a brokerID please?
UPDATE
I haven't been able to return the data as of yet, as when I used a method that didn't give me any errors it returned an empty array.
[HttpGet("{id}")]
public async Task<ActionResult<BrokerApplications>> GetBrokerApplicationsAsync(int brokerID)
{
return new BrokerApplications()
{
Broker = await _context.Broker.Where(x => x.BrokerId == brokerID).ToListAsync(),
Applications = await _context.Application.Where(a => a.BrokerId == brokerID).ToListAsync()
};
}
SO...
I wanted to find another way to get the data.
I have created a stored procedure
-- =======================================================
-- Create Stored Procedure Template for Azure SQL Database
-- =======================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
CREATE PROCEDURE ApplicationsByBroker
(
-- Add the parameters for the stored procedure here
@brokerId int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Insert statements for procedure here
SELECT dbo.Application.*, dbo.Broker.*
FROM dbo.Application, dbo.Broker
WHERE (dbo.Broker.BrokerID = @brokerId AND dbo.Application.BrokerID =
@brokerId)
END
GO
I ran this to ensure it worked using
USE SecuredMessagingDatabase;
GO
EXEC dbo.ApplicationsByBroker @BrokerId = 1000001;
And I got the expected results, How would I execute this in my web api?
Upvotes: 0
Views: 1083
Reputation: 27528
Try the following query, which have one roundtrip to database. I hope EF will not fail.
class BrokerApplication
{
public Broker Broker {get; set;)
public Application Application {get; set;)
}
[HttpGet("{id}")]
public async Task<ActionResult<BrokerApplications>> GetBrokerApplicationsAsync(int brokerID)
{
var brokers = _context.Broker.Where(x => x.Id == brokerID)
.Select(b => new BrokerApplication { Broker = b });
var applications = _context.Application.Where(a => a.BrokerId == brokerID)
.Select(a => new BrokerApplication { Application = a });
var combined = await brokers.Concat(applications).ToListAsync();
var result = BrokerApplications
{
Broker = combined.Where(c => c.Broker != null)
.Select(c => c.Broker).ToList(),
Applications = combined.Where(c => c.Application != null)
.Select(c => c.Application).ToList()
};
return result;
}
Upvotes: 0
Reputation: 5102
It's better to use Include
if two objects have a relation by BorkerId
, but you can write like this to solve the problem:
[HttpGet("{id}")]
public async Task<ActionResult<BrokerApplications>> GetBrokerApplicationsAsync(int brokerID)
{
return new BrokerApplications()
{
Broker = await _context.Broker.Where(x=>x.Id==brokerID).ToListAsync(),
Applications = await _context.Application.Where(a => a.BrokerId == brokerID).ToListAsync()
};
}
Upvotes: 1