entity-framework - Entity Framework Core 3：使用连接从多个源中提取数据

问题描述

我基本上有三个表，我需要查询信息来获取 PersonNotes。我正在使用实体框架核心 3。

Person Table
PersonNote Table
PersonNoteAttachment Table

一个人可以有多个personnote，一个personnote可以包含多个PersonNoteAttachment。

我需要 Person 表来获取映射到 PersonNote 用户数据模型中的 AuthorName 的 FirstName 和 LastName。您可以看到显示映射的映射部分。

数据模型

namespace Genistar.Organisation.Models.DataModels
{

    [Table(nameof(PersonNote), Schema = "common")]
    public class PersonNote
    {
        public int Id { get; set; }
        public int PersonId { get; set; }

        [ForeignKey("PersonId")]
        public Person Person { get; set; }
        public string Note { get; set; }

        public int AuthorId { get; set; }

        [ForeignKey("AuthorId")]
        public Person Author { get; set; }

        public string CreatedBy { get; set; }
        public DateTime Created { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime RecordStartDateTime { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime RecordEndDateTime { get; set; }
    }
}

namespace Genistar.Organisation.Models.DataModels
{

    [Table(nameof(PersonNoteAttachment), Schema = "common")]
    public class PersonNoteAttachment
    {
        public int Id { get; set; }

        public int PersonNoteId { get; set; }

        [ForeignKey("PersonNoteId")]
        public PersonNote PersonNote { get; set; }

        public string Alias { get; set; }

        public string FileName { get; set; }

        public string MimeType { get; set; }

        public int Deleted { get; set; }

        public string CreatedBy { get; set; }
        public DateTime Created { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime RecordStartDateTime { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime RecordEndDateTime { get; set; }
    }
}

用户模型 - 这是我返回给客户端应用程序的模型

namespace Genistar.Organisation.Models.User
{
    [Table(nameof(PersonNote), Schema = "common")]
    public class PersonNote
    {
        public int Id { get; set; }
        public int PersonId { get; set; }
        public string Note { get; set; }
        public int AuthorId { get; set; }
        public string AuthorName { get; set; }
        public string CreatedBy { get; set; }
        public DateTime Created { get; set; }
    }
}

映射

CreateMap<Genistar.Organisation.Models.DataModels.PersonNote, Genistar.Organisation.Models.User.PersonNote>()
                  .ForMember(t => t.Id, opt => opt.MapFrom(s => s.Id))
                  .ForMember(t => t.PersonId, opt => opt.MapFrom(s => s.PersonId))
                  .ForMember(t => t.AuthorName, opt => opt.MapFrom(s => s.Author.FirstName + " " + s.Author.LastName))
                  .ForMember(t => t.Note, opt => opt.MapFrom(s => s.Note))
                  .ForMember(t => t.AuthorId, opt => opt.MapFrom(s => s.AuthorId))
                  .ForMember(t => t.CreatedBy, opt => opt.MapFrom(s => s.CreatedBy))
                  .ForMember(t => t.Created, opt => opt.MapFrom(s => s.Created));

以下查询有效，但仅从 Person 和 PersonNote 表中提取数据。我也在考虑获取 PersonNoteAttachment。我怎么做？我基本上需要在 User.PersonNote 模型中填充 FileName 和 MimeType 字段。如果你在上面看到我已经创建了一个 PersonNoteAttachment 数据模型

存储库

public IQueryable<PersonNote> GetPersonNotes(int personId)
        {
            var personNotes = _context.PersonNotes.Include(x => x.Person).Include(x=> x.Author).Where(p => p.PersonId == personId);
            return personNotes;
        }

接口：

 [FunctionName(nameof(GetPersonNote))]
        [UsedImplicitly]
        public Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "person-note/{id}")] HttpRequest req,
            int id) => _helper.HandleAsync(async () =>
        {
            //await _helper.ValidateRequestAsync(req,  SecurityPolicies.ViewNotes);
            var personNotes = await _organisationRepository.GetPersonNotes(id).ProjectTo<PersonNote>(_mapper.ConfigurationProvider).ToListAsync();
            return new OkObjectResult(personNotes);
        });

我的方法是在存储库中按照以下方式执行此操作，但我需要在存储库中返回 PersonNote 数据模型。我无法在模型中添加这些附加字段，因为它说无效列。我该如何处理？

 var personNotes = _context.PersonNotes
                .Include(x => x.Person)
                .Include(x => x.Author)
                .Where(p => p.PersonId == personId)
                .Join(_context.PersonNotesAttachments, c => c.Id, cm => cm.PersonNoteId, (c, cm) => new
                {    
                     cm.PersonNote.Id,
                     cm.PersonNote.PersonId,
                     cm.PersonNote.Person,
                     cm.PersonNote.Note,
                     cm.FileName,
                     cm.MimeType,
                     cm.Alias,
                     cm.PersonNote.AuthorId,
                     cm.PersonNote.CreatedBy,
                     cm.PersonNote.Created
                });

标签： entity-frameworkentity-framework-core

entity-framework - Entity Framework Core 3：使用连接从多个源中提取数据

问题描述

解决方案

推荐阅读