Retrieving data from a one-to-many relationship

Question

I'm trying to retrieve data from a one-to-many relationship, in an ASP.NET MVC application.

Let's say I have two tables Posts and Category with the following attributes:

posts -id -title -category_id

category -id -name -author

I want to grab posts that belong to a category and grab the values of .name and .author from the category. This is what I have in the model:

public IQueryable<Post> GetPosts()
{
    return from post in db.Posts
      join categories in FindCategories()
      on categories.id equals post.category_id
      select new
      {
       title = post.title,
       name = categories.name,
       author = categories.author
      };
    }

And in the controller:

public ActionResult Index()
{
    var allposts = postRepository.GetPosts().ToList();
    return View(allposts);
}

The model complaints that it can't convert and IQueryable of Anonymous type into an IQueryable < Post >. I suspect it's because I'm doing Select new { }, but I'm not sure.
If I do IQueryable only then I don't have the ToList() method available in the view.

So, I'm lost. Can you help? (an by the way, I'm a complete newbie at this asp.net mvc thing.)

Answer 1

I think the problem is that you're trying to use a var outside of the scope of a single method. In your projection stage try newing-up a Post object rather than an anonymous type. e.g.

public IQueryable<Post> GetPosts()
{
return from post in db.Posts
                join categories in FindCategories()
                on categories.id equals post.category_id
                select new Post()
                {
                        title = post.title,
                        name = categories.name,
                        author = categories.author
                };
}

Answer 2

The question has been answered by Lazarus in the comments. Your method declaration states a return type of IQueryable of Post types

public IQueryable<Post> GetPosts() { //...

But your linq projects to an anonymous type and returns an IQueryable of anonymous types

select new { //anonymous type
  title = post.title,
  name = categories.name,
  author = categories.author
};  // = IQueryable<SomeCompilerGeneratedTypeNameHere>

You need to define a new type

public class PostDisplay {
  public string Title { get; set; }
  public string Name { get; set; }
  public string Author { get; set; }
}

then in your code return an IQueryable of that type

public IQueryable<PostDisplay> GetPosts() {
  return from post in db.Posts
         join categories in FindCategories()
         on categories.id equals post.category_id
         select new PostDisplay {
                    Title = post.title,
                    Name = categories.name,
                    Author = categories.author
                  };
}