SQL to LINQ (Not In Function) RSS

• • Reply
  

  Maham FRajpu...

  Member

  1 Points

  16 Posts

  SQL to LINQ (Not In Function)

  Dec 06, 2012 07:42 PM|LINK

  I am trying to convert this SQL query into LINQ, but its not returning the same results.

  SQL Query

  SELECT
  sp.CompanyName, sp.Location, p.MinGuest
  from ServiceProvider sp
  join Packages p on
  sp.SPID = p.SPID
  where
  (sp.Location = 'Defence'and p.MinGuest >= 300)
  and sp.ToSID = 1
  union all
  SELECT
  sp.CompanyName, sp.Location, p.MinGuest
  from ServiceProvider sp
  join Packages p on
  sp.SPID = p.SPID
  where
  p.MinGuest >= 300
  and sp.ToSID = 1
  and sp.SPID not in ( SELECT
  sp.SPID from ServiceProvider sp join Packages p on sp.SPID = p.SPID where (sp.Location = 'Defence'and p.MinGuest >= 300) and sp.ToSID = 1 )

  LINQ Query

   var query = (from SP in db.ServiceProviders
                           join P in db.Packages
                               on SP.SPID equals P.SPID
                           where SP.ToSID == 1 && (SP.Location == Location && SP.EstimatedCost < Budget && P.MinGuest >= 500)
                           select SP).Concat(
                          from SP in db.ServiceProviders
                          join P in db.Packages
                              on SP.SPID equals P.SPID
                          where SP.ToSID == 1 && (SP.EstimatedCost < Budget && P.MinGuest >= 500) && !(from SPs in db.ServiceProviders join Ps in db.Packages on SPs.SPID equals Ps.SPID where SPs.ToSID == 1 && (SPs.Location == Location && SPs.EstimatedCost < Budget && Ps.MinGuest >= 500) select SPs.SPID).Contains(SP.SPID)
                          select SP);

  
  
  This the NOT IN functionality is not working in LINQ. 

  • Moved by ignatandrei on Dec 07, 2012 03:26 AM
• • Reply
  

  thaicarrot

  Contributor

  5132 Points

  1465 Posts

  Re: SQL to LINQ (Not In Function)

  Dec 07, 2012 05:38 AM|LINK

  Maham FRajput

  Maham FRajput

   

  I saw Linq doesn't have the "union".

  Best Regards
  Weera
  
  
• • Reply
  

  Maham FRajpu...

  Member

  1 Points

  16 Posts

  Re: SQL to LINQ (Not In Function)

  Dec 08, 2012 04:33 AM|LINK

  .Concat() == Union in LinQ isn't so ? 

• • Reply
  

  thaicarrot

  Contributor

  5132 Points

  1465 Posts

  Re: SQL to LINQ (Not In Function)

  Dec 08, 2012 07:21 AM|LINK

  Maham FRajput

  Maham FRajput

  Union is extension of Linq, It should lives after the LinQ Query.

  Best Regards
  Weera