To all: Thank you for your help:

In the standard DNN database there are the following tables: Roles, UserRoles, Users.

I have a DNN module where I would like to populate a ListBox control with all users that have a selected Role assigned to them. The role will be selected via an asp dropdownlist populated by the Roles table. I have the following code in the "on_selecting" event of the linq datasource:

protected void ldsUsers_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{

var uRoleQuery = from uRole in db.UserRoles
where uRole.RoleID == Convert.ToInt32(ddlRoles.SelectedValue)
select new
{
uRole.UserID
};

var userQuery = from u in db.Users
where uRoleQuery.Contains(u.UserID)
select u;



e.Result = userQuery;

}

I am getting intellisense error on the uRoleQuery.Contains(u.UserID) line that says:

" The type arguments for method 'System.Linq.Queryable.Contains (System.Linq.IQueryable, TSource)' cannot be inferred from the usage. Try specifying the type arguments explicitly. "



Why? I'm not quite sure how to use the UserRoles table to cross reference to the userID in a subquery within LINQ.

Thank you for your help.

Elgin

DON'T! STOP!!

When you are working with DotNetNuke, you should use SQL only as a last resort. DotNetNuke has an extensive collection of classes and methods with which to interact with the framework and data. You should make it a point to get to know them and use them.

You can download a compiled help file from DotNetNuke.com that includes the entire framework. Look for the "Core API Help File" on the Codeplex page where you download your version of DotNetNuke.

From a post on the DotNetNuke forums:

Dim roleName As String = "Moderators"
Dim roleCtlr As New Security.Roles.RoleController
Dim objUserRoles As ArrayList = roleCtlr.GetUserRolesByRoleName(PortalId, roleName)

The object returned by the GetUserRolesByRoleName method is an arraylist of DotNetNuke.Entities.Users.UserRoleInfo objects which include UserId, Fullname, etc. as their members

The good news there is that you can then get UserInfo for each user. That includes everything available about the users, including profile properties. Doing that in SQL has some complications. Finally, if you use the provided methods, you'll insulate yourself against changes that do occur from time to time in the database structure.

Joseph,


Thank you so much for keeping me from making a huge mistake. I will take your advice and study the classes thoroughly before proceeding with my project.

I had no idea.

Thanks again.

Elgin

Joseph,

I've started my studying of the DNN API and I can see where this is going to be very beneficial. Thank you again. I'm very excited about this discovery.

I have a couple of questions about my current topic. In your example, the method used is "GetUserRolesByRoleName" which "Gets the user in a role (as UserRole objects). Why not use the "GetUsersByRoleName" method, which "Gets the users in a role (as User objects) instead?

I see that both objects have the members I need to populate my Listbox - FullName or DisplayName and UserID. So what's the difference? Is one object more suitable than the other?

Also, at the top of your reply you said "... and extensive collection of classes and methods with which to interact with the framework and data." Would this include classes that would interact with - a)User tables I've added to the DNN database, and - b)a completely separate database that has several RELATED tables which is the data used in my application?

For this application, I had an experienced C# developer get me started with some basic reporting modules for reporting on SQL Data. They used all asp SqlDataSources and SelectCommands for the project. Are you saying that the API has classes and methods that can handle all of this?

Thank you for always giving good help and advice here too.

Elgin

You are right. GetUsersByRoleName is what I should have said. GetUsers is the right thing to use.

I don't think that there are generic methods to deal with "other" tables. If you were building a module using the DotNetNuke module design patterns, you would build data providers and methodes to interact with your data, and you could call them. See the book by Mitchel Sellers for details.

I do seem to recall that there are some Linq to SQL methods in the framework. Those should work with any SQL sources, given that you are using appropriate connection strings and data sources. You'll have to dig around in the framework to find these. I often find that a few minutes of Googling can point me in the right direction.

For general SQL reporting, and even some fancy things you should not forget the Form & List module, the Reports module and the XML module. All of those have some combination of data providers, visualizers, XLT possibilities, etc. There are some pretty good blog entries from Joe Brinkman and Stefan Cullman about some of this. Also, the new Razor host module. And, a guy named Armand Datema ("Nokiko") has been doing some really cool stuff with some of these modules. You'll have to Google to find him (also at 2dnn.com). He's presenting some talks at the upcoming European DotNetNuke meeting, and has promised some blogs after that.