Linq To SQL Method with .Contains fails

Question

I have the below Linq To SQL Method. When I step through the code spcCodeIDs contains the seven entries I am expecting. However I get a run-time exception of

Method 'Boolean Contains(System.String)' has no supported translation to SQL.

What am I missing?

public static DataTable GetSPCCodeList()
{         

    using (var context = ProviderDataContext.Create())
    {
        IQueryable<tblProviderAdminSPCCode> tSPCCode = context.GetTable<tblProviderAdminSPCCode>();
        IList<string> spcCodeIDs = BLLCmo.FCApprovedSPCsForGreenSheet();
        return (tSPCCode
                .Where(spcCode => spcCode.Inactive == null && spcCodeIDs.Contains(spcCode.SPCCodeID)) 
                .OrderBy(spcCode => spcCode.SPCCodeID)
                .Select(spcCode => new { spcCode.SPCCodeID, spcCode.SPCDescription, spcCode.SPCCategoryID }))
                .CopyLinqToDataTable();
    }
}

score 6 · Accepted Answer · answered Aug 15 '11 at 15:30

6

LINQ to SQL can only support Contains translations form a concrete list and not the IList interface.. try changing your line from

IList<string> spcCodeIDs = BLLCmo.FCApprovedSPCsForGreenSheet();

to

List<string> spcCodeIDs = BLLCmo.FCApprovedSPCsForGreenSheet().ToList();

answered Aug 15 '11 at 15:30

Quintin Robinson

81,193
14
123
132

@Refracted Paladin You Bet! It doesn't really make much sense at first but you get used to the limitation. – Quintin Robinson Aug 15 '11 at 15:45

score 0 · Answer 2 · answered Aug 15 '11 at 15:33

0

You need to pass a string as a parameter to Contains. So trying passing spcCode.SPCCodeID.ToString()

answered Aug 15 '11 at 15:33

Linq To SQL Method with .Contains fails

2 Answers2