MVC3 linq joins

问题:

As a novice am trying my hands on MVC3,razor, EF I have Three connected Tables that I want to produce a view from it. In a simpleton's brief the following are about the tables

PJUsers - ID, memUID(this unique Id from membership),FirstName,LastName

PJAwards - user nominates another user for an award, this links with awardtypesID as foreign key ( awardId,bool:awardok)

PJAwartypes - (awardtypeID, awardName)

The query in the controller is like this

    var lists =
from tl in db.PJawards
join u in db.PJUsers on tl.nomineeId equals u.ID into tl_u
join i in db.PJUsers on tl.nominatorId equals i.MemUID into tl_i
where tl.awardOk 
orderby tl.awardDated ascending

from u in tl_u.DefaultIfEmpty()
from i in tl_i.DefaultIfEmpty()
select new
{
   Status = tl.awardOk,
    nomineeFname = u.FirstName,
    nomineeLname = u.LastName,
    award =  tl.PJawards.awardName,
    Dated = tl.awardDated,
    nominatorFname = i.FirstName,
    nominatorLname = i.LastName,
    nomineeCountry = u.Citizen,
    nomineeResidence = u.Residence,
    awardtypeId = tl.ID
};

somewhere i read that i have to construct a model class similar to the query in the controller

{
public class AwardUserInfo
{
    public AwardUserInfo() { }
    public bool Status { get; set; }
    public string nomineeFname { get; set; }
    public string nomineeLname { get; set; }
    public string award { get; set; }
    public string Dated { get; set; }
    public string nominatorFname { get; set; }
    public string nominatorLname { get; set; }
    public string nomineeCountry { get; set; }
    public string nomineeResidence { get; set; }
    public int awardtypeId { get; set; }

}
}

Please I learn by examples so to be able to help me assume I don't know anything

回答1:

somewhere i read that i have to construct a model class similar to the query in the controller

Try this.

I guess your ef-model is similar to

So You can create a ViewModel class

public class PJAwardsViewModel
{
    public int Id { get; set; }
    public string NominatorFName { get; set; }
    public string NomineeFname { get; set; }
    public string AwardName { get; set; }
    public bool IsAwarded { get; set; }
} 

It will be also good if You add some service class

public class PJAwardsService
{
    public static List<PJAwards> GetAll()
    {
        using (var context = new YourDBEntities())
        {
            return context.PJAwards
                .Include(x => x.PJUsers)
                .Include(x => x.PJUsers1)
                .Include(x => x.PJAwartypes).ToList();
        }
    }
}

(Don't forget to write using System.Data.Entity; )

Then You can add a ViewModelHelper class

public class PJAwardsViewModelHelper
{
    public static PJAwardsViewModel PopulatePJAwardsViewModel(PJAwards pjaward)
    {
        return new PJAwardsViewModel
        {
            Id = pjaward.Id,
            NominatorFName = pjaward.PJUsers.FirstName,
            NomineeFname = pjaward.PJUsers1.FirstName,
            AwardName = pjaward.PJAwartypes.AwardName,
            IsAwarded = pjaward.IsAwarded
        };
    }

    public static List<PJAwardsViewModel> PopulatePJAwardsViewModelList(List<PJAwards> pjawardsList)
    {
        return pjawardsList.Select(x => PopulatePJAwardsViewModel(x)).ToList();
    }
}

At the end Your controller index method will look like this

public ActionResult Index()
{
    var pjawards = PJAwardsViewModelHelper.PopulatePJAwardsViewModelList(PJAwardsService.GetAll().ToList());
    return View(pjawards);
}  

The only thing You should do is add a view (build the project before). Choose PJAwardsViewModel as a Model class and List as a scaffold template. Enjoy it.

回答2:

Here is a step by step guide by Steven Sanderson on how to use Asp.net MVC3, EF Code First with MVCScaffolding (powershell automation).

http://blog.stevensanderson.com/2011/01/13/scaffold-your-aspnet-mvc-3-project-with-the-mvcscaffolding-package/

It is a multipart blog post takes you through the exciting journey of MVC3.

All the best.