equivalent LINQ expression RSS

• • Reply
  

  nissan

  Participant

  1065 Points

  618 Posts

  equivalent LINQ expression

  May 22, 2012 06:36 PM|LINK

  What would be the equivalent LINQ expression for this sql query?

  select * from Emp
  where GroupId IN (select GroupId from Department where DeptId = 9)

  • Edited by nissan on May 22, 2012 06:45 PM
• • Reply
  

  Nasser Malik

  Star

  12546 Points

  1867 Posts

  Re: equivalent LINQ expression

  May 22, 2012 06:55 PM|LINK

  You need to construct array of GroupId first to query you record

  int[] productList = new int[] { 1, 2, 3, 4 };
  
  var myProducts = from p in db.Products
                   where productList.Contains(p.ProductID)
                  select p;
  
  see 
  http://wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql
  
  

  Please Mark as Answer if find helpful -- Nasser --
  Skype: maleknasser1
• • Reply
  

  Nasser Malik

  Star

  12546 Points

  1867 Posts

  Re: equivalent LINQ expression

  May 22, 2012 07:22 PM|LINK

  see this small demo

  public class Emp
      {
          public string Name { get; set; }
          public string Address { get; set; }
          public int GroupId { get; set; }
      }
      public class MyGroup
      {
          public string Name { get; set; }
          public int GroupId { get; set; }
          public int DeptId { get; set; }
      }
  
      protected void Page_Load(object sender, EventArgs e)
      {
          List<Emp> emps = new List<Emp>()
          {
              new Emp() { Name="Nasser", Address="123 street", GroupId=1 },
              new Emp() { Name="Joseph", Address="123 street", GroupId=1 },
              new Emp() { Name="Nissan", Address="123 street", GroupId=2 },
              new Emp() { Name="Kelsey", Address="123 street", GroupId=3 },
              new Emp() { Name="Tara", Address="123 street", GroupId=1 },
              new Emp() { Name="Randall", Address="123 street", GroupId=1 },
              new Emp() { Name="Bryn", Address="123 street", GroupId=4 }
          };
  
  
          List<MyGroup> myGroups = new List<MyGroup>()
          {
              new MyGroup() { GroupId = 1, Name="Computer", DeptId=9 } ,
              new MyGroup() { GroupId = 2, Name="Computer", DeptId=9 } ,
              new MyGroup() { GroupId = 3, Name="Computer", DeptId=10 }
          };
  
          IEnumerable<int> myGroupsId = from myGroup in myGroups
                                        where myGroup.DeptId == 9
                                        select myGroup.GroupId;
  
  
  
          var result = from emp in emps
                       where myGroupsId.Contains(emp.GroupId)
                       orderby emp.GroupId
                       select emp;
                       
  
  
          foreach (Emp emp in result.ToList<Emp>())
          {
              Response.Write("Name = " + emp.Name + " Address = " + emp.Address + " GroupId = " + emp.GroupId + "<br />");
          }

  output as

  Name = Nasser Address = 123 street GroupId = 1
  Name = Joseph Address = 123 street GroupId = 1
  Name = Tara Address = 123 street GroupId = 1
  Name = Randall Address = 123 street GroupId = 1
  Name = Nissan Address = 123 street GroupId = 2
  
  

  Please Mark as Answer if find helpful -- Nasser --
  Skype: maleknasser1
  • Marked as answer by decker dong - msft on May 28, 2012 02:07 AM
• • Reply
  

  nissan

  Participant

  1065 Points

  618 Posts

  Re: equivalent LINQ expression

  May 22, 2012 07:45 PM|LINK

  Now the problem is, groupIds is a List of <int> but item.GroupId is nullable int.
  It gives me an error:cannot convert from 'int?' to 'int' 

  I tried List<int?> groupIds.. It complains saying I can't do that..

  
  List<int> groupIds = (from item in cmdbe.Orgs
                                    where item.DeptId == id
                                    select item.GroupId).ToList();

  var phoneList = from emp in cmdbe.Emps
                              where groupIds.Contains(emp.GroupId)
                              select ppl;

   

• • Reply
  

  kashifilyaz

  Participant

  1144 Points

  198 Posts

  Re: equivalent LINQ expression

  May 22, 2012 07:55 PM|LINK

  You can convert sql to linq using following tool

  http://www.sqltolinq.com/

• • Reply
  

  Nasser Malik

  Star

  12546 Points

  1867 Posts

  Re: equivalent LINQ expression

  May 22, 2012 08:21 PM|LINK

  Contver them to nullable int array.. check following

  public class Emp
          {
              public string Name { get; set; }
              public string Address { get; set; }
              public int GroupId { get; set; }
          }
  
          public class MyGroup
          {
              public string Name { get; set; }
              public int GroupId { get; set; }
              public int DeptId { get; set; }
          }
  
          protected void Page_Load(object sender, EventArgs e)
          {
              List<Emp> emps = new List<Emp>()
              {
              new Emp() { Name="Nasser", Address="123 street", GroupId=1 },
              new Emp() { Name="Joseph", Address="123 street", GroupId=1 },
              new Emp() { Name="Nissan", Address="123 street", GroupId=2 },
              new Emp() { Name="Kelsey", Address="123 street", GroupId=3 },
              new Emp() { Name="Tara", Address="123 street", GroupId=1 },
              new Emp() { Name="Randall", Address="123 street", GroupId=1 },
              new Emp() { Name="Bryn", Address="123 street", GroupId=4 }
              };
  
  
              List<MyGroup> myGroups = new List<MyGroup>()
              {
              new MyGroup() { GroupId = 1, Name="Computer", DeptId=9 } ,
              new MyGroup() { GroupId = 2, Name="Computer", DeptId=9 } ,
              new MyGroup() { GroupId = 3, Name="Computer", DeptId=10 }
              };
  
              IEnumerable<int> myGroupsId = from myGroup in myGroups
                                            where myGroup.DeptId == 9
                                            select myGroup.GroupId;
  
              int?[] nullGroupIds = new int?[myGroupsId.Count()];
              int counter = 0;
              foreach (int i in myGroupsId)
              {
                  nullGroupIds[counter] = i;
                  counter++;
              }
              var result = from emp in emps
                           where nullGroupIds.Contains(emp.GroupId)
                           orderby emp.GroupId
                           select emp;
  
  
  
              foreach (Emp emp in result.ToList<Emp>())
              {
                  Response.Write("Name = " + emp.Name + " Address = " + emp.Address + " GroupId = " + emp.GroupId + "<br />");
              }
          }

  
  
  

  Please Mark as Answer if find helpful -- Nasser --
  Skype: maleknasser1
  • Edited by Nasser Malik on May 24, 2012 07:20 AM
  • Marked as answer by decker dong - msft on May 28, 2012 02:07 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: equivalent LINQ expression

  May 24, 2012 02:59 AM|LINK

  nissan

  What would be the equivalent LINQ expression for this sql query?

  select * from Emp
  where GroupId IN (select GroupId from Department where DeptId = 9)

  Hello：）

  Besides all the nice solutions above，you can also use "Any" to deal with the problem——

  var result = from e in XXX.Emps
               let g = from id in XXX.Department where id.DeptId==9
               where g.Any(f=>f==e.GroupId)
               select e;

• • Reply
  

  sandy060583

  Star

  8728 Points

  1626 Posts

  Re: equivalent LINQ expression

  May 24, 2012 03:54 PM|LINK

  Hi Nissan,

  try this :

  List<int> groupIds =
  (from item in cmdbe.Orgs
   where item.DeptId == id && item.GroupId.HasValue
   select item.GroupId).ToList();

  this will select GroupId which is not null.. & you also do not require null value i guess.

  Hope this help !!!

  Please mark the answer if it helps you.
  Ramani Sandeep (My Blog)
  (MCTS, MCC-2011)
  • Marked as answer by decker dong - msft on May 28, 2012 02:07 AM