DotNetNuke User Profile mapping in OpenAccess

Thread is closed for posting
2 posts, 0 answers
  1. Bill
    Bill avatar
    42 posts
    Member since:
    Feb 2011

    Posted 20 Sep 2012 Link to this post

    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…

     

     

  2.
  3. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    324 posts

    Posted 25 Sep 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
Back to Top