Linq. OrderBy and Where.

[shapper]

Hello,

I have the following Linq query:

viewData.TagsPapers = (from t in database.Tags
select new TagPaper {
Tag = t,
Extra = new TagExtra
{
FileCount =
t.FilesTags.Count,
PostCount =
t.PostsTags.Count,
}}).ToList();

Given a variable named Source that can have two values "File" or
"Post" I would like to:

1. Order the records by FileCount or PostCount if Source is "File" or
"Post"

2. Get only the records where FileCount != 0 or PostCount != 0 if
Source is "File" or "Post"

Should I use two different queries?

In any case, I need help with the Order (1) and Where (2) problems.

Thanks,
Miguel

 

[Marc Gravell]

It isn't clear what Source is here - is this a property of a Tag? If
there are both File and Post records in the set, how do you sort it?

You can union LINQ queries, so you might have

var qry1 = from t in database.Tags
where t.Source == "File" && t.FileCount != 0
orderby t.FileCount
select new {...};
var qry2 = from t in database.Tags
where t.Source == "Post" && t.PostCount != 0
orderby t.PostCount
select new {...};

var final = qry1.Union(qry2).ToList();

(untested for obvious reasons)

Marc

 

[Marc Gravell]

Ah right - I just saw your explanation of "Source"; yes - I'd have 2
queries, then:

switch(Source) {
case "File":
viewData.blah = (from t in database.Tags
where t.FileCount != 0
orderby t.FileCount
select new {...}).ToList();
break;
case "Post":
viewData.blah = second query;
break;
}

Marc

 

[shapper]

Ah right - I just saw your explanation of "Source"; yes - I'd have 2
queries, then:

switch(Source) {
  case "File":
    viewData.blah = (from t in database.Tags
              where t.FileCount != 0
              orderby t.FileCount
              select new {...}).ToList();
    break;
  case "Post":
    viewData.blah = second query;
    break;

}

Marc

Thanks!

 

[shapper]

Ah right - I just saw your explanation of "Source"; yes - I'd have 2
queries, then:

switch(Source) {
  case "File":
    viewData.blah = (from t in database.Tags
              where t.FileCount != 0
              orderby t.FileCount
              select new {...}).ToList();
    break;
  case "Post":
    viewData.blah = second query;
    break;

}

Marc

Not done yet ...

viewData.TagsPapers = (from t in database.Tags
where t.FileCount != 0
orderby t.FileCount
select new TagPaper {
Tag = t,
FileCount = t.FilesTags.Count,
PostCount = t.PostsTags.Count
}).ToList();

I get an error on where and orderby ...

FileCount is not a property of Tag! It is a property of TagPaper ...
this is why I don't know how to do this.

Any idea?

Thanks,
Miguel

 

[Marc Gravell]

either: where t.FilesTags.Count != 0
or:
select new TagPaper {
Tag = t,
FileCount = t.FilesTags.Count,
PostCount = t.PostsTags.Count
} into tmp
where tmp.FileCount != 0
select tmp).ToList();

 

[shapper]

either: where t.FilesTags.Count != 0
or:
select new TagPaper {
  Tag = t,
  FileCount = t.FilesTags.Count,
  PostCount = t.PostsTags.Count} into tmp

where tmp.FileCount != 0
select tmp).ToList();

I have the following:

viewData.TagsPapers = (from t in database.Tags
orderby t.Name
select new TagPaper {
Tag = t,
PostCount = t.PostsTags.Count
} into tp
where tp.PostCount != 0
orderby tp.PostCount
select tp).Take(50).ToList();

I need the items to be ordered by name but before that I want to get
the 50 items with the highest PostCount.
So I ordered it by PostCount and used Take(50).

However, I think Take is getting the first 50 items of the final list
and not from the tp which is ordered by PostCount.

Does this makes any sense? How can I fix this?

Note: I am using take because I think there is no TOP in Linq.

Thanks,
Miguel

 

[Marc Gravell]

"TOP" is an SQL thing; "Take" is the correct LINQ equivalent.

Really, you need to do the Name sort after the Take, which is tricky
because your TagPaper doesn't have the Name. If you add the Name to
TagPaper you could add .OrderBy(x => x.Name) between Take and
ToList(); alternatively you could order at the client using
List<T>.Sort.

There might be a way to do it with an anonymous type (completely
untested):

viewData.TagsPapers = (from t in database.Tags
select new {Name = t.Name,
TagPaper = new TagPaper {
Tag = t,
PostCount = t.PostsTags.Count
} } into tp
where tp.TagPaper.PostCount != 0
orderby tp.TagPaper.PostCount
select
tp).Take(50).OrderBy(tp=>tp.Name)
.Select(tp=>tp.TagPaper).ToList();


Marc