Invalid Cast when calling Stored Procedure from Linq-to-SQL

Linq-to-SQL business layer calls SQL stored procedure and gets:

Unable to cast object of type 'WhereSelectEnumerableIterator`2
[ERICustomersDataLayer.MergeCustomersResult,ERICustomersDataLayer.MergeCustomersResult]' 
to type 'System.Collections.Generic.List`1[System.String]'.

Stored procedure:

ALTER PROCEDURE MergeCustomers @CurrentCustomerId UNIQUEIDENTIFIER, @MergedCustomerId UNIQUEIDENTIFIER
AS
BEGIN
    DECLARE @Error VARCHAR(500);
    IF NOT EXISTS (SELECT * FROM Customer WHERE Id = @CurrentCustomerId)
    BEGIN
        SET @Error = 'Current customer ID not in customer table '
        SELECT @Error [Error]
        RETURN -1
    END

    IF NOT EXISTS (SELECT * FROM Customer WHERE Id = @MergedCustomerId)
    BEGIN
        SET @Error = 'Merged customer ID not in customer table '
        SELECT @Error [Error]
        RETURN -1
    END
END

Linq-to-SQL Code:

public List<string> Merge(Guid CurrentUserId, Guid MergedUserId)
{
    var dc = new ERICustomersDataLayer.ERICustomersDataContext();
    var rows = (
            from e in dc.MergeCustomers(CurrentUserId, MergedUserId)
            select e);

    return (List<string>)rows;
}

In the test case, the SP is not returning anything, s开发者_如何学Goo this should be the same as an empty query. However, I get the same exception even when it does return an error message.

As driis said, the linq is not returning a list but an IEnumerable of MergeCustomersResult. So you can't just cast it into a List or simply do .ToList(). You need to make sure that your var "rows" is a list of string before you can return it as one. Try something like this:

public List<string> Merge(Guid CurrentUserId, Guid MergedUserId) {
   var dc = new ERICustomersDataLayer.ERICustomersDataContext();
   var rows = (from e in dc.MergeCustomers(CurrentUserId, MergedUserId)               
              select e.Error);      
   return rows.ToList(); 
}

I'm not sure how your MergeCustomerResult is setup but I'm guessing it has a property called Error that maps to the [Error] column you are returning in your sp.

Your error simply says it cannot cast the result to a List. This makes sense, because the LINQ query is not returning a List, but an IQueryable (or IEnumerable), with an implementation based on the actual expression and underlying provider.

You can fix this error easily. Try converting it to a list instead, using the ToList extension method:

return rows.ToList();