KARIM's PLAYGROUND

Ever had a LINQ-to-SQL query that returns an anonymous type and you needed to pass the result of your query to the view? probably yes :)

Today I was faced with a problem that required me to query several table, extract the results from them and finally create a list of objects with nested values and all this should be passed to the view so that there I could iterate it and show a sensible results.

Let's first examine the tables I needed to query, here you have the schema containing the significant fields I'll need:

These are the significant fields I required to get a list of Tutors along with their ratings and their subjects. The SQL query I wrote for it was:

   1:  SELECT T.Id,

   2:         T.FirstName, 

   3:         T.LastName, 

   4:         T2.Rating, 

   5:         TutorSubjects.Id, 

   6:        (SELECT [Subject].Name

   7:         FROM [Subject]

   8:         WHERE [Subject].Id = TutorSubjects.SubjectId) SubjectName,

   9:         T.AboutMe

  10:  FROM [User] T

  11:       LEFT OUTER JOIN (SELECT Tutor.Id, SUM(Comment.Rating) Rating

  12:                        FROM [User] Tutor, Comment

  13:                        WHERE Comment.TutorId = Tutor.Id AND Tutor.Type = 2

  14:                        GROUP BY Tutor.Id) T2

  15:             ON T.Id = T2.Id

  16:       LEFT OUTER JOIN TutorSubjects

  17:             ON TutorSubjects.TutorId = T.Id

  18:  WHERE T.Type = 2

  19:  ORDER BY T2.Rating DESC

Now, the application is based on ASP.NET MVC, and it uses LINQ-to-SQL orm. First of all, we should somehow execute this query using LINQ, we could do it in several ways. Either write a ninja-query joining multiple entities or as I did, create a view in the database that executes this query and import the view to linq2sql, so that we could use it as an entity. Our new view (and the new object ) is called TutorRatingWithSubjects.

In the code, when I query it, I want to have a structure that looks somehow like this:

So that when I'll pass the results to the view I could display them without any extra effort. I could accomplish it with the C# feature - anonymous types, but the problem in here is with passing it to the view.

   1:  var tutors = from tutor in db.TutorRatingWithSubjects

   2:               select new

   3:                          {

   4:                              tutor.Id,

   5:                              tutor.FirstName,

   6:                              tutor.LastName,

   7:                              tutor.Rating,

   8:                              tutor.AboutMe,

   9:                              Subjects = (from ts in db.TutorRatingWithSubjects

  10:                                          where ts.Id == tutor.Id

  11:                                          select new

  12:                                                     {

  13:                                                         ts.SubjectId,

  14:                                                         ts.SubjectName

  15:                                                     }

  16:                                         )

  17:                          };

When I'm passing it, the whole anonymous type get's up-casted to an object when it's stored in the ViewData. Here, the problem is that you are unable (directly) to reproduce the original anonymous type from the ViewData.

There are several ways to read data from a boxed anonymous type:

Using reflection

In the view you cast it to IQueryable and iterate through it:

    <ul>
    <%
        IQueryable tutors = (IQueryable) ViewData["Tutors"];
        foreach(object v in tutors) {
      %>
       <li> <%= v.GetType().InvokeMember("FirstName", System.Reflection.BindingFlags.GetProperty, null, v, null) %></li>
  <% } %>
    </ul>

Now, while this method works, I highly discourage it's usage, as using reflection is just cheating. It has weak performance, the code is less readable, etc...

There is a more elegant way to read the results of the linq query in our view.

Using strongly-typed list

We create a nested class that has the same properties as our anonymous type:

internal class TutorsWithSubjectsResult
{
    public Guid TutorId { get; set; }
    public String FirstName { get; set; }
    public String LastName { get; set; }
    public int? Rating { get; set; }
    public string AboutMe { get; set; }

    internal class Subject
    {
        public Guid? SubjectId { get; set; }
        public String SubjectName { get; set; }
    }

    public List<Subject> Subjects { get; set; }
}

Now we need to select the data with our linq into the TutorsWithSubjectsResult:

List<TutorsWithSubjectsResult> tutors = (from tutor in db.TutorRatingWithSubjects
                                         select new TutorsWithSubjectsResult
                                                    {
                                                        TutorId = tutor.Id,
                                                        FirstName = tutor.FirstName,
                                                        LastName = tutor.LastName,
                                                        Rating = tutor.Rating,
                                                        AboutMe = tutor.AboutMe,
                                                        Subjects = (from ts in db.TutorRatingWithSubjects
                                                                    where ts.Id == tutor.Id
                                                                    select
                                                                        new TutorsWithSubjectsResult.Subject
                                                                            {
                                                                                SubjectId = ts.SubjectId,
                                                                                SubjectName = ts.SubjectName
                                                                            }
                                                                   ).ToList()
                                                    }).ToList();

Voila! we have a strongly-typed list, that could be easily unboxed in the view, or if you create a strongly typed-view you would have a type that could be the type of the ViewData.