Join between model and view in C# with EF Core

Question

WorkflowData is a table in database

public class WorkflowData : Entity
{
    public int Id { get; set; }
    // ... others properties
    public ICollection<WorkflowDataDynamicColumn> WorkflowDataDynamicColumns { get; set; }
}

WorkflowDataDynamicColumn is a view in my database.

public class WorkflowDataDynamicColumn
{
    public int WorkflowDataId { get; set; }
    public string? Value { get; set; }
}

I need to query the WorkflowData records and for each record get a key of that object a list of objects with the relationships in WorkflowDataDynamicColumn - how can I do that?

I tried with this code, but it does not work:

var query = _context.WorkflowDatas
                .AsNoTracking()
                .Include(x => x.WorkflowStatus)
                .Include(wd => wd.WorkflowDataDynamicColumns)
                .Where(x => x.WorkflowStatus != null)
                .Select(wd => new WorkflowDataDto
                              {
                                  Id = wd.Id,
                                  WorkflowStatus = wd.WorkflowStatus == null ? null : _mapper.Map<WorkflowStatusDto>(wd.WorkflowStatus),
                                  FormColumn = wd.WorkflowDataDynamicColumns
                                                 .Select(dc => new WorkflowDataFormColumnDto
                                                               {
                                                                   Id = dc.Id,
                                                                   Value = dc.Value ?? string.Empty,
                                                               }).ToList()
                              })
                .ToListAsync();

Error message:

Unable to determine the relationship represented by navigation 'WorkflowData.WorkflowDataDynamicColumns' of type 'ICollection'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

This is the payload I need:

{
  "id": 21094,
  "workflowStatus": { ... }
  "formColumn": [
    {
      "id": 1,
      "value": "abc"
    },
    {
      "id": 2,
      "value": "def"
    },
    ...
  ]
}

Context:

modelBuilder.Entity<WorkflowData>(entity =>
            {
                entity.Property(x => x.IsActive).HasDefaultValue(true);
                entity.HasMany(x => x.WorkflowDataDynamicColumns)
                    .WithOne()
                    .HasForeignKey(x => x.WorkflowDataId);
            });

            modelBuilder.Entity<WorkflowDataDynamicColumn>()
                .HasNoKey()
                .ToView("vw_WorkflowDataDetails")
                .Property(x => x.WorkflowDataId).HasColumnName("WorkflowDataId");

Answer 1

Try to refactor your WorkflowDataDynamicColumn class as below.

public class WorkflowDataDynamicColumn
{
    public int Id { get; set; } // Add primary Key
    public int WorkflowDataId { get; set; } 
    public virtual WorkflowData WorkflowData { get; set; } // Add foreign key relationship 
    public string? Value { get; set; }
}