473,324 Members | 2,246 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 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 1800
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

28
by: Marc Gravell | last post by:
In Linq, you can apparently get a meaningful body from and expression's .ToString(); random question - does anybody know if linq also includes a parser? It just seemed it might be a handy way to...
4
by: BeSharp | last post by:
I recently stumbled across a pretty interesting LINQ to SQL question and wonder, whether anybody might have an answer. (I'm doing quite some increasing LINQ evangelism down here in Germany.). ...
22
by: paululvinius | last post by:
Hi! Testing som Linq-expressions and tried to measure performance and compare it to pre-Linq programming. The folloing two methods are functional equal but the non-Linq one is twice as fast....
1
by: john | last post by:
I'm trying to build a LINQ expression that will use a dynamic construction of a LIKE statement in the WHERE clause, it would look something like this in SQL: WHERE TaskGroup Like "*00*" OR...
9
by: =?Utf-8?B?cmF1bGF2aQ==?= | last post by:
Hi all: after reading different places/sites about linq... I ran into these questions: 1. What framework do we need to run linq ? (does it depend on what version of visual studio we have?) how...
14
by: thj | last post by:
Hi, I was wondering what you guys are using and why? LINQ to SQL or NHibernate? Thanks in advance, Tommy
9
by: Cirene | last post by:
I'm about to begin a brand new, big, ASP.NET project (using 3.5 .net fw), VS 2008. I'm using MySQL as the backend (customer request.) I have absolutely no experience with LINQ and/or the Entity...
21
by: hrishy | last post by:
Hi Will LINQ be ported to Python ? regards Hrishy
4
by: CSharper | last post by:
I have following XML <root> <Person id="1"> <Name>a</Name> </Person> <Person id="2"> <Name>b</Name> </Person> </root>
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.