Select multiple tables using Linq to SQL

Question

Hi,

I'm trying to bind columns from two different tables in to gridview using Linq To Sql

Here's the bind:

var q = (from o in mail.tblmails
join c in mail.tblstaffs on o.staffId equals c.id
select new { o, c });
return View(q);

and here is where I'm calling the bind in my View.

.Columns(columns => {

       columns.Bound(o => o.dateAdded).Format("{0:MM/dd/yyyy}").Width(80);
       columns.Bound(o => o.companyId);
       //columns.Bound(c => c.staffId);
       columns.Bound(o => o.subject);
       columns.Bound(o => o.dateArchived);

   })

I'm getting an error

Exception Details: System.InvalidOperationException: The model item passed into the dictionary is of type 'System.Data.Linq.DataQuery1[<>f__AnonymousType06[System.Nullable1[System.DateTime],System.Nullable1[System.Int32],System.String,System.Nullable1[System.DateTime],System.String,System.Int32]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable1[ffs.Models.tblmail]'.

I have a feeling that the issue may have something to do with the line

< % Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage>"%>

but I'm not sure what to do to fix it.

I'm using the telerik grid extension.

Any help would be great, thanks.

Answer 1

What you are doing now (and doing wrong also) is sending the query data directly to the view. This is considered bad practice. Although possible to acces the resulting anonymous type, this method will cause to do your data acces during the view instead of in the controller.

What I recommend is you make a model (class) to represent the data and return a list of those entities (your model) to the view using:

<% Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" 
Inherits="System.Web.Mvc.ViewPage<List<yourCreatedModelClass>>"%>

Watch the part where you tell the view you are handing it a list of your objects rather than just a clean view.

Some related problem and solution is discussed here

Answer 2

I think the problem (looking at your error message) lies in the fact that you are trying to pass an anonymous type around while the view is expecting an IEnumerable<tblmail>.

What you want to do is create a data structure like:

public class Model
{
   public tblmails Mails { get; set; }
   public tblstaff Staff { get; set; }
}

Then in your linq query you would put o into Model.Mails, and then put c into Model.Staff. You then pass your IEnumerable<Model> result into your view, and hook your view up so it is expecting IEnumerable<Model> as the model.