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.

I don't know if I understand you correctly. In my opinion it is right working query which returns instances of your ORM class where Twnshp is 1, Rng is 4 and Section is 31. — pt12lol
– pt12lol, Commented Sep 20, 2013 at 19:14
It throw me this error when I Debug and look into the "query" 'The specified cast from a materialized 'System.String' type to the 'System.Int32' type is not valid.' — sss111
– sss111, Commented Sep 20, 2013 at 19:28
Let me rephrase I tried to debug and check if the query returned anything and as a result found this exception on "var query". — sss111
– sss111, Commented Sep 20, 2013 at 19:57

CSharper · Accepted Answer · 2013-09-20 19:58:33Z

1

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

edited Sep 20, 2013 at 19:58

answered Sep 20, 2013 at 19:48

CSharper

5,6206 gold badges32 silver badges54 bronze badges

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

3 Comments

CSharper Over a year ago

put ( ) around each condition of your where clause I've ran into problems not doing it

sss111 Over a year ago

The tutorial that I went through has not shown me anything regarding ADO.net Entity Data Model nor it had anything to do with .edmx. Is it mandatory to do that?

CSharper Over a year ago

This link should help you out, the second one will help you with some queries learnentityframework.com/LearnEntityFramework/tutorials/… code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

sss111 · Accepted Answer · 2013-09-23 20:37:44Z

0

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.

answered Sep 23, 2013 at 20:37

sss111

3491 gold badge7 silver badges28 bronze badges

Collectives™ on Stack Overflow

Data access using Entity Framework in ASP.NET MVC

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related