This is a migrated thread and some comments may be shown as answers.

FluentAPI : ArrayProperty Mapping not honoring the table, key names

4 Answers 105 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Christophla
Top achievements
Rank 1
Christophla asked on 07 Mar 2012, 06:37 PM
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   
}

4 Answers, 1 is accepted

Sort by
0
Christophla
Top achievements
Rank 1
answered on 07 Mar 2012, 06:49 PM
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");

0
Christophla
Top achievements
Rank 1
answered on 07 Mar 2012, 07:04 PM
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.
0
Christophla
Top achievements
Rank 1
answered on 07 Mar 2012, 07:34 PM
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....



0
Serge
Telerik team
answered on 12 Mar 2012, 05:10 PM
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
Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
Tags
General Discussions
Asked by
Christophla
Top achievements
Rank 1
Answers by
Christophla
Top achievements
Rank 1
Serge
Telerik team
Share this question
or