0

I want to write an entity query for this SQL command:

SELECT * FROM provider WHERE serial 
         NOT IN ( SELECT providerRef as Serial FROM ProviderTeam WHERE TeamRef=134)

I searched the internet but no answer.

ucMedia
  • 4,105
  • 4
  • 38
  • 46
mohsen
  • 4,698
  • 1
  • 33
  • 54
  • Check http://stackoverflow.com/questions/13342817/entity-framework-attribute-in-clause-usage. Also you can rewrite the query using EXISTS and see http://stackoverflow.com/questions/2379183/entity-framework-and-exists-clause – Vojtěch Dohnal Oct 28 '14 at 07:22

2 Answers2

2

You can rewrite your IN clause as EXISTS and use the following linq command: 

var q = from p in dbContext.Providers
        where !dbContext.ProviderTeams.Any(pt => pt.TeamRef == 134 && pt.providerRef == p.serial)
        select p;
Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
-1

I hope this is what you need:

using (var dbObj = new [DB_NAME]Entities())
{
    var list = dbObj.provider.SqlQuery("SELECT * from provider where serila NOT IN ( select providerRef as Serial from ProviderTeam where TeamRef=134)").ToList();
}
Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
Alexandru Godri
  • 528
  • 3
  • 7