Linq. Avoid Joins

[shapper]

Hello,

I have 3 tables: Posts, Tags and PostsTags.

PostsTags relates Posts with Tags and has 3 fields: PostID and TagID.

I have all the relationships between tables well defined!

I have the following query:

PostPaper paper = (from p in database.Posts
where p.PostID == id
select new PostPaper {
Post = p,
Tags = new List<Tag>(
from pt in database.PostsT ags
join t in database.Tags on pt.TagID
equals t.TagID
where pt.PostID == p.PostID
select t).ToString()
}).SingleOrDefa ult();

Can I replace the Tags part with some linq code without using join?
For example:

...
Post = p,
Tags = p.PostsTags.

Maybe use SelectMany?

Please, help me out in understanding this.

Thanks,
Miguel

[shapper]

Re: Linq. Avoid Joins

On Aug 25, 1:33 pm, shapper <mdmo...@gmail. comwrote:

Hello,
>
I have 3 tables: Posts, Tags and PostsTags.
>
PostsTags relates Posts with Tags and has 3 fields: PostID and TagID.
>
I have all the relationships between tables well defined!
>
I have the following query:
>
      PostPaper paper = (from p in database.Posts
                         where p.PostID == id
                         select new PostPaper {
                           Post = p,
                           Tags = new List<Tag>(
                             from pt in database.PostsT ags
                             join t in database.Tags on pt.TagID
equals t.TagID
                             where pt.PostID == p.PostID
                             select t).ToString()
                         }).SingleOrDefa ult();
>
Can I replace the Tags part with some linq code without using join?
For example:
>
    ...
    Post = p,
    Tags = p.PostsTags.
>
Maybe use SelectMany?
>
Please, help me out in understanding this.
>
Thanks,
Miguel

Please, anyone?

[Pavel Minaev]

Re: Linq. Avoid Joins

On Aug 25, 4:33 pm, shapper <mdmo...@gmail. comwrote:

Hello,
>
I have 3 tables: Posts, Tags and PostsTags.
>
PostsTags relates Posts with Tags and has 3 fields: PostID and TagID.
>
I have all the relationships between tables well defined!
>
I have the following query:
>
      PostPaper paper = (from p in database.Posts
                         where p.PostID == id
                         select new PostPaper {
                           Post = p,
                           Tags = new List<Tag>(
                             from pt in database.PostsT ags
                             join t in database.Tags on pt.TagID
equals t.TagID
                             where pt.PostID == p.PostID
                             select t).ToString()
                         }).SingleOrDefa ult();
>
Can I replace the Tags part with some linq code without using join?
For example:
>
    ...
    Post = p,
    Tags = p.PostsTags.
>
Maybe use SelectMany?
>
Please, help me out in understanding this.

Your query is written is the most efficient and appopriate way to do
it. This is not at all surprising - if you're working with relational
data (such as relations via IDs and link tables), then relational
operators, such as join, are the right tools for the job. If you
wanted something more "object-oriented", consider looking at LINQ to
SQL or LINQ to Entities, which hides the IDs under class-typed
properties and collections.