Data access using Entity Framework in ASP.NET MVC

Question

I have a table with these columns(type) as described below.

TABLE

------------------------------------------------------------------
Dir(str) | Twnshp(int) | Rng(int) | Section(int) | Xcell(int) | Ycell(int)
------------------------------------------------------------------

I am trying to do this query using EF.

SELECT Xcell,Ycell  FROM [CIR].[dbo].[TRS2Cell] where Twnshp = 1 and Rng = 4 and Section =31

After some study, I created a DAL Context and class as below.

PlotXYContext.cs

public class PlotXYContext :DbContext
{
    public DbSet<PlotXY> XYCells { get; set; }
}

PlotXY.cs

[Table("TRS2Cell")]
public class PlotXY
{

    public string Dir { get; set; }
    [Key]
    public int Twnshp { get; set; }
    public int Rng { get; set; }
    public int Section { get; set; }
    public int Xcell { get; set; }
    public int Ycell { get; set; }
}

Here is the code in my controller where I pass the three parameters.

PlotXYContext plotXYContext = new PlotXYContext();
        var query = from TRS2Cell in plotXYContext.XYCells
                    where TRS2Cell.Twnshp == 1
                    && TRS2Cell.Rng == 4
                    && TRS2Cell.Section == 31
                    select TRS2Cell.Xcell;

I need help with EF as I am new to it and also is this the right query? If so how do I retrieve the Xcell and Ycell values from the query. Also the table has no unique column, no nulls, nothing needs to be updated here. All I want is to do a select.

Answer 1

I found that I wasn't using the similar datatype as in my table to declare the class for it.Thant is the only issue I came across that resolved it and hence the error.

Thank you for all the replies.

Answer 2

Normally your not gonna want to do any data access code in your controller. You want to keep those separated. Also when I first started using EF i got hung up on DB Context as well when I started with MVC. If you added your Ado.Net Entity Data Model correctly the db context should be automatically created for you. If you look at "YourEntity".cs file under "Entity".edmx => "Entity".Context.tt it will look something like

public partial class VuittonEntities : DbContext
{
    public VuittonEntities()
        : base("name=VuittonEntities")
    {
    }

To help you out with EF I'm gonna post all my code for a query.

So your Model class in the models folder will look like.

public class RoleGridViewModel
{
    public int UserID       { get; set; }
    public string UserFirst    { get; set; }
    public string UserLast     { get; set; }
    public string UserRole     { get; set; }
    public string UserRoleDesc { get; set; }
}

This is your Data Access layer function: Here I'm creating a list of my model class because I'm gonna populate it in a gridview later on.

 public List<RoleGridViewModel> GridRoles()
    {
        using (VuittonEntities db = new VuittonEntities())
        {
            return (from users in db.User
                    join roles in db.UserRole on users.RoleID equals roles.RoleID
                    select new RoleGridViewModel
                    {
                        UserID = users.UserID,
                        UserFirst = users.FirstName,
                        UserLast = users.LastName,
                        UserRole = roles.Role,
                        UserRoleDesc = roles.Role_Desc
                    }).ToList();

        }
    }

Here in your Controller you can call it like this. Normally you would call a businezz layer from your controller I'm going straight to the Data layer to show you how its done. Here var roles holds your query. I'm using Json result here but this can also be done in an action result

public JsonResult RolesGrid() {

        var roles = new UserDAL().GridRoles();

        return Json(roles, JsonRequestBehavior.AllowGet);
    }

If you just want to select a single item you have to use .First() at the end of the query like this...

  public string currentRole(UserViewModel uvm)
    {            
        using (VuittonEntities db = new VuittonEntities())
        {
            return (from us in db.User
                    join usRole in db.UserRole on us.RoleID equals usRole.RoleID
                    where (us.RoleID == uvm.RoleID) && (us.UserID == uvm.UserID)
                    select usRole.Role).First();
        }
    }