WEB API实体框架父子发布到SQL数据库 [英] WEB API Entity-Framework Parent Child Posting to SQL Database
问题描述
我正在根据第三方将发送的数据编写我的第一个WEB-API服务。以下是他们将发送的布局：
I am writing my first WEB-API service based on data that a 3rd party will be sending. Below is the layout they will be sending:
<StandardTitleOrderRequest>
<Authentication>
<UserName>{$USERNAME}</UserName>
<Password>{$PASSWORD}</Password>
</Authentication>
<Borrowers>
<Borrower>
<FirstName>{$BORROWER_FIRST_NAME}</FirstName>
<MiddleName>{$BORROWER_MIDDLE_NAME}</MiddleName>
<LastName>{$BORROWER_LAST_NAME}</LastName>
<SSN>{$BORROWER_SSN}</SSN>
</Borrower>
</Borrowers>-
<Property>
<Address>{$SUBJECT_ADDRESS_STREET}</Address>
<City>{$SUBJECT_ADDRESS_CITY}</City>
<State>{$SUBJECT_ADDRESS_STATE}</State>
<Zip>{$SUBJECT_ADDRESS_ZIP}</Zip>
<County>{$SUBJECT_ADDRESS_COUNTY}</County>
</Property>-
<Contact>
<Name>{$CONTACT_NAME}</Name>
<Phone>{$CONTACT_PHONE}</Phone>
<Fax>{$CONTACT_FAX}</Fax>
<Email>{$CONTACT_EMAIL}</Email>
</Contact>-
<OrderInfo>
<LoanNumber>{$LOAN_NUMBER}</LoanNumber>
<LoanAmount>{$LOAN_AMOUNT}</LoanAmount>
<ToBeInsured/>
<FileNumber>{$FileID}</FileNumber>
<OrderId>{$ORDER_ID}</OrderId >
<CostCenter>{$BRANCH_NAME}</CostCenter>
<Product>{$PRODUCT_NUMBER}</Product>
<Notes>{$ORDER_NOTES}</Notes>
<ResponseURL>{$RESPONSE_URL}</ResponseURL>
<PID></PID>
<PayOffLoanNumber></PayOffLoanNumber>
<ClientCode>{$CLIENT_ID}</ClientCode>
</OrderInfo>
我用在以下4个表格（订单，借款人，联系人，属性）中，每个表格都具有OrderID。该订单将有多个借款人，1个联系人和1个财产。我建立了一个基本的网络api（使用Fiddler），可以让我GET / POST / PUT / DELETE订单，联系人或借款人或财产。我不确定如何添加发布订单的逻辑，然后添加该订单的每个子元素（借款人，联系人，属性）。我正在使用VS 2017和EF6。
I have created a database with the following 4 tables (Orders, Borrowers, Contacts, Properties) the OrderID is common to each table. The order will have multiple borrowers and 1 contact and 1 property. I have a basic web api built that (using Fiddler) will allow me to GET/POST/PUT/DELETE an order or contact or borrower or property. I am not sure how to add the logic that posts the order and then each of the child elements for that order (borrower(s), contact, property). I am using VS 2017 and EF 6.
我假设（也许是错误的）我需要向表中添加相互引用的外键，然后进行更新数据模型，但是当我这样做时，我开始遇到各种错误。
I am assuming (perhaps wrongly) that I need to add foreign keys to my tables that reference back to each other and then update the data model but when I do that I start to get various errors.
第三方将一次发布1个事务，因此传入的数据不会成批
The 3rd party will be posting 1 transaction at a time so the incoming data will not be in bulk and will be transmitted as XML.
我尝试查看整个Web api服务上的各种教程，但其中90％都只处理一个表POST。关于接收XML数据并发布到父级和1个或多个子级的特定细节，实际上没有任何内容。另外，我的应用当前仅接受JSON格式的传入数据。以下是OrdersController中的代码，这些代码将成为发布到数据库的基础（POST https：//
I have tried looking at various tutorials on the whole web api service but 90% of them just deal with a single table POST. Nothing really that goes into specific details about receiving XML data and posting to a parent and 1 or more children. Plus my app will currently only accept the incoming data in JSON format. Below is the code in the OrdersController that will be the basis for the post to the database (POST https://localhost:xxxx/api/Orders)am looking for some direction.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using OrdersDataAccess;
namespace waEtitle.Controllers
{
public class OrdersController : ApiController
{
/// <summary>
/// Get Order Information
/// </summary>
/// <returns></returns>
///
public IEnumerable<Order> Get()
{
using (FirstCloseEntities entities = new FirstCloseEntities())
{
return entities.Orders.ToList();
}
}
public HttpResponseMessage Get(int id)
{
using (FirstCloseEntities entities = new FirstCloseEntities())
{
var entity = entities.Orders.FirstOrDefault(o => o.OrderID == id);
if (entity != null)
{
return Request.CreateResponse(HttpStatusCode.OK, entity);
}
else
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with id = " + id.ToString() + " not found.");
}
}
}
public HttpResponseMessage Post([FromBody] Order order)
{
try
{
using (FirstCloseEntities entities = new FirstCloseEntities())
{
entities.Orders.Add(order);
entities.SaveChanges();
var message = Request.CreateResponse(HttpStatusCode.Created, order);
message.Headers.Location = new Uri(Request.RequestUri + order.OrderID.ToString());
return message;
}
}
catch (Exception ex)
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
}
}
public HttpResponseMessage Delete(int ID)
{
try
{
using (FirstCloseEntities entities = new FirstCloseEntities())
{
var entity = entities.Orders.FirstOrDefault(c => c.OrderID == ID);
if (entity == null)
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with ID = " + ID.ToString() + " not found to delete.");
}
else
{
entities.Orders.Remove(entity);
entities.SaveChanges();
return Request.CreateResponse(HttpStatusCode.OK);
}
}
}
catch (Exception ex)
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
}
}
public HttpResponseMessage Put(int id, [FromBody]Order order)
{
try
{
using (FirstCloseEntities entities = new FirstCloseEntities())
{
var entity = entities.Orders.FirstOrDefault(c => c.OrderID== id);
if (entity == null)
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with Id = " + id.ToString() + " not found to update.");
}
else
{
entity.LoanNumber = order.LoanNumber;
entity.LoanAmount = order.LoanAmount;
entity.ToBeInsured = order.ToBeInsured;
entity.FileNumber = order.FileNumber;
entity.CostCenter = order.CostCenter;
entity.Product = order.Product;
entity.Notes = order.Notes;
entity.ResponseURL = order.ResponseURL;
entity.PID = order.PID;
entity.PayOffLoanNumber = order.PayOffLoanNumber;
entity.ClientCode = order.ClientCode;
entities.SaveChanges();
return Request.CreateResponse(HttpStatusCode.OK, entity);
}
}
}
catch (Exception ex)
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
}
}
}
}
表：
/* ==Scripting Parameters==
Source Server Version : SQL Server 2008 R2 (10.50.1617)
Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
Source Database Engine Type : Standalone SQL Server
Target Server Version : SQL Server 2017
Target Database Engine Edition : Microsoft SQL Server Standard Edition
Target Database Engine Type : Standalone SQL Server
*/
USE [FirstCloseAPI]
GO
/****** Object: Table [dbo].[Borrowers] Script Date: 9/26/2017 1:50:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Borrowers](
[borID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[FirstName] [varchar](max) NULL,
[MiddleName] [varchar](max) NULL,
[LastName] [varchar](max) NULL,
[SSN] [varchar](max) NULL,
CONSTRAINT [PK_Borrowers] PRIMARY KEY CLUSTERED
(
[borID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Contacts] Script Date: 9/26/2017 1:50:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contacts](
[conId] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[Name] [varchar](max) NULL,
[Phone] [varchar](max) NULL,
[Fax] [varchar](max) NULL,
[Email] [varchar](max) NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[conId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Orders] Script Date: 9/26/2017 1:50:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[LoanNumber] [varchar](max) NULL,
[LoanAmount] [money] NULL,
[ToBeInsured] [bit] NULL,
[FileNumber] [varchar](max) NULL,
[CostCenter] [varchar](max) NULL,
[Product] [varchar](max) NULL,
[Notes] [varchar](max) NULL,
[ResponseURL] [varchar](max) NULL,
[PID] [int] NULL,
[PayOffLoanNumber] [varchar](max) NULL,
[ClientCode] [varchar](max) NULL,
CONSTRAINT [PK_OrderHeader] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Properties] Script Date: 9/26/2017 1:50:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Properties](
[proID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[Address] [varchar](max) NULL,
[City] [varchar](max) NULL,
[State] [varchar](max) NULL,
[Zip] [varchar](max) NULL,
[County] [varchar](max) NULL,
CONSTRAINT [PK_Property] PRIMARY KEY CLUSTERED
(
[proID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Borrowers] WITH NOCHECK ADD CONSTRAINT [FK_Borrowers_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[Borrowers] NOCHECK CONSTRAINT [FK_Borrowers_Orders]
GO
ALTER TABLE [dbo].[Contacts] WITH NOCHECK ADD CONSTRAINT [FK_Contacts_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[Contacts] NOCHECK CONSTRAINT [FK_Contacts_Orders]
GO
ALTER TABLE [dbo].[Properties] WITH NOCHECK ADD CONSTRAINT [FK_Properties_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[Properties] NOCHECK CONSTRAINT [FK_Properties_Orders]
GO
当前我的GET正在返回
Currently my GET is returning
<Order>
<Borrowers />
<ClientCode>cc1</ClientCode>
<Contacts />
<CostCenter>900-111</CostCenter>
<FileNumber>11111</FileNumber>
<LoanAmount>1111.0000</LoanAmount>
<LoanNumber>111</LoanNumber>
<Notes>notes1</Notes>
<OrderID>1</OrderID>
<PID>1</PID>
<PayOffLoanNumber>po1</PayOffLoanNumber>
<Product>letter</Product>
<Properties />
<ResponseURL>yahoo.com</ResponseURL>
<ToBeInsured>true</ToBeInsured>
</Order>
如您所见，没有联系人或借款人等。
and as you can see there are no contacts or borrowers etc.
**
更新：----------------- ----------------------------------
UPDATE: ---------------------------------------------------
**
我能够通过更改以下步骤使Get和GET（整数ID）命令正常工作并返回正确的数据：
I was able to get my Get and GET (int ID) commands to work and return the correct data by changing the procedures as:
public IEnumerable<Order> Get()
{
using (FirstCloseAPIEntities entities = new FirstCloseAPIEntities())
{
var entity = entities.Orders.ToList();
List<Borrower> borrowers = entities.Borrowers.ToList();
List<Contact> contacts = entities.Contacts.ToList();
List<Property> properties = entities.Properties.ToList();
//return entities.Orders.ToList() ;
return entity;
}
}
public HttpResponseMessage Get(int id)
{
using (FirstCloseAPIEntities entities = new FirstCloseAPIEntities())
{
var entity = entities.Orders.FirstOrDefault(o => o.OrderID == id);
List<Borrower> borrowers = entities.Borrowers.Where(b => b.OrderID == id).ToList();
List<Contact> contacts = entities.Contacts.Where(c => c.OrderID == id).ToList();
List<Property> properties = entities.Properties.Where(p => p.OrderID == id).ToList();
if (entity != null)
{
return Request.CreateResponse(HttpStatusCode.OK, entity);
}
else
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with id = " + id.ToString() + " not found.");
}
}
}
但是，因为我要作为HTML，我然后得到了错误：
However, because I am requesting as HTML I then was getting the error:
The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; charset=utf-8'
所以我必须将以下内容添加到Global.asax.cs文件中：
So I had to add the following to the Global.asax.cs file:
GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);
这给了我一个成功的响应，但是，无论我在类型应用程序的标题中放入了什么/ xml application / json我总是以JSON的形式返回响应。
This gave me back a successful response, however, no matter what I put in the headers for type application/xml application/json I am always getting the reponse back as JSON.
Content-Type: application/xml
accept: application/xml
accept-encoding: gzip, deflate
accept-language: en-US,en;q=0.8
user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36
Cache-Control: no-cache
Content-Length: 707
Content-Type: application/json; charset=utf-8
Date: Tue, 26 Sep 2017 19:46:42 GMT
Expires: -1
Pragma: no-cache
Server: Microsoft-IIS/10.0
X-Aspnet-Version: 4.0.30319
X-Powered-By: ASP.NET
X-Sourcefiles: =?UTF-8?B?YzpcdXNlcnNcZG91Zy5oYW1pbHRvblxkb2N1bWVudHNcdmlzdWFsIHN0dWRpbyAyMDE3XFByb2plY3RzXHdzRXRpdGxlXHdzRXRpdGxlXGFwaVxPcmRlcnNcMQ==?=
Raw
JSON
{
"OrderID": 1,
"LoanNumber": "111",
"LoanAmount": 1111,
"ToBeInsured": true,
"FileNumber": "11111",
"CostCenter": "900-111",
"Product": "letter",
"Notes": "notes1",
"ResponseURL": "yahoo.com",
"PID": 1,
"PayOffLoanNumber": "po1",
"ClientCode": "cc1",
"Borrowers": [
{
"borID": 1,
"OrderID": 1,
"FirstName": "Ura",
"MiddleName": "O",
"LastName": "Pepper",
"SSN": "111-11-1111"
},
{
"borID": 3,
"OrderID": 1,
"FirstName": "Ima",
"MiddleName": "J",
"LastName": "Pepper",
"SSN": "222-22-2222"
}
],
"Contacts": [
{
"conId": 1,
"OrderID": 1,
"Name": "Jackie the box",
"Phone": "414-555-1243",
"Fax": "414-222-1245",
"Email": "j@test.com"
}
],
"Properties": [
{
"proID": 1,
"OrderID": 1,
"Address": "123 Main Street",
"City": "Anytown",
"State": "WI",
"Zip": "10012
"County": "Westchester"
}
],
}
所以我仍然对如何获取XML响应感到困惑，然后最后一部分是能够获取将要提供的信息，如本文顶部和POST所示。它到正确的表。
So I am still confused as to how to get the response in XML and then the last part is to be able to take the information that will be supplied as shown at the top of this post and POST it to the correct tables.
推荐答案
我的原始帖子的 UPDATED注释中的代码更改允许我通过JSON数据进行GET和POST到网络我在本地主机上创建的api，因此能够解决我的问题。
My code changes in the "UPDATED" remarks of my original post allowed me to GET and POST by JSON data to the web api I created on the local host so I was able to work through my issue.
我当前的其他问题与以XML而不是JSON形式发布数据以及将Web api移至已经存在的面向公众的网站有关。我将这些问题作为单独的问题发布。
My current additional issues are related to POSTing the data as XML instead of JSON and being able to move the web api to an already existing public facing website. I will post those as separate questions.
这篇关于WEB API实体框架父子发布到SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
