Getting error when using "not in" in command text

Question

Hi All,

I have an EntityDataSource in which command text contains not in.

The queries looks like :-

SELECT  User.LastName,  User.Suffix  
FROM    [User]
WHERE  [User].UserId not in (select [Contractors].UserId from Contractors)

This results in following error:-

The element type 'Edm.Guid' and the CollectionType 'Transient.collection[Transient.rowtype[(UserId ,Edm.Guid(Nullable=True,DefaultValue=))](Nullable=True,DefaultValue=)]' are not compatible. The IN expression only supports entity, primitive, and reference types.  

Any ideas how this can be fixed?

Answer 1

That is because you cannot use a IN clausule with Guid (unique identifier) types. Your UserId is of unique identifier type, so use another way to check that without an IN, like a temporary table, etc.

Try with a left join with the Tables, research more about SQL, here is an example:

SELECT  User.LastName,  User.Suffix  
FROM    [User]
Left Join [Contractors] on [User].UserId = [Contractors].UserId
WHERE [Contractors].UserId IS NULL

I'm not sure if that is gonna work, I cannot test it right now. I'm sure there is a better way to do what I am doing, but that was the error that you were having....