FluentAPI : ArrayProperty Mapping not honoring the table, key names

[Christophla]

I am using the Array Property mapping and it is not honoring the table name that i set.

I specifically set the table name to "Security_UserToUsersGroup" below in mapping configuration, but receive the error:

Telerik.OpenAccess.Exceptions.DataStoreException : Delete all link table rows failed: Telerik.OpenAccess.RT.sql.SQLException: Invalid object name 'Security_UsersGroup_users'.

SQL:

CREATE TABLE dbo.Security_UserToUsersGroup (

    "UsersGroupId"  uniqueidentifier NOT NULL,

    "SequenceId"    int NOT NULL,

    "Username"      nvarchar(255) NOT NULL,

    CONSTRAINT "PK_UserToUsersGroup" PRIMARY KEY("UsersGroupId","SequenceId")

)


C#


/// <summary>

/// A named group for users, which we can define operations on.

/// </summary>

public class UsersGroup : INameable

{

    ///<summary>

    /// Create a new instance of <c>UsersGroup</c>

    ///</summary>

    public UsersGroup()

    {

        //Users = new string[0];

        //AllParents = new HashSet<UsersGroup>().ToList();

        //AllChildren = new HashSet<UsersGroup>().ToList();

        //DirectChildren = new HashSet<UsersGroup>().ToList();

    }

 

    #region [ Fields ]

 

    protected Guid? parentId;

 

    #endregion

 

    #region [ Properties ]

    /// <summary>

    /// Gets or sets the id of this entity

    /// </summary>

    /// <value>The id.</value>

    public virtual Guid Id { get; set; }

 

    /// <summary>

    /// Gets or sets the name of this entity.

    /// </summary>

    /// <value>The name.</value>

    /// <remarks>

    /// The name can be set only on creation, and is not changed

    /// afterward.

    /// </remarks>

    public virtual string Name { get; set; }

 

    /// <summary>

    /// Gets or sets the users.

    /// </summary>

    /// <value>The users.</value>

    public virtual string[] Users { get; set; }

 

    /// <summary>

    /// Gets or sets the parent of this group

    /// </summary>

    /// <value>The parent.</value>

    public virtual UsersGroup Parent { get; set; }

 

    /// <summary>

    /// Gets or sets the direct children of this group (nested one level)

    /// </summary>

    /// <value>The directChildren.</value>

    public virtual IList<UsersGroup> DirectChildren { get; set; }

 

    /// <summary>

    /// Gets or sets all children of this users group, at all nesting levels

    /// </summary>

    /// <value>All children.</value>

    public virtual IList<UsersGroup> AllChildren { get; set; }

 

    /// <summary>

    /// Gets or sets all parent of this users group, at all nesting levels

    /// </summary>

    /// <value>All children.</value>

    public virtual IList<UsersGroup> AllParents { get; set; }

    #endregion

 

    #region [ DataMapping Configuration ]

    public static MappingConfiguration<UsersGroup> CreateConfiguration()

    {

        var mapping = new MappingConfiguration<UsersGroup>();

        mapping.MapType(c => new

        {

            Id = c.Id,

            Name = c.Name,

            ParentId = c.parentId

        }).ToTable(MappingTables.UsersGroups);

 

        mapping.HasProperty(c => c.Id).IsIdentity(KeyGenerator.Guid);

 

        mapping.HasProperty(c => c.Name).IsNotNullable().HasLength(255);

 

        mapping.HasProperty(c => c.Users)

               .WithSequenceColumn("SequenceId")

               .WithArrayValue("Username")

               .WithForeignKey("UsersGroupId")

               .WithTable("Security_UserToUsersGroups");

 

        mapping.HasAssociation(p => p.Parent)

               .WithOpposite(c => c.DirectChildren)

               .HasConstraint((p, c) => p.parentId == c.Id)

               .WithLoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)

               .IsManaged();

 

        mapping.HasAssociation(c => c.AllChildren)

               .WithOpposite(e => e.AllParents)

               .WithLoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)

               .IsManaged()

               .MapJoinTable(MappingTables.UsersGroupsHierarchy, (child, parent) => new

               {

                   ParentGroupId = parent.Name,

                   ChildGroupId = child.Name

               });

 

        return mapping;

    }

    #endregion   

}


[Christophla]

I was able to get it to work using the default table and column names that i discerned from the various error messages:

CREATE TABLE dbo.Security_UsersGroup_Users (
    "Id"    uniqueidentifier NOT NULL,
    "Seq"   int NOT NULL,
    "Val"       nvarchar(255) NOT NULL,
    CONSTRAINT "PK_UserToUsersGroup" PRIMARY KEY("Id","Seq")
)
GO

It appears that your extension methods aren't actually registering properly with the enhancer....

.WithSequenceColumn("SequenceId")
                  .WithArrayValue("Username")
                  .WithForeignKey("UsersGroupId")
                  .WithTable("UserToUsersGroups");

[Christophla]

Of course, now I can't execute a query against the array property:

public UsersGroup[] GetAssociatedUsersGroupFor(ISecuredUser user)
       {
           return _context.GetAll<UsersGroup>().Where(t => t.Users.Contains(user.SecurityInfo.Name)).ToArray<UsersGroup>();
       }

throws the exception:

Error Message:
System.InvalidOperationException : An exception occured during the execution of '
Extent<HAF.EBS.Security.Framework.Model.UsersGroup>().Where(t => t.Users.Contains(value(HAF.EBS.Security.Framework.Services.AuthorizationRepository+<>c__DisplayClass2).user.SecurityInfo.Name))'.
Failure: Unable to interpret expression as server side enumerable.
Parameter name: expression
Actual value was t.Users.

[Christophla]

I created a major workaround (hopefully temporarily) using the low-level ADO API:

public UsersGroup[] GetAssociatedUsersGroupFor(ISecuredUser user)
{
    IList<UsersGroup> usersGroups = new List<UsersGroup>();

    OAConnection oaConnection = _context.Connection;
    string sqlQueryString = string.Format("select Id from {0} where Val = @Username", MappingTables.UsersToUsersGroups);

    using (OACommand oaCommand = oaConnection.CreateCommand())
    {
        OAParameter[] oaParams = new OAParameter[] {
               new OAParameter(){
                   ParameterName = @"Username",
                   DbType = System.Data.DbType.String,
                   Value = user.SecurityInfo.Name}};

        oaCommand.CommandText = sqlQueryString;
        oaCommand.Parameters.AddRange(oaParams);
        DbDataReader reader = oaCommand.ExecuteReader();

        while (reader.Read())
        {
            var id = reader.GetGuid(0);
            var found = _context.GetAll<UsersGroup>().Where(t => t.Id == id).SingleOrDefault();
            usersGroups.Add(found);
        }
    }

    return usersGroups.ToArray<UsersGroup>();
}

This seems to work... but it would be wonderful if i could include an array property mapping in a linq statement and have it translate to the equivalent server-side sql....

[Serge]

Hello Christopher,

 First of all let me apologize for the late reply. We took our time to investigate and fix the issues you have stumbled upon. Both are verified bugs and are already fixed (should be available in our next service pack). 

However I would like to give you some ideas. You can use OpenAccess to generate your database schema even during runtime. More on that in this blog post. 

Please find your Telerik points updated for spotting these issues. And please do not hesitate to let us know should you face further trouble. 
 
All the best,
Serge
the Telerik team