This project is read-only.

Insert SQL wrong when selecting from core table where the entity has an extension table


To reproduce I created an entity based off of Customer in Northwind called CustomerWithComments, I then created an extension table CustomerComments with primary key CustomerID and a single field Comment.
I setup the entity and entity mapping:
[Table(Name = "Customers", Alias = "C")]
    [ExtensionTable(Name = "CustomerComments", Alias = "CC", KeyColumns = "CustomerID", RelatedAlias = "C", RelatedKeyColumns = "CustomerID")]
    [Column(Member = "CustomerId", IsPrimaryKey = true, Alias = "C")]
    [Column(Member = "ContactName", Alias = "C")]
    [Column(Member = "CompanyName", Alias = "C")]
    [Column(Member = "Phone", Alias = "C")]
    [Column(Member = "City", Alias = "C", DbType = "NVARCHAR(20)")]
    [Column(Member = "Country", Alias = "C")]
    [Column(Member = "Comment", Alias = "CC", DbType = "NVARCHAR(MAX)")]
    [Association(Member = "Orders", KeyMembers = "CustomerID", RelatedEntityID = "Orders", RelatedKeyMembers = "CustomerID")]
    public override IEntityTable<CustomerWithComments> CustomersWithComments
        get { return base.CustomersWithComments; }
Based off the unit tests:
var cust = new CustomerWithComments
            CustomerID = "XX1",
            CompanyName = "Company1",
            ContactName = "Contact1",
            City = "Seattle",
            Country = "USA",
            Comment = "New Comment"
        var result = db.CustomersWithComments.Insert(cust, c => c.City);
Fails. The wrong table alias is used in BasicMapping GetInsertResult which results an invalid select statement.
INSERT INTO [Customers]([City], [CompanyName], [ContactName], [Country], [CustomerID], [Phone])
VALUES (@p0, @p1, @p2, @p3, @p4, NULL)
SELECT @CustomerID = t0.[CustomerID]
FROM [Customers] AS t0
WHERE (t0.[CustomerID] = @p4)
INSERT INTO [CustomerComments]([Comment], [CustomerID])
VALUES (@p5, @CustomerID)
SELECT t1.[City]
FROM [CustomerComments] AS t1
WHERE (t1.[CustomerID] = @p4)
The last statement should be a select on Customer not CustomerComments.


firestrand wrote Apr 28, 2010 at 2:46 PM

BasicMapping.GetInsertResults(...) does not properly handle the case where the result is part of an extension table. The fix should be an override in AdvanceMapping which would handle extension table results.

wrote Sep 3, 2010 at 4:42 AM

wrote Feb 14, 2013 at 8:44 PM