Avatar of Allan
Allan
Flag for United States of America asked on

LINQ - query for pair values

Hi Experts!

Thanks for reading this.

Say there are three tables:
PostTags:
   PostTagID (PK)
   TagName

PostTagPairs
   PostTagPairID (PK)
   PostID (FK)
   PostTagID (FK)

Posts
   PostID (PK)
   PostTitle

Open in new window

The following query will return TagName associated with Posts:
var myPost = (from bp in db.Posts
              where bp.PostID == postId
              select new
              {
                  bp.PostTitle,
                  tags = from p in bp.PostTagPairs select p.PostTag.TagName
              }).SingleOrDefault();

Open in new window

Question is how do you get the query above to return PostTagID along with TagName and iterate it in a for loop?
foreach(?? in myPost.tags)
       {
         ??
       } 

Open in new window

TIA!
ASP.NETC#.NET Programming

Avatar of undefined
Last Comment
Fernando Soto

8/22/2022 - Mon
SOLUTION
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Fernando Soto

Hi allanau20;

The following should give you what you want.

var myPost = (from bp in db.Posts
              where bp.PostID == postId
              select new
              {
                  bp.PostTitle,
                  tags = (from p in bp.PostTagPairs 
                          select new 
                          { 
                              TagName = p.PostTag.TagName, 
                              PostTagID = p.PostTag.PostTagID
                          }).ToList() 
              }).SingleOrDefault();

foreach( var pt in myPost.tags )
{
    Console.WriteLine("The TagName is : {0} The PostTagID is : {1}", pt.TagName, pt.PostTagID);
}

Open in new window

Allan

ASKER
thanks guys; let me try it now and get back to you...
Allan

ASKER
Hi FernandoSoto,

Got this error:

{"LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[<>f__AnonymousType0`2[System.String,System.Int32]] ToList[<>f__AnonymousType0`2](System.Collections.Generic.IEnumerable`1[<>f__AnonymousType0`2[System.String,System.Int32]])' method, and this method cannot be translated into a store expression."}


Any ideas?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Allan

ASKER
Hi jyparask,

looks like it doesn't recognize temp:

tempany ideas?
Ioannis Paraskevopoulos

Try this one

            var query = from t in PostTags
                        join pt in PostTagPairs
                        on t.PostTagId equals pt.PostTagId into
                        select new
                        {
                               p.PostTitle,
                               t.TagName
                        };

Giannis

PS what is your environment?
Allan

ASKER
sorry Giannis.

This is razor on MVC3 with EF 4.1, .net 4.0; i'll try it now.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Allan

ASKER
Hi Giannis.

for this --->  p.PostTitle,

I don't see where p (Posts) is defined?
also, would like to pull PostTagID (along with TagName). thx!
Ioannis Paraskevopoulos

Do you use temp somewhere else in your code? Try renaming temp on my initial post to something else.
Ioannis Paraskevopoulos

Hey,  it says tenp. This is a misspell. Change the error to temp.

Giannis
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Allan

ASKER
Thanks. I'm on the road. I'll try it again in 4hrs.
ASKER CERTIFIED SOLUTION
Fernando Soto

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Allan

ASKER
Thanks FernandoSoto; that works.

I'll want to try out yparask solution if you don't mind. thx.
Ioannis Paraskevopoulos

Hi,

If you like, you may also check an article i wrote for joining in LINQ, that has just been published.

https://www.experts-exchange.com/Programming/Languages/.NET/LINQ/A_11369-Joining-in-LINQ.html

If you wish feel free to leave a comment.

Giannis
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Allan

ASKER
Thanks Giannis your solution would work too.
I guess if I wanted to PostTitle (outside the for loop) I cannot get to it like this?
var Title = query.PostTitle

Anyways, appreciate your help and will provide comment to your article later this week.
Fernando Soto

Not a problem allanau20, glad I could help.