简化使用多级Select子查询的Linq查询。

huangapple go评论89阅读模式
英文:

Simplify Linq query with Select subquery multiple levels

问题

我有这个Linq查询。 有人可以帮助我简化它吗? 我正在尽可能地提高性能。 我正在使用第三方api和一个已筛选字段的DB列表。
Field是我们代码中的Class。 其他一切都是OnBase Hyland对象。
FilteredFields列表是需要迭代的主数据列表。

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 == filteredField.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()); // 不起作用

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

        }
    }
}

类定义

public partial class Field
{
     public long FieldId { get; set; } 
     public string Name { get; set; }
     public bool KwFlag { get; set; }
     public string KwName { get; set; }
     public bool WvFlag { get; set; }
     public string WvApp { get; set; }
     public string WvClass { get; set; }
     public string WvAttr { get; set; }
     public string WvDatasetName { get; set; }
     public string WvFilterApp { 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; }    
}

请注意,我对一些变量和属性的名称进行了更正以符合通常的C#命名约定。

英文:

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&lt;Models.Field&gt;();
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 &amp;&amp; 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 =&gt; a.Name == filteredField.wv_filter).SingleOrDefault().ID,
                        DropdownType = Models.Field.DropdownTypeEnum.FilterEnum
                    }
                )
            })
    };

    fieldsList1.AddRange(fieldAsAttribute.ToList()); //Doesn&#39;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&lt;Models.Field&gt;();
 public KeywordTypeList KeywordTypes =&gt; _keywordTypeList ?? (_keywordTypeList = new KeywordTypeListImplementation(base.Application));
 public ApplicationList Applications =&gt; _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 =&gt; base.Application.WorkView.GetClassByID(ClassID);

    internal long ClassID { get; set; }

        
 }

答案1

得分: 1

尝试以下查询:

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, filteredField.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();
英文:

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 =&gt;  filteredField.wv_attr == attribute.Name)
    from filter in application.Filters
        .Where(filter =&gt; 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();

huangapple
  • 本文由 发表于 2023年8月9日 10:27:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/76864199-2.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定