CODEWITHSUNDEEP
c#asp.netsqlasp.net-mvc-4
user2046422
user2046422

Reputation:

Custom queries in MVC 4 / Entity Framework?

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...

Upvotes: 1

Views: 5877

Answers (3)

İbrahim Özbölük
İbrahim Özbölük

Reputation: 3202

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();

Upvotes: 0

joumasehare
joumasehare

Reputation: 36

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

Upvotes: 1

Vasil Trifonov
Vasil Trifonov

Reputation: 1867

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.

Upvotes: 2

Related Questions