Returning a column from a linked table in LINQ to SQL

Question

My problem is that I am trying to return a simple query that contains an object Story. The Story object has a UserId in the table which links to aspnet_users' UserId column. I have created a partial class for Story that adds the UserName property since it does not exist in the table itself.

The following query gets all stories; however, a pagination helper takes the query and returns only what's necessary once this is passed back to the controller.

     public IQueryable<Story> FindAllStories(){
        var stories = (from s in db.Stories
                       orderby s.DateEntered descending
                       select new Story
                       {     
                          Title = s.Title,
                          StoryContent = s.StoryContent,
                          DateEntered =  s.DateEntered,
                          DateUpdated =  s.DateUpdated,
                          UserName = s.aspnet_User.UserName
                        }
                        );

        return stories;
    }

When the helper does a .count() on the source it bombs with the following exception:

"Explicit construction of entity type 'MyWebsite.Models.Story' in query is not allowed."

Any ideas? It's not a problem with the helper because I had this working when I simply had the UserName inside the Story table. And on a side note - any book recommendations for getting up to speed on LINQ to SQL? It's really kicking my butt. Thanks.

Answer 1

The problem is precisely what it tells you: you're not allowed to use new Story as the result of your query. Use an anonymous type instead (by omitting Story after new). If you still want Story, you can remap it later in LINQ to Objects:

    var stories =  from s in db.Stories
                   orderby s.DateEntered descending
                   select new
                   {     
                      Title = s.Title,
                      StoryContent = s.StoryContent,
                      DateEntered =  s.DateEntered,
                      DateUpdated =  s.DateUpdated,
                      UserName = s.aspnet_User.UserName
                    };

     stories = from s in stories.AsEnumerable() // L2O
               select new Story
               {
                   Title = s.Title,
                   StoryContent = s.StoryContent,
                   ...
               };

Answer 2

If you really need to return an IQueryable from your method and still need the Username of the user you can use DataContext.LoadOptions to eagerload your aspnet_user objects.

See this example.