Simplify Linq query with Select subquery multiple levels

Question

I have this Linq query. Can anyone help me to simplify it ? I am trying to make this as performance effective as possible. I am working against a third party api and a DB list of filtered fields. Field is our Class in our code. Everything else is a OnBase Hyland object. FilteredFields List is the master list of data that need to iterating it.

var keywordTypeList = app.Core.KeywordTypes;
var applicationList = app.WorkView.Applications;
var datasetList = app.WorkView.Datasets;
var fieldsList = new List<Models.Field>();
var fieldAsAttribute =
    from filteredField in filteredFields.ToList()
    join application in applicationList on filteredField.wv_app equals application.Name
    select new
    {
        aclassQuery = (
            from aclass in applicationClasses
            where aclass.Name == filteredField.wv_class && application.Name == flteredField.wv_app
            select new
            {

                attributeQuery = (
                    from attribute in aclass.Attributes
                    where attribute.Name == filteredField.wv_attr
                    select new Models.Field
                    {
                        FieldId = filteredField.field_id,
                        Name = filteredField.field_name,
                        HasDropdown = true,
                        DropdownId = application.Filters.Where(a => a.Name == filteredField.wv_filter).SingleOrDefault().ID,
                        DropdownType = Models.Field.DropdownTypeEnum.FilterEnum
                    }
                )
            })
    };

    fieldsList1.AddRange(fieldAsAttribute.ToList()); //Doesn't work

    foreach(var one in fieldAsAttribute)
    {
        foreach(var two in one.aclassQuery)
        {
            foreach(var three in two.attributeQuery)
            {

            }
        }
    }
```

Class definitions

public partial class field
{
     public long field_id { get; set; } 
     public string field_name { get; set; }
     public bool kw_flag { get; set; }
     public string kw_name { get; set; }
     public bool wv_flag { get; set; }
     public string wv_app { get; set; }
     public string wv_class { get; set; }
     public string wv_attr { get; set; }
     public string wv_dataset_name { get; set; }
     public string wv_filter_app { get; set; }
}

var fieldsList = new List<Models.Field>();
 public KeywordTypeList KeywordTypes => _keywordTypeList ?? (_keywordTypeList = new KeywordTypeListImplementation(base.Application));
 public ApplicationList Applications => _applications ?? (_applications = new ApplicationListImplementation(base.Application));

public  class Application 
{     
    public ClassList Classes;

    public FilterList Filters;

    public long ID { get; internal set; }

    public string Name { get; internal set; }
}


public class Class 
{
    public AttributeList Attributes;

    public long ID { get; internal set; }

    public string Name { get; internal set; }

    internal Application WVApplication { get; set; }
}

public sealed class Attribute  
{
    public Class Class => base.Application.WorkView.GetClassByID(ClassID);

    internal long ClassID { get; set; }

        
 }

Answer 1

Try the following query:

var fieldsQuery =
    from filteredField in filteredFields
    join application in applicationList on filteredField.wv_app equals application.Name
    join aclass in applicationClasses on 
        new { filteredField.wv_class, flteredField.wv_app } 
        equals 
        new { wv_class = aclass.Name, wv_app = application.Name } 
    from attribute in aclass.Attributes
        .Where(attribute =>  filteredField.wv_attr == attribute.Name)
    from filter in application.Filters
        .Where(filter => filteredField.wv_filter == filter.Name)
        .Take(1)
        .DefaultIfEmpty()
    select new Models.Field
    {
        FieldId = filteredField.field_id,
        Name = filteredField.field_name,
        HasDropdown = true,
        DropdownId = filter?.ID,
        DropdownType = Models.Field.DropdownTypeEnum.FilterEnum
    };

var fieldsList = fieldsQuery.ToList();