Odata를 사용하여 EF 기반 DTO 쿼리

Question

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 프로파일 러에 표시되는 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')

다음은 내 데이터베이스 우선 엔티티 모델입니다.

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

내 DTO는 다음과 같습니다.

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

DTO에는 관계 모델이 없습니다. 예를 들어 EF에는 ClientRefs 및 ContactInfo 모델을 연결하는 ClientContactInfoComp 모델이 있지만 DTO에서는 ClientContract가 ContactInfoContract로 직접 참조됩니다.

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

Answer 1

예 다음 링크에서 결과를 얻었습니다 : https : // localhost : 44327 / odata / clientcontract

https : // localhost : 44327 / odata / clientcontract? $ filter = Id eq 4

https : // localhost : 44327 / odata / clientcontract? $ expand = documents, contactsinfo

Startup.cs



   public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddControllers();//.AddNewtonsoftJson(); ;
        services.AddDbContext<DbContext>(options =>
        options.UseSqlServer("connectionstring"));
        services.AddOData();
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
        }

        app.UseHttpsRedirection();

        app.UseRouting();

        app.UseAuthorization();

        app.UseEndpoints(endpoints =>
        {
            endpoints.MapControllers();
            //endpoints.EnableDependencyInjection();
            endpoints.Expand().Select().Filter().OrderBy().Count().MaxTop(10);
            endpoints.MapODataRoute("odata", "odata", GetEdmModel());
        });

        
    }

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

    
}

Answer 2

을 사용할 때는 속성 이름이 아니라 속성 이름을 사용해야 OData합니다.

OData클라이언트 라이브러리 OriginalNameAttribute는 서버가 클래스 / 멤버 이름을 내보낼 때 클래스 / 멤버 이름에 대한 지식을 얻기 위해 자체 속성에 의존 합니다. 여기 에서 볼 수있는 세부 사항 입니다.

[DataContract] 속성을 제거하면 작동합니다.

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

}