Join/Where with LINQ and Lambda

Problem

I’m experiencing difficulty with a LINQ and Lambda query. I’m getting a lot of errors so far. Here’s how I did it:

int id = 1;
var query = database.Posts.Join(database.Post_Metas,
                                post => database.Posts.Where(x => x.ID == id),
                                meta => database.Post_Metas.Where(x => x.Post_ID == id),
                                (post, meta) => new { Post = post, Meta = meta });

I’m not sure if this query is correct because I’m new to LINQ.

Asked by David

Solution #1

If you’re used to SQL syntax, I feel that utilizing the LINQ query syntax is much cleaner, more natural, and easier to discover errors:

var id = 1;
var query =
   from post in database.Posts
   join meta in database.Post_Metas on post.ID equals meta.Post_ID
   where post.ID == id
   select new { Post = post, Meta = meta };

However, if you insist on utilizing lambdas, you’re employing incorrect syntax. Using the LINQ extension techniques, here’s the same query:

var id = 1;
var query = database.Posts    // your starting point - table in the "from" statement
   .Join(database.Post_Metas, // the source table of the inner join
      post => post.ID,        // Select the primary key (the first part of the "on" clause in an sql "join" statement)
      meta => meta.Post_ID,   // Select the foreign key (the second part of the "on" clause)
      (post, meta) => new { Post = post, Meta = meta }) // selection
   .Where(postAndMeta => postAndMeta.Post.ID == id);    // where statement

Answered by Daniel Schaffer

Solution #2

This could be interpreted in two ways. I created the following queries using LINQPad (invaluable if you’re new to LINQ) and a dummy database:

Posts.Join(
    Post_metas,
    post => post.Post_id,
    meta => meta.Post_id,
    (post, meta) => new { Post = post, Meta = meta }
)

or

from p in Posts
join pm in Post_metas on p.Post_id equals pm.Post_id
select new { Post = p, Meta = pm }

In this scenario, I believe the LINQ syntax is more readable (I change between the two depending upon which is easiest to read).

However, if you have proper foreign keys in your database (for example, between post and post meta), you usually won’t require an explicit join unless you’re trying to load a big number of records. You appear to be attempting to load a single post and associated metadata in your example. Assuming that each post has a lot of post meta entries, you could do something like this:

var post = Posts.Single(p => p.ID == 1);
var metas = post.Post_metas.ToList();

If you don’t want to deal with the n+1 problem, you can tell LINQ to SQL to load all of the relevant items at once (albeit this is a more complex topic for when you’re more comfortable with L2S). “When you load a Post, additionally load all of its associated records via the foreign key represented by the ‘Post metas’ field,” explains the example below:

var dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<Post>(p => p.Post_metas);

var dataContext = new MyDataContext();
dataContext.LoadOptions = dataLoadOptions;

var post = Posts.Single(p => p.ID == 1); // Post_metas loaded automagically

Many LoadWith calls on a single set of DataLoadOptions for the same type, or many different types, are feasible. However, if you do this frequently, you might want to consider caching.

Answered by Damian Powell

Solution #3

Daniel provides an excellent description of the syntactic relationships, but I created this paper for my team to help them grasp it a little better. I hope this information is useful to someone.

Answered by Talspaugh27

Solution #4

The key choices on your keyboard are incorrect. They should return the key to utilize in the join by taking an object of the type of the table in question. I believe you are referring to the following:

var query = database.Posts.Join(database.Post_Metas,
                                post => post.ID,
                                meta => meta.Post_ID,
                                (post, meta) => new { Post = post, Meta = meta });

The where clause can be used after the key selector, rather than as part of it.

Answered by Mark Byers

Solution #5

I’m posting since I spent an entire day staring at these examples when I first started learning LINQ + EntityFramework.

This is simple if you’re using EntityFramework and have a navigation property named Meta on your Post model object set up. What are you waiting for if you’re utilizing entity and don’t have that navigation property?

database
  .Posts
  .Where(post => post.ID == id)
  .Select(post => new { post, post.Meta });

You’d set up the property first if you were doing code first.

class Post {
  [Key]
  public int ID {get; set}
  public int MetaID { get; set; }
  public virtual Meta Meta {get; set;}
}

Answered by Andy V

Post is based on https://stackoverflow.com/questions/2767709/join-where-with-linq-and-lambda