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.PostsTags
join t in database.Tags on pt.TagID
equals t.TagID
where pt.PostID == p.PostID
select t).ToString()
}).SingleOrDefault();

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]

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.PostsTags
                             join t in database.Tags on pt.TagID
equals t.TagID
                             where pt.PostID == p.PostID
                             select t).ToString()
                         }).SingleOrDefault();

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]

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.PostsTags
                             join t in database.Tags on pt.TagID
equals t.TagID
                             where pt.PostID == p.PostID
                             select t).ToString()
                         }).SingleOrDefault();

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.