471,337 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

Linq. Where

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
Sep 6 '08 #1
10 1727
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
Sep 7 '08 #2
On Sep 7, 9:56*am, Marc Gravell <marc.grav...@gmail.comwrote:
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

Sep 7 '08 #3
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
Sep 7 '08 #4
On Sep 7, 8:16*pm, Marc Gravell <marc.grav...@gmail.comwrote:
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
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
Sep 7 '08 #5
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
Sep 7 '08 #6
On Sep 7, 10:53*pm, Marc Gravell <marc.grav...@gmail.comwrote:
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
Sep 7 '08 #7
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

Sep 7 '08 #8
On Sep 7, 11:33*pm, Marc Gravell <marc.grav...@gmail.comwrote:
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
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
Sep 7 '08 #9
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
Sep 7 '08 #10
On Sep 8, 12:08*am, Marc Gravell <marc.grav...@gmail.comwrote:
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
Sep 7 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

28 posts views Thread by Marc Gravell | last post: by
4 posts views Thread by BeSharp | last post: by
22 posts views Thread by paululvinius | last post: by
1 post views Thread by john | last post: by
9 posts views Thread by =?Utf-8?B?cmF1bGF2aQ==?= | last post: by
14 posts views Thread by thj | last post: by
9 posts views Thread by Cirene | last post: by
21 posts views Thread by hrishy | last post: by
4 posts views Thread by CSharper | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.