Performing SQL "in" equivalent in EF

I have two rateable objects E and S, S 开发者_如何学Cis contained within E so you say E.S and they both use the same object R to be rated; R will always have ONLY ONE rated object at a time so if R.E.HasValue == true then R.S.HasValue will never be true.

I have all the R objects from the database and I have E. What would be the lambda expression to get all Rs Where R.S in E.S??

UPDATE

I found this MSDN Documentation where it says:

Referencing a non-scalar variables, such as an entity, in a query is not supported. When such a query executes, a NotSupportedException exception is thrown with a message that states "Unable to create a constant value of type EntityType. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

So... I can't use A-Dubb's answer since the query reads

R.Where(r => r.SID.HasValue).Where(r => E.S.Contains(r.S))

Which results in a NotSupportedException... I still have the property R.SID which is of type int? but how could I then query all E.S to get their IDs?

P.S. E.S. is of type EntityCollection.

I will say that IN clauses are normally represented with call to Contains. For example

public class R {
   public int Id {get; set;}
}

public IEnumerable<R> InClause(IEnumerable<int> ids) {
   var subset = ids.ToList();

   return dbContext.Query<R>.Where(r => subset.Contains(r.Id));
}

I know that's not the exact API call in EF, but it should help set the basis for your solution. Maybe you already know how to generate IN clauses but I figure if you did then you presumably wouldn't be stuck. If the IN clause isn't the difficult part of the query then what is? Maybe the piece of business logic you mentioned? Again, a C# object model would help.

The problem with using contains though, is that its closer to sql "like" than "in". So if you have user entry on say 8 digit ID's and someone enters 0 then contains will bring you back ALL id's with zero somewhere in it.