2
Vote

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

description

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)
 
DECLARE @CustomerID NVARCHAR(max)
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.

comments

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.