LINQ to Entities Issue

Question

using(SampleEntities entities = new SampleEntities()) {
var userMap = from ad in entities.SampleGroupsSet
              from uid in distinctUserIDs
              where ad.ShortUserID.ToLower() == uid.ToLower()
              select new {shortID = uid, longID = ad.UserID};

string userID = "sampleId";
var longIDs = from map in userMap
              where map.shortID.ToLower() == userID.ToLower()
              select map.longID;

if (longIDs != null && longIDs.Count() > 0)
{
    ...
}
...

I'm running into an issue where if I am querying for the Count of longIDs I'm getting an exception:

"Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

Has anyone encountered this? Thanks.

Answer 1

You have two issues. This:

uid.ToLower()

...can't be converted to SQL. This:

          where map.shortID.ToLower() == userID.ToLower()

Is the wrong way to do a case-insensitive restriction. It defeats using an index, and causes the issue you reference. Instead, do:

          where map.shortID.Equals(userID, StringComparison.OrdinalIgnoreCase) // or whatever...

Second issue: You seem to be trying to do a "where in". But this is the wrong way. In EF 4, you do:

where distinctUserIDs.Contains(ad.ShortUserID)

In EF 1 it's more involved.