none
Linq Query RRS feed

  • Question

  • Hi,

    I am trying to query a table (i.e table A) with two colunms as fallow (with example):

    SupplierOrderID  |   SupplierID

    11                              2
    12                              4
    12                              5
    23                              6
    23                              7
    23                              2
    24                              2
    in my code i have the SupplierID i.e 2
    i want the linq query to return 11 and 24 (only) beacuse 11 and 24 are the last one for 2 (2 also come with 23 but 23 has 6 and 7 so i dont need them for now)

    how should i compose such a linq query?

    Thanks
    Ziv

    Wednesday, October 21, 2009 2:55 PM

Answers

  • Hi Ziv,

    If I got you correctly, you can do this by selecting all the SupplierOrderIDs that have SupplierID 2 except those ones that have SupplierID not 2. LINQifying this, you can do:


    var query1 = table.Where(t=>t.SupplierOrderID==2).Select(t=>t.SupplierID); //get records with SupplierID = 2
    var query2 = table.Where(t=>t.SupplierOrderID!=2).Select(t=>t.SupplierID); //get records with SupplierID <> 2
    var result = query1.Except(query2);

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by ronziv Wednesday, October 21, 2009 7:01 PM
    Wednesday, October 21, 2009 5:38 PM

All replies

  • Hi Ziv,

    If I got you correctly, you can do this by selecting all the SupplierOrderIDs that have SupplierID 2 except those ones that have SupplierID not 2. LINQifying this, you can do:


    var query1 = table.Where(t=>t.SupplierOrderID==2).Select(t=>t.SupplierID); //get records with SupplierID = 2
    var query2 = table.Where(t=>t.SupplierOrderID!=2).Select(t=>t.SupplierID); //get records with SupplierID <> 2
    var result = query1.Except(query2);

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by ronziv Wednesday, October 21, 2009 7:01 PM
    Wednesday, October 21, 2009 5:38 PM
  • Being "last" is difficult unless you define an order first but if you want only those SupplierOrderIDs for which 2 is the sole SupplierID then I think

    from a in db.As
    where a.SupplierID == 2 
    && !db.As.Any(a1 => a1 != a 
                          && a1.SupplierOrderID == a.SupplierOrderID)
    select a.SupplierOrderID

    MVP XML My blog
    Wednesday, October 21, 2009 5:54 PM
  • Thank you very much for you help :)

    the obove is only an example, which mean 2 was only for demonstration. The SupplierID can be any number > 0 (int)

    Is the suggested queries are still relevent?

    Thanks,
    Ziv
    Wednesday, October 21, 2009 6:53 PM