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 1: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 3:42 AM

wrote Feb 14, 2013 at 7:44 PM