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

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

Simplify Linq query with Select subquery multiple levels

问题

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

  1. var keywordTypeList = app.Core.KeywordTypes;
  2. var applicationList = app.WorkView.Applications;
  3. var datasetList = app.WorkView.Datasets;
  4. var fieldsList = new List<Models.Field>();
  5. var fieldAsAttribute =
  6.     from filteredField in filteredFields.ToList()
  7.     join application in applicationList on filteredField.wv_app equals application.Name
  8.     select new
  9.     {
  10.         aclassQuery = (
  11.             from aclass in applicationClasses
  12.             where aclass.Name == filteredField.wv_class && application.Name == filteredField.wv_app
  13.             select new
  14.             {
  16.                 attributeQuery = (
  17.                     from attribute in aclass.Attributes
  18.                     where attribute.Name == filteredField.wv_attr
  19.                     select new Models.Field
  20.                     {
  21.                         FieldId = filteredField.field_id,
  22.                         Name = filteredField.field_name,
  23.                         HasDropdown = true,
  24.                         DropdownId = application.Filters.Where(a => a.Name == filteredField.wv_filter).SingleOrDefault().ID,
  25.                         DropdownType = Models.Field.DropdownTypeEnum.FilterEnum
  26.                     }
  27.                 )
  28.             })
  29.     };
  31. fieldsList1.AddRange(fieldAsAttribute.ToList()); // 不起作用
  33. foreach(var one in fieldAsAttribute)
  34. {
  35.     foreach(var two in one.aclassQuery)
  36.     {
  37.         foreach(var three in two.attributeQuery)
  38.         {
  40.         }
  41.     }
  42. }

类定义

  1. public partial class Field
  2. {
  3.      public long FieldId { get; set; } 
  4.      public string Name { get; set; }
  5.      public bool KwFlag { get; set; }
  6.      public string KwName { get; set; }
  7.      public bool WvFlag { get; set; }
  8.      public string WvApp { get; set; }
  9.      public string WvClass { get; set; }
  10.      public string WvAttr { get; set; }
  11.      public string WvDatasetName { get; set; }
  12.      public string WvFilterApp { get; set; }
  13. }
  1. var fieldsList = new List<Models.Field>();
  2. public KeywordTypeList KeywordTypes => _keywordTypeList ?? (_keywordTypeList = new KeywordTypeListImplementation(base.Application));
  3. public ApplicationList Applications => _applications ?? (_applications = new ApplicationListImplementation(base.Application));
  1. public class Application 
  2. {     
  3.     public ClassList Classes;
  5.     public FilterList Filters;
  7.     public long ID { get; internal set; }
  9.     public string Name { get; internal set; }
  10. }
  12. public class Class 
  13. {
  14.     public AttributeList Attributes;
  16.     public long ID { get; internal set; }
  18.     public string Name { get; internal set; }
  20.     internal Application WVApplication { get; set; }
  21. }
  23. public sealed class Attribute  
  24. {
  25.     public Class Class => base.Application.WorkView.GetClassByID(ClassID);
  27.     internal long ClassID { get; set; }    
  28. }

请注意，我对一些变量和属性的名称进行了更正以符合通常的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.

  1. var keywordTypeList = app.Core.KeywordTypes;
  2. var applicationList = app.WorkView.Applications;
  3. var datasetList = app.WorkView.Datasets;
  4. var fieldsList = new List&lt;Models.Field&gt;();
  5. var fieldAsAttribute =
  6.     from filteredField in filteredFields.ToList()
  7.     join application in applicationList on filteredField.wv_app equals application.Name
  8.     select new
  9.     {
  10.         aclassQuery = (
  11.             from aclass in applicationClasses
  12.             where aclass.Name == filteredField.wv_class &amp;&amp; application.Name == flteredField.wv_app
  13.             select new
  14.             {
  16.                 attributeQuery = (
  17.                     from attribute in aclass.Attributes
  18.                     where attribute.Name == filteredField.wv_attr
  19.                     select new Models.Field
  20.                     {
  21.                         FieldId = filteredField.field_id,
  22.                         Name = filteredField.field_name,
  23.                         HasDropdown = true,
  24.                         DropdownId = application.Filters.Where(a =&gt; a.Name == filteredField.wv_filter).SingleOrDefault().ID,
  25.                         DropdownType = Models.Field.DropdownTypeEnum.FilterEnum
  26.                     }
  27.                 )
  28.             })
  29.     };
  31.     fieldsList1.AddRange(fieldAsAttribute.ToList()); //Doesn&#39;t work
  33.     foreach(var one in fieldAsAttribute)
  34.     {
  35.         foreach(var two in one.aclassQuery)
  36.         {
  37.             foreach(var three in two.attributeQuery)
  38.             {
  40.             }
  41.         }
  42.     }
  43. ```

Class definitions

  1. public partial class field
  2. {
  3.      public long field_id { get; set; } 
  4.      public string field_name { get; set; }
  5.      public bool kw_flag { get; set; }
  6.      public string kw_name { get; set; }
  7.      public bool wv_flag { get; set; }
  8.      public string wv_app { get; set; }
  9.      public string wv_class { get; set; }
  10.      public string wv_attr { get; set; }
  11.      public string wv_dataset_name { get; set; }
  12.      public string wv_filter_app { get; set; }
  13. }
  1. var fieldsList = new List&lt;Models.Field&gt;();
  2.  public KeywordTypeList KeywordTypes =&gt; _keywordTypeList ?? (_keywordTypeList = new KeywordTypeListImplementation(base.Application));
  3.  public ApplicationList Applications =&gt; _applications ?? (_applications = new ApplicationListImplementation(base.Application));
  1. public  class Application 
  2. {     
  3.     public ClassList Classes;
  5.     public FilterList Filters;
  7.     public long ID { get; internal set; }
  9.     public string Name { get; internal set; }
  10. }
  13. public class Class 
  14. {
  15.     public AttributeList Attributes;
  17.     public long ID { get; internal set; }
  19.     public string Name { get; internal set; }
  21.     internal Application WVApplication { get; set; }
  22. }
  24. public sealed class Attribute  
  25. {
  26.     public Class Class =&gt; base.Application.WorkView.GetClassByID(ClassID);
  28.     internal long ClassID { get; set; }
  30.         
  31.  }

答案1

得分: 1

尝试以下查询：

  1. var fieldsQuery =
  2.     from filteredField in filteredFields
  3.     join application in applicationList on filteredField.wv_app equals application.Name
  4.     join aclass in applicationClasses on 
  5.         new { filteredField.wv_class, filteredField.wv_app } 
  6.         equals 
  7.         new { wv_class = aclass.Name, wv_app = application.Name } 
  8.     from attribute in aclass.Attributes
  9.         .Where(attribute => filteredField.wv_attr == attribute.Name)
  10.     from filter in application.Filters
  11.         .Where(filter => filteredField.wv_filter == filter.Name)
  12.         .Take(1)
  13.         .DefaultIfEmpty()
  14.     select new Models.Field
  15.     {
  16.         FieldId = filteredField.field_id,
  17.         Name = filteredField.field_name,
  18.         HasDropdown = true,
  19.         DropdownId = filter?.ID,
  20.         DropdownType = Models.Field.DropdownTypeEnum.FilterEnum
  21.     };
  23. var fieldsList = fieldsQuery.ToList();
英文:

Try the following query:

  1. var fieldsQuery =
  2.     from filteredField in filteredFields
  3.     join application in applicationList on filteredField.wv_app equals application.Name
  4.     join aclass in applicationClasses on 
  5.         new { filteredField.wv_class, flteredField.wv_app } 
  6.         equals 
  7.         new { wv_class = aclass.Name, wv_app = application.Name } 
  8.     from attribute in aclass.Attributes
  9.         .Where(attribute =&gt;  filteredField.wv_attr == attribute.Name)
  10.     from filter in application.Filters
  11.         .Where(filter =&gt; filteredField.wv_filter == filter.Name)
  12.         .Take(1)
  13.         .DefaultIfEmpty()
  14.     select new Models.Field
  15.     {
  16.         FieldId = filteredField.field_id,
  17.         Name = filteredField.field_name,
  18.         HasDropdown = true,
  19.         DropdownId = filter?.ID,
  20.         DropdownType = Models.Field.DropdownTypeEnum.FilterEnum
  21.     };
  23. var fieldsList = fieldsQuery.ToList();

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

发表评论

匿名网友

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

确定