list compare and replace on matching condition linq C#

Question

public class UserValues
{
    public string UserId { get; set; }
    public int FieldId { get; set; }
    public string FieldValue { get; set; } 
}

public class LookupMeta
{
    public int FieldId { get; set; }
    public int Id { get; set; }
    public int FieldValueId { get; set; }
    public string Title { get; set; }
}

I have kept this in 2 different lists after reading it from DB.

Now I want to compare both the list with

• FieldId == FieldId
• and FieldValue equals Id

then replace FieldValue from uservalues to FieldValueId from lookupMeta

UserValues
    .Where(x => LookupMeta.Any(y => 
        y.FieldId == x.FieldId && 
        y.FieldValueId.Equals(x.FieldValue)))
    .Select(x => x.FieldValue.Replace(x.FieldValue, ???))
        

I am looking at this link as well. I am struck C# LINQ code for two list compare and replace

Is it good to have in List and doing like this or is there any other optimized way?

Answer 1

If I follow you correctly, then the .Join() method in LINQ may be of use to you. Here I use it to accomplish what I think you're after.

UserValues
.Join(
    LookupMeta, 
    uv => new { uv.FieldId, uv.FieldValue },
    lm => new { lm.FieldId, lm.FieldValueId },
    (uv,lm) => {
        uv.FieldValue = lm.FieldValueId;
        return uv;
    }
);

The second and third lines in the method build anonymous objects from the source tables. The values of these are matched to make a link.

The last line takes the joined entries as inputs and then gives your output. In your case, I just return the UserValues entry. But before I do I change its "FieldValue" property to the "FieldValueId" property of the LookupMeta entry.

You have some inconsistencies. For instance, you talk about matching FieldValue to Id in the paragraph, but in the code you match FieldValue to FieldValueId. Also, you use == in one comparison and .Equals() in the other. No wrong answer here. I just don't know your underlying objects. So you may have to modify my code a bit to get what you want. But it shows the general strategy that I hope will work for you.

Answer 2

Based on the comment that has been left on pwilcox's answer it seems like the OP is look for a solution where the unmatched rows are also included. That means instead of using inner join we are looking for a left outer join.

In the world of Linq this could be achieved via a combination of GroupJoin, SelectMany and Select operators.

In order to be able to join on two different columns we have to introduce an intermediate class to be able to tell the types of the GroupJoin. So, I have created the following class:

internal class IntermediateKey
{
    public int Id { get; set; }
    public string Value { get; set; }
}

We also have to define a comparer for this class to be able to find matching data:

internal class IntermediateKeyComparer : IEqualityComparer<IntermediateKey>
{
    public bool Equals(IntermediateKey x, IntermediateKey y)
    {
        return x.Id == y.Id && x.Value == y.Value;
    }

    public int GetHashCode(IntermediateKey obj)
    {
        return obj.Id.GetHashCode() + obj.Value.GetHashCode();
    }
}

Please bear in mind that this implementation is quite simplified. The correct way to implement it is shown in this thread.

Now can define our query as this:

var comparer = new IntermediateKeyComparer();
var result = userValues
    .GroupJoin(
        lookupMetas,
        uv => new IntermediateKey { Id = uv.FieldId, Value = uv.FieldValue },
        lm => new IntermediateKey  { Id =  lm.FieldId, Value = lm.Id.ToString() },
        (uv, lm) => new { Value = uv, Lookups = lm},
        comparer)
    .SelectMany(
        pair => pair.Lookups.DefaultIfEmpty(),
        (paired, meta) => new { Value = paired.Value, Lookup = meta})
    .Select(res =>
    {
        res.Value.FieldValue = res.Lookup?.FieldValueId.ToString() ?? res.Value.FieldValue;
        return res.Value;
    });

• We defined that userValues should be left outer joined on lookupMetas
  • if uv's FieldId is matches to lm's FieldId
  • and if uv's FieldValue is matches to lm's Id's string representation
• With the SelectMany we choose either the matching LookupMeta entity or null
• With the Select we update the UserValue's FieldValue property only if there is a related LookupMeta otherwise we use its original value.

Now let's see how this works with some sample data:

static void Main(string[] args)
{
    var userValues = new List<UserValue>
    {
        new UserValue { FieldId = 1, FieldValue = "2"},
        new UserValue { FieldId = 2, FieldValue = "3"},
        new UserValue { FieldId = 4, FieldValue = "5"}
    };

    var lookupMetas = new List<LookupMeta>
    {
        new LookupMeta { FieldId = 1, Id = 2, FieldValueId = 20 },
        new LookupMeta { FieldId = 2, Id = 3, FieldValueId = 30 },
        new LookupMeta { FieldId = 3, Id = 4, FieldValueId = 40 },
    };

    var comparer = new IntermediateKeyComparer();
    var result = userValues
        .GroupJoin(
            lookupMetas,
            uv => new IntermediateKey { Id = uv.FieldId, Value = uv.FieldValue },
            lm => new IntermediateKey  { Id =  lm.FieldId, Value = lm.Id.ToString() },
            (uv, lm) => new { Value = uv, Lookups = lm},
            comparer)
        .SelectMany(
            pair => pair.Lookups.DefaultIfEmpty(),
            (x, meta) => new { Value = x.Value, Lookup = meta})
        .Select(res =>
        {
            res.Value.FieldValue = res.Lookup?.FieldValueId.ToString() ?? res.Value.FieldValue;
            return res.Value;
        });

    foreach (var maybeUpdatedUserValue in result)
    {
        Console.WriteLine($"{maybeUpdatedUserValue.FieldId}: {maybeUpdatedUserValue.FieldValue}");
    }
}

The output will be:

1: 20
2: 30
4: 5

So, as you can see there is no matching LookupMeta for the last UserValue that's why its FieldValue remained intact.