EntityDataSource create a where clause

Question

With this classes:

class User
{
int UserId{get;set;}
string UserName{get;set;}
}

class Role
{
int RoleId{get;set;}
string RoleName{get;set;}
}

class User_Role
{
int UserId{get;set;}
int RoleId{get;set;}
}

I need to show in one ListBox the Roles that are Availables and in other ListBox the Roles that the User already have and can not be repeated. I did this in the code behind:

//Initialize the ObjectContext
MyDbContext ctx = new MyDbContext();
int userId; //some value pass by url;
var listRolesIds = (ctx.Roles.Select(r => r.RoleId )).ToList();
var listRolesIdsAsigned = ctx.User_Role.Where(u => u.UserId == userId).Select(u => new {u.UserId}).ToList();

var listRolesIdsAvailables = listRoles.Except(listRolesAsigned);

//once i have the ids, code for Bind the ListBox with the availables roles

It works but its dificult to maintain and I like to make it with an EntityDataSource but I dont know how to do it. Please if anyone can help me I would be grateful, thanks.

Are you working with code first ? – Raphaël Althaus Jun 01 '12 at 13:00 — Raphaël Althaus, Jun 01 '12 at 13:00

score 0 · Accepted Answer · answered Jun 01 '12 at 16:42

With EntityDataSource you can use .Where property to specify filter. Note that that the filter is in ESql (More details on ESql: http://msdn.microsoft.com/en-us/library/bb399560). EntityDataSource works natively with ObjectContext and not with DbContext. You can get an ObjectContext instance for your DbContext by using:

var objectContext = ((IObjectContextAdapter)myDbContext).ObjectContext;

To supply your own object context to the EntityDataSource control you need to handle OnContextCreating event and assign the context you took from your DbContext instance to EntityDataSourceContextCreatingEventArgs.Context property. Here are the details:

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.entitydatasource.contextcreating

Here is an example of how to use .Where in EntityDataSourceControl: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.entitydatasource.where.aspx and another one about filtering in general: http://msdn.microsoft.com/en-us/library/cc488531

Hi Pawel, thanks for awnser, I finally got the query I need its this: var query = (from role in ctx.Role join userRole in ctx.User_Role on role.RoleId equals userRole.RoleId where userRole.UserId == userIdPassByUrl select new { role.RoleId, role.RoleName }).Distinct(); How can I set this query to my EntityDataSource, Im experience problem with the Distinct clause. Thanks again. — ramo2712, Jun 04 '12 at 08:36
For distinct you need to use Select property where you should be able to put DISTINCT. Here is how you use the EntityDataSourceControl.Select property http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.entitydatasource.select.aspx and here is documentation for ESql SELECT that discusses DISTINCT http://msdn.microsoft.com/en-us/library/bb399554 — Pawel, Jun 04 '12 at 17:56

EntityDataSource create a where clause

1 Answers1