Entity Framework Query - how to return desired results

Question

I have this query

ViewBag.Tags = db.Tag_Group
   .Where(u => 
      u.Tags.Any(d => d.CategoryName.Contains("|" + CategoryID + "|")) 
      & u.Tags.Any(s=>s.Tag_List.Any(d=>d.Item.ItemsOnStock>0)))
   .OrderBy(y => y.TagGrupName)
   .ToList();

It returns all groups and not only ItemsOnStock>0 groups.

What am I doing wrong ?

Tag_List has many Item and many Tags. Tag_Group has many Tags

Table Tag_List is used to store Tags for specific Items.

I only need List<Tag_Group> for items that are on stock. Is there some better way to do this ?

Have you tried looking at the SQL Statement that this gets turned into and running it yourself against the database? This EF query looks fine to me and I can't see what is wrong. — Cameron Aavik, Dec 11 '17 at 02:45

score 0 · Accepted Answer · answered Dec 11 '17 at 08:16

This is a disguised "filtered Include" question and the answer is similar to the usual work-around for lack of filtering in Include.

In your case, you better start the query at the Tags and group them by their TagGroup (of which I assume the navigation property exists):

db.Tags
  .Where(t => t.CategoryName.Contains("|" + CategoryID + "|") 
           && t.Tag_List.Any(d => d.Item.ItemsOnStock > 0))
  .GroupBy(t => t.TagGroup)
  .AsEnumerable()
  .Select(g => g.Key)
  .OrderBy(y => y.TagGroupName)
  .ToList();

Remember to disable lazy loading, so the groups' Tags collections won't be populated fully when you access them.

score 0 · Answer 2 · answered Dec 11 '17 at 09:23

Could it be that you get the wrong result because of your one & instead of &&?

So you have a class Tag_Group. Every object of Tag_Group has zero or more Tags.

Every Tag has a string CategoryName and an IEnumerable Tag_List that contains zero or more Items. Every Item has an int(?) property ItemOnStock. The int part is not important. It can be any class as long as you can compare it with zero.

It seems you want all Tag_Group objects that have at least one Tag in its sequence of Tags that:

Has a CategoryName equal to "|" + CategoryID + "|"
AND has at least one item in Tag_List that has an ItemOnStock > 0

Or could it be that you want all Tag_Group objects that

Has at least one Tag in Tags with a CategoryName equal to "|" + CategoryID + "|"
OR has at least one Tag in Tags that has at leas one item in Tag_List that has an ItemOnStock > 0. This Tag may be a different Tag than the one with the CategoryName

I assume you want the AND option.

Although you didn't say so, I assume db is your dbContext, and you want your query to be performed by the database, so AsQueryable, and not AsEnumerable.

var CategoryId = ...
string categoryName = "|" + CategoryId + "|";

var result = db.Tag_Group
   // I want only those tagGroupElements that have at least one Tag
   // in sequence Tags that matches both conditions:
   .Where(tagGroupElement => tagGroupEleent.Tags
             // the tagGroupElement should have a matching CategoryName
       .Where(tag => tag.CategoryName == categoryName)
             // and the tagGroupElement should have at least one item
             // in Tag_List with ItemOnStock > 0
          && tag.Tag_List
              .Where(tagListElement => tagListElement.ItemOnStock > 0)
              .Any())
       .Any());

score 0 · Answer 3 · answered Dec 11 '17 at 22:07

Thank you both for your help :)

I ended up using a bit modified Gert's answer since I needed Tags grouped by TagGroup

db.Tags
  .Where(t => t.CategoryName.Contains("|" + CategoryID + "|") 
           && t.Tag_List.Any(d => d.Item.ItemsOnStock > 0))
  .GroupBy(t => t.TagGroup.GroupName)
  .OrderBy(y => y.Key)
  .ToList();

I learned a lot from your answers

Entity Framework Query - how to return desired results

3 Answers3