DotNetNuke User Profile mapping in OpenAccess

[Bill]

I have created a Silverlight app using OpenAccess connecting to my SQL Server DotNetNuke database and using RIA Services.

What I have Now:

I can view the entire DotNetNuke users table in the Silverlight page and sort, group, edit and  update.
Awesome… So far so good.

What I want:

Because DotNetNuke allows adding custom properties which are stored in 2 other tables (1 for Prop definitions and another for values- see attached diagram) I am not sure how I should handle this in ORM.
I can get to Firstname, Last, Email etc (base user fields) but the others like region, Country etc are custom properties defined elsewhere.

From what I have Googled so far I am thinking maybe what I need to do is remap my entities in my Data Model   to be able to access all the properties as if they were in a single table- by flattening the entities with Vertical mapping or something….

What I hope to achieve in the end is a way to access the custom props directly by name from the main Users table as if the custom properties were in that table and have the updates work ( as they do with the fields in the Users table now) without writing a ton of code.

I do have this SQL function I created in the past that allows me to access the custom properties by Property name but I am not sure if or how I should use it in the model.

 

ALTER FUNCTION [dbo].[fnGetUserProfilePropertyValue]
(
    -- Add the parameters for the function here
    @PortalId int,
    @UserId int,
    @PropertyName nvarchar(50)
)
RETURNS nvarchar(3750)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @PropertyValue nvarchar(3750)
    -- Add the T-SQL statements to compute the return value here
    SELECT  @PropertyValue=ISNULL(PropertyValue,'')
    FROM [dbo].[UserProfile] aiup
    INNER JOIN [dbo].[ProfilePropertyDefinition] aiups ON aiup.PropertyDefinitionID = aiups.PropertyDefinitionID
    WHERE aiups.PropertyName = @PropertyName
    AND aiup.UserID = @UserId
    AND aiups.PortalID = @PortalId

    -- Return the result of the function
    RETURN @PropertyValue

END

 

In the past I have accessed the custom props in a query using that function like this:

SELECT      Bla Bla Bla,
dbo.AI_fnGetUserProfilePropertyValue(@Xportalid, AI_UserPortals.UserId, N'Prefix')  AS Prefix,
dbo.AI_fnGetUserProfilePropertyValue(@Xportalid, AI_UserPortals.UserId, N'Street') AS Street,
Etc…

That works great in a query but I am stuck when it comes to moving this to ORM.

Any ideas?

In the past I have accessed the custom props in a query using that function like this:

 

SELECT      Bla Bla Bla,
dbo.AI_fnGetUserProfilePropertyValue(@Xportalid, AI_UserPortals.UserId, N'Prefix')  AS Prefix,                          dbo.AI_fnGetUserProfilePropertyValue(@Xportalid, AI_UserPortals.UserId, N'Street') AS Street,
Etc…

 

 

[Viktor Zhivkov]

Hello Bill,

There is no way to map EAV (Entity-Attribute-Value) table rows to entity properties using OpenAccess.

You can define a method that loads all required details for single user or define a FetchStrategy that loads all related records from the two additional tables when loading more then one row from User data.
I suggest that you define your User class to contain all required additional properties in a partial class extension.
The code should look like this:

01.//generated User entity class code file:
02.public partial class User
03.{
04.    public int UserId { get; set;}
05.    //and so on
06.}
07.
08.// new partial User entity class code file:
09.public partial class User
10.{
11.    public string Country { get; set; }
12.    // add more additional properties here to enable strong typing
13.}

Next in your data layer (or business layer if there is no data layer) you can define a method that loads all User properties using LINQ queries:

01.public User GetUser(int userId, int portalId)
02.{
03.    var dataModel = YourDataModelType();
04.    //load the user data from User table
05.    var user = (from u in dataModel.Users
06.                where u.UserId == userId
07.                select u).SingleOrDefault();
08.
09.    //check if user is found
10.
11.    var details = (from profile in dataModel.UserProfile
12.                            join definition in dataModel.ProfilePropertyDefinition on profile.PropertyDefinitionId equals definition.PropertyDefinitionId
13.                where profile.UserId == userId && definition.PortalId == portalId
14.                select new {Name = definition.PropertyName, Value = (profile.PropertyValue ?? string.Empty) }).ToDictionary(p => p.Name);
15.
16.    //set the values to the user instance
17.    user.Country = details["Country"];
18.    //user.Region = details["Region"]; //and so on...
19.
20.    return user;
21.}

Keep in mind that this solution provides READ-ONLY access to these properties!

If you need to load the additional properties for many users at once you can define a Fetch Strategy using code like this:

01.public IEnumerable<User> GetUsersWithDetails()
02.{
03.            var dataModel = new YourDataModel();
04.
05.            FetchStrategy strategy = new FetchStrategy();
06.            strategy.LoadWith<User>(c => c.UserProfile);
07.            strategy.LoadWith<UserProfile>(c => c.ProfilePropertyDefinition);
08.
09.            dataModel.FetchStrategy = strategy;
10.
11.            var users = (from u in dataModel.Users
12.                        where u.SomeProperty == "some value" //provide filter if necessary
13.                        select u).ToList();
14.
15.            foreach(var user in users)
16.            {
17.                        var dictionary = user.UserProfiles.ToDictionary(profile => profile.ProfilePropertyDefinition.PropertyName, profile => (profile.PropertyValue ?? string.Empty));
18.                        user.Country = dictionary["Country"];
19.            }
20.            return users;
21.}

Please accept this code with a grain of salt since I have written all of it without  you data model and there could be some small syntactic errors, but you should be able to understand the idea.

Let me know if you have any difficulties implementing the proposed solution.

Kind regards,
Viktor Zhivkov
the Telerik team