c# - 如何在一个控制器/JOIN 中从我的数据库返回/获取两个模型/表或使用存储过程

Question

我正在尝试创建获取请求，当提供代理 ID 时，它会返回 Broker 表中的所有代理信息以及 Application 表中的所有代理应用程序。这两个表通过代理 ID 连接。

我尝试了一些在 stakoverflow 上找到的方法，但一直遇到错误。

public class BrokerApplications
    {
        public virtual ICollection<Broker> Broker { get; set; }
        public virtual ICollection<Application> Applications { get; set; }
    }

    

我试过了

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

但是收到一条错误消息说

"cs0029: 不能将类型 'System.Linq.IQueryable<SMWEBAPI.Models.Application> 隐式转换为 'Microsoft.AspNetCore.Mvc.ActionResult<SMWebAPI.Controllers.BrokerAppsController.BrokerApplicattions>

这是我尝试的时候

[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;

}

但是当我尝试从 URL 调用时，这会返回以下错误消息

InvalidCastException：无法转换类型为“Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable 1[SMWebAPI.Models.Application]' to type 'System.Collections.Generic.List1 [SMWebAPI.Models.Application]”的对象。BrokerAppsController.cs + lstApps = (List)_context.Application.Where(a => a.BrokerId == brokerID) 中的 SMWebAPI.Controllers.BrokerAppsController.GetBrokerApplications(int brokerID)；

有人可以帮我在提供brokerID时如何从这个Get方法返回吗？

---

更新

到目前为止，我还无法返回数据，因为当我使用一种没有给我任何错误的方法时，它返回了一个空数组。

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

所以...

我想找到另一种获取数据的方法。

我创建了一个存储过程

    -- =======================================================
-- 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

我运行它以确保它使用

USE SecuredMessagingDatabase;  
GO  
EXEC dbo.ApplicationsByBroker @BrokerId = 1000001;

我得到了预期的结果，我将如何在我的 web api 中执行它？

标签： c#sqlasp.net-corestored-proceduresentity-framework-core

Answer 1

Include如果两个对象之间有 关系，最好使用BorkerId，但你可以这样写来解决问题：

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

  
}