Interroger les DTO basés sur EF à l'aide d'Odata

Aug 17 2020

J'ai une configuration d'API Web ASP.NET Core avec une base de données SQL Server et un modèle de données EF.

Versions:

  • EF: Microsoft.EntityFrameworkCore 5.0.0-aperçu.7.20365.15
  • OData: Microsoft.AspNetCore.OData 7.4.1
  • .Net Core 3.1

La question est: je ne peux pas utiliser le filtre, sélectionnez dans développer (développer imbriqué). Exemples d'URL pour lesquelles OData n'ajoute pas de filtres à la condition where de la requête SQL qui apparaît sur SQL Server Profiler:

https: // localhost: 44327 / odata / clientcontract?$expand=ContactsInfo($filtre = valeur eq '100003265')

https: // localhost: 44327 / odata / clientcontract?$expand=Documents($filter = numéro de document eq '100003265')

Voici mes modèles d'entité basés sur la base de données:

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

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

Dans les DTO, il n'y a pas de modèle de relation. Par exemple: dans EF, il existe un modèle ClientContactInfoComp, qui connecte les modèles ClientRefs et ContactInfo, mais dans DTO, ClientContract est directement référencé avec ContactInfoContract.

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

Réponses

user3315759 Aug 18 2020 at 00:45

Oui, j'obtiens le résultat des liens suivants: 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();
    }

    
}
MichaelWang Aug 20 2020 at 20:51

Vous devez utiliser le nom de la propriété et non l'attribut Name lors de l'utilisation OData.

ODataLa bibliothèque cliente s'appuie sur son propre attribut OriginalNameAttributepour acquérir des connaissances sur les noms de classe / membre lorsque le serveur les émet. Les détails que vous pouvez voir d' ici .

Cela fonctionne lorsque je supprime l'attribut [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; }

}