How to query existing table when using code first in entity framework

Question

I am using Entity Framework 6 Code First. I created a separate table to contain the UserId from the aspnet_Users table and a field for Department. So the user listed with the departments have only access to those departments.

Since all of my table are generated via code first and is using the db.context. The membership and roles are pre-generated table from MVC. How to I get a list of UserId from the aspnet_Users table when it's not in the db.context? The aspnet tables are pre-generated via a script in the entity framework.

How do I query tables in MVC outside of my db.context?

user47589 · Accepted Answer · 2015-05-29 19:42:28Z

1

Your DbContext will have a Database property. Off of that you will find two overloads for general SQL queries:

DbRawSqlQuery    SqlQuery   (Type elementType, string sql, params object[] parameters);
DbRawSqlQuery<T> SqlQuery<T>(string sql, params object[] parameters);

For example:

var result = ctx.Database.SqlQuery<Foo>("select * from foo");

More information here

answered May 29, 2015 at 19:42

user47589

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

2 Comments

wirble Over a year ago

var users = context.Database.SqlQuery<SelectListItem>("Select UserId, UserName from aspnet_users"); What exactly do I put in the "Foo" part? if I want to assign this to the ViewBag for a @Html.DropDownList. The above line gives me null....

wirble Over a year ago

Thanks @Amy for directly answering the question. Gave me the clue I needed for a solution. Added my complete solution for other beginner's like myself.

Brandon O'Dell · Accepted Answer · 2015-05-29 19:53:32Z

1

Since you are using .Net Membership, you could always call the

MembershipProvider.GetAllUsers(int pageIndex, int pageSize, out int totalRecords)

Then from that result, generate a list of UserIds. The role provider also offers similar functionality with a Roles.GetAllRoles() method.

The membership provider and role provider offer many more useful methods to hopefully get you the data you are looking for. If they still don't have what you are after you have a couple of more options. You can use your db context to execute raw SQL. See MSDN for more info.

Another option is to create additional entity classes that match the DB structure of those tables, and add them to your DB Context. The downside to this approach is it could allow another developer, or even yourself to create and update users and roles without going through the proper providers, in which case you would lose some functionality. If that is a concern, you could always create a DB View and map to that to ensure read only access. It's a bit more overhead, but does give you type safety and a familiar way to query the data.

answered May 29, 2015 at 19:53

Brandon O'Dell

1,1711 gold badge15 silver badges23 bronze badges

1 Comment

wirble Over a year ago

using the membership GetAllUsers returns 0. I think because I am using the SqlRoleManager in a windows authentication environment. Not sure, but going with @amy method

wirble · Accepted Answer · 2015-06-02 14:23:10Z

Here is what I did to get the complete solution I wanted:

To query the data:

 string sql = "Select UserId, UserName from aspnet_users where applicationid='" + Config.Instance.AppId() + "'";

 using (var context = new NameSystemContext())
 {
      var users = context.Database.SqlQuery<AspnetUser>(sql);
      ViewBag.UserId = new SelectList(users, "UserId", "UserName").ToList();
 }

In your view models or somewhere define a class:

class AspnetUser
{


    [Key]
    //[Column(Order = 1)]
    public Guid UserId { get; set; }

    //[Key]
    //[Column(Order = 2)]
    public string UserName { get; set; }


}

//ViewBag.UserId could be use in the view

@Html.DropDownList("UserId", null, "Select...", htmlAttributes: new { @class = "form-control" })

Collectives™ on Stack Overflow

How to query existing table when using code first in entity framework

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related