Linq. Where

[shapper]

Hello,

I create the following Linq query to select all the tags associated to
Posts:

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

I would like to select and count the tags but only the ones that are
related to Posts that are published.

Post object in Posts table has a property named IsPublished which is
true when it is published.

Any idea of how to transform my query to also accomplish this.

Thanks,
Miguel

 

[Marc Gravell]

If I understand correctly, you want the TagPaper.Count to be the count
of published posts against the tag - is that correct?

In which case, you should be able to use the predicate form of
Count(), assuming that t.PostsTags.Count is actually
t.PostsTags.Count() [i.e. theLINQ extension method, not a property]

....
select new TagPaper {
Tag = t,
Count = t.PostsTags.Count(post=>post.IsPublished)
}
....

Marc

 

[shapper]

If I understand correctly, you want the TagPaper.Count to be the count
of published posts against the tag - is that correct?

In which case, you should be able to use the predicate form of
Count(), assuming that t.PostsTags.Count is actually
t.PostsTags.Count() [i.e. theLINQ  extension method, not a property]

...
select new TagPaper {
  Tag = t,
  Count = t.PostsTags.Count(post=>post.IsPublished)}

...

Marc

Hi,

Yes, Count is the Linq extension method.

However, PostsTags is a table that relates Posts with Tags and has
only two columns (PostID and TagID)

The IsPublished column in in Posts table. So I get the following
error:

'App.Models.PostsTag' does not contain a definition for 'IsPublished'
and no extension method 'IsPublished' accepting a first argument of
type 'App.Models.PostsTag' could be found (are you missing a using
directive or an assembly reference?

Any idea?

Thanks,
Miguel

 

[Marc Gravell]

The IsPublished column in in Posts table. So I get the following

error:

Sorry, but it is going to be hard to suggest the right approach since
we don't know your schema, and it isn't obvious from the post how
things link together...

Marc

 

[shapper]

Sorry, but it is going to be hard to suggest the right approach since
we don't know your schema, and it isn't obvious from the post how
things link together...

Marc

Hi,

Here is my SQL Script that creates the Table Scheme if it helps:

-- Posts
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
Body nvarchar(max) null,
IsPublished bit null,
UpdatedAt datetime null
)

-- PostsTags
create table dbo.PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
on delete cascade
)

-- Tags
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) null
)

Then I created the LinqToSQL dbml file automatically in VS 2008.

TagPaper is just a wrapper of object Tag that contains two properties:
Tag and Count (int). This last one to store the count value.

Does this help?

Thanks,
Miguel

 

[Marc Gravell]

So since PostTags bridges the two, I would assume that LINQ-to-SQL
puts in the parent/child links (for PostTags) in both directions? (if
not, you can enable it in the designer) - so you should be able to use
something like:

select new TagPaper {
Tag = t,
Count = t.PostsTags.Count(pt=>pt.Post.IsPublished)
}

Marc

 

[shapper]

So since PostTags bridges the two, I would assume that LINQ-to-SQL
puts in the parent/child links (for PostTags) in both directions? (if
not, you can enable it in the designer) - so you should be able to use
something like:

select new TagPaper {
  Tag = t,
  Count = t.PostsTags.Count(pt=>pt.Post.IsPublished)

}

Marc

It seems to be working. I just needed to change to:

Count = t.PostsTags.Count(pt => (bool)pt.Post.IsPublished)

or

Count = t.PostsTags.Count(pt => pt.Post.IsPublished == true)

Because if not I get the error:
Cannot implicitly convert type 'bool?' to 'bool'. An explicit
conversion exists (are you missing a cast?)

Thanks,
Miguel

 

[Marc Gravell]

Cannot implicitly convert type 'bool?' to 'bool'. An explicit

conversion exists (are you missing a cast?)

Oh, right - I didn't spot that it is nullable; in which case, you
probably want think about how you want to handle NULLs; you could
check .HasValue, or you could use null-coalescing; for example, if you
always want to exclude them,

Count(pt => pt.Post.IsPublished ?? false)

I haven't checked, but that should do the job... if IsPublished is
NULL it will treat it as false, i.e. exclude it.

Marc

 

[shapper]

Oh, right - I didn't spot that it is nullable; in which case, you
probably want think about how you want to handle NULLs; you could
check .HasValue, or you could use null-coalescing; for example, if you
always want to exclude them,

Count(pt => pt.Post.IsPublished ?? false)

I haven't checked, but that should do the job... if IsPublished is
NULL it will treat it as false, i.e. exclude it.

Marc

I thought SQL bool type is never null ... well, at least I am treating
as it and I always require to be true or false.

But your suggestion is good just in case.

Thanks,
Miguel

 

[Marc Gravell]

Any SQL type can be nullable (in SQL Server, at least).

"bit NOT NULL" is never null; you have "bit NULL", so it can be null.
If you don't want it to be null, change your database to "bit NOT
NULL" are regenerate your DBML(or edit it by hand in the xml editor) -
it will be a lot easier to code against.

Marc

 

[shapper]

Any SQL type can be nullable (in SQL Server, at least).

"bit NOT NULL" is never null; you have "bit NULL", so it can be null.
If you don't want it to be null, change your database to "bit NOT
NULL" are regenerate your DBML(or edit it by hand in the xml editor) -
it will be a lot easier to code against.

Marc

I will follow your suggestion.

Thank you once again,
Miguel