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();
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();
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());
}
Submit an article or tip