如何在不影响性能的情况下使用Intersect等集合函数？ [英] How to use Collection Functions like Intersect without impacting performance ?

问题描述

大家好，

短篇小说：

Hello everyone,
Short Story :

UP2Entities up2ent = new UP2Entities();
List<ViewAllItem> items = new List<ViewAllItem>();
           items = up2ent.ViewAllItems.ToList();

            lstYesTag = pnlTags.Children.OfType<Label>().Where(l1 => ((SolidColorBrush)l1.Background).Color == Colors.Green).Select(l => Convert.ToInt32(l.Tag)).ToList();
            lstNoTag = pnlTags.Children.OfType<Label>().Where(l1 => ((SolidColorBrush)l1.Background).Color == Colors.Red).Select(l => Convert.ToInt32(l.Tag)).ToList();
            if (lstYesTag.Count > 0) items = items.Where(r => up2ent.Items.Find(r.ItemID).ItemsTags.All(itg => lstYesTag.Contains(itg.TagID))).ToList();
            if (lstNoTag.Count > 0) items = items.Where(r => up2ent.Items.Find(r.ItemID).ItemsTags.All(itg => !lstNoTag.Contains(itg.TagID))).ToList();

当我运行这段代码时，调试器在60秒后休息需要太长时间!!



更长版本：

i有一个视图（ViewAllItems），显示项目信息列表

视图有一个字段ItemId

i有一个表（Items）也有一个字段ItemId与表（ItemsTags）一对多相关



i需要根据用户输入的标签过滤视图，作为两个整数列表

lstYesTag ==>项目需要的标签ID

lstNoTag ==>标签ID需要没有





经过一些搜索我认为这个问题是linq翻译的方式查询到SQL查询

所以我通过扩展项目尝试了其他形式的查询：

when i run this code it takes sooo long that the debugger after 60 seconds breaks !!

Longer Version :
i have a view (ViewAllItems) that displays a list of item information
the view has a field ItemId
i have a table (Items) that also have a field ItemId and is related to table (ItemsTags) one-to-many

i need to filter the view base on tags the user inputs as two lists of integer
lstYesTag ==> the tag ids that the item need to have
lstNoTag ==> the tag ids that the need Not to have


after some searching i came to assume that the problem is in the way linq translates my query to SQL query
so i tried other forms of query by extending the Item by :

public static class MyExtensions
   {
       public static bool IsTagsAll(this Item myItem , List<int> tagsList)
       {
           return   myItem.ItemsTags.Select(itg => itg.TagID).ToList().Intersect(tagsList).ToList().Count == tagsList.Count;

       }
}

并将代码更改为：

and changed the code to :

if (lstYesTag.Count > 0) items = items.Where(r => up2ent.Items.Find(r.ItemID).IsTagsAll(lstYesTag)).ToList();

但仍然得到相同的结果



i am实体框架的新手，

i之前使用类似的方法和linq到数据集并且它工作得很好



i得到了一些不使用Views的建议，但是我使用它的原因是我以前用来获取数据的查询速度太慢，尽管相当简单



任何建议实现我的目标与任何其他意思是??



谢谢大家



更新::

i被告知问题是我太多的ToList（）调用，

所以我做了一个简单的测试，

i最小化了ToList的使用，并尝试使用linq到实体的相同逻辑还有另一次使用linq到数据集



第一次运行的结果时间：
linq到实体的
是13000毫秒
$在linq到数据集的b $ b是95毫秒

秒：

566

93

third

98

80

测试代码：

but still got the same result

i am new to entityframework ,
i used similar methods with linq to datasets before and it worked perfectly

i got some recommendations not to use Views , but the reason i used it was that the Query i used to get the data in the first place was too slow despite being fairly simple

any suggestions to achieve my goal with any other means ??

Thanks everyone

Update ::
i was told that the problem is my too many ToList() calls ,
so i made a simple test,
i minimized the ToList use , and tried the same logic once with linq to entities and another time with linq to dataset

the result time in first run :
in linq to entities was 13000 milliseconds
in linq to datasets was 95 milliseconds
second :
566
93
third
98
80
the testing code :

UP2 ds = new UP2();
       UP2TableAdapters.ItemsTableAdapter daItems = new UP2TableAdapters.ItemsTableAdapter();
       UP2TableAdapters.ItemsTagsTableAdapter daItemsTag = new UP2TableAdapters.ItemsTagsTableAdapter();
       UP2TableAdapters.TagsTableAdapter daTags = new UP2TableAdapters.TagsTableAdapter();
       UP2Entities up2Ent = new UP2Entities();
 private void btn_Click(object sender, RoutedEventArgs e)
       {
           populateEntity();

           populateDataset();


       }
       void populateEntity()
       {
           lst.Items.Clear();
           Stopwatch st = new Stopwatch();
           st.Start();
           List<Item> items = new List<Item>();
           items = up2Ent.Items.Take(500).ToList();
           prgrss.Maximum = up2Ent.Items.Count();
           foreach (Item i in items)
           {
               lst.Items.Add(i.Name + " : ");
               foreach (ItemsTag it in i.ItemsTags)
               {
                   lst.Items.Add("    " + it.Tag.Name);
               }
               lst.Items.Add(st.ElapsedMilliseconds.ToString());
               prgrss.Value += 1;


           }
           st.Stop();
           MessageBox.Show(st.ElapsedMilliseconds.ToString());
       }
       void populateDataset()
       {
           lst.Items.Clear();
           Stopwatch st = new Stopwatch();
           st.Start();
           List<UP2.ItemsRow> items = new List<UP2.ItemsRow>();
           items = ds.Items.Take(500).ToList();
           prgrss.Maximum = up2Ent.Items.Count();
           foreach (UP2.ItemsRow i in items)
           {
               lst.Items.Add(i.Name + " : ");
               foreach (UP2.ItemsTagsRow it in i.GetItemsTagsRows())
               {
                   lst.Items.Add("    " + it.TagsRow.Name);
               }
               lst.Items.Add(st.ElapsedMilliseconds.ToString());
               prgrss.Value += 1;


           }
           st.Stop();
           MessageBox.Show(st.ElapsedMilliseconds.ToString());
       }

推荐答案

这篇关于如何在不影响性能的情况下使用Intersect等集合函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！