After getting distinct list, using the results in a query

Question

I originally got help with getting a distinct list from a larger list with multiple nodes to group. I think that worked.

I now need help on how to use that list.

Here is my code:

var LOE = results.Body
                 .getEntitiesResponse
                 .getEntities
                 .listOfEntities
                 .Select(x=>new string[]{x.entityIdentification.DUNS,x.entityIdentification.DUNSPlus4})
                 .Distinct();

foreach (var d in LOE)
{
    using (OleDbConnection conn = new OleDbConnection(cm.ConnectionString))
    {
        using (OleDbCommand cmd = new OleDbCommand())
        {
            cmd.CommandText = "sam.DeleteRecordsToBeUpdated";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@a", d.DUNS);  //This is my problem area
            cmd.Parameters.AddWithValue("@b", d.DUNSPlus4); //This is my problem area
            cmd.Connection = conn;

            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Can someone help me with how to use the new object created in the first line?

Maybe I am not setting the first line properly? I can't seem to use the object as I am trying to.

what error do you get? post the procedure definition as well what is `LOE` here? — Rahul, Mar 29 '19 at 18:09
I think the answer to https://stackoverflow.com/questions/55422458/how-to-group-by-2-items-of-a-list-into-another-list/55423256#55423256 is going to help me. I needed to create a class forthe results to land into properly. — KeithL, Mar 29 '19 at 18:11
Sure, or use `d[0]` and `d[1]` instead of `d.DUNS` and `d.DUNSPlus4`. — D Stanley, Mar 29 '19 at 18:13
Also, [don't use `AddWithValue`](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — DavidG, Mar 29 '19 at 18:21

score 3 · Accepted Answer · answered Mar 29 '19 at 18:10

Your problem is with the first statement

var LOE = results.Body.getEntitiesResponse.getEntities.listOfEntities
          .Select(x=>new string[]{x.entityIdentification.DUNS,x.entityIdentification.DUNSPlus4})
          .Distinct();

You should have it like below

var LOE = results.Body.getEntitiesResponse.getEntities.listOfEntities
            .Select(x => new {
                x.entityIdentification.DUNS,
                x.entityIdentification.DUNSPlus4
            }).Distinct();

In your case, you are selecting an array, instead of an anonymous class

haldo · Answer 2 · 2019-03-29T18:40:10.170

1

You could create a class to map the values into:

public class DunsMapping()
{
    public string Duns { get; set; }
    public string DunsPlus4 { get; set; }

    public DunsMapping(string duns, string duns4)
    {
        Duns = duns;
        DunsPlus4 = duns4;
    }
}

Then the linq would become:

var LOE = results.Body
             .getEntitiesResponse
             .getEntities
             .listOfEntities
             .Select(x=>new DunsMapping(x.entityIdentification.DUNS,
                                        x.entityIdentification.DUNSPlus4))
             .Distinct();

Or, if you needed to return a list of distinct entities you can use GroupBy:

var LOE = results.Body
             .getEntitiesResponse
             .getEntities
             .listOfEntities
             .GroupBy(g => new { g.DUNS, g.DUNSPlus4 })
             .Select(g => g.First());

which will return IEnumerable<YourEntity>.

edited Mar 29 '19 at 18:40

answered Mar 29 '19 at 18:20

haldo

14,512
5
46
52

Have you tested it? I don't think it will work because you not defined `Equals` and `GetHashCode` in `DunsMapping` class – Aleks Andreev Mar 29 '19 at 18:22
Oh, for the `Distinct()`? You might be right... I'll test now – haldo Mar 29 '19 at 18:24
@AleksAndreev I tested an it does work, but updated answer anyway – haldo Mar 29 '19 at 18:40

After getting distinct list, using the results in a query

2 Answers2

Linked