LINQ to Entity: using Contains in the "select" portion throws unexpected error

Question

I've got a LINQ query going against an Entity Framework object. Here's a summary of the query:

//a list of my allies
List<int> allianceMembers = new List<int>() { 1,5,10 };

//query for fleets in my area, including any allies (and mark them as such)
var fleets = from af in FleetSource
             select new Fleet 
             {
                 fleetID = af.fleetID,
                 fleetName = af.fleetName,
                 isAllied = (allianceMembers.Contains(af.userID) ? true : false)
             };

Basically, what I'm doing is getting a set of fleets. The allianceMembers list contains INTs of all users who are allied with me. I want to set isAllied = true if the fleet's owner is part of that list, and false otherwise.

When I do this, I am seeing an exception: "LINQ to Entities does not recognize the method 'Boolean Contains(Int32)' method"

I can understand getting this error if I had used the contains in the where portion of the query, but why would I get it in the select? By this point I would assume the query would have executed and returned the results. This little ditty of code does nothing to constrain my data at all.

Any tips on how else I can accomplish what I need to with setting the isAllied flag?

Thanks

Answer 1

This poached from a previous answer...

Contains not supported.

IN and JOIN are not the same operator (Filtering by IN never changes the cardinality of the query).

Instead of doing it that way use the join method. It's somewhat difficult to understand without using the query operators, but once you get it, you've got it.

var foo = 
model.entitySet.Join(  //Start the join
values, //Join to the list of strings
e => e.Name, // on entity.Name
value => value, //equal to the string
(ModelItem ent, String str) => ent);//select the entity

Here it is using the query operators

var foo = from e in model.entitySet
join val in values on
e.Name equals val
select e;

Answer 2

Basically the entity framework attempts to translate your LINQ query into a SQL statement but doesn't know how to handle the Contains.

What you can do instead is retrieve your fleets from the database and set the isAllied property later:

var fleets = (from af in FleetSource
              select new Fleet 
              {
                  fleetID = af.fleetID,
                  fleetName = af.fleetName,
                  userId = af.userId
              }).AsEnumerable();

foreach (var fleet in fleets)
{
    fleet.isAllied = (allianceMembers.Contains(fleet.userID) ? true : false);
}

Answer 3

var fleets = from af in FleetSource;

var x = from u in fleets.ToList()
                         select new Fleet
                         {
                            fleetID = u.fleetID,
                            fleetName = u.fleetName,
                            isAllied = (allianceMembers.Contains(u.userID) ? true : false)
                         }

calling ToList() on fleets the query is executed, later you can use Contains().

Answer 4

Everyone above me is wrong!!! (No offense ...) It doesn't work because you are using the IList overload of "Contains" and not the IEnumerable overload of "Contains". Simply change to:

allianceMembers.Contains(af.userID)

By adding the , you are telling the compiler to use the IEnumerable overload instead of the IList overload.