Using ViewData with join query

Question

Update

I now have it working to an extent that the view is populated but my query is wrong so no data is retrieved.

My ViewModel

public class ViewProductions
{

    public string Venuename { get; set; }
    public string Showname { get; set; }
    public DateTime ProductionYear { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }


}  

Query

var query =  
                     from f in _db.Production
                     join g in _db.Run on f.show equals g.venue
                     select new ViewProductions {
                            Venuename = g.venue,
                            Showname = f.show,

                            StartDate = g.startDate,
                            EndDate = g.endDate


                     };

        return View(query);

I have the query in SQL format

    SELECT Production.show, Run.venue, Run.startDate, Run.endDate,  Production.director, Production.designer
FROM  Production INNER JOIN
 Run ON Production.show = Run.show

Could anyone help me convert that to linq?

Thanks again

Answer 1

That is a good start. Remembering the idea is that the ViewModel will contain all the data needed by the view. Make it as big or as little as it needs to be.

if you will be displaying a list of Show, you might need to change it to something like:

 public List<Show> {get; private set;}

if in a given object you only use one property. Don't worry about creating a ViewData property instead of using the entire object. So an example:

public class Venue
{
    public string Name;
    public string State;
    public string City;
    public int Capacity;
    ...
}

Let's say for this given ViewModel you only need the Venue's name. No need to add the entire Venue to it, simply add a VenuName property.

A blog with a nice explanation.

EDIT

Here are some nice examples using System.Data.Linq.SqlClient.SqlMethods specifically, DATEDIFF. you could do something like this:

 where SqlMethods.DateDiffMonth(Book.EntryDate, DateTime.Now) <= 3 order by dates.startDate

EDIT 2

Try something like this:

Couple of pointers, notice the ViewModelProduction(), you have to put the () to indicate you are creating new objects. the ToList() is to convert the IEnumerable that LINQ returns. Also in this case, I'm using the orderby on the startDate. Check out some LINQ examples. And this very useful tool: LinqPad.

var query = (from p in db.Production
            join r in _db.Run on p.show equals r.show
            orderby r.startDate
            select new ViewModelProduction()
            {
                show = p.show,
                venue = r.venue,
                startDate = r.startDate,
                endDate = r.endDate,
                director = p.director,
                designer = p.director
            }).ToList();