none
How to select distinct and count the number of occurrences on for each row RRS feed

  • Question

  • I tried to google and used Stack overflow but the answers there seems not to give me a result but gave me an ORA error. Hope I find better luck here. I have been at this for 2 days now since I have no experience with complex linq code.

    I did my googling and after trying out the answered about distinct select and count on SO, it didn't work for my case.

    http://stackoverflow.com/questions/4884462/linq-distinct-count

    So basically I merged multiple tables based on the ID, and I have various other columns as well somethings like this:

        ID     TIME     LOC     DEVICE#     MagVal
        1      9122     AB      1          .88
        1      9122     AB      1          .88
        1      9122     AB      2          .88
        2      9133     CD      1          .875
        2      9133     CD      2          .875
        2      9133     CD      3          .876


    So I want it to give me 2 lines which is

        ID     TIME     LOC     DEVICE#     MagVal
        1      9122     AB      2          .88
        2      9133     CD      3          .875 (This MagVal could be varied)

    Monday, September 15, 2014 8:02 PM

Answers

  • Hello RozenKristal,

    >> So I want it to give me 2 lines which is

    Is the DEVICE# also varied? If it is, you could assume the result is combined with three parts: one is ID,Time and LOC, the second part is two columns ID,DEVICE# and the last part is ID, MagVal, so you need to join the table itself twice and provide the collections for both DEVICE# and MagVal, here is an example you could have a check:

    List<int> ids = new List<int>() { 1, 3 };
    
                    List<string> columns = new List<string>() { "2", "4" };
    
                    var result = (from o1 in db.Orders
    
                                  group new { OrderName = o1.OrderName, OrderID = o1.OrderID } by o1.OrderName into oo1
    
                                  join o2 in db.Orders on oo1.Key equals o2.OrderName //fetch DEVICE
    
                                  join o3 in db.Orders on oo1.Key equals o3.OrderName //fetch MagVal
    
                                  where ids.Any(i => i == o2.OrderID) & columns.Any(c => c.Contains(o3.NewColumn2))
    
                                  select new { oo1.Key, o2.OrderID, o3.NewColumn2 }).ToList();
    

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 16, 2014 9:09 AM
    Moderator