我實作了一個 api，我使用了 EF 核心。
我有一個復雜的結構，它的核心物體是一個我稱之為 Project 的物體。我應該說我使用 EF Core 作為 DB First。然后我首先創建了我的資料庫，之后我使用“Scaffold-Database”在代碼中創建了我的模型。
該專案的模型是：
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
namespace myProj_Model.Entities
{
public partial class Projects
{
public Projects()
{
Boreholes = new HashSet<Boreholes>();
ProjectsWorkAmounts = new HashSet<ProjectsWorkAmount>();
Zones = new HashSet<Zones>();
}
public long Id { get; set; }
public string Number { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public string ClientName { get; set; }
public int? NoOfRecord { get; set; }
public byte[] Logo { get; set; }
public int? LogoWidth { get; set; }
public int? LogoHeight { get; set; }
public string Version { get; set; }
public byte? Revision { get; set; }
public byte? WorkValueIsLimit { get; set; }
public long? CreatedBy { get; set; }
public DateTime? CreatedDate { get; set; }
public long? ModifiedBy { get; set; }
public DateTime? ModifiedDate { get; set; }
public virtual Users CreatedBy_User { get; set; }
public virtual Users ModifiedBy_User { get; set; }
public virtual ProjectsDrillingLog ProjectsDrillingLog { get; set; }
public virtual ProjectsDutchCone ProjectsDutchCone { get; set; }
public virtual ProjectsGap ProjectsGap { get; set; }
//public virtual ProjectsLogDrafting ProjectsLogDrafting { get; set; }
public virtual ProjectsRole ProjectsRole { get; set; }
public virtual ProjectsUnc ProjectsUnc { get; set; }
public virtual ICollection<Boreholes> Boreholes { get; set; }
public virtual ICollection<ProjectsWorkAmount> ProjectsWorkAmounts { get; set; }
public virtual ICollection<Zones> Zones { get; set; }
}
}
我再次提到模型是由“腳手架”命令創建的。CRUD 操作由 GenericRepository 處理：
using geotech_Tests_Model.Entities;
using geotech_Tests_Model.Interfaces;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace myProj_Model.Repositories
{
public class GenericRepository<T> : IGenericRepository<T> where T : class
{
protected geotechContext _context { get; set; }
public GenericRepository(geotechContext context)
{
this._context = context;
}
public void Add(T entity)
{
try
{
_context.Set<T>().Add(entity);
_context.SaveChanges();
}
catch (Exception eXp)
{
string Myname = eXp.Message;
}
}
public void AddRange(IEnumerable<T> entities)
{
try
{
_context.Set<T>().AddRange(entities);
_context.SaveChanges();
}
catch (Exception eXp)
{
string Myname = eXp.Message;
}
}
public void Update(T entity)
{
try
{
_context.Set<T>().Update(entity);
_context.SaveChanges();
}
catch (Exception eXp)
{
string Myname = eXp.Message;
}
}
public void UpdateRange(IEnumerable<T> entities)
{
try
{
_context.Set<T>().UpdateRange(entities);
_context.SaveChanges();
}
catch (Exception eXp)
{
string Myname = eXp.Message;
}
}
public IQueryable<T> Find()
{
return _context.Set<T>().AsQueryable();
}
public List<T> Find(FilterStruct filterstruct)
{
Expression<Func<T, bool>> myExpresion = Expresion(filterstruct);
return _context.Set<T>().AsQueryable().IgnoreAutoIncludes().Where(myExpresion).ToList ();
}
public T GetById(long id)
{
return _context.Set<T>().Find(id);
}
public void Remove(T entity)
{
try
{
_context.Set<T>().Remove(entity);
_context.SaveChanges();
}
catch (Exception eXp)
{
string Myname = eXp.Message;
}
}
public void RemoveRange(IEnumerable<T> entities)
{
try
{
_context.Set<T>().RemoveRange(entities);
_context.SaveChanges();
}
catch (Exception eXp)
{
string Myname = eXp.Message;
}
}
public Expression< Func<T, bool>> Expresion(FilterStruct filters)
{
//IQueryable<T> myQry = _context.Set<T>().AsQueryable<T>();
//IQueryable<T> myQryFilter = _context.Set<T>().AsQueryable<T>();
List<QueryStruct> queries = filters.Queries;
Expression predicateBody = null;
ParameterExpression myExp = Expression.Parameter(typeof(T), typeof(T).Name);
if (queries != null)
{
foreach (QueryStruct query in queries)
{
Expression e1 = null;
Expression left = Expression.Property(myExp, typeof(T).GetProperty(query.columnName));
Type actualType = Type.GetType(left.Type.FullName);
var myValue = Convert.ChangeType(query.value, actualType);
Expression right = Expression.Constant(myValue);
e1 = ApplyOperand(left, right, query.operatorName);
if (predicateBody == null)
predicateBody = e1;
else
{
predicateBody = ApplyAndOr(predicateBody, e1, query.AndOr);
}
}
}
//var p = Expression.Parameter(typeof(T), typeof(T).Name);
//if (predicateBody == null) predicateBody = Expression.Constant(true);
//MethodCallExpression whereCallExpression = Expression.Call(
//typeof(Queryable),
//"Where", new Type[] { myQryFilter.ElementType },
//myQryFilter.Expression, Expression.Lambda<Func<T>, bool> > (predicateBody, myExp));
var Lambda = Expression.Lambda <Func<T, bool>>(predicateBody, myExp);
return Lambda;
}
public static Expression ApplyOperand(Expression Left, Expression Rigth, OperandEnum Operand)
{
Expression result = null;
switch (Operand)
{
case (OperandEnum.Equal):
{
result = Expression.Equal(Left, Rigth);
break;
}
case (OperandEnum.NotEqual):
{
result = Expression.NotEqual(Left, Rigth);
break;
}
case (OperandEnum.GreaterThan):
{
result = Expression.GreaterThan(Left, Rigth);
break;
}
case (OperandEnum.GreaterThanOrEqual):
{
result = Expression.GreaterThanOrEqual(Left, Rigth);
break;
}
case (OperandEnum.LessThan):
{
result = Expression.LessThan(Left, Rigth);
break;
}
case (OperandEnum.LessThanOrEqual):
{
result = Expression.LessThanOrEqual(Left, Rigth);
break;
}
}
return result;
}
public static Expression ApplyAndOr(Expression Left, Expression Rigth, AndOrEnum AndOr)
{
Expression result = null;
switch (AndOr)
{
case (AndOrEnum.And):
{
result = Expression.And(Left, Rigth);
break;
}
case (AndOrEnum.AndAlso):
{
result = Expression.AndAlso(Left, Rigth);
break;
}
case (AndOrEnum.AndAssign):
{
result = Expression.AndAssign(Left, Rigth);
break;
}
case (AndOrEnum.Or):
{
result = Expression.Or(Left, Rigth);
break;
}
case (AndOrEnum.OrAssign):
{
result = Expression.OrAssign(Left, Rigth);
break;
}
case (AndOrEnum.OrElse):
{
result = Expression.OrElse(Left, Rigth);
break;
}
}
return result;
}
}
}
我的 Startup.cs 中有這樣的 ConfigureServices：
public void ConfigureServices(IServiceCollection services)
{
services.AddCors(options =>
{
options.AddPolicy(_appCorsPolicy,
builder =>
{
builder.WithOrigins("http://127.0.0.1:23243")
.AllowAnyHeader();
//.AllowAnyMethod();
});
});
//****************************************************************************************
services.AddControllers();
services.AddControllersWithViews().AddNewtonsoftJson(options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore);
services.Configure<IISOptions>(options =>
{
});
services.AddAutoMapper(typeof(Startup));
// Register the Swagger generator, defining 1 or more Swagger documents
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "Organization, .Net Core", Version = "V 01" });
// Set the comments path for the Swagger JSON and UI.
var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
c.IncludeXmlComments(xmlPath);
});
string connectionStr = Configuration.GetConnectionString("DefaultConnection");
services.AddDbContext<geotechContext>(options => options.UseLazyLoadingProxies().UseSqlServer(connectionStr));
//Farzin
services.Add(new ServiceDescriptor(typeof(IProjectsService), typeof(ProjectsService), ServiceLifetime.Scoped));
// {AddServicesHere}
}
之后，我為我的專案物體提供了一個服務類。GetById 是我在 Service 類中擁有的幾個函式之一。
public EventResult GetById(long id)
{
EventResult result = new EventResult();
//result.Data = service.GetById (id);
result.Data = service.Find().IgnoreAutoIncludes().Where(a => (a.Id == id)).FirstOrDefault();
return result;
}
現在的主要問題是，回應時間相當長。我跟蹤每一行代碼，甚至是執行的 sql 命令。Sql profiler 顯示發送到 SqlServer 的 sql 命令。命令是：
exec sp_executesql N'SELECT TOP(1) [g].[Id], [g].[gtp_ClientName], [g].[CreatedBy], [g].[CreatedDate], [g].[gtP_Description], [g].[gtp_Logo], [g].[gtp_LogoHeight], [g].[gtp_LogoWidth], [g].[ModifiedBy], [g].[ModifiedDate], [g].[gtP_Name], [g].[gtp_NoOfRecord], [g].[gtP_ProjectNumber], [g].[gtp_Revision], [g].[gtp_Version], [g].[gtp_WorkValueIsLimit]
FROM [gt_Projects] AS [g]
WHERE [g].[Id] = @__id_0',N'@__id_0 bigint',@__id_0=1
沒有任何關系，這個 sql 的執行結果是一個簡單的行，沒有任何額外的資料。但是我在 swagger 中得到的答案是包含所有相關資料的復雜記錄。
查詢結果
我希望招搖的結果是這樣的
預期結果
但結果是這樣的：
{
"errorNumber": 0,
"errorMessage": "",
"eventId": 0,
"data": {
"createdBy_User": {
"projectsRoleId1": null,
"boreholeCreatedByNavigations": [],
"boreholeModifiedByNavigations": [],
"boreholeTypeCreatedByNavigations": [
{
"boreholes": [],
"id": 1,
"abbriviation": "P",
"name": "Primary",
"description": "Primary Boreholes",
"order": 1,
"color": null,
"createdBy": 1,
"createdDate": "1400-05-27T00:00:00",
"modifiedBy": 1,
"modifiedDate": "1400-05-27T00:00:00"
}
],
"boreholeTypeModifiedByNavigations": [
{
"boreholes": [],
"id": 1,
"abbriviation": "P",
"name": "Primary",
"description": "Primary Boreholes",
"order": 1,
"color": null,
"createdBy": 1,
"createdDate": "1400-05-27T00:00:00",
"modifiedBy": 1,
"modifiedDate": "1400-05-27T00:00:00"
}
],
"boreholesWorkAmountCreatedByNavigations": [],
"boreholesWorkAmountModifiedByNavigations": [],
"dailyActivityDaCoSupervisorNavigations": [],
"dailyActivityDaSpecialistNavigations": [],
"dailyActivityDaSupervisorNavigations": [],
"dailyActivityDaTechnision01Navigations": [],
"dailyActivityDaTechnision02Navigations": [],
"created_Projects": [
{
"projectsDrillingLog": null,
"projectsDutchCone": null,
"projectsGap": null,
"projectsRole": null,
"projectsUnc": null,
"boreholes": [],
"projectsWorkAmounts": [],
"zones": [],
"id": 2,
"number": "001",
"name": "Yes",
"description": "ss",
"clientName": "ss",
"noOfRecord": 1,
"logo": null,
.........
.........
"id": 5,
"workActivity": 6,
"project": 1,
"activityPredicted": 5,
"activityActual": null,
"startDatePredicted": null,
"endDatePredicted": null,
"startDateActual": null,
"endDateActual": null,
"createdBy": null,
"createdDate": null,
"modifiedBy": null,
"modifiedDate": null
}
],
"zones": [],
"id": 1,
"number": "001",
"name": "No",
"description": "ss",
"clientName": "ss",
"noOfRecord": 1,
"logo": null,
"logoWidth": 11,
"logoHeight": 11,
"version": "1",
"revision": 1,
"workValueIsLimit": 1,
"createdBy": 1,
"createdDate": "1400-06-01T00:00:00",
"modifiedBy": 1,
"modifiedDate": "1400-06-01T00:00:00"
}
}
答案包含近 4700 行資料，這發生在我的資料庫幾乎為空時。我不知道為什么和我應該做什么，以獲得我的預期結果作為答案。
uj5u.com熱心網友回復：
我注意到你有.UseLazyLoadingProxies()這意味著你可以從資料庫加載一些資料，然后通過訪問屬性觸發加載更多資料；例如，您只下載一個專案，但是一旦您嘗試訪問其鉆孔集合，該訪問將觸發另一個查詢，例如SELECT * FROM Boreholes WHERE projectId = (whatever the current project id is)在回傳鉆孔集合之前填充鉆孔集合。
這意味著當序列化程式列舉您的開始Project的屬性時，查找它可以序列化的資料，而不是在訪問導航屬性到某些相關資料時獲得“空”或“空”，這是觸發資料庫查找的序列化程式每個相關物體......然后它序列化所有這些相關物體并列舉它們的道具觸發更多查找。
一開始只是一個專案，滾雪球加載樹上下的每個相關物體，甚至可能是整個資料庫，只是因為您已經要求序列化程式將專案物件轉換為 json..
洗掉延遲加載代理功能將阻止這種情況，但專案的其他部分不會在訪問時自動加載資料；你對此所做的可能是關于如何加載相關資料的更廣泛的設計決策。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/313323.html