使用Odata查询基于EF的DTO [英] Querying DTOs based on EF using Odata
本文介绍了使用Odata查询基于EF的DTO的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个带有SQL Server数据库和EF数据模型的ASP.NET Core Web API设置.
版本:
- EF:Microsoft.EntityFrameworkCore 5.0.0-preview.7.20365.15
- OData:Microsoft.AspNetCore.OData 7.4.1
- .Net Core 3.1
问题是:我无法使用过滤器，请在扩展(嵌套扩展)中选择.OData不会在SQL Server Profiler上看到的SQL查询的where条件中添加过滤器的示例URL:
https://localhost:44327/odata/clientcontract?$ expand = ContactsInfo($ filter = value eq'100003265')
https://localhost:44327/odata/clientcontract?$ expand = Documents($ filter = documentnnumber eq'100003265')
这些是我的数据库优先实体模型:
公共局部类ClientRef{公开的ClientRef(){地址=新的HashSet< Address>();资产=新的HashSet< Asset>();ClientContactInfoComps = new HashSet< ClientContactInfoComp>();ClientRelationCompClient1Navigations = new HashSet< ClientRelationComp>();ClientRelationCompClient2Navigations = new HashSet< ClientRelationComp>();客户=新的HashSet< Client>();CommentComps =新的HashSet< CommentComp>();公司=新的HashSet< Company>();文件=新的HashSet< Document>();PhysicalPeople =新的HashSet< PhysicalPerson>();}[列("Inn"))]public int ID {get;放;}公共虚拟ICollection< Address>地址{get;放;}公共虚拟ICollection< Asset>资产{得到；放;}公共虚拟ICollection< ClientContactInfoComp>ClientContactInfoComps {get;放;}公共虚拟ICollection< ClientRelationComp>ClientRelationCompClient1Navigations {get;放;}公共虚拟ICollection< ClientRelationComp>ClientRelationCompClient2Navigations {get;放;}公共虚拟ICollection< Client>客户{放;}公共虚拟ICollection< CommentComp>CommentComps {get;放;}公共虚拟ICollection< Company>公司{放;}公共虚拟ICollection< Document>文件{get;放;}公共虚拟ICollection< PhysicalPerson>PhysicalPeople {get;放;}}公共部分类文档{public int ID {get;放;}公共字符串DocumentNumber {get;放;}public int DocumentType {get;放;}public int Inn {获取;放;}公共DateTime ValidFrom {get;放;}公开DateTime ValidTo {get;放;}公开DateTime?DocumentExpireDate {get;放;}公共虚拟ClientRef InnNavigation {get;放;}}公共局部类ClientContactInfoComp{public int ID {get;放;}public int Inn {获取;放;}公共int ContactInfoId {放;}公共诠释?点{get;放;}公共DateTime ValidFrom {get;放;}公共DateTime ValidTo {get;放;}[ForeignKey("ContactInfoId"))]公共ContactInfo ContactInfo {get;放;}公共虚拟ClientRef InnNavigation {get;放;}}公共局部类ContactInfo{公共ContactInfo(){CallHistories = new HashSet< CallHistory>();ClientContactInfoComps = new HashSet< ClientContactInfoComp>();CommentComps =新的HashSet< CommentComp>();}public int ID {get;放;}公共诠释?输入{get;放;}公共字符串值{get;放;}公共虚拟ICollection< CallHistory>CallHistories {get;放;}公共虚拟ICollection< ClientContactInfoComp>ClientContactInfoComps {get;放;}公共虚拟ICollection< CommentComp>CommentComps {get;放;}}公共局部类CommentComp{public int ID {get;放;}public int Inn {获取;放;}公共诠释?CommentId {获取；放;}公共诠释?ContactId {获取；放;}公共虚拟评论评论{放;}公共虚拟ContactInfo联系人{放;}公共虚拟ClientRef InnNavigation {get;放;}}公共部分类评论{公众评论(){CommentComps =新的HashSet< CommentComp>();}public int ID {get;放;}公共字符串Text {get;放;}公开DateTime?CreateTimestamp {get;放;}公共字符串创建者{get;放;}公共虚拟ICollection< CommentComp>CommentComps {get;放;}} 这些是我的DTO:
[DataContract]公共类ClientContract{公共ClientContract(){ContactsInfo =新列表< ContactInfoContract>();文档=新列表< DocumentContract>();关系=新List< RelationContract>();ClientComment = new CommentContract();}[DataMember(Name ="INN")][钥匙]public int INN {get;放;}[DataMember(Name ='validfrom'')]公共DateTime ValidFrom {get;放;}[DataMember(Name ="validto")]公开DateTime ValidTo {get;放;}[DataMember(Name ="clienttype")]公用ClientType ClientType {放;}[DataMember(Name ="companyname")]公用字符串CompanyName {get;放;}[DataMember(Name ="firstname"))]公共字符串FirstName {get;放;}[DataMember(Name ="lastname"))]公共字符串LastName {get;放;}[DataMember(Name ="fathername")]公共字符串FatherName {get;放;}[DataMember(Name ="pinnumber")]公共字符串PinNumber {get;放;}[DataMember(名称=生日"))]公开DateTime?BirthDate {获取；放;}[DataMember(Name ="positioncustom"")]公共字符串PositionCustom {get;放;}[DataMember(Name ="position")]public int Position {get;放;}[DataMember(名称="monthlyincome"))公共十进制月度收入{get;放;}[DataMember(Name ="clientcomment")]public CommentContract ClientComment {获取；放;}[DataMember(Name ="contactsinfo")]公共列表< ContactInfoContract>ContactsInfo {get;放;}[DataMember(Name ="documents")][ForeignKey(文档"))]公共列表< DocumentContract>文件{get;放;}[DataMember(Name ='relations')]公共列表< RelationContract>关系{get;放;}}公共类DocumentContract{[钥匙]public int ID {get;放;}[DataMember(Name ="documentNumber")]公共字符串documentNumber {get;放;}[DataMember(Name ="documentType")]public int documentType {get;放;}[DataMember(Name ="documentexpiredate")]公开DateTime?documentExpireDate {get;放;}}[数据合同]公共类ContactInfoContract{公共ContactInfoContract(){ContactComment =新的CommentContract();}[钥匙]public int ID {get;放;}[DataMember(Name ="type")]public int Type {get;放;}[DataMember(Name ="value")]公共字符串值{get;放;}[DataMember(Name ="contactComment")]public CommentContract ContactComment {获取；放;}}公共类CommentContract{[DataMember(Name ="Text"))公共字符串Text {get;放;}[DataMember(Name ="creator")]公共字符串创建者{get;放;}} 在DTO中没有关系模型.例如:在EF中，有一个ClientContactInfoComp模型，该模型连接ClientRefs和ContactInfo模型，但在DTO中，ClientContract直接由ContactInfoContract引用.
Startup.cs中的模型构建器
私有IEdmModel GetEdmModel(){var builder = new ODataConventionModelBuilder();builder.EntitySet< ClientContract>("ClientContract").EntityType.HasKey(x => x.INN).Name ="ClientRef";builder.EntitySet< DocumentContract>("DocumentContract")；builder.EntitySet< ContactInfoContract>("ContactInfoContracts").EntityType.HasKey(x => x.Id);返回builder.GetEdmModel();}公共类ClientContractController:ControllerBase{[EnableQuery(MaxExpansionDepth = 10)]公共IQueryable< ClientContract>得到(){var clientRefs = _context.ClientRefs.include(x => x.Clients.Where(x => x.ValidFrom< DateTime.Now&&&x.ValidTo> DateTime.Now)).include(x => x.PhysicalPeople.Where(x => x.ValidFrom< DateTime.Now&& x.ValidTo> DateTime.Now)).include(x => x.Companies.Where(x => x.ValidFrom< DateTime.Now&&&x.ValidTo> DateTime.Now)).include(x => x.Documents.Where(x => x.ValidFrom< DateTime.Now&&&x.ValidTo> DateTime.Now)).include(x => x.ClientContactInfoComps.Where(x => x.ValidFrom< DateTime.Now&& x.ValidTo> DateTime.Now)).thenInclude(x => x.ContactInfo).include(x => x.Assets.Where(x => x.ValidFrom< DateTime.Now&& x.ValidTo> DateTime.Now));列表< ClientContract>合同=新的List< ClientContract>();foreach(clientRefs中的var clientRef){ClientContract clientContract = new ClientContract(){INN = clientRef.Id};foreach(clientRef.Clients中的var c){clientContract.ClientType =(ClientType)c.ClientType;}foreach(clientRef.PhysicalPeople中的var pp){clientContract.FirstName = pp.FirstName;clientContract.LastName = pp.LastName;}foreach(clientRef.Companies中的var comp){clientContract.CompanyName = comp.CompanyName;}foreach(clientRef.Documents中的var doc){clientContract.Documents.Add(new DocumentContract(){documentNumber = doc.DocumentNumber，documentExpireDate = doc.DocumentExpireDate，documentType = doc.DocumentType});}foreach(clientRef.ClientContactInfoComps中的var comp){clientContract.ContactsInfo.Add(新的ContactInfoContract{类型= comp.ContactInfo.Type.Value，值= comp.ContactInfo.Value});}Contracts.Add(clientContract);}返回合同.AsQueryable();}} 解决方案
使用 OData 时，应使用属性名称而不是属性名称.
OData 客户端库依靠它自己的属性 OriginalNameAttribute 来获取有关服务器发出的类/成员名的知识.您可以在
公共类ClientContract{公共ClientContract(){ContactsInfo =新列表< ContactInfoContract>();}[钥匙]public int INN {get;放;}公用字符串CompanyName {get;放;}公共列表< ContactInfoContract>ContactsInfo {get;放;}}公共类ContactInfoContract{[钥匙]public int ID {get;放;}[DataMember(Name ="type")]public int Type {get;放;}[DataMember(Name ="value")]公共字符串值{get;放;}} I have an ASP.NET Core Web API setup with a SQL Server database and an EF data model.
Versions:
- EF: Microsoft.EntityFrameworkCore 5.0.0-preview.7.20365.15
- OData: Microsoft.AspNetCore.OData 7.4.1
- .Net Core 3.1
The question is: I can't use filter, select in expand (nested expand). Example URLs which OData does not add filters to the where condition of SQL query which is seen on SQL Server Profiler:
https://localhost:44327/odata/clientcontract?$expand=ContactsInfo($filter=value eq '100003265')
https://localhost:44327/odata/clientcontract?$expand=Documents($filter=documentnnumber eq '100003265')
These are my database-first entity models:
public partial class ClientRef
{
public ClientRef()
{
Addresses = new HashSet<Address>();
Assets = new HashSet<Asset>();
ClientContactInfoComps = new HashSet<ClientContactInfoComp>();
ClientRelationCompClient1Navigations = new HashSet<ClientRelationComp>();
ClientRelationCompClient2Navigations = new HashSet<ClientRelationComp>();
Clients = new HashSet<Client>();
CommentComps = new HashSet<CommentComp>();
Companies = new HashSet<Company>();
Documents = new HashSet<Document>();
PhysicalPeople = new HashSet<PhysicalPerson>();
}
[Column("Inn")]
public int Id { get; set; }
public virtual ICollection<Address> Addresses { get; set; }
public virtual ICollection<Asset> Assets { get; set; }
public virtual ICollection<ClientContactInfoComp> ClientContactInfoComps { get; set; }
public virtual ICollection<ClientRelationComp> ClientRelationCompClient1Navigations { get; set; }
public virtual ICollection<ClientRelationComp> ClientRelationCompClient2Navigations { get; set; }
public virtual ICollection<Client> Clients { get; set; }
public virtual ICollection<CommentComp> CommentComps { get; set; }
public virtual ICollection<Company> Companies { get; set; }
public virtual ICollection<Document> Documents { get; set; }
public virtual ICollection<PhysicalPerson> PhysicalPeople { get; set; }
}
public partial class Document
{
public int Id { get; set; }
public string DocumentNumber { get; set; }
public int DocumentType { get; set; }
public int Inn { get; set; }
public DateTime ValidFrom { get; set; }
public DateTime ValidTo { get; set; }
public DateTime? DocumentExpireDate { get; set; }
public virtual ClientRef InnNavigation { get; set; }
}
public partial class ClientContactInfoComp
{
public int Id { get; set; }
public int Inn { get; set; }
public int ContactInfoId { get; set; }
public int? Point { get; set; }
public DateTime ValidFrom { get; set; }
public DateTime ValidTo { get; set; }
[ForeignKey("ContactInfoId")]
public ContactInfo ContactInfo { get; set; }
public virtual ClientRef InnNavigation { get; set; }
}
public partial class ContactInfo
{
public ContactInfo()
{
CallHistories = new HashSet<CallHistory>();
ClientContactInfoComps = new HashSet<ClientContactInfoComp>();
CommentComps = new HashSet<CommentComp>();
}
public int Id { get; set; }
public int? Type { get; set; }
public string Value { get; set; }
public virtual ICollection<CallHistory> CallHistories { get; set; }
public virtual ICollection<ClientContactInfoComp> ClientContactInfoComps { get; set; }
public virtual ICollection<CommentComp> CommentComps { get; set; }
}
public partial class CommentComp
{
public int Id { get; set; }
public int Inn { get; set; }
public int? CommentId { get; set; }
public int? ContactId { get; set; }
public virtual Comment Comment { get; set; }
public virtual ContactInfo Contact { get; set; }
public virtual ClientRef InnNavigation { get; set; }
}
public partial class Comment
{
public Comment()
{
CommentComps = new HashSet<CommentComp>();
}
public int Id { get; set; }
public string Text { get; set; }
public DateTime? CreateTimestamp { get; set; }
public string Creator { get; set; }
public virtual ICollection<CommentComp> CommentComps { get; set; }
}
These are my DTOs:
[DataContract]
public class ClientContract
{
public ClientContract()
{
ContactsInfo = new List<ContactInfoContract>();
Documents = new List<DocumentContract>();
Relations = new List<RelationContract>();
ClientComment = new CommentContract();
}
[DataMember(Name = "INN")]
[Key]
public int INN { get; set; }
[DataMember(Name = "validfrom")]
public DateTime ValidFrom { get; set; }
[DataMember(Name = "validto")]
public DateTime ValidTo { get; set; }
[DataMember(Name = "clienttype")]
public ClientType ClientType { get; set; }
[DataMember(Name = "companyname")]
public string CompanyName { get; set; }
[DataMember(Name = "firstname")]
public string FirstName { get; set; }
[DataMember(Name = "lastname")]
public string LastName { get; set; }
[DataMember(Name = "fathername")]
public string FatherName { get; set; }
[DataMember(Name = "pinnumber")]
public string PinNumber { get; set; }
[DataMember(Name = "birthdate")]
public DateTime? BirthDate { get; set; }
[DataMember(Name = "positioncustom")]
public string PositionCustom { get; set; }
[DataMember(Name = "position")]
public int Position { get; set; }
[DataMember(Name = "monthlyincome")]
public decimal MonthlyIncome { get; set; }
[DataMember(Name = "clientcomment")]
public CommentContract ClientComment { get; set; }
[DataMember(Name = "contactsinfo")]
public List<ContactInfoContract> ContactsInfo { get; set; }
[DataMember(Name = "documents")]
[ForeignKey("Documents")]
public List<DocumentContract> Documents { get; set; }
[DataMember(Name = "relations")]
public List<RelationContract> Relations { get; set; }
}
public class DocumentContract
{
[Key]
public int Id { get; set; }
[DataMember(Name = "documentNumber")]
public string documentNumber { get; set; }
[DataMember(Name = "documentType")]
public int documentType { get; set; }
[DataMember(Name = "documentexpiredate")]
public DateTime? documentExpireDate { get; set; }
}
[DataContract]
public class ContactInfoContract
{
public ContactInfoContract()
{
ContactComment = new CommentContract();
}
[Key]
public int Id { get; set; }
[DataMember(Name = "type")]
public int Type { get; set; }
[DataMember(Name = "value")]
public string Value { get; set; }
[DataMember(Name = "contactComment")]
public CommentContract ContactComment { get; set; }
}
public class CommentContract
{
[DataMember(Name = "Text")]
public string Text { get; set; }
[DataMember(Name = "creator")]
public string Creator { get; set; }
}
In DTOs there is not relation model. For instance: in EF, there is a ClientContactInfoComp model, which connects ClientRefs and ContactInfo models, but in DTO ClientContract is directly referenced with ContactInfoContract.
Model Builder in Startup.cs
private IEdmModel GetEdmModel()
{
var builder = new ODataConventionModelBuilder();
builder.EntitySet<ClientContract>("ClientContract").EntityType.HasKey(x => x.INN).Name = "ClientRef";
builder.EntitySet<DocumentContract>("DocumentContract");
builder.EntitySet<ContactInfoContract>("ContactInfoContracts").EntityType.HasKey(x => x.Id);
return builder.GetEdmModel();
}
public class ClientContractController : ControllerBase
{
[EnableQuery(MaxExpansionDepth = 10)]
public IQueryable<ClientContract> Get()
{
var clientRefs = _context.ClientRefs
.Include(x => x.Clients.Where(x => x.ValidFrom < DateTime.Now && x.ValidTo > DateTime.Now))
.Include(x => x.PhysicalPeople.Where(x => x.ValidFrom < DateTime.Now && x.ValidTo > DateTime.Now))
.Include(x => x.Companies.Where(x => x.ValidFrom < DateTime.Now && x.ValidTo > DateTime.Now))
.Include(x => x.Documents.Where(x => x.ValidFrom < DateTime.Now && x.ValidTo > DateTime.Now))
.Include(x => x.ClientContactInfoComps.Where(x => x.ValidFrom < DateTime.Now && x.ValidTo > DateTime.Now))
.ThenInclude(x => x.ContactInfo)
.Include(x => x.Assets.Where(x => x.ValidFrom < DateTime.Now && x.ValidTo > DateTime.Now));
List<ClientContract> contracts = new List<ClientContract>();
foreach (var clientRef in clientRefs)
{
ClientContract clientContract = new ClientContract() { INN = clientRef.Id };
foreach(var c in clientRef.Clients)
{
clientContract.ClientType = (ClientType) c.ClientType;
}
foreach (var pp in clientRef.PhysicalPeople)
{
clientContract.FirstName = pp.FirstName;
clientContract.LastName = pp.LastName;
}
foreach (var comp in clientRef.Companies)
{
clientContract.CompanyName = comp.CompanyName;
}
foreach (var doc in clientRef.Documents)
{
clientContract.Documents.Add(new DocumentContract()
{
documentNumber = doc.DocumentNumber,
documentExpireDate = doc.DocumentExpireDate,
documentType = doc.DocumentType
});
}
foreach (var comp in clientRef.ClientContactInfoComps)
{
clientContract.ContactsInfo.Add(new ContactInfoContract
{
Type = comp.ContactInfo.Type.Value,
Value = comp.ContactInfo.Value
});
}
contracts.Add(clientContract);
}
return contracts.AsQueryable();
}
}
解决方案
You should use the property name and not the attribute Name when use OData.
OData client library relies on it's own attribute OriginalNameAttribute to gain knowledge about class/member names as server emits them. The details you can see from here.
It works when I remove [DataContract] attribute.
public class ClientContract
{
public ClientContract()
{
ContactsInfo = new List<ContactInfoContract>();
}
[Key]
public int INN { get; set; }
public string CompanyName { get; set; }
public List<ContactInfoContract> ContactsInfo { get; set; }
}
public class ContactInfoContract
{
[Key]
public int Id { get; set; }
[DataMember(Name = "type")]
public int Type { get; set; }
[DataMember(Name = "value")]
public string Value { get; set; }
}
这篇关于使用Odata查询基于EF的DTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
