Adding multiple 1:1 associations

2 posts, 0 answers
  1. Bruce
    Bruce avatar
    1 posts
    Member since:
    Mar 2012

    Posted 01 Aug 2013 Link to this post

    Hello,

    I have a set of tables that we are using to hold audit information.  The primary table Audit may or may not have 1:1 associated records in the AuditConfig or AuditException tables depending on whether the audit event is associated with configuration changes and/or exceptions.  The classes for these tables are below:
    public class SybAudit {
        public int EventNo { get; set; }
        public string EventID { get; set; }
        public DateTime EventDate { get; set; }
        public EventActions EventAction { get; set; }
        public string ServiceID { get; set; }
        public string SessionID { get; set; }
        public string RequestType { get; set; }
        public string UserID { get; set; }
        public string DeviceName { get; set; }
        public string ApplicationName { get; set; }
        public string Message { get; set; }
      
        public SybAuditConfig Config { get; set; }
        public SybAuditException Exception { get; set; }
    }
     
    public class SybAuditConfig {
        public int EventNo { get; set; }
        public string ConfigID { get; set; }
        public int VersionNo { get; set; }
        public string Name { get; set; }
        public string ObjectType { get; set; }
     
        public SybAudit Audit { get; set; }
    }
     
    public class SybAuditException {
        public int EventNo { get; set; }
        public string Message { get; set; }
        public string Exception { get; set; }
     
        public SybAudit Audit { get; set; }
    }

    I use the following mappings for these tables:
    // Audit Mapping
    var auditMapping = new MappingConfiguration<SybAudit>();
    auditMapping.MapType(i => new {
        i.EventNo,
        i.EventID,
        i.EventDate,
        i.EventAction,
        i.SessionID,
        i.ServiceID,
        i.RequestType,
        i.UserID,
        i.DeviceName,
        i.ApplicationName,
        i.Message
    }).ToTable("sybAudit");
     
    // Properties
    auditMapping.HasProperty(i => i.EventNo).IsIdentity(KeyGenerator.Autoinc);
    auditMapping.HasProperty(i => i.EventID).HasLength(36);
    auditMapping.HasProperty(i => i.EventDate);
    auditMapping.HasProperty(i => i.EventAction);
    auditMapping.HasProperty(i => i.SessionID).HasLength(36).IsNullable();
    auditMapping.HasProperty(i => i.ServiceID).HasLength(50).IsNullable();
    auditMapping.HasProperty(i => i.RequestType).HasLength(50).IsNullable();
    auditMapping.HasProperty(i => i.UserID).HasLength(100).IsNullable();
    auditMapping.HasProperty(i => i.DeviceName).HasLength(100).IsNullable();
    auditMapping.HasProperty(i => i.ApplicationName).HasLength(50).IsNullable();
    auditMapping.HasProperty(i => i.Message).WithInfiniteLength().IsNullable();
     
    // Audit Config Mapping
    var configMapping = new MappingConfiguration<SybAuditConfig>();
    configMapping.MapType(i => new {
        i.EventNo,
        i.ConfigID,
        i.VersionNo,
        i.Name,
        i.ObjectType
    }).ToTable("sybAuditConfig");
     
    // Properties
    configMapping.HasProperty(i => i.EventNo).IsIdentity();
    configMapping.HasProperty(i => i.ConfigID).HasLength(36);
    configMapping.HasProperty(i => i.VersionNo);
    configMapping.HasProperty(i => i.Name).HasLength(100);
    configMapping.HasProperty(i => i.ObjectType).HasLength(100);
     
    // Audit Exception Mapping
    var exceptionMapping = new MappingConfiguration<SybAuditException>();
    exceptionMapping.MapType(i => new {
        i.EventNo,
        i.Message,
        i.Exception
    }).ToTable("sybAuditException");
     
    // Properties
    exceptionMapping.HasProperty(i => i.EventNo).IsIdentity();
    exceptionMapping.HasProperty(i => i.Message).HasLength(250);
    exceptionMapping.HasProperty(i => i.Exception).WithInfiniteLength();
     
    // Association 1
    //auditMapping.HasAssociation(a => a.Config)
    //            .WithOpposite(b => b.Audit)
    //            .HasConstraint((a, b) => a.EventNo == b.EventNo)
    //            .IsRequired();
    //configMapping.HasAssociation(a => a.Audit)
    //            .WithOpposite(b => b.Config)
    //            .HasConstraint((a, b) => a.EventNo == b.EventNo);
     
    // Association 2
    auditMapping.HasAssociation(a => a.Exception)
               .WithOpposite(e => e.Audit)
               .HasConstraint((a, e) => a.EventNo == e.EventNo)
               .IsRequired();
    exceptionMapping.HasAssociation(a => a.Audit)
               .WithOpposite(b => b.Exception)
               .HasConstraint((a, b) => a.EventNo == b.EventNo);
     
    mappings.Add(exceptionMapping);
    mappings.Add(configMapping);
    mappings.Add(auditMapping);

    I create a new Audit event object as follows:
    var auditEvent = new Audit() {
                        EventID = Guid.NewGuid().ToString(),
                        EventDate = DateTime.Now,
                        EventAction = EventActions.Insert,
                        ServiceID = "Exception",
                        SessionID = Guid.NewGuid().ToString(),
                        RequestType = "Exception",
                        UserID = "MyUser",
                        DeviceName = "MyDevice",
                        ApplicationName = "AuditTests",
                        Message = "Exception Event",
     
                        Exception = new AuditException() {
                            Message = ex.Message,
                            Exception = ex.ToString()
                        }
    };

    If I leave the code following the "Association 1" commented in the mapping above, and save this object to the database, a SQL trace shows the following scripts being generated:

    declare @p1 int
    set @p1=1
    exec sp_prepexec @p1 output,N'@p0 nvarchar(50),@p1 nvarchar(100),@p2 int,@p3 datetime,@p4 nvarchar(36),@p5 varchar(15),@p6 nvarchar(50),@p7 nvarchar(50),@p8 nvarchar(36),@p9 nvarchar(100)',N'declare @generated_ids table([EventNo] int)
    insert [sybAudit] ([ApplicationName], [DeviceName], [EventAction], [EventDate], [EventID], [Message], [RequestType], [ServiceID], [SessionID], [UserID])
    output inserted.[EventNo] into @generated_ids
     VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)
    select t.[EventNo]
    from @generated_ids as g join [sybAudit] as t on g.[EventNo] = t.[EventNo]
    where @@ROWCOUNT > 0',@p0=N'AuditTests',@p1=N'MyDevice',@p2=1,@p3='2013-08-01 14:01:53.310',@p4=N'5740a8a7-cecf-4009-9046-a7af2458b22f',@p5='Exception Event',@p6=N'Exception',@p7=N'Exception',@p8=N'82452695-82c6-4abb-ae8a-09be2abe6437',@p9=N'MyUser'
    select @p1
     
    declare @p1 int
    set @p1=2
    exec sp_prepexec @p1 output,N'@p0 int,@p1 varchar(229),@p2 nvarchar(250)',N'INSERT INTO [sybAuditException] ([EventNo], [Exception], [Message]) VALUES (@p0, @p1, @p2)',@p0=58,@p1='System.Exception: Hello @ 02:01:53 PM
       at UnitTests.AuditTests.DatabaseAuditTool_CanAdd_ExceptionAuditEvent() in c:\Source.Net\Sybrin.10\Services\Sybrin10.Server\trunk\Sybrin10.Server\UnitTests\Data Tests\AuditTests.cs:line 46',@p2=N'Hello @ 02:01:53 PM'
    select @p1

    If I uncomment the code following the "Association 1" commented in the mapping above, and then save this object to the database, a SQL trace shows the following scripts being generated:
    declare @p1 int
    set @p1=1
    exec sp_prepexec @p1 output,N'@p0 int',N'SELECT [EventNo] AS COL1, [ConfigID] AS COL2, [Name] AS COL3, [ObjectType] AS COL4, [VersionNo] AS COL5 FROM [sybAuditConfig] WHERE [EventNo] = @p0                                       ',@p0=0
    select @p1
     
     
    declare @p1 int
    set @p1=2
    exec sp_prepexec @p1 output,N'@p0 int,@p1 varchar(229),@p2 nvarchar(250)',N'INSERT INTO [sybAuditException] ([EventNo], [Exception], [Message]) VALUES (@p0, @p1, @p2)',@p0=0,@p1='System.Exception: Hello @ 02:05:53 PM
       at UnitTests.AuditTests.DatabaseAuditTool_CanAdd_ExceptionAuditEvent() in c:\Source.Net\Sybrin.10\Services\Sybrin10.Server\trunk\Sybrin10.Server\UnitTests\Data Tests\AuditTests.cs:line 46',@p2=N'Hello @ 02:05:53 PM'
    select @p1
     
     
    declare @p1 int
    set @p1=3
    exec sp_prepexec @p1 output,N'@p0 nvarchar(50),@p1 nvarchar(100),@p2 int,@p3 datetime,@p4 nvarchar(36),@p5 varchar(15),@p6 nvarchar(50),@p7 nvarchar(50),@p8 nvarchar(36),@p9 nvarchar(100)',N'declare @generated_ids table([EventNo] int)
    insert [sybAudit] ([ApplicationName], [DeviceName], [EventAction], [EventDate], [EventID], [Message], [RequestType], [ServiceID], [SessionID], [UserID])
    output inserted.[EventNo] into @generated_ids
     VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)
    select t.[EventNo]
    from @generated_ids as g join [sybAudit] as t on g.[EventNo] = t.[EventNo]
    where @@ROWCOUNT > 0',@p0=N'AuditTests',@p1=N'MyDevice',@p2=1,@p3='2013-08-01 14:05:53.617',@p4=N'59a7c454-ed14-4b7c-9b24-124537baffc8',@p5='Exception Event',@p6=N'Exception',@p7=N'Exception',@p8=N'388a97bb-1881-445a-aa59-8410775501b8',@p9=N'MyUser'
    select @p1

    As you can see, the insert into the SybAudit table is happening AFTER the insert into the SybAuditException table.  Thus the EventNo value for the SybAuditException record is 0, and not the EventNo of the AutoInc number generated for the SybAudit record.

    What do I need to do to fix this?
  2. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 02 Aug 2013 Link to this post

    Hi Bruce,

    Thank you for your detailed description and providing your setup.
    I was able to set up your scenario correctly by defining the constraints on the  AuditConfig and AuditException classes. Both associations worked in my environment. See the following mapping of the associations:

    // Association 1
    auditMapping.HasAssociation(a => a.Config)
                .WithOpposite(b => b.Audit);                       
    configMapping.HasAssociation(a => a.Audit)
                .WithOpposite(b => b.Config)
                .HasConstraint((a, b) => a.EventNo == b.EventNo)
                .IsRequired();
     
    // Association 2
    auditMapping.HasAssociation(a => a.Exception)
               .WithOpposite(e => e.Audit)
               ;
    exceptionMapping.HasAssociation(a => a.Audit)
               .WithOpposite(b => b.Exception)
               .HasConstraint((a, b) => a.EventNo == b.EventNo)
               .IsRequired();

    I hope this fixes your problem.
    Do not hesitate to ask in case you have any other question.

    Regards,
    Ralph
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
  3. DevCraft banner
Back to Top