MVC LINQ to SQL JOIN for custom type fails for a strongly typed view

Question

I have a simple data model of two tables, email and recipients, email can be sent to one or more recipients

I have setup the database with the two tables, created the Linq to SQL repository, built the controllers and the strongly typed view.

This works fine when I want to select all records from the database

public IList<AllMailDetail> ListAll()
    {
        var allMail =
            from m in _datacontext.mail_receiveds
      join r in _datacontext.mail_recipients on m.DeliveryId equals r.DeliveryId
            select new AllMailDetail {
                                        DeliveryId = m.DeliveryId,
                                        MessageId = m.MessageId,
                                        SentFrom = m.SentFrom,
                                        FilePath = m.FilePath,
                                        FileName = m.FileName,
                                        SentDateTime = m.SentDateTime,
                                        ReceivedDateTime = m.ReceivedDateTime,
                                        Subject = m.Subject,
                                        SpamScore = m.SpamScore,
                                        IsSpam = m.IsSpam,
                                        SenderIP = m.SenderIP,
                                        Header = m.Header,
                                        SentTo = r.SentTo
                                      };

        return allMail.ToList <AllMailDetail>();
    }

The custom type class

public class AllMailDetail
{
    public int DeliveryId { get; set; }
    public int? MessageId { get; set; }
    public string SentFrom { get; set; }
    public string FilePath { get; set; }
    public string FileName { get; set; }
    public string SentDateTime { get; set; }
    public DateTime ReceivedDateTime { get; set; }
    public string Subject { get; set; }
    public byte? SpamScore { get; set; }
    public bool? IsSpam { get; set; }
    public string SenderIP { get; set; }
    public string Header { get; set; }
    public string SentTo { get; set; }
}

The controller simply sends the contents from the repository to the strongly typed view

public ActionResult Index()
    {
        return View(_repository.ListAll());
    }

To get just one mail record from the database I have the following code that accepts a deliveryId

public IQueryable<AllMailDetail> GetMail(int? id)
    {
        var allMail =
            from m in _datacontext.mail_receiveds
            join r in _datacontext.mail_recipients 
            on m.DeliveryId equals r.DeliveryId
            where m.DeliveryId == id
            select new AllMailDetail
            {
                DeliveryId = m.DeliveryId,
                MessageId = m.MessageId,
                SentFrom = m.SentFrom,
                FilePath = m.FilePath,
                FileName = m.FileName,
                SentDateTime = m.SentDateTime,
                ReceivedDateTime = m.ReceivedDateTime,
                Subject = m.Subject,
                SpamScore = m.SpamScore,
                IsSpam = m.IsSpam,
                SenderIP = m.SenderIP,
                Header = m.Header,
                SentTo = r.SentTo
            };

        return allMail;
    }

And its controller code

public ActionResult Details(int? id)
    {       
        var mail = _repository.GetMail(id);

        if (mail == null)
            return View("NotFound");

        return View(mail);
    }

I had been trying to display the output for a single record by also using a strongly typed view having Inherits="System.Web.Mvc.ViewPage At the top of the aspx page but I got the following error

The model item passed into the dictionary is of type 'System.Data.Linq.DataQuery`1[projectMail.Models.AllMailDetail]' but this dictionary requires a model item of type projectMail.Models.AllMailDetail'.

I fixed this error after much searching and found this post most helpful http://stackoverflow.com/questions/278941/mvc-linq-to-sql-table-join-record-display

so my view is no longer strongly typed and I build the page as follows

<% foreach (projectMail.Models.AllMailDetail item in (IEnumerable)ViewData.Model)
    { %>

       ...items...

 <%  } %>

This works fine, but it seems the long way round. The thing I can’t figure out is

1. Why does the second query need to be IQueryable
2. Why didn’t it work when the view was strongly typed
3. How can it be made to work with a strongly typed view
4. Is this the best way of dealing with joins in MVC using LINQ to SQL

Answer 1

Hmmmm, Try in the controller

return View(_repository.GetMail( id).SingleOrDefault());

You're trying to bind an IQueryable datasource to an AllMailDetail View, the above should fix you.