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

Question

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, so this should be the same as an empty query. However, I get the same exception even when it does return an error message.

Does your stored proc actually select anything?? It seems it only checks for the customer's existence twice, but in the case of either of the two actually being present, nothing really happens... — marc_s
– marc_s, Commented Apr 12, 2011 at 20:55

TKTS · Accepted Answer · 2011-04-12 22:18:10Z

2

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.

answered Apr 12, 2011 at 22:18

TKTS

1,2811 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bob Jones Over a year ago

It's so obvious when I see it now. I was trying to cast the entire row (e) instead of the single column [Error]. Thans @TKTS

driis · Accepted Answer · 2011-04-12 20:50:02Z

1

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();

answered Apr 12, 2011 at 20:50

driis

165k46 gold badges272 silver badges345 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related