I'm working on an API project using ASP.NET Core and EF Core 6. I have created a relational database using the Code-First approach. The problem is that when I try to get all data in the table PmSchedules from the database, it takes too long while I have only 20 records in the table.
When I have 30 records in that table, PostMan throws an error and says:
Error: Maximum response size reached
Some of the properties related to that table are:
public class PmSchedule
{
[Key]
public long PmScheduleId { get; set; }
[Required]
public long ScheduleNumber { get; set; }
[Required]
[DataType(DataType.DateTime)]
public DateTime ScheduledStartDate { get; set; }
[Required]
[DataType(DataType.DateTime)]
public DateTime ScheduledEndDate { get; set; }
[Required]
[DataType(DataType.Text)]
public string MainFileName { get; set; }
[DataType(DataType.Text)]
public string? UploadedFileName { get; set; }
[DataType(DataType.DateTime)]
public DateTime? CompletionDate { get; set; }
[Required]
[DataType(DataType.Text)]
public string? Status { get; set; }
//Navigation properties
public virtual CostCenter CostCenter { get; set; }
public virtual ServiceType ServiceType { get; set; }
//For Adding two foreinkeys
[Required]
public int MainUploaderId { get; set; }
public User MainUploader { get; set; }
public int? UploaderId { get; set; }
public User Uploader { get; set; }
[Required]
public int WorkCenterId { get; set; }
public WorkCenter WorkCenter { get; set; }
}
public class WorkCenter
{
[Key]
public int WorkCenterId { get; set; }
[Required]
public WorkCenters Work_Center { get; set; }
[Required]
public bool IsActive { get; set; }
//Navigation propery
public virtual List<UserWorkCenter>? UserWorkCenters { get; set; }
public virtual List<PmSchedule>? PmSchedules { get; set; }
public virtual List<PmDataSet>? PmDataSets { get; set; }
}
public enum WorkCenters
{
Electrical,
Mechanical
}
public class ServiceType
{
[Key]
public int ServiceTypeId { get; set; }
[Required]
[DataType(DataType.Text)]
public string Title { get; set; }
[Required]
public bool IsActive { get; set; } = true;
//Navigation properties
public virtual List<PmDataSet>? PmDataSets { get; set; }
public virtual List<PmSchedule>? PmSchedules { get; set; }
}
public class CostCenter
{
[Key]
public int CostCenterId { get; set; }
[Required]
[DataType(DataType.Text)]
public string Title { get; set; }
[Required]
public bool IsActive { get; set; } = true;
//Navigation properties
public virtual List<PmDataSet>? PmDataSets { get; set; }
public virtual List<PmSchedule>? PmSchedules { get; set; }
public virtual List<Equipment>? Equipments { get; set; }
public virtual List<UserCostCenter>? UserCostCenters { get; set; }
}
I'm using following code to produce the response:
var pmSchedule = await _sqlServerContext.PmSchedules
.Include(x => x.CostCenter)
.Include(x => x.ServiceType)
.Include(x => x.WorkCenter)
.ToListAsync();
if (pmSchedule.Count > 0)
{
return Ok(pmSchedule);
}
return NoContent();
How can I fix this problem?
Update 1:
The API response for only two database entries are:
[
{
"pmScheduleId": 2,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-808ab30a-889a-4890-82b3-67e6d06da7c0.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": {
"costCenterId": 2,
"title": "USA",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
{
"pmScheduleId": 3,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-1fd2a4c8-0607-4258-beed-81f043c4fb5f.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": null,
"serviceType": {
"serviceTypeId": 1,
"title": "Monthly",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
]
},
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": {
"workCenterId": 1,
"work_Center": 0,
"isActive": true,
"userWorkCenters": null,
"pmSchedules": [
null,
null
],
"pmDataSets": null
}
}
],
"equipments": null,
"userCostCenters": null
},
"serviceType": {
"serviceTypeId": 1,
"title": "Monthly",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
{
"pmScheduleId": 3,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-1fd2a4c8-0607-4258-beed-81f043c4fb5f.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": {
"costCenterId": 2,
"title": "USA",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
],
"equipments": null,
"userCostCenters": null
},
"serviceType": null,
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": {
"workCenterId": 1,
"work_Center": 0,
"isActive": true,
"userWorkCenters": null,
"pmSchedules": [
null,
null
],
"pmDataSets": null
}
}
]
},
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": {
"workCenterId": 1,
"work_Center": 0,
"isActive": true,
"userWorkCenters": null,
"pmSchedules": [
null,
{
"pmScheduleId": 3,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-1fd2a4c8-0607-4258-beed-81f043c4fb5f.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": {
"costCenterId": 2,
"title": "USA",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
],
"equipments": null,
"userCostCenters": null
},
"serviceType": {
"serviceTypeId": 1,
"title": "Monthly",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
]
},
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": null
}
],
"pmDataSets": null
}
},
{
"pmScheduleId": 3,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-1fd2a4c8-0607-4258-beed-81f043c4fb5f.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": {
"costCenterId": 2,
"title": "USA",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
{
"pmScheduleId": 2,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-808ab30a-889a-4890-82b3-67e6d06da7c0.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": null,
"serviceType": {
"serviceTypeId": 1,
"title": "Monthly",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
]
},
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": {
"workCenterId": 1,
"work_Center": 0,
"isActive": true,
"userWorkCenters": null,
"pmSchedules": [
null,
null
],
"pmDataSets": null
}
},
null
],
"equipments": null,
"userCostCenters": null
},
"serviceType": {
"serviceTypeId": 1,
"title": "Monthly",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
{
"pmScheduleId": 2,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-808ab30a-889a-4890-82b3-67e6d06da7c0.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": {
"costCenterId": 2,
"title": "USA",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
],
"equipments": null,
"userCostCenters": null
},
"serviceType": null,
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": {
"workCenterId": 1,
"work_Center": 0,
"isActive": true,
"userWorkCenters": null,
"pmSchedules": [
null,
null
],
"pmDataSets": null
}
},
null
]
},
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": {
"workCenterId": 1,
"work_Center": 0,
"isActive": true,
"userWorkCenters": null,
"pmSchedules": [
{
"pmScheduleId": 2,
"scheduleNumber": 100,
"scheduledStartDate": "2022-06-22T00:00:00",
"scheduledEndDate": "2022-07-05T00:00:00",
"mainFileName": "2022-7-9-808ab30a-889a-4890-82b3-67e6d06da7c0.docx",
"uploadedFileName": null,
"completionDate": null,
"status": "Registered",
"costCenter": {
"costCenterId": 2,
"title": "USA",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
],
"equipments": null,
"userCostCenters": null
},
"serviceType": {
"serviceTypeId": 1,
"title": "Monthly",
"isActive": true,
"pmDataSets": null,
"pmSchedules": [
null,
null
]
},
"mainUploaderId": 1,
"mainUploader": null,
"uploaderId": null,
"uploader": null,
"workCenterId": 1,
"workCenter": null
},
null
],
"pmDataSets": null
}
}
]
CodePudding user response：
I used the [JsonIgnore] attribute in some of the properties causing circular reference in JSON. The problem is solved.
CodePudding user response：
why You marked all the string fields as [DataType(DataType.Text)] That is the mistake. remove all [DataType(DataType.Text)] and add Length attribute. It should fix your issue.