Блог

Главная - Вопросы по программированию - Запрашивает DTO на основе EF с использованием Odata

Запрашивает DTO на основе EF с использованием Odata

#asp.net-core #odata #webapi

#asp.net-core #odata #webapi

Вопрос:

У меня есть ASP.NET Настройка основного веб-API с базой данных SQL Server и моделью данных EF.

Версии:

  • EF: Microsoft.EntityFrameworkCore 5.0.0-предварительный просмотр.7.20365.15
  • OData: Microsoft.AspNetCore.OData 7.4.1
  • .Net Core 3.1

Вопрос в том, что я не могу использовать filter, выберите в expand (вложенное расширение). Пример URL-адресов, для которых OData не добавляет фильтры к условию where SQL-запроса, которое отображается в профилировщике SQL Server:

https://localhost:44327/odata/clientcontract ?$expand=ContactsInfo($filter= значение 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 существует модель ClientContactInfoComp, которая соединяет модели ClientRefs и ContactInfo, но в 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 amp;amp; x.ValidTo > DateTime.Now))
                         .Include(x => x.PhysicalPeople.Where(x => x.ValidFrom < DateTime.Now amp;amp; x.ValidTo > DateTime.Now))
                         .Include(x => x.Companies.Where(x => x.ValidFrom < DateTime.Now amp;amp; x.ValidTo > DateTime.Now))
                         .Include(x => x.Documents.Where(x => x.ValidFrom < DateTime.Now amp;amp; x.ValidTo > DateTime.Now))
                         .Include(x => x.ClientContactInfoComps.Where(x => x.ValidFrom < DateTime.Now amp;amp; x.ValidTo > DateTime.Now))
                         .ThenInclude(x => x.ContactInfo)
                         .Include(x => x.Assets.Where(x => x.ValidFrom < DateTime.Now amp;amp; 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();
    }
}

Ответ №1:

Да, я получаю результат по следующим ссылкам: https://localhost:44327/odata/clientcontract

https://localhost:44327/odata/clientcontract ?$filter= Идентификатор, равный 4

https://localhost:44327/odata/clientcontract ?$expand=документы,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();
    }

    
}

Ответ №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; }

}