Custom queries in MVC 4 / Entity Framework?

Question

I currently have a working CRUD application built using ASP.net MVC 4 and the Entity Framework. I use an EDMX model for the tables, which are deployed in a database on a live server.

I want to know how to query the tables in my controllers to return a view which contains columns from each table (join), as one table, based on a column header returned from a query string

These are my tables, I have getters and setters for them in their respective models and the model is "Model1.edmx"

AccCompany                             AccControl
    ID                                     ID
    Code                                   ControlCode
    CompanyID                              Nominal
    AccountsCompany                        CostCentre
    Company                                Department

I just don't understand how to join the tables using custom methods, as the MVC framework. EF seemed to do everything itself - in terms of the actual query...

Which is the linking column between your two tables?

PGallagher
– PGallagher

2013-02-07 11:00:48 +00:00
Commented Feb 7, 2013 at 11:00 — PGallagher
– PGallagher, Commented Feb 7, 2013 at 11:00

Vasil Trifonov · Accepted Answer · 2013-02-07 11:40:53Z

2

If I understand your question correctly, that's what you need

var views = from company in context.AccCompany
            join control in context.AccControl 
              on company.Code equals control.ControlCode
            where company.Code == Request.QueryString["Code"]
            select new JoinedView 
            { 
                CompanyId: company.CompanyID, 
                Code: company.Code,
                ControlId: controlId, 
                Nominal: control.Nominal
                // any other columns you need
            }

where JoinedView is a class containing the columns from both entities.

answered Feb 7, 2013 at 11:40

Vasil Trifonov

1,86717 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2046422 Over a year ago

Would I then just return View(views); ?

Vasil Trifonov Over a year ago

Sure. If this is what you need.

joumasehare · Accepted Answer · 2013-02-07 11:56:00Z

1

There are two approaches that I like.

The first is straight forward, using navigation methods from entity framework:

Controller:

public ActionResult Details(short id = 0)
{
    AccCompany accComp = db.AccCompany.Find(id);
    if (accComp == null)
    {
        return HttpNotFound();
    }
    return View(accComp);
}

View:

@model Some.Entities.AccCompany

<div class="displayLabel">
     @Html.DisplayNameFor(model => model.Company)
</div>
<div class="displayField">
    @Html.DisplayFor(model => model.Company)
</div>

<div class="displayLabel">
     @Html.DisplayNameFor(model => model.AccControl.CostCentre)
</div>
<div class="displayField">
    @Html.DisplayFor(model => model.AccControl.CostCentre)
</div>

The second one involves creating a custom "View Model" for the specific view and using that as your model in the view, better for validations imo:

SomeViewModel.cs:

public class SomeViewModel
{
    [Required]
    public string Company { get; set; }

    [Required]
    [Display(Name = "Cost Centre")]
    public string CostCentre { get; set; }
}

Then populate that in your controller:

public ActionResult Details(short id = 0)
{
    AccCompany accComp = db.AccCompany.Find(id);

if (accComp == null)
    {
        return HttpNotFound();
    }

SomeViewModel vm = new SomeViewModel();

vm.Company = accComp.Comany;
vm.CostCentre = accComp.AccControl.CostCentre;

    return View(vm);
}

Then the view:

@model Some.SomeViewModel

<div class="displayLabel">
     @Html.DisplayNameFor(model => model.Company)
</div>
<div class="displayField">
    @Html.DisplayFor(model => model.Company)
</div>

<div class="displayLabel">
     @Html.DisplayNameFor(model => model.CostCentre)
</div>
<div class="displayField">
    @Html.DisplayFor(model => model.CostCentre)
</div>

Hope this helps

answered Feb 7, 2013 at 11:56

joumasehare

362 bronze badges

3 Comments

user2046422 Over a year ago

I'm getting errors with the second approach vm.Company = accComp.Company Cannot implicitly convert type int to string vm.CostCentre = accComp.AccControl.CostCentre; Does not contain definition for AccControl

joumasehare Over a year ago

1) Make sure your view model has the same data types as the classes generated for you by EF. Or just ToString 2) Seems like there is no link between AccCompany, and AccControl. Ensure that the foreign keys is setup properly in SQL server AND your edmx. As a work around, you could do something like: vm.CostCentre = db.AccControls.Where(c=>c.ControlCode == accComp.Code).FirstOrDefault().CostCentre;

joumasehare Over a year ago

No problem. I recommend reading up on entity framework and relationships. It will definitely help you in the long run :D

İbrahim Özbölük · Accepted Answer · 2013-04-16 09:30:21Z

0

if you know Linq you don't need to write more code for custom queries.

public ApiEntities Content = new ApiEntities();
public IQueryable<T> GetCustomQuery<T>(System.Linq.Expressions.Expression<Func<T, bool>> where) where T : EntityObject
{
   return Content.CreateObjectSet<T>().Where(where);
}

using

var R = new Repo();
//Single query
R.GetCustomQuery<AccCompany>(x => x.Code == Request.QueryString["Code"]).FirstOrDefault();
//list query
R.GetCustomQuery<AccCompany>(x => x.Code == Request.QueryString["Code"]).ToList();

edited Apr 16, 2013 at 9:30

answered Apr 16, 2013 at 9:02

İbrahim Özbölük

3,22225 silver badges20 bronze badges

Collectives™ on Stack Overflow

Custom queries in MVC 4 / Entity Framework?

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related