Nested class with Compound Key

May 4, 2011 at 3:15 PM

I am attempting to create a nested class that contains the primary key for a table (when the primary key is compound).  Retrievals I can get working by using the dot (.) convention via the Column mapping attribute.  However, when I try to Insert a new entity, the excuted SQL does not include these columns or values.

Below is an example using the IQToolkit test harness.  (And yes, it's contrived since Customer doesn't actually have compound primary key.)  Making these changes along with numerous updates to the existing tests (not shown) and attempting to insert a new row gives rise to the error "Cannot insert the value NULL into column 'CustomerID', table 'Northwind.dbo.Customers'; column does not allow nulls. INSERT fails. The statement has been terminated."  This is because the generated SQL does not include the key field ('CustomerID') from the nested class.

exec sp_executesql N'INSERT INTO [Customers]([City], [CompanyName], [ContactName], [Country], [Phone])
VALUES (@p0, @p1, @p2, @p3, NULL)',N'@p0 nvarchar(20),@p1 nvarchar(max) ,@p2 nvarchar(max) ,@p3 nvarchar(max) ',@p0=N'Seattle',@p1=N'Company1',@p2=N'Contact1',@p3=N'USA'

Additionally, you may notice a number of other errors in the tests related to associations.  I'm not sure how to resolve those either, but I suspect it's a limitation of my knowledge of the toolkit at this point.  (I tried various combinations of Key.CustomerID for the attributes to no avail.)  If someone has suggestions for this as well, it would be greatly appreciated.

 

public class CustomerKey
{
	public CustomerKey() {}
	public CustomerKey(string customerID) { this.CustomerID = customerID; }
	public string CustomerID;
}

public class Customer
{
	public Customer() { this.Key = new CustomerKey(); }

	public CustomerKey Key;
	//public string CustomerID;

	public string ContactName;
	public string CompanyName;
	public string Phone;
	public string City;
	public string Country;
	public IList<Order> Orders;
}

...

[Table]
[Column(Member = "Key.CustomerID", Name = "CustomerId", IsPrimaryKey = true)]
[Column(Member = "ContactName")]
[Column(Member = "CompanyName")]
[Column(Member = "Phone")]
[Column(Member = "City", DbType="NVARCHAR(20)")]
[Column(Member = "Country")]
[Association(Member = "Orders", KeyMembers = "CustomerID", RelatedEntityID = "Orders", RelatedKeyMembers = "CustomerID")]
public override IEntityTable<Customer> Customers

Below is an example of one of the modified tests to support the nested class.
public void TestInsertCustomerNoResult()
{
	var cust = new Customer
	{
		Key = new CustomerKey { CustomerID = "XX1" },
		CompanyName = "Company1",
		ContactName = "Contact1",
		City = "Seattle",
		Country = "USA"
	};
	var result = db.Customers.Insert(cust);
	this.AssertValue(1, result);  // returns 1 for success
}

Dec 18, 2011 at 6:16 AM

I managed to fix this one.

 

In BasicMapping.cs

protected virtual IEnumerable<ColumnAssignment> GetColumnAssignments(Expression table, Expression instance, MappingEntity entity, Func<MappingEntity, MemberInfo, bool> fnIncludeColumn)
        {
            foreach (var m in this.mapping.GetMappedMembers(entity))
            {
                if (this.mapping.IsColumn(entity, m) && fnIncludeColumn(entity, m))
                {
                    yield return new ColumnAssignment(
                        (ColumnExpression)this.GetMemberExpression(table, entity, m),
                        Expression.MakeMemberAccess(instance, m)
                        );
                }
            }
        }

Change this function from private to 'protected virtual' as shown above.

In AdvancedMapping.cs

protected override IEnumerable<ColumnAssignment> GetColumnAssignments(Expression table, Expression instance, MappingEntity entity, Func<MappingEntity, MemberInfo, bool> fnIncludeColumn)
        {
            return GetColumnAssignments(table, instance, entity, fnIncludeColumn, null);
        }

declare this new function which forwards the method call to the advanced one. I didn't test your exact code snippet, but did test the exact same pattern and managed a successful insert. I guess update and delete would still require further evaluation.