LINQ - Merge two queries and exclude items from the first query

Question

I'm pretty sure this falls under a "UNION" scenario but I'm really just looking for the best approach to solve my problem (even if it's not a UNION).

I have a query that looks like this:

var query = context.AffiliateConfigurations.Where(x => x.AffiliateId == affiliateId).Select(config => new ViewModels.ConfigurationItem
                {
                    ConfigurationId = config.AffiliateConfigurationId,
                    ConfigKey = config.ConfigKey,
                    ConfigValue = config.ConfigValue,
                    UpdatedDate = config.UpdatedDate,
                    ConfigurationType = ViewModels.ConfigurationType.Affiliate
                });

What I want to do is add some more results to that query. I have another table called SiteConfiguration that has the EXACT same schema but I want to add only rows from that table where the ConfigKey does not already exist in my original query.

I have something like the following currently (and it works), but I'm looking for a "pure" LINQ way to do it:

var items = context.AffiliateConfigurations.Where(x => x.AffiliateId == affiliateId).Select(config => new ViewModels.ConfigurationItem
    {
        ConfigurationId = config.AffiliateConfigurationId,
        ConfigKey = config.ConfigKey,
        ConfigValue = config.ConfigValue,
        UpdatedDate = config.UpdatedDate,
        ConfigurationType = ViewModels.ConfigurationType.Affiliate
        }).ToList();

var query = context.SiteConfigurations.Select(config => new ViewModels.ConfigurationItem
{
    ConfigurationId = config.SiteConfigurationId,
    ConfigKey = config.ConfigKey,
    ConfigValue = config.ConfigValue,
    UpdatedDate = config.UpdatedDate
});

foreach (var item in query)
{
    if (items.All(x => x.ConfigKey != item.ConfigKey))
    {
        items.Add(item);
    }
}

Answer 1

So your Question is "I have two collections and i want to merge them.how do i exclude items from the second collection,if the item's property is matching with another item's property on the first list."

Yes LINQ's UNION is what you need in such scenarios

All you need to do is a Write a simple Comparer class(Implementing the IEqualityComparer interface)for your ConfigurationItem

class ConfigEqualityComparer : IEqualityComparer<ConfigurationItem>
{

    public bool Equals(ConfigurationItem a, ConfigurationItem b)
    {
        if (a.ConfigKey == b.ConfigKey)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public int GetHashCode(ConfigurationItem a)
    {
            //do some hashing here
            //int hCode = IntegerField1 ^ IntegerField2;
            return hCode.GetHashCode();
    }

}

That is all you need.You can now run the UNION query and get your expected output.

 var comparer = new ConfigEqualityComparer();
 var result = Enumerable.Union<ConfigurationItem>(items, query, comparer);

Answer 2

I think the LINQ Union operator is what you want. You just need to create an class that implements the IEqualityComparer<T> interface for your item. Here is some demo code. Try the foreach without the comparer to see the dupes included, or as is to have them removed. I was running the code in LINQPad, if you're using Visual Studio you'll need to move the Main method into a class and call it.

void Main()
{
    List<DataObject> set1 = new List<DataObject>();
    List<DataObject> set2 = new List<DataObject>();

    set1.Add(new DataObject("a"));
    set1.Add(new DataObject("b"));
    set1.Add(new DataObject("c"));
    set1.Add(new DataObject("d"));
    set1.Add(new DataObject("e"));

    set2.Add(new DataObject("c"));
    set2.Add(new DataObject("d"));
    set2.Add(new DataObject("e"));
    set2.Add(new DataObject("f"));
    set2.Add(new DataObject("g"));
    set2.Add(new DataObject("h"));

    // try as  
    // foreach (DataObject d in set1.Union(set2)) {
    // and dupes will be present

    foreach (DataObject d in set1.Union(set2, new DOComparer())) {
        Console.WriteLine(d);
    }
}

// Define other methods and classes here
public class DataObject {
    public DataObject(string value) {
        Value = value;
    }
    public string Value {get;private set;}

    public override string ToString() {
        return Value;
    }
}

public class DOComparer:IEqualityComparer<DataObject> {
    public bool Equals(DataObject do1, DataObject do2) {
        return do1.Value.Equals(do2.Value);
    }

    public int GetHashCode(DataObject d) {
        return d.Value.GetHashCode();
    }
}