Adding multiple 1:1 associations

[Bruce]

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?

[Ralph Waldenmaier]

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